Google-sheets – Need to break columns of unique data into multiple rows

google sheetsgoogle-apps-script

I have data that looks like this in Sheet 2 of GS:

Company|State|ID|........|Order#1|Description 1|Qty 1|Order#2|Description #2|Qty #2||Order#3|Description #3|Qty 3|

but I would like for it to look like this in Google Sheets on Sheet 3:

Company|State|ID|........|Order#1|Description 1|Qty 1|
Company|State|ID|........|Order#2|Description 2|Qty 2|
Company|State|ID|........|Order#3|Description 3|Qty 3|

I have already taken a stab of this on script editor in Google Sheets, but I can't seem to make it work: 

// http://webapps.stackexchange.com/a/40060/29140
function SPLIT() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sh0 = ss.getSheets()[1], sh1 = ss.getSheets()[2];

  // get data from sheet 2
  var data = sh0.getDataRange().getValues();

  // create array to hold data
  var aMain = new Array();

  // iterate through data and add to array
  for(var i=1, dLen=data.length; i<dLen; i++) {
    for(var j=1; j<61

        ; j++) {
      aMain.push([data[i][1],data[i][j],data[i][61]]);
    }
  }

  // add array of data to third sheet
  sh1.getRange(2, 1, aMain.length, 28).setValues(aMain);
}

Did I do something wrong with the last line of code? Is that why I can't run it? Could someone help me understand what I did wrong?

Best Answer

Short answer

The loop requires several corrections:

  1. JavaScript arrays indexes start on 0 but rows and columns numbering on SpreadsheetApp methods start on 1,
  2. The way that push is being coded to fill the array is wrong.

Explanation

Besides the issues mentioned on the short answer section, there are some things that I prefer to handle in a different way, including make it more general in order to help more viewers:

  1. Get the sheets by name instead of position. I prefer this because I move sheets more frequently than change their names, by the other hand on large spreadsheets is easier to remember the name than the position. They should be edited to fit the readers needs.

  2. Avoid to hard-code array dimensions as much as possible. In this case itś assumed that the number of output columns and rows could change from time to time, so the number of columns is the only one that is hard-coded. It should be edited to fit the circumstances requirements.

Alternative script

function fixTable(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var origin = ss.getSheetByName('Sheet1');
  var input = origin.getDataRange().getValues();
  
  // cols is the number of columns of the output. Change it as required.
  var cols = 6; 
  var rows = input[0].length / cols;
  
  var destination = ss.getSheetByName('Sheet2');
  var output = [];

  // Loop to fill the output rows
  for(var i = 1;i<=rows;i++){ 
    var row = [];
    // Loop to fill the columns of each output row
    for(var j = 1; j<=cols;j++){ 
    row.push(input[0][i*j-1]);
    }
    output.push(row);
  }
  destination.getRange(2, 1, rows, cols).setValues(output);
}
Related Topic