Google Calendar删除事件邮件通知GAS脚本优化需求

核心问题修正与优化 #

原脚本存在以下问题：

• 删除事件通知逻辑参数传递错误（传入数组而非事件详情对象）
• 首次运行时lastUpdated属性为空导致的日期解析错误
• 重复触发未有效拦截
• 邮件通知分散发送，未合并

修正后的完整代码 #

function monitorMyCalendar(e) {
  if (!e) {
    console.log('无触发器事件参数');
    return;
  }

  try {
    const isNotExist = '无';
    // 锁机制：拦截重复触发，超时30秒
    const lock = LockService.getScriptLock();
    if (!lock.tryLock(30000)) {
      console.log('脚本正在运行，跳过本次触发');
      return;
    }

    const properties = PropertiesService.getScriptProperties();
    // 初始化历史更新时间
    let lastUpdated = properties.getProperty('lastUpdated');
    lastUpdated = lastUpdated ? new Date(lastUpdated) : new Date(0);
    const noticedDate = formatDate_(lastUpdated);

    const currentTime = new Date();
    const currentDate = formatDate_(currentTime);

    const today = new Date();
    today.setHours(0, 0, 0, 0);
    const twoWeeksLater = new Date(today);
    twoWeeksLater.setDate(twoWeeksLater.getDate() + 14);
    const sixMonthsLater = new Date(today);
    sixMonthsLater.setMonth(today.getMonth() + 6);

    const calendar = CalendarApp.getCalendarById(e.calendarId);
    const events = calendar.getEvents(today, sixMonthsLater);

    let noticeCount = 0;
    const mailBodies = {
      new: [],
      updated: [],
      deleted: []
    };
    const currentEventIds = new Set(events.map(event => event.getId()));

    // 处理新增/更新事件
    for (const event of events) {
      const eventUpdated = event.getLastUpdated();
      const isNew = eventUpdated > lastUpdated;
      const isUpdated = event.getStartTime() > lastUpdated || event.getEndTime() > lastUpdated;
      
      if (isNew || isUpdated) {
        const eventDetails = {
          title: event.getTitle() || '标题' + isNotExist,
          startTime: event.getStartTime() ? formatDate_(event.getStartTime()) : '时间' + isNotExist,
          endTime: event.getEndTime() ? formatDate_(event.getEndTime()) : '时间' + isNotExist,
          updateTime: formatDate_(eventUpdated),
          description: event.getDescription() || '详情' + isNotExist,
          location: event.getLocation() || '地点' + isNotExist,
          url: 'https://www.google.com/calendar/event?eid=' + Utilities.base64Encode(event.getId().split('@')[0] + ' ' + e.calendarId),
          calendarName: calendar.getName(),
        };
        if (isNew) {
          mailBodies.new.push(eventDetails);
        } else {
          mailBodies.updated.push(eventDetails);
        }
        noticeCount++;
      }
    }

    // 处理删除事件：比对表格存储的历史事件
    const ss = SpreadsheetApp.openById("你的表格ID"); // 替换为实际表格ID
    const sheetName = e.calendarId.slice(0, 16);
    let sheet = ss.getSheetByName(sheetName);
    if (!sheet) sheet = ss.insertSheet(sheetName);
    
    const savedEvents = sheet.getDataRange().getValues();
    // 仅处理两周内的历史事件
    const twoWeeksSavedEvents = savedEvents.filter(row => {
      if (!row[5]) return false;
      const eventStartTime = new Date(row[5]);
      return eventStartTime >= today && eventStartTime <= twoWeeksLater;
    });

    // 找出已删除的事件ID
    const deletedEventIds = twoWeeksSavedEvents
      .map(row => row[0])
      .filter(eventId => !currentEventIds.has(eventId));

    // 生成删除事件的通知详情
    for (const eventId of deletedEventIds) {
      const deletedRow = savedEvents.find(row => row[0] === eventId);
      if (!deletedRow) continue;
      
      mailBodies.deleted.push({
        title: deletedRow[3] || '标题' + isNotExist,
        startTime: deletedRow[5] ? formatDate_(new Date(deletedRow[5])) : '时间' + isNotExist,
        endTime: deletedRow[6] ? formatDate_(new Date(deletedRow[6])) : '时间' + isNotExist,
        updateTime: formatDate_(currentTime), // 删除时间取当前脚本运行时间
        description: deletedRow[8] || '详情' + isNotExist,
        location: deletedRow[7] || '地点' + isNotExist,
        calendarName: calendar.getName(),
      });
      noticeCount++;
    }

    // 发送合并通知邮件
    if (noticeCount > 0) {
      sendMergedNotification_(mailBodies, noticedDate, currentDate);
      // 更新历史时间戳
      properties.setProperty('lastUpdated', currentTime.toISOString());
      properties.setProperty('lastNotificationDate', currentTime.toISOString());
    }

    // 保存当前事件到表格（仅保留两周内的事件，优化存储）
    const filteredEvents = events.filter(event => {
      const startTime = event.getStartTime();
      return startTime >= today && startTime <= twoWeeksLater;
    });
    const values = filteredEvents.map(event => [
      event.getId(), 
      calendar.getName(),
      e.calendarId,  
      event.getTitle(), 
      event.getLastUpdated(),
      event.getStartTime(),
      event.getEndTime(),
      event.getLocation(),
      event.getDescription(), 
    ]);
    sheet.clearContents();
    if (values.length > 0) {
      sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
    }

    lock.releaseLock();
  } catch (error) {
    console.error('脚本运行错误:', error);
    LockService.getScriptLock().releaseLock();
  }
}

