GAS技术求助：表单提交后按Product ID中间4位排序并插入空行

一、核心功能实现代码 #

以下是完整的表单提交触发函数，包含顶部新增行写入数据、按指定规则排序、不同Product ID中间4位间插入空白行的逻辑：

function onFormSubmit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formSheet = ss.getSheetByName("表单录入表"); // 替换为你的表单响应表名称
  const dbSheet = ss.getSheetByName("数据库表"); // 替换为你的DB表名称
  
  // 1. 获取表单提交的最新数据（用触发器事件对象直接拿更高效）
  const formData = e.values;
  
  // 2. 在DB表顶部新增一行并写入数据
  dbSheet.insertRowBefore(1);
  dbSheet.getRange(1, 1, 1, formData.length).setValues([formData]);
  
  // 3. 添加辅助列（存储Product ID第6-9位，已有则跳过）
  const helperCol = dbSheet.getLastColumn() + 1;
  if (dbSheet.getRange(1, helperCol).getValue() !== "Product_Mid4") {
    dbSheet.getRange(1, helperCol).setValue("Product_Mid4");
    // ArrayFormula自动计算中间4位，无需手动维护
    dbSheet.getRange(2, helperCol).setFormula(`=ArrayFormula(if(A2:A<>"",mid(A2:A,6,4),""))`);
  }
  
  // 4. 按辅助列（中间4位）和批次号排序（批次号列号可按需调整）
  const dataRange = dbSheet.getRange(2, 1, dbSheet.getLastRow()-1, dbSheet.getLastColumn());
  dataRange.sort([
    {column: helperCol, ascending: true}, // 先按中间4位升序
    {column: 2, ascending: true} // 再按批次号升序，替换为你的批次号列号
  ]);
  
  // 5. 从下往上遍历，在不同中间4位间插入空白行（避免行号偏移）
  const mid4Values = dbSheet.getRange(2, helperCol, dbSheet.getLastRow()-1, 1).getValues().flat();
  for (let i = mid4Values.length - 1; i > 0; i--) {
    if (mid4Values[i] !== mid4Values[i-1]) {
      dbSheet.insertRowBefore(i+2);
      // 更新ArrayFormula范围，确保新行自动计算
      dbSheet.getRange(2, helperCol).setFormula(`=ArrayFormula(if(A2:A<>"",mid(A2:A,6,4),""))`);
    }
  }
}

代码关键说明 #

• 直接用e.values获取表单提交数据，比读取响应表最后一行更高效
• 辅助列通过ArrayFormula自动生成中间4位，无需在GAS中重复计算
• 从下往上插入空白行，避免插入操作导致后续行号混乱，保证判断逻辑准确
• 排序时先按中间4位聚合，再按批次号排序，符合生产控制表的分组需求

二、表格优化建议 #

1. 绑定表单触发器：在GAS编辑器中点击「编辑」→「当前项目的触发器」，添加「表单提交」触发器绑定上述函数，实现自动触发，无需手动运行
2. 数据验证：在表单的Product ID字段设置验证规则（比如长度≥9位），避免格式错误导致中间4位提取失败
3. 隐藏辅助列：右键Product_Mid4列标选择「隐藏列」，不影响功能的同时保持表格美观
4. 批量操作优化：尽量用批量读写替代单行操作，减少脚本运行时间（代码已按此逻辑实现）
5. 历史数据归档：当DB表数据量过大时，定期将旧数据移至单独的「历史数据表」，避免主表卡顿
6. 重复提交拦截：在GAS中增加判断逻辑（比如根据Product ID+批次号校验），防止重复数据录入

三、关于DUMMY列与ArrayFormula的说明 #

你提到的DUMMY列思路完全可行，代码中的Product_Mid4列就是这个逻辑的落地：通过ArrayFormula自动关联每行Product ID生成中间4位，既简化了GAS的计算逻辑，也让排序和分组判断更直观。隐藏该列后完全不影响使用，完美匹配你的需求。

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