CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Samuel Murphy
Samuel Murphy

Reputation: 11

Export email body into Google Sheets into more than one cell

I have the code below that I am using to export the body of specific gmail messages which have a specific label.

The problem is that it parses the body into just 1 cell in the google sheet.

However, if I manually copy and then 'paste values' paste the data into google sheet, the table format of the gmail body is maintained into the required number of cells.

How would I add the 'paste value' element into the code?

var sheet = SpreadsheetApp.getActiveSheet();
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

function getEmails() {
  var label = GmailApp.getUserLabelByName("Nanigans");
  var threads = label.getThreads();
  var row = 2;
  for (var i = 0; i < threads.length; i++) {
    var messages=threads[i].getMessages();
    for (var m=0; m < messages.length; m++) {   
      (sheet.getRange(row,1).setValue(messages[m].getPlainBody()),  {contentsOnly: true});
      row++;
    }
  }
}

function onOpen() {
  var menuEntries = [ {name: "Load Emails", functionName: "getEmails"} ];
  spreadsheet.addMenu("Email", menuEntries);
}

Upvotes: 1

Views: 225

Answers (1)

user3717023
user3717023

Reputation:

I don't know why it's better to have text spanning multiple cells, but this is how you can do it.

var message = messages[m].getPlainBody(); 
var strSplit = str.split('\n').map(function(a) {return [a.trim()];});
sheet.getRange(row, 1, strSplit.length, 1).setValues(strSplit);

The second line of code splits the string and places pieces into separate rows, along the way trimming whitespace around line breaks.

Upvotes: 1

Related Questions