I was wondering which Google Apps Script function may help me to split a Google Sheets cell value into n parts (given a separator) and replicate the whole row as different occurrences for that split. So, f.i., given this table:

the desired output should be:

In this example, the separator is the char _

CodePudding user response：

You could probably do this with a regular spreadsheet formula (lookout for incoming solution from

CodePudding user response：

see:

=INDEX(QUERY(SPLIT(FLATTEN(IF(IFERROR(SPLIT(C1:C, "_"))="",, 
 A1:A&"​"&B1:B&"​"&SPLIT(C1:C, "_"))), "​"), "where Col2 is not null", ))

CodePudding user response：

Splitting Column 3

function brkaprt() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  osh.clearContents();
  const vs = sh.getRange(2,1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  let obj = {pA:[]};
  let o = vs.reduce((ac,[a,b,c],i) => {
    c.split("_").forEach(e =>ac.push([a,b,e]) )
    return ac;
  },[]);
  o.unshift(["Name","Country","Sport"]);  
  Logger.log(JSON.stringify(o));
  osh.getRange(1,1,o.length,o[0].length).setValues(o);

}

Execution log
10:56:15 AM Notice  Execution started
10:56:16 AM Info    [["Name","Country","Sport"],["John","USA","Basketball"],["John","USA","Golf"],["John","USA","Tennis"],["Mary","Canada","Tennis"],["Mary","Canada","Golf"]]
10:56:17 AM Notice  Execution completed