0

I have a script that successfully extracts the body of an e-mail:

var msgBody = messages[m].getBody();

This message contains 650+ lines of information, and I would like to put each line of text in a different row in the same column (the way it would work if I were to paste it into cell H1 for instance. When I run this command:

listserveSheet.getRange("H1").setValues(msgBody)

It naturally puts ALL 650+ lines of information in that one cell instead of cells H1 through H652 (or whatever the last one would be for any given e-mail).

Any idea how to alter my variable to make it able to set the values the way I desire? I was thinking maybe loop through the variable and set each value separately. But I don't know how to pull a single line out of the variable each time through the loop.

DHolcomb
  • 71
  • 7
  • Split the msgBody using the Newline Character \n. So it turns out be an array, with each line as its elements. Then set this array to rows using `getRange(row, column, numRows, numColumns).setValues(msgBodyArray)`. See for [splitting text](https://stackoverflow.com/a/21895299/1553408) and for [getting 2d range](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getRange(Integer,Integer,Integer,Integer)) and [writing array values in sheet](https://developers.google.com/apps-script/reference/spreadsheet/range#setValues(Object)) – Suhail Ansari Jun 26 '18 at 06:58
  • Thanks much for the pointers Suhail. See my "answer" below, plus one additional question, if you have a moment. – DHolcomb Jun 26 '18 at 18:24

1 Answers1

0

Thanks to Suhail's pointers in the comment above, I was able to find my way to an answer.

var msgBody = messages[m].getBody();
var dest = msgBody.split("\n");

Did the splitting just fine. Then I was able to set the individual values into individual cells with this. 

for (var i =0; i < dest.length; i++) {
  listserveSheet.getRange("H"+ (i+1)).setValue(dest[i]);
}

I had tried to use a setValues operation, but clearly, I didn't grok the 2d range business. I suspect that if I did understand 2d ranges, using setValues on a larger range, as opposed to looping setValue on cells would have been faster. Here's the bit I tried (as well as several other permutations that didn't work:

listserveSheet.getRange(1,8,dest.length,dest[0].length).setValues(dest);

Thanks again for the help

DHolcomb
  • 71
  • 7