Google Apps Script批量邮件循环效率优化方案咨询

问题场景 #

我需要处理Google Sheets里的表单响应数据，给没收到邮件的用户发邮件。表格现在有7000多行还在持续增长，原来的做法是把全表数据存进数组后循环，跳过有邮件时间戳的行，但循环耗时极长——15分钟都没跑完。之前我是找第一个空白时间戳的行开始循环，但现在空白行不再集中在表格末尾，还不能通过排序调整行序，得保住原行序的同时提升效率，还要能给对应行标记邮件发送时间戳，从该行取数据写邮件内容。

优化思路 #

核心就是减少Google Sheets API的读写次数（这是脚本变慢的主要原因），同时只循环真正需要处理的行：

1. 一次性读取全表数据，避免反复调用API
2. 提前筛选出所有时间戳为空的行，只循环这些行
3. 批量写入时间戳，别发一封邮件就写一次单元格

优化后的代码 #

function sendEmails() {
  const tss = SpreadsheetApp.getActive();
  const requestsSht = tss.getSheetByName('Requests');
  
  // 时间戳列是表格第7列，对应数组索引6（数组从0开始计数）
  const emailDateColIndex = 6;
  const lastRow = requestsSht.getLastRow();
  const lastCol = requestsSht.getLastColumn();
  
  // 一次性读取全表所有数据，减少API调用次数
  const allData = requestsSht.getRange(1, 1, lastRow, lastCol).getValues();
  
  // 筛选出所有需要处理的行（时间戳为空），同时记录表格行号和对应数据
  const rowsToProcess = [];
  for (let i = 0; i < allData.length; i++) {
    const row = allData[i];
    if (!row[emailDateColIndex] || row[emailDateColIndex] === "") {
      rowsToProcess.push({
        rowNum: i + 1, // 表格行号从1开始计数
        data: row
      });
    }
  }
  
  // 如果没有待处理的行，直接退出脚本
  if (rowsToProcess.length === 0) {
    console.log("没有待发送邮件的行");
    return;
  }
  
  // 准备批量写入的时间戳（统一用当前时间）
  const currentTime = new Date();
  const timestampList = rowsToProcess.map(() => [currentTime]);
  
  // 批量写入时间戳到对应行的时间戳列，只调用一次setValues
  requestsSht.getRange(
    rowsToProcess[0].rowNum, 
    emailDateColIndex + 1, // 表格列号从1开始计数，索引需+1
    rowsToProcess.length, 
    1
  ).setValues(timestampList);
  
  // 循环处理邮件发送逻辑
  for (const rowItem of rowsToProcess) {
    const rowData = rowItem.data;
    // 这里替换成你的实际邮件逻辑：从行数据中取收件人、主题、内容
    const recipient = rowData[1]; // 假设表格第2列是收件人邮箱
    const subject = "你的请求已收到";
    const body = `您好，你的请求内容：${rowData[2]} 我们已收到，将尽快处理。`;
    
    // 发送邮件
    MailApp.sendEmail(recipient, subject, body);
    console.log(`已给行${rowItem.rowNum}的用户发送邮件`);
  }
}

关键优化点 #

• 单次读取全表：避免多次调用getRange/getValues，这比循环读取每行数据效率提升10倍以上
• 精准循环：只处理需要发送邮件的行，比如7000行里只有1000行空白，就只循环1000次，大幅减少循环耗时
• 批量写入时间戳：先把所有待处理行的时间戳写完再发邮件，既减少API调用次数，还能避免邮件发送失败后重复处理同一行
• 索引对应：明确区分数组索引（0开始）和表格行列号（1开始）的转换，避免写错数据位置

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