0

I have data which users submit in columns. I am trying to convert them from columnar into stacked rows so a database can read them as single columns, rather than having to pull in X columns to capture the data.

You can see in the Columnar Example screenshot where I am presently, and in the Stacked Example where I want to be. I can do this in either Google Sheets using App Script - load the data as columnar then convert to stacked and move to BigQuery - OR - use Google Cloud Storage to load the columnar data then use Node to convert to stacked when moving to BigQuery.

Either way, this needs to get into BigQuery as the Stacked Example.

Any ideas on how to do this?

Columnar Example

Stacked Example

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
Bava
  • 73
  • 1
  • 10
  • Another option that would work - transform this entirely in Google Sheets, from one sheet to another, then I can take it from there. Just looking for the appropriate app-script to do so. – Bava Feb 20 '20 at 14:39
  • Does this answer your question? [How do you create a "reverse pivot" in Google Sheets?](https://stackoverflow.com/questions/24954722/how-do-you-create-a-reverse-pivot-in-google-sheets) – TheMaster Feb 20 '20 at 15:49
  • If you share a sample sheet with data similar to the data pictured, I can give you a single formula to put in A1 in another tab, that will work for very large datasets. – MattKing Feb 20 '20 at 21:08
  • @MattKing How do you suggest I share? I can't attach an Excel file here. – Bava Feb 21 '20 at 02:34
  • @TheMaster that is very close. I will take a look and see if I can tweak it to make it work. Thank you for sharing. – Bava Feb 21 '20 at 02:35

1 Answers1

1

The function below does the following:

  • Get all data from the sheet where your original data is located (called Source, please change accordingly) with getDataRange.
  • Remove the headers from the retrieved array and append them to your destination sheet (called Target) with shift, slice and appendRow.
  • Iterate through the rest of rows with a forEach and, for each row, iterate through each successive group of four columns with a for loop, appending (1) the first for columns of each row and (2) each successive group of four columns in a new row in your destination sheet. 
function myFunction() {
  var ss = SpreadsheetApp.getActive();
  var sourceSheet = ss.getSheetByName('Source'); // Change accordingly
  var targetSheet = ss.getSheetByName('Target'); // Change accordingly
  targetSheet.clearContents(); // Removes all old content from target sheet before appending new data (remove this if you don't want that)
  var sourceValues = sourceSheet.getDataRange().getValues();
  var numCols = 4; // Number of desired columns in destination spreadsheet
  var headers = sourceValues.shift().slice(0, numCols * 2);
  targetSheet.appendRow(headers); // Remove if the destination sheet already has headers
  sourceValues.forEach(function(row) { // Iterate through each row in source sheet
    for (var i = numCols; i < row.length; i += numCols) { // Iterate through each group of four columns (excluding first four) in each row
      var part1 = row.slice(0, numCols); // First four columns
      var part2 = row.slice(i, i + numCols); // Each successive group of four
      var targetRow = part1.concat(part2); // Concatenate the four first columns with each group of four
      targetSheet.appendRow(targetRow); // Append row (8 columns)
    }
  });  
}
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • Reviewing this and will let you know if this solves my issue. Thank you for taking the time to provide this. – Bava Feb 21 '20 at 03:06
  • Still working through this. I will post what I end up with and mark as complete once done. – Bava Mar 04 '20 at 19:25