Google Sheets自定义邮件合并触发Gmail API限额问题求助

需求背景 #

使用Google Apps Script实现以下功能：

• 基于已创建的5个邮件草稿，替换主题中的{{Code}}、正文中的{{Name}}，个性化发送至指定邮箱；
• 仅当表格中选中与草稿主题同名的下拉选项时，触发邮件发送。

问题现象 #

脚本运行后仅发送1-2封邮件，即触发Gmail API配额/速率限制。

原脚本代码 #

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  var drafts = cacheDrafts(); // Cache drafts to reduce API calls

  if (drafts === null) {
    Logger.log('Too many drafts to cache in one day. Try again later.');
    return;
  }

  // Process emails in batches to avoid hitting Gmail API limits
  var batchSize = 50; // Adjust batch size as needed
  for (var start = 1; start < data.length; start += batchSize) {
    var end = Math.min(start + batchSize, data.length);
    processBatch(data.slice(start, end), drafts, sheet, start);
    Utilities.sleep(1000); // Pause between batches to avoid hitting rate limits
  }
}

function processBatch(batchData, drafts, sheet, startIndex) {
  for (var i = 0; i < batchData.length; i++) {
    var row = batchData[i];
    var emailAddress = row[2]; // Assuming column A is Email
    var name = row[1]; // Assuming column B is Name
    var templateName = row[10]; // Assuming column C is Template
    var code = row[0]; // Assuming column D is Code
    
    if (templateName) {
      var draft = getDraftByTemplateName(templateName, drafts);
      if (draft) {
        var personalizedBody = draft.body.replace('{{Name}}', name);
        var personalizedSubject = draft.subject.replace('{{Code}}', code);
        
        try {
          GmailApp.sendEmail(emailAddress, personalizedSubject, '', {
            htmlBody: personalizedBody,
            attachments: draft.attachments
          });
          sheet.getRange(startIndex + i + 1, 10).setValue(''); // Clear the template selection after sending the email
        } catch (e) {
          Logger.log('Failed to send email: ' + e.toString());
        }
      } else {
        Logger.log('No draft found for template: ' + templateName);
      }
    }
  }
}

function cacheDrafts() {
  var maxDraftsToCache = 20; // Limit the number of drafts to cache in one run
  try {
    var drafts = GmailApp.getDrafts();
    var draftCache = {};
    for (var i = 0; i < Math.min(drafts.length, maxDraftsToCache); i++) {
      var draft = drafts[i].getMessage();
      var subject = draft.getSubject();
      draftCache[subject] = {
        body: draft.getBody(),
        subject: draft.getSubject(),
        attachments: draft.getAttachments()
      };
    }
    return draftCache;
  } catch (e) {
    Logger.log('Failed to cache drafts: ' + e.toString());
    return null;
  }
}

function getDraftByTemplateName(templateName, draftCache) {
  for (var subject in draftCache) {
    if (subject.includes(templateName)) { // Check if subject includes the template name
      return draftCache[subject];
    }
  }
  return null;
}

问题分析与修复方案 #

核心原因 #

1. 速率限制触发：GmailApp.sendEmail调用速率过高，原脚本仅设置1秒批次间隔，且单批次内无邮件发送间隔，短时间高频调用触发Gmail速率限制；
2. 不必要API消耗：cacheDrafts函数获取最多20个草稿而非仅目标5个模板草稿，额外消耗API配额；
3. 无重复发送校验：表格中若存在重复选中模板的行，会重复调用发送接口，加速配额消耗。

具体修复步骤 #

1. 降低发送速率，增加间隔

调整批次间隔和单邮件发送间隔，避免触发速率限制：

// 在sendEmail函数中，将批次间隔从1000ms改为5000ms
Utilities.sleep(5000); 

// 在processBatch的循环中，每发送一封邮件后增加2秒间隔
try {
  GmailApp.sendEmail(emailAddress, personalizedSubject, '', {
    htmlBody: personalizedBody,
    attachments: draft.attachments
  });
  sheet.getRange(startIndex + i + 1, 10).setValue('');
  Utilities.sleep(2000); // 新增单邮件发送间隔
} catch (e) {
  Logger.log('Failed to send email: ' + e.toString());
}

2. 优化草稿缓存逻辑

仅缓存需要的5个模板对应的草稿，减少API调用：

function cacheDrafts(templateNames) {
  var draftCache = {};
  var drafts = GmailApp.getDrafts();
  for (var i = 0; i < drafts.length; i++) {
    var draft = drafts[i].getMessage();
    var subject = draft.getSubject();
    // 仅保留目标模板草稿
    if (templateNames.includes(subject)) {
      draftCache[subject] = {
        body: draft.getBody(),
        subject: draft.getSubject(),
        attachments: draft.getAttachments()
      };
    }
    // 找到5个目标草稿后提前终止循环
    if (Object.keys(draftCache).length === 5) break;
  }
  return draftCache;
}

同时在sendEmail函数中，先提取表格中的模板名称并传入缓存函数：

function sendEmail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  
  // 提取表格中所有模板名称并去重
  var templateNames = data.map(row => row[10]).filter(Boolean);
  templateNames = [...new Set(templateNames)];
  
  var drafts = cacheDrafts(templateNames);

  if (Object.keys(drafts).length === 0) {
    Logger.log('No target drafts found.');
    return;
  }

  var batchSize = 10; // 缩小批次大小，建议10-20封/批次
  for (var start = 1; start < data.length; start += batchSize) {
    var end = Math.min(start + batchSize, data.length);
    processBatch(data.slice(start, end), drafts, sheet, start);
    Utilities.sleep(5000);
  }
}

3. 增加发送状态校验

新增一列（如第11列）标记发送状态，避免重复发送：

function processBatch(batchData, drafts, sheet, startIndex) {
  for (var i = 0; i < batchData.length; i++) {
    var row = batchData[i];
    var emailAddress = row[2];
    var name = row[1];
    var templateName = row[10];
    var code = row[0];
    var sendStatus = row[11]; // 第11列为发送状态
    
    // 仅当模板选中且未发送时执行
    if (templateName && sendStatus !== '已发送') {
      var draft = getDraftByTemplateName(templateName, drafts);
      if (draft) {
        var personalizedBody = draft.body.replace('{{Name}}', name);
        var personalizedSubject = draft.subject.replace('{{Code}}', code);
        
        try {
          GmailApp.sendEmail(emailAddress, personalizedSubject, '', {
            htmlBody: personalizedBody,
            attachments: draft.attachments
          });
          sheet.getRange(startIndex + i + 1, 10).setValue('');
          sheet.getRange(startIndex + i + 1, 11).setValue('已发送'); // 标记发送状态
          Utilities.sleep(2000);
        } catch (e) {
          Logger.log('Failed to send email: ' + e.toString());
        }
      } else {
        Logger.log('No draft found for template: ' + templateName);
      }
    }
  }
}

4. 可选：改用MailApp提升配额

若不需要Gmail高级功能，MailApp每日配额更高（1000封），替换发送逻辑：

MailApp.sendEmail({
  to: emailAddress,
  subject: personalizedSubject,
  htmlBody: personalizedBody,
  attachments: draft.attachments
});

内容的提问来源于stack exchange，提问作者Azharuddin Redwan