How to avoid exceeding maximum execution time with Google Apps Script & Gmail?

Question

I am trying to build a table including the following data for all emails sent/received within a specific time frame from my gmail account: (1) Message Id, (2) Sender email address, (3) Recipient email address, (4) Day and time, (5) Subject

The following script seems to work however it exceeds maximum execution time allowed by Google Apps Script. Would you be able to help me amend it so that it can run into batches?

function dateToString(date) {
  return date.getFullYear() + "/" + (date.getMonth() + 1) + "/" + date.getDate();
}

function searchByTimeFrame(from, to) {
  var threads = GmailApp.search("after:"+dateToString(from) +" before:"+dateToString(to)+" in:anywhere");
  var results = [];
  for (var i = 0; i < threads.length; i++) {
    Logger.log(threads[i].getFirstMessageSubject() + " (count: " + threads[i].getMessageCount() + ")");
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var message = messages[j];
      var sender = message.getFrom();
      var recipientsStr = message.getTo()
      results.push([message.getId(), sender, recipientsStr, message.getDate(), message.getSubject()]);
      continue;
    }
  }

  return results;
}

function main() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var from = ss.getRange("B4").getValue();
  var to = ss.getRange("B5").getValue();
  var results = searchByTimeFrame(from, to);

  if(results.length > 0) {
    var sheet = ss.insertSheet();
    var header = sheet.getRange("A1:E1").setValues([['Id', 'From', 'To', 'Date', 'Subject']]).setFontWeight("bold");;
    var cell = sheet.getRange("A2:E"+(results.length+1));
    cell.setValues(results);
  } else {
    var ui = SpreadsheetApp.getUi(); // Same variations.
    ui.alert('No email found...');
  }
}

Andrew Roberts Andrew Roberts · Accepted Answer · 2016-01-24T20:45:16

Use a batch library like CBL (MdcB85Ns0dBpktAKfKuamwZpmfj86RiLA).

CBL initiates a trigger to go off in 7 mins (startOrResumeContinousExecutionInstance())
Once you get close to 5 mins (isTimeRunningOut()) the present index is thread stored
The next time it runs the index is loaded from the batch key

I've not tested it but you can see the general idea - you'll need to update main() to append the rows rather then overwrite each time.

var FUNCTION_NAME = "main";
var EMAIL_RECIPIENT = "!!! PUT YOUR EMAIL ADDRESS HERE !!!";

function dateToString(date) {
  return date.getFullYear() + "/" + (date.getMonth() + 1) + "/" + date.getDate();
}

function searchByTimeFrame(from, to) {

  CBL.startOrResumeContinousExecutionInstance(FUNCTION_NAME)

  var threads = GmailApp.search("after:" + dateToString(from) + " before:" + dateToString(to) + " in:anywhere");
  var results = [];
  var i = CBL.getBatchKey(FUNCTION_NAME) || 0;

  for (; i < threads.length; i++) {
    Logger.log(threads[i].getFirstMessageSubject() + " (count: " + threads[i].getMessageCount() + ")");
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var message = messages[j];
      var sender = message.getFrom();
      var recipientsStr = message.getTo()
      results.push([message.getId(), sender, recipientsStr, message.getDate(), message.getSubject()]);
      continue; // AJR: What's this for?
      if (CBL.isTimeRunningOut(FUNCTION_NAME)) {
        CBL.setBatchKey(FUNCTION_NAME, i)
        break;
      }
    }
  }

  if (i === threads.length) {
    CBL.endContinuousExecutionInstance(FUNCTION_NAME, EMAIL_RECIPIENT, "GMail search finished")
  }

  return results;
}

function main() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var from = ss.getRange("B4").getValue();
  var to = ss.getRange("B5").getValue();
  var results = searchByTimeFrame(from, to);

  if(results.length > 0) {
    var sheet = ss.insertSheet();
    var header = sheet.getRange("A1:E1").setValues([['Id', 'From', 'To', 'Date', 'Subject']]).setFontWeight("bold");;
    var cell = sheet.getRange("A2:E"+(results.length+1));
    cell.setValues(results);
  } else {
    var ui = SpreadsheetApp.getUi(); // Same variations.
    ui.alert('No email found...');
  }
}

How to avoid exceeding maximum execution time with Google Apps Script & Gmail?

1 Answers