Break multiple value cell into different rows via Apps Scripts

Question

I'm looking to split a cell that have multiple values separated by "\n" via App Script. Itried a few functions that were proposed but nothing seemed to work. Below you can see a sample format and desired outcome.

Thanks.

Do NOT share spreadsheets/images as the only source of data, to avoid closure of the question. Make sure to add input and expected output as plain text table to the question. Click here to create a table easily, which are easier to copy/paste as well. Also, note that your email address can also be accessed by the public, if you share Google files. — TheMaster
– TheMaster, Commented Nov 3, 2022 at 18:52
Is there any maximum amount of values that can appear on a single cell? — Fernando Lara
– Fernando Lara, Commented Nov 3, 2022 at 22:34

Tanaike · Accepted Answer · 2022-11-03 22:52:51Z

2

I believe your goal is as follows.

You want to split each cell value with \n using Google Apps Script.
You want to put the split values in the vertical direction.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set your sheet name.
  const range = sheet.getRange("A2:A" + sheet.getLastRow());
  const values = range.getValues().flatMap(([a]) => a ? a.split("\n").map(e => [e.trim()]) : []);
  range.offset(0, 1, values.length).setValues(values);
}

When this script is run, the source values are retrieved from the column "A". And, the converted values are put in column "B".

Reference:

flatMap()

answered Nov 3, 2022 at 22:52

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Break multiple value cell into different rows via Apps Scripts

1 Answer 1

Sample script:

Reference:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Reference:

Comments

Your Answer

Sign up or log in

Post as a guest

Related