如何避免超过Google Apps Script和Gmail的最大执行时间？

Question

>我正在尝试构建一个表格，其中包含从我的gmail帐户发送/接收的特定时间范围内的所有电子邮件的以下数据：（1）邮件ID，（2）发件人电子邮件地址，（3）收件人电子邮件地址，（4）日期和时间，（5）主题

以下脚本似乎有效，但它超过了Google Apps Script允许的最大执行时间。 你能帮我修改它，以便它可以分批运行吗？

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...');
  }
}

Answer 1

使用像 CBL （MdcB85Ns0dBpktAKfKuamwZpmfj86RiLA） 这样的批处理库。

• CBL 启动一个触发器在 7 分钟内关闭 （startOrResumeContinousExecutionInstance（））
• 一旦接近 5 分钟（isTimeRunningOut（）），当前索引就会被线程存储
• 下次运行时，将从批处理键加载索引

我还没有测试过它，但你可以看到大致的想法 - 你需要更新 main（） 来附加行，而不是每次都覆盖。

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...');
  }
}