Google Appscript transpose dynamic data group from one column

I’ve been jogging my brain trying to figure out how to write this script to transpose data from one sheet to another from a pretty dirty sheet.

There are other questions like this but none seem to be like my particular use case.

This is how the sheet is currently structured (somewhat):

The biggest issue here is I have no concrete idea how many rows a particular group of data will be, But I know there are always a bunch of blank rows between each group of data.

I found a script that took me half way:

function myFunction() {
  //Get values of all nonEmpty cells
  var ss = SpreadsheetApp.getActiveSheet();
  var values = ss.getRange("D:D").getValues().filter(String);

  //Create object with 3 columns max
  var pasteValues = [];
  var row = ["","",""];
  for (i = 1; i<values.length+1; i++){
    row.splice((i%3)-1,1,values[i-1]);
    if(i%3 == 0){
      pasteValues.push(row);
      var row = ["","",""]
    }
  }
  if(row != []){
    pasteValues.push(row)
  }

  //Paste the object in columns A to C
  ss.getRange(1,1,pasteValues.length,pasteValues[0].length).setValues(pasteValues);

}

But in that case the asker dataset was fixed. I can loosely say that the max number of rows each group would have is 10(this is an assumption after browsing 3000 rows of the sheet…but if the script can know this automatically then it would be more dynamic). So with that in mind…and after butchering the script…I came up with this…which in no way works how it should currently(not all the data is being copied):

function myFunction() {
 
  var copyfrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('copyfrom')
  var copyto = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('copyto')
  
  var values = copyfrom.getRange("A:A").getValues().filter(Array);

  var pasteValues = [];

  var row = [];

  for (i = 1; i<values.length; i++){

       if(values[i] != ""){
       row.push(values[i])
       }
     
      Logger.log(row);

      if(i%10 == 0){
      pasteValues.push(row);
      row = []
    }

  }

  if(row != []){
    pasteValues.push(row)
  }
  copyto.getRange(1,1,pasteValues.length,pasteValues[0].length).setValues(pasteValues);

}

I’m pretty sure I should maybe still be using array.splice() but haven’t been successful trying to implement it achieve what i want, here’s how the transposed sheet should look:

Info:

Each group of addresses inside the “copyfrom” sheet would be separated by at least 1 blank line
The length of an address group is not static, some can have 5 rows, others can have 8, but address groups are always separated by blank rows

Any help is appreciated

Answer

You are right to iterate all input values, and I can suggest the similar code:

function myFunction() {
  var copyfrom = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('copyfrom')
  var copyto = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('copyto')
  
  var values = copyfrom.getRange("A:A").getValues();
  var pasteValues = [[]];  // keep result data here 

  values.forEach(function(v) {  // Iterate all input values
    // The last row to be filled in currently
    var row = pasteValues[pasteValues.length - 1];
    if (v[0]) {
      row.push(v[0]);
    } else if (row.length > 0) {
      while (row.length < 10) {
        row.push('');  // Adjust row length 
      }
      pasteValues.push([]);
    } 
  });
  if (pasteValues[pasteValues.length - 1].length == 0) pasteValues.pop();

  copyto.getRange(1, 1, pasteValues.length, pasteValues[0].length).setValues(pasteValues);
}

Advertisement

Answer