I have a complex list of public holidays that I want to be able to parse and process in Google Sheets, but right now the values are delivered to me in three columns, with multiple values in the final column, separated by commas (locations where the public holidays are observed).

I can use the "Split data to columns" option to split the values, but that doesn't solve the issue that I need one list in three columns.

A link to the data is here: https://docs.google.com/spreadsheets/d/1xvGerIveHVKqeSCczioTyDk4ksNH8TJMpaxKkXzMIUI/edit?usp=sharing

Currently when this is updated I go through a manual process to clean it up and get it in the right format, and I've tried multiple ways to try and programmatically split all the values but haven't been able to figure it out. Any help would be greatly appreciated!

CodePudding user response：

To Three Columns

function toothree() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const osh = ss.getSheetByName('Sheet1');
  const vs = sh.getRange(2,1,sh.getLastRow() - 1,sh.getLastColumn()).getValues();
  let obj = {pA:[]};
  let vo = [];
  vs.forEach(r => {
    let loc = r[2].split(',').map(e => e.trim());
    loc.forEach(l => {
      vo.push([r[0],r[1],l]);
    })
  });
  vo.unshift(['Date','Description','Location']);
  osh.clear();
  osh.getRange(1,1,vo.length,vo[0].length).setValues(vo);
}

Portion of Sheet1:

CodePudding user response：

Try this in row 1:

={'Current Format'!A1:C1;'Current Format'!A2:B,Index(Split('Current Format'!C2:C,","),,1)}