Home > Software design >  Split value of cell and replicate the whole row
Split value of cell and replicate the whole row

Time:11-22

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:

Name Country Sport
John USA Basketball_Golf_Tennis
Mary Canada Tennis_Golf

the desired output should be:

Name Country Sport
John USA Basketball
John USA Golf
John USA Tennis
Mary Canada Tennis
Mary Canada Golf

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 enter image description here

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", ))

enter image description here

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
A B C
1 Name Country Sport
2 John USA Basketball
3 John USA Golf
4 John USA Tennis
5 Mary Canada Tennis
6 Mary Canada Golf
  • Related