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