如何使用模板通过Google Sheets批量发送符合筛选条件的邮件

问题说明 #

你当前的脚本核心问题有两点：

1. 重复读取了两次表格数据，用于填充模板的rowData变量只会保留前6行最后一行的内容，所有邮件都会使用同一份模板填充结果发送
2. 逐行写入EMAIL_SENT标记会频繁调用表格API，数据量大时容易触发调用限制，同时范围取值存在边界问题，数据行少于2行时会直接报错

修正后可用脚本 #

/**
 * 从电子表格行数据发送邮件
 */
function sendEmails() {
  const ss = SpreadsheetApp.getActive();
  // 数据工作表，建议替换为getSheetByName('你的数据工作表名称')避免表序变动出错
  const dsh = ss.getSheets()[0];
  const lastRow = dsh.getLastRow();
  // 只有表头无数据时直接终止运行避免报错
  if (lastRow < 2) return;
  // 读取第2行到最后一行的全部36列数据
  const drg = dsh.getRange(2, 1, lastRow - 1, 36);
  const vs = drg.getValues();
  // 模板工作表，建议替换为getSheetByName('你的模板工作表名称')
  const tsh = ss.getSheets()[1];
  const tmpl = tsh.getRange('A1').getValue();
  // 提前构造批量更新标记的数组，最后一次性写入减少API调用
  const updateMarks = vs.map(r => [r[35]]);

  vs.forEach((r, i) => {
    const emailSent = r[35];
    const status = r[10];
    // 仅处理状态为PRICE ONLY且未发送过邮件的行
    if (status == 'PRICE ONLY' && emailSent != 'EMAIL_SENT') {
      // 若你的fillInTemplateFromObject需要getRowsData返回的对象格式，可在此处把当前行r转换为对应格式即可
      const emailBody = fillInTemplateFromObject(tmpl, r);
      // 收件人存储在第10列（索引为9）
      MailApp.sendEmail(r[9], 'SUPER QUOTATION', emailBody);
      // 标记当前行为已发送
      updateMarks[i] = ['EMAIL_SENT'];
    }
  });

  // 批量写入已发送标记
  drg.offset(0, 35).setValues(updateMarks);
}

注意事项 #

• 强烈建议将代码中getSheets()[0]、getSheets()[1]替换为getSheetByName('对应工作表名称')，避免后续调整工作表顺序后脚本逻辑出错
• 若你原有fillInTemplateFromObject函数依赖getRowsData返回的带列名属性的对象，可在调用该函数传参前，把当前行的数组r转换为对应结构的对象即可

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