Google Sheets脚本单次触发多次重复发送Payload问题求助

最终更新：我在其他开发者创建的表格中工作，发现对方创建了大量额外触发器。由于这是我首个Google Sheets应用，直到深入学习触发器相关知识后才发现问题。感谢各位提供排查方向的答主。

我开发了一个Google Sheets脚本，当名为"Automation"的触发列（第15列）的值改为"Add to Monday"时，会收集对应行前10列的数据，作为Payload发送到指定端点。

当前问题：每次手动修改该列值为"Add to Monday"，脚本都会被执行8次，导致重复发送相同Payload，在Monday.com生成重复条目。

初始代码如下：

function onEditInstallable(e) {
    console.log("function executed");
    var triggerColumn = 15; // Replace with the column number that triggers the action
    var targetUrl = "https://endpoint.url/"; // placeholder endpoint - actual endpoint removed 
  
    var range = e.range;
    var column = range.getColumn();
    var value = range.getValue();
  
    // Check if the edit occurred in the correct sheet, column, and value
    if (
      column === triggerColumn && value == "Add to Monday"
    ) {
  var row = range.getRow();
    var sheet = range.getSheet();
    var values = sheet.getRange(row, 1, 1, 10).getValues()[0];
    var payload = {
      "col1": values[0],
      "col2": values[1],
      "col3": values[2],
      "col4": values[3],
      "col5": values[4],
      "col6": values[5],
      "col7": values[6],
      "col8": values[7],
      "col9": values[8],
      "col10": values[9]
      };

      console.log(payload);
  
      // Send the data to Zapier endpoint
      var options = {
        method: "post",
        payload: payload,
      };
  
      var success = false;
  
      try {
        var response = UrlFetchApp.fetch(targetUrl, options);
        if (response.getResponseCode() === 200) {
          success = true;
        }
      } catch (error) {
        success = false;
      }
  
    // Temporarily remove data validation
    var validationRule = range.getDataValidation();
    range.clearDataValidations();

    // Update the trigger column with the status
    var status = success ? "Success" : "Failed";
    range.setValue(status);

    // Reapply data validation
    range.setDataValidation(validationRule);
  }
  }

我尝试加入Lock Service优化，情况有所改善：单次触发仍会触发8次脚本，但Monday.com仅生成1-2条重复条目，但问题未彻底解决。优化后的代码如下：

function onEditInstallable(e) {
  var triggerColumn = 15; // Replace with the column number that triggers the action

  // Check if the edit occurred in the correct sheet and column
  if (e.range.getColumn() === triggerColumn) {
    // Acquire a lock to prevent concurrent executions
    var lock = LockService.getScriptLock();
    try {
      if (lock.tryLock(10000)) { // Wait up to 10 seconds to acquire the lock
        processTrigger(e);
      } else {
        console.log("Function is already running.");
      }
    } catch (e) {
      console.error("Error acquiring lock:", e);
    } finally {
      lock.releaseLock();
    }
  }
}

function processTrigger(e) {
  var targetUrl = "https://endpoint.url/"; // placeholder endpoint - actual endpoint removed

  var range = e.range;
  var value = range.getValue();

  if (value === "Add to Monday") {
    var row = range.getRow();
    var sheet = range.getSheet();
    var values = sheet.getRange(row, 1, 1, 10).getValues()[0];
    var payload = {
      "col1": values[0],
      "col2": values[1],
      "col3": values[2],
      "col4": values[3],
      "col5": values[4],
      "col6": values[5],
      "col7": values[6],
      "col8": values[7],
      "col9": values[8],
      "col10": values[9]
    };

    console.log(payload);

    // Send the data to Zapier endpoint
    var options = {
      method: "post",
      payload: payload,
    };

    var success = false;

    try {
      var response = UrlFetchApp.fetch(targetUrl, options);
      if (response.getResponseCode() === 200) {
        success = true;
      }
    } catch (error) {
      success = false;
    }

    // Temporarily remove data validation
    var validationRule = range.getDataValidation();
    range.clearDataValidations();

    // Update the trigger column with the status
    var status = success ? "Success" : "Failed";
    range.setValue(status);

    // Reapply data validation
    range.setDataValidation(validationRule);
  }
};

补充说明 #

触发逻辑：修改"Automation"列的值为"Add to Monday"，脚本会收集对应行的数据发送到端点。测试阶段会将该列值改为"Success"，后续计划确认数据同步到Monday.com后再更新状态。

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