如何在Google Sheets中合并具有相同资产ID的两行数据

核心功能 #

• 表单提交新交易记录后自动校验资产ID是否重复
• 相同资产ID自动合并数据，符合以下规则：
  • 用最新交易的日期覆盖原有记录的购买日期
  • 资产ID保持不变
  • 最新交易的持仓数量累加到原有记录的总持仓中
  • 自动删除末尾的重复提交记录

完整可运行代码 #

function mergeDuplicateAssets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formSS = ss.getSheetByName("Portfolio");
  const lastRow = formSS.getLastRow();
  // 数据从第2行开始，表头在第1行
  if (lastRow < 2) return; // 无有效数据直接退出

  // 读取最后一行的新提交数据
  const newRecord = formSS.getRange(lastRow, 1, 1, 3).getValues()[0];
  const newAssetId = newRecord[1]; // 第2列是资产ID
  const newDate = newRecord[0]; // 第1列是购买日期
  const newAmount = newRecord[2]; // 第3列是持仓数量

  // 读取之前所有行的资产ID列（B列）
  const existingIdsRange = formSS.getRange(2, 2, lastRow - 2, 1);
  const existingIds = existingIdsRange.getValues().flat();

  // 查找是否有重复的资产ID
  const duplicateRowIndex = existingIds.indexOf(newAssetId);
  if (duplicateRowIndex === -1) return; // 无重复直接退出

  // 原有重复行的实际行号（数组从0开始，加上表头1行，加上起始行偏移）
  const targetRow = duplicateRowIndex + 2;

  // 合并数据：更新日期
  formSS.getRange(targetRow, 1).setValue(newDate);
  // 合并持仓数量
  const originAmount = formSS.getRange(targetRow, 3).getValue();
  formSS.getRange(targetRow, 3).setValue(originAmount + newAmount);
  // 删除末尾的重复记录
  formSS.deleteRow(lastRow);
}

部署设置 #

• 打开你的Google表格，点击顶部菜单「扩展程序」-「Apps Script」进入脚本编辑器
• 删除编辑器默认的代码，粘贴上述代码
• 点击保存按钮，为项目命名（比如「投资组合合并工具」）
• 设置自动触发：点击编辑器左侧的「触发器」按钮，点击「添加触发器」
  • 选择要运行的函数：mergeDuplicateAssets
  • 选择事件来源：「电子表格」
  • 选择事件类型：「表单提交时」
  • 保存后按照提示授权脚本访问表格的权限即可

注意事项 #

• 确保你的工作表名称为Portfolio，如果名称不同需要修改代码中getSheetByName的参数
• 确保表格的第1列是购买日期、第2列是资产ID、第3列是持仓数量，列顺序不对可自行调整代码中对应的索引值

内容的提问来源于stack exchange，提问作者Osman P.