Google Script提取Gmail邮箱触发执行超时问题求助

原脚本可从指定Gmail标签提取邮箱地址，处理少量邮件正常，但邮件量较大时会触发Exceeded maximum execution time超时错误。以下是原脚本：

function GetAddresses ()
{
  // Get the active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();  

  // Label to search
  var userInputSheet = ss.getSheets()[0];
  
  var labelName = userInputSheet.getRange("B2").getValue();
  
  // Create / empty the target sheet
  var sheetName = "Label: " + labelName;
  var sheet = ss.getSheetByName (sheetName) || ss.insertSheet (sheetName, ss.getSheets().length);
  sheet.clear();
  
  // Get all messages in a nested array (threads -> messages)
  var addressesOnly = [];
  var messageData = [];

  var startIndex = 0;
  var pageSize = 100;
  while (1)
  {
    // Search in pages of 100
    var threads = GmailApp.search ("label:" + labelName, startIndex, pageSize);
    if (threads.length == 0)
      break;
    else
      startIndex += pageSize;
       
    // Get all messages for the current batch of threads
    var messages = GmailApp.getMessagesForThreads (threads);
    
    // Loop over all messages
    for (var i = 0; i < messages.length ; i++)
    {
      // Loop over all messages in this thread
      for (var j = 0; j < messages[i].length; j++)
      {
        var mailFrom = messages[i][j].getFrom ();
        var mailDate = messages[i][j].getDate ();
        
        // mailFrom format may be either one of these:
        // name@domain.com
        // any text <name@domain.com>
        // "any text" <name@domain.com>
        
        var name = "";
        var email = "";
        var matches = mailFrom.match (/\s*"?([^"]*)"?\s+<(.+)>/);
        if (matches)
        {
          name = matches[1];
          email = matches[2];
        }
        else
        {
          email = mailFrom;
        }
        // Check if (and where) we have this already
        var index = addressesOnly.indexOf (mailFrom);
        if (index > -1)
        {
          // We already have this address -> remove it (so that the result is ordered by data from new to old)
          addressesOnly.splice(index, 1);
          messageData.splice(index, 1);
        }
        
        // Add the data
        addressesOnly.push (mailFrom);
        messageData.push ([name, email, mailDate]);
      }
    }
  }
  
  // Add data to corresponding sheet
  sheet.getRange (1, 1, messageData.length, 3).setValues (messageData);
}


//
// Adds a menu to easily call the script
//
function onOpen ()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet ();
  
  var menu = [ 
    {name: "Extract email addresses",functionName: "GetAddresses"}
  ];  
  
  sheet.addMenu ("Start Extracting", menu);    
}

优化方案 #

1. 分批执行与进度存储 #

利用PropertiesService存储当前处理进度，拆分任务为多个批次执行，避免单次运行超时。每次处理完一批后保存断点，下次执行从断点继续。

2. 高效去重逻辑 #

用Map替代数组存储已处理的邮件地址，将查找和删除操作的时间复杂度从O(n)降至O(1)，大幅提升大数据量下的处理效率。

3. 分批写入表格 #

每批处理完成后立即写入数据，避免内存堆积过多数据，同时降低单次写入的资源消耗。

修改后的完整脚本 #

// 初始化任务：清空目标表格，重置进度
function initExtraction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var userInputSheet = ss.getSheets()[0];
  var labelName = userInputSheet.getRange("B2").getValue();
  
  var sheetName = "Label: " + labelName;
  var sheet = ss.getSheetByName(sheetName) || ss.insertSheet(sheetName, ss.getSheets().length);
  sheet.clear();
  
  // 存储任务参数到属性服务
  var props = PropertiesService.getScriptProperties();
  props.setProperty('labelName', labelName);
  props.setProperty('startIndex', '0');
  props.setProperty('addressMap', JSON.stringify({})); // 记录已处理的地址
  
  SpreadsheetApp.getUi().alert("初始化完成，开始分批处理邮件。可手动点击「处理下一批邮件」或设置自动触发器。");
}

// 处理下一批邮件
function processNextBatch() {
  var props = PropertiesService.getScriptProperties();
  var labelName = props.getProperty('labelName');
  var startIndex = parseInt(props.getProperty('startIndex'));
  var addressMap = JSON.parse(props.getProperty('addressMap'));
  
  if (!labelName) {
    SpreadsheetApp.getUi().alert("未找到任务，请先运行「初始化提取任务」。");
    return;
  }
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "Label: " + labelName;
  var sheet = ss.getSheetByName(sheetName);
  
  var pageSize = 100;
  var threads = GmailApp.search("label:" + labelName, startIndex, pageSize);
  
  if (threads.length === 0) {
    // 处理完成，清理属性
    props.deleteProperty('labelName');
    props.deleteProperty('startIndex');
    props.deleteProperty('addressMap');
    SpreadsheetApp.getUi().alert("所有邮件处理完成！");
    return;
  }
  
  var messages = GmailApp.getMessagesForThreads(threads);
  var batchData = [];
  
  for (var i = 0; i < messages.length; i++) {
    for (var j = 0; j < messages[i].length; j++) {
      var mailFrom = messages[i][j].getFrom();
      var mailDate = messages[i][j].getDate();
      
      var name = "";
      var email = "";
      var matches = mailFrom.match(/\s*"?([^"]*)"?\s+<(.+)>/);
      if (matches) {
        name = matches[1];
        email = matches[2];
      } else {
        email = mailFrom;
      }
      
      // 去重：移除已存在的记录标记
      if (addressMap[mailFrom]) {
        delete addressMap[mailFrom];
      }
      
      addressMap[mailFrom] = true;
      batchData.push([name, email, mailDate]);
    }
  }
  
  // 写入当前批次数据到表格
  var lastRow = sheet.getLastRow();
  if (lastRow === 0) {
    sheet.getRange(1, 1, 1, 3).setValues([["姓名", "邮箱地址", "邮件日期"]]);
    lastRow = 1;
  }
  sheet.getRange(lastRow + 1, 1, batchData.length, 3).setValues(batchData);
  
  // 更新进度
  startIndex += pageSize;
  props.setProperty('startIndex', startIndex.toString());
  props.setProperty('addressMap', JSON.stringify(addressMap));
  
  SpreadsheetApp.getUi().alert("已处理至第 " + startIndex + " 封邮件，继续处理中...");
}

// 表格打开时添加菜单
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var menu = [
    {name: "初始化提取任务", functionName: "initExtraction"},
    {name: "处理下一批邮件", functionName: "processNextBatch"}
  ];
  sheet.addMenu("邮件地址提取", menu);
}

使用说明 #

1. 刷新Google Sheet，会看到「邮件地址提取」菜单。
2. 点击「初始化提取任务」，确认B2单元格已填写目标Gmail标签。
3. 手动多次点击「处理下一批邮件」，或设置时间驱动触发器（如每5分钟运行一次processNextBatch），直到提示处理完成。

内容的提问来源于stack exchange，提问作者Muhammad Irfan