Google Sheets日历事件脚本优化需求：自动触发+去重

需求说明 #

现有脚本可通过Google表单响应表格创建Google Calendar事件，需完成两项优化：

• 实现单元格变更或新增行时自动触发脚本
• 避免重复创建事件，利用表格最后一列（H列）的Event ID，已存在则更新对应日历事件，不存在则创建并记录ID

优化后完整脚本 #

// 处理表单提交触发的新增行
function onFormSubmit(e) {
  processEventRow(e.range.getRow());
}

// 处理手动编辑单元格触发的行更新
function onEdit(e) {
  processEventRow(e.range.getRow());
}

// 核心处理逻辑：创建或更新日历事件
function processEventRow(rowNum) {
  const sh = SpreadsheetApp.getActiveSpreadsheet();
  const ss = sh.getActiveSheet();
  const cal = CalendarApp.getDefaultCalendar();

  // 获取当前行的所有数据（A-G列）和已有的Event ID（H列）
  const rowData = ss.getRange(rowNum, 1, 1, 7).getValues()[0];
  const existingEventId = ss.getRange(rowNum, 8).getValue();

  const eventTitle = rowData[3];
  const location = rowData[6];
  const date = new Date(rowData[4]);
  const time = new Date(rowData[5]);
  
  // 合并日期和时间
  date.setHours(time.getHours(), time.getMinutes());
  const endDate = new Date(date.getTime() + 60 * 60000); // 默认1小时时长

  if (existingEventId) {
    // 已有Event ID，更新事件
    try {
      const event = cal.getEventById(existingEventId);
      event.setTitle(eventTitle);
      event.setTime(date, endDate);
      event.setLocation(location);
    } catch (err) {
      // 若ID无效，重新创建事件
      createNewEvent(cal, eventTitle, date, endDate, location, ss, rowNum);
    }
  } else {
    // 无Event ID，创建新事件并记录ID
    createNewEvent(cal, eventTitle, date, endDate, location, ss, rowNum);
  }
}

// 创建新事件并写入Event ID到表格
function createNewEvent(cal, title, start, end, location, sheet, row) {
  const event = cal.createEvent(title, start, end, {location: location});
  const eventId = event.getId();
  sheet.getRange(row, 8).setValue(eventId);
}

关键改动说明 #

• 自动触发逻辑：新增onFormSubmit和onEdit两个触发函数，分别对应表单提交新增行、手动编辑单元格的场景，触发时自动调用核心处理逻辑
• 避免重复与更新机制：
  • 读取当前行的Event ID（H列），存在则更新事件的标题、时间、地点；无效则重新创建
  • 不存在则创建新事件，将生成的ID写入对应行的H列
  • 仅处理触发变更的单一行，而非遍历所有行，提升执行效率
• 修复原脚本Bug：原脚本仅将Event ID写入H2单元格，优化后写入对应行的H列，确保每行事件ID对应正确

触发器设置步骤 #

1. 打开Google表格，点击顶部菜单「工具」→「脚本编辑器」
2. 在脚本编辑器左侧点击「触发器」图标（闹钟样式）
3. 添加两个新触发器：
   • 第一个：选择函数onFormSubmit，事件源选「表单提交」，保存
   • 第二个：选择函数onEdit，事件源选「从电子表格」，事件类型选「编辑时」，保存

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