基于Google Sheet日期触发提醒邮件的脚本故障排查求助

已知条件 #

• G列为预计归还日期（提醒日期）
• J列存储收件人邮箱地址
• L列为归还状态

需求 #

当L列（归还状态）为空时，在G列的提醒日期当天向J列对应的邮箱发送提醒邮件。

原脚本的问题点 #

1. 数据范围获取错误
   var dataRange= sheet.getRange(startRow,numRows) 写法错误，getRange(row, column) 仅会选中单个单元格，无法获取所有需要处理的行和列，导致后续循环无有效数据可处理。

2. 收件人邮箱硬编码
   脚本将emailAddress固定为dummy@gmail.com，未使用J列（对应索引row[9]）的实际收件人地址，无法向目标用户发送邮件。

3. 未校验归还状态
   完全忽略需求中“L列为空才发送”的核心条件，未检查L列（对应索引row[11]）是否为空，不符合业务逻辑。

4. 日期匹配的兼容性问题
   使用toLocaleDateString()比较日期会受系统地区设置影响，不同环境下格式可能不一致（如MM/DD/YYYY vs DD/MM/YYYY），导致日期判断失效。

修正后的脚本 #

var EMAIL_SENT = "EMAIL_SENT";

function sendEmails() {
  // 统一日期处理，避免地区格式差异
  const today = new Date();
  today.setHours(0, 0, 0, 0);
  const todayTimestamp = today.getTime();

  const sheet = SpreadsheetApp.getActiveSheet();
  const startRow = 2;
  const numRows = sheet.getLastRow() - startRow + 1; // 仅处理有数据的行，提升效率
  const dataRange = sheet.getRange(startRow, 1, numRows, 12); // 覆盖A到L列的有效数据范围
  const data = dataRange.getValues();

  for (let i = 0; i < data.length; i++) {
    const row = data[i];
    const emailAddress = row[9]; // J列，实际收件人邮箱
    const returnStatus = row[11]; // L列，归还状态
    const reminderDate = new Date(row[6]); // G列，提醒日期
    reminderDate.setHours(0, 0, 0, 0);
    const reminderTimestamp = reminderDate.getTime();
    const emailSent = row[10]; // K列，标记是否已发送邮件

    // 跳过不符合条件的行
    if (reminderTimestamp !== todayTimestamp) continue;
    if (returnStatus !== "") continue;
    if (emailSent === EMAIL_SENT) continue;
    if (!emailAddress) continue;

    // 组装邮件内容（若列位置变动，需对应调整row索引）
    const subject = `RC Reminder # ${row[3]}`;
    const message = `Reminder for ${row[4]} RC of vehicle ${row[3]} handed over to ${row[5]} against ${row[2]} on ${row[0]}`;

    // 发送邮件并标记已发送
    MailApp.sendEmail(emailAddress, subject, message, {name: 'Sam'});
    sheet.getRange(startRow + i, 11).setValue(EMAIL_SENT);
    SpreadsheetApp.flush();
  }
}

额外说明 #

• 用getLastRow()替代固定999行，避免处理空行，提升脚本运行效率
• 采用时间戳比较日期，彻底解决地区格式不一致导致的匹配错误
• 增加空邮箱判断，避免因无有效收件人导致的发送失败
• 明确标注各业务列对应的索引，方便后续维护调整

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