How do you create a "reverse pivot" in Google Sheets?

Question

I am trying to produce a "reverse pivot" function. I have searched long and hard for such a function, but cannot find one that is already out there.

I have a summary table with anywhere up to 20 columns and hundreds of rows, however I would like to convert it into a flat list so I can import to a database (or even use the flat data to create more pivot tables from!)

So, I have data in this format:

	Customer 1	Customer 2	Customer 3
Product 1	1	2	3
Product 2	4	5	6
Product 3	7	8	9

And need to convert it to this format:

 Customer  |  Product  | Qty -----------+-----------+---- Customer 1 | Product 1 |   1 Customer 1 | Product 2 |   4 Customer 1 | Product 3 |   7 Customer 2 | Product 1 |   2 Customer 2 | Product 2 |   5 Customer 2 | Product 3 |   8 Customer 3 | Product 1 |   3 Customer 3 | Product 2 |   6 Customer 3 | Product 3 |   9 

I have created a function that will read the range from sheet1 and append the re-formatted rows at the bottom of the same sheet, however I am trying to get it working so I can have the function on sheet2 that will read the whole range from sheet1.

No matter what I try, I can't seem to get it to work, and was wondering if anybody could give me any pointers?

Here is what I have so far:

function readRows() {   var sheet = SpreadsheetApp.getActiveSheet();   var rows = sheet.getDataRange();   var numRows = rows.getNumRows();   var values = rows.getValues();    heads = values[0]      for (var i = 1; i <= numRows - 1; i++) {     for (var j = 1; j <= values[0].length - 1; j++) {        var row = [values[i][0], values[0][j], values[i][j]];        sheet.appendRow(row)     }   } }; 

Answer 1

I wrote a simple general custom function, which is 100% reusable you can unpivot / reverse pivot a table of any size.

In your case you could use it like this: =unpivot(A1:D4,1,1,"customer","sales")

So you can use it just like any built-in array function in spreadsheet.

Please see here 2 examples: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765

The following is the source:

/**  * Unpivot a pivot table of any size.  *  * @param {A1:D30} data The pivot table.  * @param {1} fixColumns Number of columns, after which pivoted values begin. Default 1.  * @param {1} fixRows Number of rows (1 or 2), after which pivoted values begin. Default 1.  * @param {"city"} titlePivot The title of horizontal pivot values. Default "column".  * @param {"distance"[,...]} titleValue The title of pivot table values. Default "value".  * @return The unpivoted table  * @customfunction  */ function unpivot(data,fixColumns,fixRows,titlePivot,titleValue) {     var fixColumns = fixColumns || 1; // how many columns are fixed   var fixRows = fixRows || 1; // how many rows are fixed   var titlePivot = titlePivot || 'column';   var titleValue = titleValue || 'value';   var ret=[],i,j,row,uniqueCols=1;    // we handle only 2 dimension arrays   if (!Array.isArray(data) || data.length < fixRows || !Array.isArray(data[0]) || data[0].length < fixColumns)     throw new Error('no data');   // we handle max 2 fixed rows   if (fixRows > 2)     throw new Error('max 2 fixed rows are allowed');    // fill empty cells in the first row with value set last in previous columns (for 2 fixed rows)   var tmp = '';   for (j=0;j<data[0].length;j++)     if (data[0][j] != '')        tmp = data[0][j];     else       data[0][j] = tmp;    // for 2 fixed rows calculate unique column number   if (fixRows == 2)   {     uniqueCols = 0;     tmp = {};     for (j=fixColumns;j<data[1].length;j++)       if (typeof tmp[ data[1][j] ] == 'undefined')       {         tmp[ data[1][j] ] = 1;         uniqueCols++;       }   }    // return first row: fix column titles + pivoted values column title + values column title(s)   row = [];     for (j=0;j<fixColumns;j++) row.push(fixRows == 2 ? data[0][j]||data[1][j] : data[0][j]); // for 2 fixed rows we try to find the title in row 1 and row 2     for (j=3;j<arguments.length;j++) row.push(arguments[j]);   ret.push(row);    // processing rows (skipping the fixed columns, then dedicating a new row for each pivoted value)   for (i=fixRows; i<data.length && data[i].length > 0; i++)   {     // skip totally empty or only whitespace containing rows     if (data[i].join('').replace(/\s+/g,'').length == 0 ) continue;      // unpivot the row     row = [];     for (j=0;j<fixColumns && j<data[i].length;j++)       row.push(data[i][j]);     for (j=fixColumns;j<data[i].length;j+=uniqueCols)       ret.push(          row.concat([data[0][j]]) // the first row title value         .concat(data[i].slice(j,j+uniqueCols)) // pivoted values       );   }    return ret; } 

Answer 2

That is basically array manipulation... below is a code that does what you want and writes back the result below existing data.

You can of course adapt it to write on a new sheet if you prefer.

function transformData(){   var sheet = SpreadsheetApp.getActiveSheet();   var data = sheet.getDataRange().getValues();//read whole sheet   var output = [];   var headers = data.shift();// get headers   var empty = headers.shift();//remove empty cell on the left   var products = [];     for(var d in data){       var p = data[d].shift();//get product names in first column of each row       products.push(p);//store     }   Logger.log('headers = '+headers);   Logger.log('products = '+products);   Logger.log('data only ='+data);   for(var h in headers){     for(var p in products){  // iterate with 2 loops (headers and products)       var row = [];       row.push(headers[h]);       row.push(products[p]);       row.push(data[p][h])       output.push(row);//collect data in separate rows in output array     }   }   Logger.log('output array = '+output);   sheet.getRange(sheet.getLastRow()+1,1,output.length,output[0].length).setValues(output); } 

to automatically write the result in a new sheet replace last line of code with these :

  var ns = SpreadsheetApp.getActive().getSheets().length+1   SpreadsheetApp.getActiveSpreadsheet().insertSheet('New Sheet'+ns,ns).getRange(1,1,output.length,output[0].length).setValues(output);