求基于Google Form响应更新Google Sheets Supply totals表的脚本及教程

前置准备 #

• 确保你的Google Form已经关联到当前的Google Sheets文档，表单提交的响应会自动写入到表格的「表单响应」工作表中
• 确认表单至少包含三个必填字段：存储位置、物料类型、crates submitted（提交箱数，可填正负数对应增减）
• 打开目标表格，点击顶部「扩展程序」>「Apps Script」进入脚本编辑页

完整示例代码 #

// 表单提交触发的主函数，后续触发器要绑定这个函数，不要随意修改函数名
function updateSupplyTotalsOnFormSubmit(e) {
  // ---------- 配置区域：请根据你的表格实际情况修改以下参数 ----------
  const SUPPLY_SHEET_NAME = "Supply totals"; // 要更新的库存工作表名称
  const HEADER_ROW_NUM = 1; // 库存表的表头所在行号（表格第一行对应数字1）
  // 库存表各字段的列索引（A列对应0，B列对应1，依次类推）
  const SUPPLY_LOCATION_COL = 0; // 存储位置所在列的索引
  const SUPPLY_MATERIAL_TYPE_COL = 1; // 物料类型所在列的索引
  const SUPPLY_CRATE_COUNT_COL = 2; // 箱数统计字段所在列的索引
  // 表单响应表的各字段列索引，根据你表单字段的排列顺序调整
  const FORM_LOCATION_COL = 1; // 表单提交的存储位置字段所在列的索引
  const FORM_MATERIAL_TYPE_COL = 2; // 表单提交的物料类型字段所在列的索引
  const FORM_CRATES_SUBMITTED_COL = 3; // 表单提交的crates submitted字段所在列的索引
  // ---------- 配置区域结束 ----------

  // 1. 获取当前打开的电子表格对象
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 2. 获取最新的表单提交数据，通过事件对象直接获取效率更高，不需要全表扫描响应数据
  const latestFormResponse = e.values;
  // 若事件对象获取失败，可替换为下方代码手动拉取最新响应行，二选一即可
  // const formSheet = spreadsheet.getSheetByName("表单响应 1"); // 替换为你实际的响应工作表名称
  // const latestFormResponse = formSheet.getRange(formSheet.getLastRow(), 1, 1, formSheet.getLastColumn()).getValues()[0];

  // 3. 提取表单提交的三个核心字段值，trim方法去除首尾空格避免匹配误差
  const submittedLocation = latestFormResponse[FORM_LOCATION_COL].trim();
  const submittedMaterial = latestFormResponse[FORM_MATERIAL_TYPE_COL].trim();
  const submittedCrates = Number(latestFormResponse[FORM_CRATES_SUBMITTED_COL]);

  // 4. 读取库存工作表的全量数据
  const supplySheet = spreadsheet.getSheetByName(SUPPLY_SHEET_NAME);
  const supplyData = supplySheet.getDataRange().getValues();

  // 5. 遍历库存表逐行匹配目标条目
  for (let i = HEADER_ROW_NUM; i < supplyData.length; i++) {
    const currentLocation = supplyData[i][SUPPLY_LOCATION_COL].trim();
    const currentMaterial = supplyData[i][SUPPLY_MATERIAL_TYPE_COL].trim();
    // 同时匹配存储位置和物料类型两个条件
    if (currentLocation === submittedLocation && currentMaterial === submittedMaterial) {
      // 计算新的库存值：原有数值+提交的箱数，原有值为空的话默认按0计算
      const oldCount = Number(supplyData[i][SUPPLY_CRATE_COUNT_COL]) || 0;
      const newCount = oldCount + submittedCrates;
      // 写入更新后的值，表格行号=索引+1，列号=索引+1，因为表格行列从1开始计数，数组索引从0开始
      supplySheet.getRange(i + 1, SUPPLY_CRATE_COUNT_COL + 1).setValue(newCount);
      // 匹配到对应条目后直接退出循环，避免不必要的遍历
      break;
    }
  }
}

触发器配置步骤 #

• 脚本保存后，点击左侧菜单栏「触发器」按钮（闹钟形状图标）
• 点击右下角「添加触发器」，按以下参数配置：
  • 选择要运行的函数：updateSupplyTotalsOnFormSubmit
  • 选择要运行的部署：「Head」
  • 选择事件来源：「电子表格」
  • 选择事件类型：「表单提交时」
• 点击保存，按照提示登录Google账号完成权限授权即可

注意事项 #

• 代码中配置区域的列索引需要和你实际的表格结构完全对应，列索引从0开始计数（A列对应0，B列对应1）
• 表单提交的「存储位置」「物料类型」内容要和库存表中的对应内容完全一致，包括大小写、空格，否则会匹配失败
• 如果你需要处理匹配不到库存条目的场景，可以在遍历结束后添加日志记录或者自动新增库存行的逻辑

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