I’m looking for assistance in adjusting my data setup in Google Sheets. Currently, I have a pivot table format that I need to change into a flattened list for smoother database uploads.
Current data format:
Client A
Client B
Client C
Product 1
1
2
3
Product 2
4
5
6
Product 3
7
8
9
Expected output structure:
Client | Product | Quantity
---------+-----------+--------
Client A | Product 1 | 1
Client A | Product 2 | 4
Client A | Product 3 | 7
Client B | Product 1 | 2
Client B | Product 2 | 5
Client B | Product 3 | 8
Client C | Product 1 | 3
Client C | Product 2 | 6
Client C | Product 3 | 9
I’ve written a function to work with the current sheet, but I’m finding it challenging to retrieve data from one sheet and display the adjusted information onto another sheet. Here’s what I have so far:
function processData() {
var activeSheet = SpreadsheetApp.getActiveSheet();
var dataRange = activeSheet.getDataRange();
var totalRows = dataRange.getNumRows();
var dataValues = dataRange.getValues();
headers = dataValues[0]
for (var i = 1; i <= totalRows - 1; i++) {
for (var j = 1; j <= dataValues[0].length - 1; j++) {
var entry = [dataValues[i][0], dataValues[0][j], dataValues[i][j]];
activeSheet.appendRow(entry)
}
}
};
What modifications can I make to ensure it reads data from one sheet and outputs the reformatted data into another?
Your script’s appending to the same sheet it’s reading from - that’s why it’s a mess. You need a separate target sheet for output. Here’s what I do: Get references to both sheets in your function. Replace activeSheet.appendRow(entry) with var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FlattenedData'); targetSheet.appendRow(entry); Create that target sheet first, or add error handling to make it automatically. Better yet - collect all flattened rows in an array, then use setValues() to write everything at once instead of appendRow() in the loop. Way faster for big datasets since you’re making fewer API calls. I learned this processing large pivot tables - the performance difference is huge.
Use getSheetByName() instead of getActiveSheet() for both sheets:
var sourceSheet = ss.getSheetByName('pivot');
var targetSheet = ss.getSheetByName('flattened');
Then update your appendRow to target the new sheet. Don’t forget to clear the target sheet first with targetSheet.clear() - otherwise you’ll get duplicates every time you run it.
You’re targeting the wrong sheet for output. Fix this by specifying both source and destination sheets explicitly. I ran into the same thing when converting crosstab data for imports. Replace your first line with var sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSourceSheetName'); and add var outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourOutputSheetName'); before the loops. Then swap activeSheet.appendRow(entry) to outputSheet.appendRow(entry). I’d also add headers to your output sheet first - something like outputSheet.getRange(1,1,1,3).setValues([['Client','Product','Quantity']]); before processing data. This way your source data stays untouched while you build the flattened structure on a separate sheet.