function formatDate_(date) {
  return Utilities.formatDate(new Date(date), 'JST', 'yyyy/MM/dd HH:mm');
}

function sendMergedNotification_(mailBodies, noticedDate, currentDate) {
  const recipientEmail = '你的目标邮箱'; // 替换为实际接收邮箱
  let subject = `Google Calendar 日程变更通知 [${currentDate}]`;
  let body = `上次通知时间: ${noticedDate}\n本次通知时间: ${currentDate}\n\n`;

  // 新增事件部分
  if (mailBodies.new.length > 0) {
    body += '=== 新增日程 ===\n';
    mailBodies.new.forEach(item => {
      body += `创建时间: ${item.updateTime}\n标题: ${item.title}\n开始时间: ${item.startTime}\n结束时间: ${item.endTime}\n地点: ${item.location}\n详情: ${item.description}\n链接: ${item.url}\n\n`;
    });
  }

  // 更新事件部分
  if (mailBodies.updated.length > 0) {
    body += '=== 更新日程 ===\n';
    mailBodies.updated.forEach(item => {
      body += `更新时间: ${item.updateTime}\n标题: ${item.title}\n开始时间: ${item.startTime}\n结束时间: ${item.endTime}\n地点: ${item.location}\n详情: ${item.description}\n链接: ${item.url}\n\n`;
    });
  }

  // 删除事件部分
  if (mailBodies.deleted.length > 0) {
    body += '=== 删除日程 ===\n';
    mailBodies.deleted.forEach(item => {
      body += `删除时间: ${item.updateTime}\n标题: ${item.title}\n开始时间: ${item.startTime}\n结束时间: ${item.endTime}\n地点: ${item.location}\n详情: ${item.description}\n\n`;
    });
  }

  MailApp.sendEmail(recipientEmail, subject, body);
}

关键优化说明 #

1. 重复触发拦截：使用tryLock(30000)设置30秒超时，同一时间仅允许一个脚本实例运行，避免单次日历更新触发多次通知
2. 删除事件识别：通过Set存储当前事件ID，快速比对历史数据找出已删除事件，比原Map更高效
3. 邮件合并：将新增、更新、删除事件合并为单封邮件发送，符合需求中的合并通知要求
4. 存储优化：仅存储两周内的事件，减少表格数据量，提升比对效率
5. 初始化处理：首次运行时自动初始化lastUpdated为0时间，避免日期解析错误

使用注意事项 #

• 替换代码中的你的表格ID和你的目标邮箱为实际值
• 确保脚本已配置Calendar和Spreadsheet的权限
• 为目标日历设置日历更新触发器，触发类型选择从日历接收事件

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