求助：勾选复选框时基于付款日期提前3周发送日历邀请脚本

原代码存在的问题 #

• 函数未接收e参数（这是触发编辑事件的核心参数，用于获取编辑的单元格信息）
• CalendarApp.createAllDayEvent()调用逻辑错误，不能先空调用再重复执行，且参数缺失
• 未读取付款日期来计算提前3周的提醒时间
• 未配置日历事件的参会者，无法发送邮件邀请
• 存在语法错误：缺少闭合的括号与大括号
• 未实现“已发送”标记，会导致重复触发

修复后的完整代码 #

function onEdit(e) {
  // 定义关键配置
  const SHEET_NAME = "Sheet1";
  const APPROVAL_COLUMN = 12; // 复选框所在列（第12列）
  const PAYMENT_DATE_COLUMN = 13; // 假设付款日期在第13列，可根据实际调整
  const EMAIL_SENT_COLUMN = 14; // 标记已发送的列，可根据实际调整
  const EMAIL_SENT_FLAG = "EMAIL_SENT";

  const sheet = e.source.getActiveSheet();
  const sheetName = sheet.getName();
  const range = e.range;
  const row = range.getRow();
  const column = range.getColumn();
  const isChecked = range.getValue(); // 复选框值为布尔值，无需转字符串

  // 判断是否满足触发条件：指定工作表、指定列、复选框被勾选、未发送过
  if (sheetName === SHEET_NAME && column === APPROVAL_COLUMN && isChecked && sheet.getRange(row, EMAIL_SENT_COLUMN).getValue() !== EMAIL_SENT_FLAG) {
    // 获取付款日期并计算提前3周的提醒日期
    const paymentDate = sheet.getRange(row, PAYMENT_DATE_COLUMN).getValue();
    if (!(paymentDate instanceof Date)) {
      SpreadsheetApp.getUi().alert("第" + row + "行的付款日期格式错误");
      return;
    }
    const reminderDate = new Date(paymentDate);
    reminderDate.setDate(reminderDate.getDate() - 21); // 提前3周（21天）

    // 创建日历事件并发送邮件邀请
    const eventTitle = "付款提醒：发票" + sheet.getRange(row, 1).getValue(); // 假设第1列是发票号，可调整
    const guestEmails = "xxx@example.com, yyy@example.com"; // 替换为实际参会邮箱
    try {
      CalendarApp.createAllDayEvent(
        eventTitle,
        reminderDate,
        { guests: guestEmails, sendInvites: true }
      );

      // 标记为已发送，避免重复触发
      sheet.getRange(row, EMAIL_SENT_COLUMN).setValue(EMAIL_SENT_FLAG);
      SpreadsheetApp.flush();
    } catch (error) {
      SpreadsheetApp.getUi().alert("创建日历事件失败：" + error.message);
    }
  }
}

关键说明 #

1. 触发器配置：这个函数是onEdit简单触发器，无需手动配置，只要编辑工作表就会触发；如果需要权限（比如发送邮件），需改为可安装触发器
2. 日期计算：通过setDate()减去21天实现提前3周的逻辑
3. 参会者邀请：在createAllDayEvent的第三个参数中配置guests和sendInvites: true，自动发送邮件邀请
4. 重复触发防护：通过EMAIL_SENT_COLUMN标记已处理的行，避免复选框再次勾选时重复执行
5. 错误处理：添加try-catch捕获异常，通过弹窗提示错误信息

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