How to split a single rows in Google Sheets into multiple rows depending on one cell values that are separated by commas using Google App Script?

CodePudding user response：

Split Rows into multiple rows

function splitRowintorows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const osh = ss.getSheetByName("Sheet1");
  osh.clearContents();
  const vs = sh.getDataRange().getValues()
  console.log(JSON.stringify(vs));
  let a = vs.flat().map(e => [e]);
  osh.getRange(1, 1, a.length, a[0].length).setValues(a);
  console.log(JSON.stringify(a))
}

Execution log
5:53:16 PM  Notice  Execution started
5:53:17 PM  Info    [["COL1","COL2","COL3","COL4","COL5"],[15,2,0,6,15],[9,1,18,12,5],[19,19,4,1,11],[1,0,5,9,15],[6,7,6,16,15],[11,16,13,19,12],[19,0,19,9,12],[5,0,19,19,1],[10,7,19,14,8]]
5:53:17 PM  Info    [["COL1"],["COL2"],["COL3"],["COL4"],["COL5"],[15],[2],[0],[6],[15],[9],[1],[18],[12],[5],[19],[19],[4],[1],[11],[1],[0],[5],[9],[15],[6],[7],[6],[16],[15],[11],[16],[13],[19],[12],[19],[0],[19],[9],[12],[5],[0],[19],[19],[1],[10],[7],[19],[14],[8]]
5:53:17 PM  Notice  Execution completed

CodePudding user response：

I believe your goal is as follows.

• From How to split a single rows in Google Sheets into multiple rows depending on one cell values that are separated by commas using Google App Script?, for example, you want to convert a value of sample1,sample2,sample3 in a cell to 3 columns using Google Apps Script.

In this case, how about the following sample script?

In these samples, it supposes that the values are put in the column "A". So, when you test these scripts, please set the values like sample1,sample2,sample3 to the column "A".

Sample script 1:

In this sample script, splitTextToColumns() is used.

function sample1() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getRange("A1:A"   sheet.getLastRow()).splitTextToColumns();
}

Sample script 2:

In this sample script, getValues and setValues are used.

function sample2() {
  const sheetName = "Sheet1"; // Please set the sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A1:A"   sheet.getLastRow());
  const values = range.getValues().map(([a]) => a.split(",").map(e => e.trim()));
  range.clearContent();
  range.offset(0, 0, values.length, values[0].length).setValues(values);
}

References:

• splitTextToColumns()
• getValues()
• setValues(values)