How do you create a “reverse pivot” in Google Shee

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

Here is a demo file that uses a method using built-in custom functions and array formulas:

1. Create a new sheet and rename it as "Aux"
2. In the Aux sheet add the following formulas: (this assumes that the source data is in a sheet named data)
   A1: =COUNTA(data!A:A) Calculate the number of rows.
   A2: =COUNTA(data!1:1) Calculate the number of columns.
   A3: =CELL("address",data!A1) Intermediate step.
   A4: =LEFT(A3,FIND("!",A3)-1) Calculates the name of the sheet with the source data.
3. Create a new sheet
4. Add the following the new sheet
   A1: Row headers
   A2:

=ArrayFormula(
  VLOOKUP(
    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A1)+1+1,
    {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)},
    2
  )
)

B1: Column headers
B2:

=ArrayFormula(
  VLOOKUP(
    SIGN(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))),
    {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)},
    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A2)+1+2
  )
)

C1: Values
C2:

=ArrayFormula(
  VLOOKUP(
    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A1)+1+1,
    {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)},
    MOD(ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2))-1,Aux!A2)+1+2
  )
)

Description of the main constructs

• ROW(INDIRECT("A1:A"&Aux!A1*Aux!A2) returns an array of consecutive numbers that has the same height as the required final result.
• {(ROW(INDIRECT("A1:A"&Aux!A1+1))),INDIRECT(Aux!A4&"!R1C1:R"&Aux!A1+1&"C"&Aux!A2+1,false)} returns a array with the first column including the row index, and the next columns are the source data.

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);

Answer 3

Please try this code:

https://github.com/Max-Makhrov/GoogleSheets/blob/master/UnpivotTable.js

It gives more power on what you are pivoting

Input sample:

  input:

    * file            =     file object, 
                            default: ActiveSpreadsheet
    * strHead1        =     "'Wide_Table_Sample2'!A2:B2";
    * strHead2Start   =     "'Wide_Table_Sample2'!C1:C2";
    * strLabels       =     'PlanFact,Metric,Volume'; 
                            default: 'Label1,Label2,Label3...'

Answer 4

If your data has a single unique key column, this spreadsheet may have what you need.

Your unpivot sheet will contain:

• The key column =OFFSET(data!$A$1,INT((ROW()-2)/5)+1,0)
• The column header column =OFFSET(data!$A$1,0,IF(MOD(ROW()-1,5)=0,5,MOD(ROW()-1,5)))
• The cell value column =INDEX(data!$A$1:$F$100,MATCH(A2,data!$A$1:$A$100,FALSE),MATCH(B2,data!$A$1:$F$1,FALSE))

where 5 is the number of columns to unpivot.

---

I did not make the spreadsheet. I happened across it in the same search that led me to this question.

Answer 5

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 && data[i][0];i++)
  {
    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;
}