表单提交时匹配Inventory序列号勾选复选框的脚本失效求助

我正在创建一个脚本，用于将表单提交的序列号与Inventory工作表中的数据进行比对，若匹配则勾选对应复选框。目前该脚本在手动编辑工作表时可正常运行，但通过表单提交时无法生效，恳请提供帮助。

表单脚本 #

function onFormSubmit(e) {
      var formResponsesSheetName = "Form Responses";
      var targetSheetName = "Inventory";
    
      const formResponsesSheet = 
      SpreadsheetApp.openByUrl('URL').getSheetByName('Form responses')
      const targetSheet = 
      SpreadsheetApp.openByUrl('URL').getSheetByName('Inventory');  
      
      if (formResponsesSheet && targetSheet) {
       var valueToCompare = [6];
       
       let targetColumn = targetSheet.getRange("C:C").getValues();
       let checkboxColumn = targetSheet.getRange("D:D");
       let checkboxValues = checkboxColumn.getValues();
    
       for (var i = 0; i < targetColumn.length; i++) {
         if (targetColumn[i][0] === valueToCompare) {
           checkboxValues[i][0] = true;
         } else {
            checkboxValues[i][0] = false;
         }
    }
}

工作表脚本 #

function onEdit(e) {
  var sheet = e.source.getSheetByName("Form responses");
  var range = e.range;
  
  if (sheet.getName() === "Form responses" && range.getColumn() === 7) {
    var valueToCompare = range.getValue();
    var targetSheet = e.source.getSheetByName("Inventory");
    var targetColumn = targetSheet.getRange("C:C").getValues();
    var checkboxColumn = targetSheet.getRange("D:D");
    var checkboxValues = checkboxColumn.getValues();
    
    for (var i = 0; i < targetColumn.length; i++) {
      if (targetColumn[i][0] === valueToCompare) {
        checkboxValues[i][0] = true;
      } else {
        checkboxValues[i][0] = false;
      }
    }
    
    checkboxColumn.setValues(checkboxValues);
  }
}

问题核心原因及修复方案 #

1. 表单提交脚本的致命问题 #

• 硬编码无效值：valueToCompare = [6] 是固定数组，根本没有获取表单提交的实际序列号，完全无法匹配数据
• 未写回修改：遍历修改复选框状态后，没有调用checkboxColumn.setValues(checkboxValues)将修改同步到工作表
• 冗余操作：重复调用SpreadsheetApp.openByUrl('URL')，可以合并为一次获取表格实例
• 名称不统一：代码中表单响应表名称大小写不一致（变量名Form Responses vs 实际调用Form responses）

2. 触发器逻辑误区 #

onEdit是简单触发器，仅对手动编辑工作表的操作生效，表单提交属于程序自动写入，不会触发这个函数，必须使用表单提交专属的可安装触发器。

修复后的表单提交脚本 #

function onFormSubmit(e) {
  const formResponsesSheetName = "Form Responses";
  const targetSheetName = "Inventory";
  const ss = SpreadsheetApp.openByUrl('你的表格URL'); // 替换为实际表格URL
  const formResponsesSheet = ss.getSheetByName(formResponsesSheetName);
  const targetSheet = ss.getSheetByName(targetSheetName);  

  if (!formResponsesSheet || !targetSheet) return;

  // 获取表单提交的序列号（对应原onEdit逻辑中的第7列，数组索引从0开始）
  const valueToCompare = e.values[6];
  
  // 优化：仅获取有数据的行，避免整列遍历浪费性能
  const lastRow = targetSheet.getLastRow();
  const targetColumn = targetSheet.getRange(1, 3, lastRow, 1).getValues();
  const checkboxColumn = targetSheet.getRange(1, 4, lastRow, 1);
  const checkboxValues = checkboxColumn.getValues();

  for (let i = 0; i < targetColumn.length; i++) {
    checkboxValues[i][0] = targetColumn[i][0] === valueToCompare;
  }

  // 必须执行这一步，将修改后的复选框状态写回工作表
  checkboxColumn.setValues(checkboxValues);
}

最后配置步骤 #

在脚本编辑器中，点击「编辑」→「当前项目的触发器」，添加一个可安装触发器：

• 选择函数：onFormSubmit
• 选择事件源：「表单」
• 选择事件类型：「表单提交」

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