I have a summary table with about 20 columns and up to a hundred of rows, however I would like to convert it into a flat list so I can import to a database.
This solution is not working properly in my case and my knowledge of JS is far beneath the ability to adjust it properly.
There're three tabs in the Example sheet:
- Source Data - dummy data of what I currently have
- Desired Result - what I want to convert Source Data to
- What I Get - result I get when using the solution mentioned above
Sheet is shared, so you can try and test the script (Menu > Script > Run). It would create a new tab automatically.
Here's what I came up with after some JS learning and googling. If anyone can suggest how to make it shorter/cleaner/simpler - I'm all ears. Might be far from perfect, but it does exactly what I need.
function transpose(){
var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SOURCE DATA');
var ss = SpreadsheetApp.getActiveSpreadsheet();
var numColumns = source.getLastColumn();
var numRows = source.getLastRow();
Logger.log('numColumns = ' +numColumns);
Logger.log('numRows = ' +numRows);
//GET NUMBER OF HEADERS (PRODUCTS)
var products = []; // get product models in the first row
for (var b = 2; b <= numColumns; b++){
if (source.getRange(1, b).getValue() != "") {
products.push([source.getRange(1, b).getValue()]); //store
}
}
// PRODUCTS and SITES INTO COLUMNS
var output = [];
var sites = []; // get sites list
for (var a = 3; a <= numRows; a++){
if (source.getRange(a, 1).getValue() != "") {
sites.push([source.getRange(a, 1).getValue()]); //store
}
}
for(var p in products){
for(var s in sites){
var row = [];
row.push(sites[s]);
row.push(products[p]);
output.push(row);//collect data in separate rows in output array
}
}
var date = Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "M/d/yyyy");
Logger.log('Date = ' +date)
ss.insertSheet(date,0).getRange(1,1,output.length,output[0].length).setValues(output);
var newSheet = ss.getSheetByName(date);
// COPY REGIONS
var numProducts = products.length; // number of models
Logger.log('numProducts = ' +numProducts);
var i = 1;
var j = 3 // first column number to copy
do {
var colC = newSheet.getRange("C1:C").getValues();
var copyToCell = colC.filter(String).length+1;
Logger.log('copyTo R = ' +copyToCell);
source.getRange(3,2,numRows-2,1).copyTo(newSheet.getRange(copyToCell,3), {contentsOnly:true});
i++;
source.getRange(3,j,numRows-2,2).copyTo(newSheet.getRange(copyToCell,4), {contentsOnly:true});
j+=2;
}
while (i <= numProducts);
while (j < numColumns);
// SORT BY SITE AND PRODUCT
newSheet.getDataRange().sort([1, 2]);
}
I wrote this easy general custom function for unpivoting / reverse-pivoting in Google Spreadsheet. Note: this is a custom function like any other built-in spreadsheet function, so you don't have to run it, you can use it like unique() or any other array function.
In your case it would as easy as using the following code: =unpivot(A1:H6,2,2,"product","MIN","MAX")
You can find the example here: https://docs.google.com/spreadsheets/d/12TBoX2UI_Yu2MA2ZN3p9f-cZsySE4et1slwpgjZbSzw/edit#gid=422214765
And this is the code:
/**
* 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;
}
I think you can use CONCAT to get a unique row key from location and region and use the method from my answer here which doesn't require extra scripts.
If you do something fancy with the key concatenation (e.g. CONCAT(A3, "~", A4)), you can even extract your original location and region in the unpivot table (e.g. REGEXEXTRACT(A1, "(\w+)~") and REGEXEXTRACT(A1, "~(\w+)")).
