Split multiline cell into multiple rows Google Sheets - Apps Script

Question

I'm trying to split the contents in a column of cells into single rows. Below is a screenshot of the data. There are two columns whose cell content I would like to split, but keeping the end result in two columns.

In the screen shot all the content in column A could be split into multiple rows in column C (example) and the same would happen for the content in Column B where the end result will be all the content added to column D or wherever.

The number of rows to be split changes across multiple spreadsheets so we will need to use last row.

Input

Expected output

Thank you!!!

user_17487348 · Answer

Script

You can use the following script which will read the values from an input sheet with the name Sheet1, split the values and output them in a new sheet Result sheet that will automatically be created. You might need to adjust the sheet names to fit your needs.

function lists() {
  Logger.log("Starting script...")

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inputSheetName = "Sheet1";
  const sheet = ss.getSheetByName(inputSheetName);
  if(sheet === null) {
    Logger.log(`Sheet ${inputSheetName} must be present!`);
    return;
  }
  // create new sheet for result and delete sheet if it already exists
  const resSheetName = "Result sheet"
  let resSheet = ss.getSheetByName(resSheetName);
  if(resSheet !== null) ss.deleteSheet(resSheet);
  resSheet = ss.insertSheet(resSheetName);

  // get input values 
  const firstColumn = sheet.getRange(2, 1, sheet.getLastRow(), 1).getValues();
  const secondColumn = sheet.getRange(2, 2, sheet.getLastRow(), 1).getValues();

  // split values into one large array
  const delimiter = " "
  const resultFirstColumn = colSplit(firstColumn, delimiter);
  const resultSecondColumn = colSplit(secondColumn, delimiter);
  Logger.log(`First column splitted: ${resultFirstColumn}`);
  Logger.log(`Second column splitted: ${resultSecondColumn}`);
  // write result to output
  resSheet.getRange(1, 1).setValue("Result 1");
  resSheet.getRange(2, 1, resultFirstColumn.length, 1).setValues(resultFirstColumn);
  resSheet.getRange(1, 2).setValue("Result 2");
  resSheet.getRange(2, 2, resultSecondColumn.length, 1).setValues(resultSecondColumn);

  Logger.log("Done.")
}

function colSplit(col, delimiter){
  // we need a 2D array like this [[row1], [row2], [row3], ... ]
  return col.flatMap((cell) => cell.toString().split(delimiter).map(item => [item]))
}

Input

Output

user_15019380 · Answer

You can try in another sheet

=QUERY(iferror(FLATTEN(ARRAYFORMULA(split(Sheet1!A:A,char(10))))),"where Col1 is not null")

Split multiline cell into multiple rows Google Sheets - Apps Script

Input

Expected output

2 Answers

Script

Input

Output