Adding new line to text in Google spreadsheet cell via apps script

9
votes

I have an apps script which takes email addresses from one spreadsheet from multiple cells and adds them them to another spreadsheet into 1 cell only. Currently the email addresses are added to that cell and separated by a ", ".

I would like to add the email addresses into that same cell but add a new line after each address.

I know it is possible to have new lines in a cell when manually adding text, by typing CTRL-Enter.

How can this be achieved in apps script?

I have so far tried to append "\n" or "\r" or "\r\n" to the string, to no avail, so I have reverted my code back to adding ", " instead.

sheet = SpreadsheetApp.getActiveSheet();
sheet.clear();

sheet.appendRow(["Num Emails", "Emails"]);

var LMEmails = "";
var count  = 0;
for (var i = 0; i < reviewers.LMEmails.length; i++) {
  if (count) {
    LMEmails += ", " + reviewers.LMEmails[i];
  } else {
    LMEmails += reviewers.LMEmails[i];
  }
  count++;
}

data = [count, LMEmails];

sheet.appendRow(data);

If anyone could help, I would very much appreciate it

Regards Crouz

3
google-apps-scriptgoogle-sheets
Here \n is working just fine with setValue(). What does LMEmails += "\n" + reviewers.LMEmails[i]; leads to? – Kriggs
It leads to [email protected]@[email protected], but as you can see, I do not use setValue(). In reality, I have many more headers and columns to populate, the code you see there is truncated – Crouzilles
Instead of appendRow use setValue. ss.getRange( ss.getLastRow(), 0, 1, 2).setValue(data); – Kriggs
I tried tat, but instead of updating the next empty row it updates the same row (top one), and strangely enough it updates all cells in the row with the same value, the first value in the data array. – Crouzilles
Made a mistake in the getRange, correctly would be: ss.getRange( ss.getLastRow() + 1, 1, 1, 2).setValue(data); – Kriggs

3 Answers

11
votes

After searching for the same question, this was my solution:

var stringToDisplay = 'FirstBit' + String.fromCharCode(10) + 'SecondBit';

then

workSheet.getRange(1,1).setValue(stringToDisplay);

output will be in a single cell, :
FirstBit
SecondBit

2
votes

Google Spreadsheets seems to ignore the new line if there are no characters after it.

Using "/n" works fine as long as you add at least one other character after it, this could be a space as shown below.

For example:

var myString = 'First Bit' + "\n " + "Second Bit";
0
votes

It should be:

data = [[count, LMEmails]];