Google Apps Script提醒邮件功能故障排查与需求优化

针对你遇到的5个核心问题，以下是修正后的代码及关键说明：

关键修复点 #

• 实现提前1、3、7天的多级提醒，自动跳过周末（可按需调整）
• 邮件内容完全匹配预期格式，包含请求类型、设备数量、请求日期（DD/MM/YYYY）、请求时间（24小时制GMT+8）
• 统一发送至3个管理员邮箱+表单提交者邮箱，避免重复发送
• 支持表单提交和编辑触发，触发时仅验证当前行的请求日期，重新计算提醒时间
• 修复原代码语法错误，添加提醒发送标记列，防止重复发送同一提醒

修正后完整代码 #

// 表单提交/编辑触发，仅处理当前行数据
function onFormSubmitOrEdit(e) {
  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  if (row === 1) return; // 跳过表头

  // 配置列索引（根据你的表格实际列调整）
  const config = {
    requestedDateCol: 6,     // 请求日期列（F列）
    requestedTimeCol: 7,     // 请求时间列（G列）
    typeCol: 4,              // 请求类型列（D列）
    deviceCountCol: 5,       // 设备数量列（E列，需根据实际调整）
    submitterEmailCol: 3,    // 提交者邮箱列（C列）
    sentRemindersCol: 8      // 已发送提醒标记列（H列，记录已发送的提醒天数，如"1,3,7"）
  };

  // 获取当前行数据
  const rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
  const requestedDate = new Date(rowData[config.requestedDateCol - 1]);
  const requestedTime = parseTime(rowData[config.requestedTimeCol - 1]);
  const requestType = rowData[config.typeCol - 1];
  const deviceCount = rowData[config.deviceCountCol - 1];
  const submitterEmail = rowData[config.submitterEmailCol - 1];
  const sentReminders = rowData[config.sentRemindersCol - 1] || "";

  // 合并请求日期和时间，转换为GMT+8时区
  const gmtOffset = 8 * 60 * 60 * 1000; // GMT+8偏移量（毫秒）
  const combinedDateTime = new Date(requestedDate.getTime() + gmtOffset);
  combinedDateTime.setHours(requestedTime.getHours());
  combinedDateTime.setMinutes(requestedTime.getMinutes());

  // 管理员邮箱列表
  const adminEmails = ["recipient1@example.com", "recipient2@example.com", "recipient3@example.com"];
  const allRecipients = [...adminEmails, submitterEmail].join(",");

  // 需要检查的提前天数列表
  const reminderDays = [1, 3, 7];

  reminderDays.forEach(day => {
    // 计算提醒发送时间（提前N天，跳过周末）
    const reminderDateTime = new Date(combinedDateTime);
    reminderDateTime.setDate(reminderDateTime.getDate() - day);
    // 调整到工作日（周一至周五）
    while (reminderDateTime.getDay() === 0 || reminderDateTime.getDay() === 6) {
      reminderDateTime.setDate(reminderDateTime.getDate() - 1);
    }

    // 获取当前时间（GMT+8）
    const currentDateTime = new Date(Date.now() + gmtOffset);
    // 检查是否到了发送时间，且该提醒未发送过
    const shouldSend = currentDateTime >= reminderDateTime && !sentReminders.includes(day.toString());

    if (shouldSend) {
      // 构建邮件内容
      const formattedDate = Utilities.formatDate(combinedDateTime, "GMT+8", "dd/MM/yyyy");
      const formattedTime = Utilities.formatDate(combinedDateTime, "GMT+8", "HH:mm");
      const emailBody = `This is a reminder for the upcoming ${requestType} service request.
Date: ${formattedDate} (Requested Date)
Time: ${formattedTime} (Requested Time - 24hrs format, GMT +8)
Device Count: ${deviceCount}

Please contact your PIC or XX @ "phone number", "email" if you have any queries.`;

      // 发送邮件
      MailApp.sendEmail({
        to: allRecipients,
        subject: "Reminder - Upcoming service request",
        body: emailBody,
        name: "Service Request Reminder", // 可自定义发送者名称
        replyTo: "your-support-email@example.com" // 可自定义回复邮箱
      });

      // 更新已发送标记
      const newSentReminders = sentReminders ? `${sentReminders},${day}` : day.toString();
      sheet.getRange(row, config.sentRemindersCol).setValue(newSentReminders);

      // 日志记录
      Logger.log(`Sent ${day}-day reminder to: ${allRecipients}`);
      Logger.log(`Request Type: ${requestType}, Device Count: ${deviceCount}`);
    }
  });
}

// 解析时间字符串/数字为Date对象
function parseTime(time) {
  if (typeof time === 'string') {
    const timeParts = time.split(":");
    if (timeParts.length > 1) {
      const hours = parseInt(timeParts[0], 10);
      const minutes = parseInt(timeParts[1], 10);
      return new Date(2000, 0, 1, hours, minutes);
    }
  } else if (typeof time === 'number') {
    const hours = Math.floor(time / 100);
    const minutes = time % 100;
    return new Date(2000, 0, 1, hours, minutes);
  }
  return new Date(); // 解析失败时返回当前时间
}

// 创建表单提交和编辑触发器
function createTriggers() {
  // 删除现有触发器避免重复
  const existingTriggers = ScriptApp.getProjectTriggers();
  existingTriggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));

  // 创建表单提交触发器
  ScriptApp.newTrigger('onFormSubmitOrEdit')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onFormSubmit()
    .create();

  // 创建表单编辑触发器（单元格修改时触发）
  ScriptApp.newTrigger('onFormSubmitOrEdit')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onEdit()
    .create();
}

// 初始化触发器
createTriggers();

使用说明 #

1. 调整列索引：根据你的Google Sheets实际列位置，修改config对象中的列索引值
2. 配置管理员邮箱：替换adminEmails中的示例邮箱为实际管理员邮箱
3. 自定义邮件信息：修改邮件底部的联系信息（PIC、电话、邮箱）
4. 运行初始化：执行createTriggers函数，创建所需的触发器

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