基于单元格值拆分记录至多行的Google Sheets自动化方案问询

需求说明 #

农场分为40个区域，为简化录入流程，将使用相同物料和总用量的区域合并成组记录（比如A组4个区域共用4kg某物料），需自动把这类分组记录拆分为单区域独立记录：每个区域对应1条记录，用量为总用量除以组内区域数量，同步到指定工作表，且能响应手动录入或表单提交操作自动执行拆分。

实现步骤 #

1. 配置区域组映射 #

先在表格内新建一张「区域组配置」工作表，维护分组名称与对应区域号的映射关系，示例结构如下：

2. 编写Google Apps Script脚本 #

打开目标谷歌表格，点击「扩展程序」→「Apps脚本」，粘贴以下代码（需根据实际表名、列顺序调整参数）：

function splitGroupRecords(e) {
  // 定义各工作表名称与列规则（列索引从0开始）
  const sourceSheetName = "录入表";
  const targetSheetName = "拆分表";
  const configSheetName = "区域组配置";
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(sourceSheetName);
  const targetSheet = ss.getSheetByName(targetSheetName);
  const configSheet = ss.getSheetByName(configSheetName);
  
  // 构建区域组-区域号映射对象
  const configData = configSheet.getDataRange().getValues();
  const groupMap = {};
  for (let i = 1; i < configData.length; i++) {
    const groupName = configData[i][0];
    const areas = configData[i][1].split(",").map(num => parseInt(num.trim()));
    groupMap[groupName] = areas;
  }
  
  // 获取待拆分的新增记录（兼容表单提交与手动录入两种场景）
  let newRecord;
  if (e && e.values) {
    newRecord = e.values;
  } else {
    const lastRow = sourceSheet.getLastRow();
    newRecord = sourceSheet.getRange(lastRow, 1, 1, sourceSheet.getLastColumn()).getValues()[0];
  }
  
  // 解析记录字段（示例列顺序：日期、分组名称、总用量、物料名称、备注）
  const [date, groupName, totalUsage, material, remark] = newRecord;
  const areas = groupMap[groupName];
  if (!areas) return; // 未匹配到对应分组，终止执行
  
  const singleUsage = parseFloat(totalUsage) / areas.length;
  
  // 生成拆分后的单区域记录
  const rowsToAdd = areas.map(area => [
    date,
    area,
    singleUsage.toFixed(2), // 保留两位小数，可按需调整
    material,
    remark
  ]);
  
  // 将拆分结果写入目标工作表
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, rowsToAdd.length, rowsToAdd[0].length).setValues(rowsToAdd);
}

3. 设置自动触发规则 #

• 表单提交触发：在Apps脚本编辑器左侧点击「触发器」→「添加触发器」，选择函数splitGroupRecords，事件源选「表单提交」，保存即可。
• 手动录入触发：事件源选「从电子表格」，事件类型选「更改」或「编辑」，保存后每次录入新记录都会自动执行拆分。

注意事项 #

• 录入表的列顺序需与脚本中字段解析逻辑一致，若调整列顺序需同步修改脚本内的字段解构部分。
• 区域组配置表的「区域列表」需用英文逗号分隔区域号，避免多余空格或特殊字符。
• 总用量列需设置为数字格式，否则脚本会出现计算错误。

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