如何通过Google Sheet Apps Script按日期条件发批量邮件并更新联系日期？

核心修改说明 #

• 新增日期过滤逻辑：仅给最后联系日期早于9个月前的用户发送邮件
• 邮件发送完成后，自动将当前日期同步更新到对应用户的最后联系日期列
• 优化了数据读写方式，用批量操作替代逐行调用，提升脚本运行效率

function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var listSheet = ss.getSheetByName('List');
  var templateSheet = ss.getSheetByName('Template');
  
  // 读取邮件模板的主题和内容
  var emailSubject = templateSheet.getRange(2, 1).getValue();
  var templateContent = templateSheet.getRange(2, 2).getValue();
  
  // 计算当前日期以及9个月前的日期
  const today = new Date();
  const nineMonthsPrior = new Date(today.getFullYear(), today.getMonth() - 9, today.getDate());
  
  // 获取List表中所有数据（跳过第一行表头）
  const lastRow = listSheet.getLastRow();
  const dataRange = listSheet.getRange(2, 1, lastRow - 1, 5);
  const rowsData = dataRange.getValues();
  const dateUpdateRange = listSheet.getRange(2, 3, lastRow - 1, 1); // 最后联系日期所在列（默认C列），请根据实际列调整
  
  // 遍历每一行用户数据
  rowsData.forEach((row, index) => {
    const firstName = row[0];
    const email = row[1]; // 邮箱所在列（默认B列），请根据实际列调整
    const lastContact = row[2]; // 最后联系日期所在列（默认C列），请根据实际列调整
    const authorName = row[3];
    const bookTitle = row[4];
    
    // 检查最后联系日期是否超过9个月，且日期格式有效
    if (lastContact instanceof Date && lastContact < nineMonthsPrior) {
      // 替换模板中的个性化占位符
      const personalizedMessage = templateContent
        .replace("<firstName>", firstName)
        .replace("<authorName>", authorName)
        .replace("<bookTitle>", bookTitle);
      
      // 发送邮件
      MailApp.sendEmail(email, emailSubject, personalizedMessage);
      
      // 更新当前行的最后联系日期
      rowsData[index][2] = today;
    }
  });
  
  // 批量写入更新后的日期，提升运行效率
  dateUpdateRange.setValues(rowsData.map(row => [row[2]]));
}

关键注意事项 #

1. 列顺序调整：代码默认列顺序为「姓名（A列）、邮箱（B列）、最后联系日期（C列）、作者名（D列）、书名（E列）」，如果你的工作表列布局不同，需修改代码中对应的索引（如row[1]、row[2]）和dateUpdateRange的列参数
2. 日期格式校验：确保List表中「最后联系日期」列的单元格格式设置为日期型，避免因格式错误导致脚本无法正确判断
3. 运行限制：Google Apps Script对每日发送邮件数量有限制（普通账号每日上限100封），若用户数量较多，可考虑拆分批次运行

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