Google Sheet OnChange trigger to copy incoming row but needs to Split into multiple rows (based on column)

user_18679666 0
Viewed 52

I have a Google Sheet with the 'Form Responses' sheet being populated by an external integration (JotForm).
I have an onChange trigger which responds to the incoming row, and copies the data to either one worksheet or another (Quote or Order based on a specific column value). This works fine, but I would like to split the row into multiple rows - using a 'new line' delimiter that will be present in one of the fields. The purpose of this is to enable granular updates to line items by a 3rd party.

Image of incoming data

Image or resulting data that I wish to have

google-apps-script google-sheets
1 Answers

It can be something like this:

function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data  = sheet.getDataRange().getValues();
  var table = split_rows(data);

  sheet
    .clearContents()
    .getRange(1,1,table.length,table[0].length)
    .setValues(table);
}


function split_rows(data) {
  var rows = [];

  while (data.length) {
    var row = data.shift();
    var items = row[4].split('\n');

    while(items.length) {
      var item = items.shift();
      rows.push([row[0], ...row.slice(2,4), item, ...row.slice(5)]);
    }
  }
  
  return rows;
}

Input:

enter image description here

Output:

enter image description here

Update

Here is the example of the main function that takes a last row from the active sheet, splits it (with the same as above function split_rows()) and appends the result rows at the end of the sheet 'Sheet2':

function main_append_rows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var last_row = data.pop();
  var new_rows = split_rows([last_row]);

  var dest_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  while(new_rows.length) dest_sheet.appendRow(new_rows.shift());
}
user_142654690
Related