How to split multiline text to rows using app script?

March 19, 2024

I need to split the multiline data in to rows using app script, so it should look like this,

Code	Name	Date	Task	Unit	progress
code1	user1	date1	task1	unit1	done
code2	user2	date2	task2	unit2	ongoing
code3	user3	date3	task3	unit3	need revision

I know simple script like how to split text to columns using delimiter, for example like this:

function myFunction() {
  
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sh= ss.getSheetByName('Sheet1');
  var lr= sh.getLastRow();
  var range1= sh.getRange('A2:A' + lr);
  range1.splitTextToColumns('\n');

}

But it’s just split the the text to columns, not to the row below, and obviously it won’t not work for the array of data. How can I split multiline text to rows using app script?

Thank you in advance.

>Solution :

In your situation, how about the following sample script?

Sample script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet1');

  // --- I modified the below script.
  var values = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  var newValues = values.flatMap(r => {
    var temp = r.map(c => c.split("\n").map(d => d.trim()));
    var maxLen = Math.max(...temp.map(e => e.length));
    temp = temp.map(e => e.length < maxLen ? [...e, Array(maxLen - e.length).fill(null)] : e);
    return temp[0].map((_, col) => temp.map((row) => row[col] || null));
  });
  sh.getRange(2, 1, newValues.length, newValues[0].length).clearContent().setValues(newValues);
}