Google Script: "Your input contains more than the maximum of 50000 characters in a single cell"

Question

Hi I was trying to extract PlainBody contents of my emails and was on the point of finishing extracting my emails until at some point I encountered an error saying Your input contains more than the maximum of 50000 characters in a single cell.

How will I set a limit on how many characters will I extract in Google App script to avoid this error.?

  function getEmailsRange() {

  //Change sheet3 to sheet you would like the data to be pasted
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Raw");
  sheet.getRange('A1:D15000').clearContent();
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  //maximum emails to be extracted 500
  //is:unread in:MANILA
  var threads = GmailApp.search("after:2017/5/5 before:2017/5/6 in:REPORT",0,500);
  var menuEntries = [ {name: "Load Emails", functionName: "getEmails"} ];
  spreadsheet.addMenu("Email Extract", menuEntries);
  var row = 1;
  for (var i = 0; i < 50000; i++) {
    var messages=threads[i].getMessages();
    for (var m=0; m < messages.length; m++) {   

      sheet.getRange(row,4).setValue(messages[m].getDate());
      sheet.getRange(row,2).setValue(messages[m].getPlainBody());
      sheet.getRange(row,1).setValue(messages[m].getSubject());
      sheet.getRange(row,3).setValue(messages[m].getFrom());
      row++; 

    }
  }
}
//var ss = SpreadsheetApp.getActiveSpreadsheet();


function Deleterow() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName('Processed'); // change to your own
  var values = s.getDataRange().getValues();

  for (var row in values)
    if (values[row][5] == 'DELETE')
      s.deleteRow(parseInt(row)+1);
};

Answer 1

The issue is because you are setting the body of an email to a cell in the spreadsheet and a cell cannot have more than 50000 characters.

You can change the following line:

sheet.getRange(row,2).setValue(messages[m].getPlainBody());

to

  sheet.getRange(row,2).setValue(messages[m].getPlainBody().substring(0, 50000));

This will add only the first 50000 characters into the cell. Other options would be to extract the emails to more than one cell or to extract the emails into a google doc.

Answer 2

You can work around this by using the appendRow function on the Sheet class.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

var data = "some really long string greater than 50k characters";
// write content to a new row created, no 50k error!
sheet.appendRow([data]);

var lastRowRange = sheet.getRange(sheet.getLastRow(), 1);

// copy newly created content to desired range
var outputRange = sheet.getRange("A1");
lastRowRange.copyTo(outputRange);

// clear newly created row
lastRowRange.clearContent();