寻求从Google Sheets自动创建日历事件的触发解决方案

背景 #

• 负责演出预订统筹工作，此前流程：销售代表通过Pipedrive(PD)录入信息，手动在PD日历创建活动并与Outlook(OL)双向同步，人为失误导致日历信息混乱，影响正常工作。

现有解决方案 #

• 搭建Google Sheets(SS)模板：销售仅需填写地点、时间、活动类型，其余信息自动填充
• 编写两个Google Apps Script脚本：
  • 脚本1：创建日历事件并记录EventID
  • 脚本2：根据EventID编辑事件
  • 脚本功能正常，但目前仅支持手动运行

当前触发困境 #

Installable Triggers无法满足需求，存在以下局限：

• Open触发：会重复创建事件
• Edit/Change触发：易生成重复事件
• 表单提交触发：不符合单合同对应独立SS的业务模式
• 不愿依赖Monday.com或Zapier，也不想手动触发或让销售代表操作脚本，需要完全无手动干预的触发方案

可行解决方案 #

1. 单元格状态标记+定时扫描触发（推荐） #

给Sheets模板新增「触发状态」列（例如列Z），预设值为待处理，脚本执行完成后自动更新为已完成；若需编辑事件，可将状态改为待编辑。

创建时间驱动的Installable Trigger（例如每15分钟运行一次），脚本逻辑如下：

function autoHandleCalendarEvents() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const allData = activeSheet.getDataRange().getValues();
  
  // 跳过表头，遍历数据行
  for (let rowIndex = 1; rowIndex < allData.length; rowIndex++) {
    const currentRow = rowIndex + 1;
    const triggerStatus = allData[rowIndex][25]; // Z列（索引25）
    const storedEventId = allData[rowIndex][26]; // AA列存EventID
    const eventLocation = allData[rowIndex][0]; // A列：地点
    const eventTime = allData[rowIndex][1]; // B列：时间
    const eventType = allData[rowIndex][2]; // C列：活动类型

    // 处理待创建事件
    if (triggerStatus === '待处理') {
      const newEventId = createCalendarEvent(eventLocation, eventTime, eventType);
      // 更新EventID和状态
      activeSheet.getRange(currentRow, 27).setValue(newEventId);
      activeSheet.getRange(currentRow, 26).setValue('已完成');
    }
    // 处理待编辑事件
    else if (triggerStatus === '待编辑' && storedEventId) {
      editCalendarEvent(storedEventId, eventLocation, eventTime, eventType);
      activeSheet.getRange(currentRow, 26).setValue('已完成');
    }
  }
}

• 核心优势：通过状态标记精准控制执行逻辑，彻底避免重复事件；定时扫描确保所有待处理任务被自动执行，完全无需人工干预。

2. Pipedrive Webhook触发（实时性最优） #

利用数据源头Pipedrive的Webhook功能，当销售在PD中创建/更新合同信息时，直接触发Google Apps Script执行：

1. 将现有脚本部署为Web App，设置访问权限为「任何人，甚至匿名」（可通过Pipedrive Webhook的签名验证请求合法性，避免恶意调用）
2. 在Pipedrive后台配置Webhook，触发条件选择「创建Deal」或「更新Deal」，回调URL填写Web App的部署地址
3. 脚本中接收PD的Webhook数据，自动写入对应独立Sheets，并调用创建/编辑日历事件的逻辑

• 核心优势：从数据源头触发，实时性强；完全贴合单合同对应独立SS的业务模式，无需人工操作。

3. 优化Edit/Change触发逻辑（最小改动方案） #

针对Edit/Change触发重复事件的问题，通过事件范围过滤+状态标记优化脚本：

function optimizedOnChange(e) {
  const changedRange = e.range;
  const targetSheet = changedRange.getSheet();
  
  // 仅当修改的是地点、时间、活动类型列（A-C列）且为数据行时触发
  if (changedRange.getColumn() >= 1 && changedRange.getColumn() <= 3 && changedRange.getRow() > 1) {
    const currentRow = changedRange.getRow();
    const triggerStatus = targetSheet.getRange(currentRow, 26).getValue(); // Z列状态
    const storedEventId = targetSheet.getRange(currentRow, 27).getValue(); // AA列EventID

    if (triggerStatus === '待处理') {
      // 执行创建事件逻辑
    } else if (triggerStatus === '待编辑' && storedEventId) {
      // 执行编辑事件逻辑
    }
  }
}

• 注意：需使用Installable onChange Trigger（简单触发器onEdit无法访问Calendar API），并通过状态标记避免重复执行。

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