求助：使用Google Apps Script跨工作表按唯一ID更新/新增行

源工作表中有工单列表，用户修改状态/操作字段并点击“提交”时，需通过A列工单编号在目标工作表中匹配：

• 匹配到则用源表数据更新目标表对应行
• 未匹配到则在目标表末尾新增条目

现有脚本仅支持同表格内的标签页同步，无法跨不同工作表运行，以下是修改后的可用方案：

原脚本（仅支持同表格内标签页） #

function UpdateFunction() {
  // 1. Retrieve values from the source and target sheets.
  var ss = SpreadsheetApp.getActive();
  var [srcSheet, targetSheet] = ['Source', 'Copy of Source'].map(s => ss.getSheetByName(s));
  var [srcValues, targetValues] = [[srcSheet, "A2:H"], [targetSheet, "A2:H"]].map(s => s[0].getLastRow() == 1 ? [] : s[0].getRange(s[1] + s[0].getLastRow()).getValues());

  // 2. Create objects for searching values of the column "A".
  var [srcObj, targetObj] = [srcValues, targetValues].map(e => e.reduce((o, [a, ...b]) => (o[a] = b, o), {}));

  // 3. Check update values at the target sheet.
  var updatedValues = targetValues.map(([a, ...b]) => [a, ...(srcObj[a] || b)]);
  // 4. Check append values.
  var appendValues = srcValues.reduce((ar, [a, ...b]) => {
    if (!targetObj[a]) ar.push([a, ...b]);
    return ar;
  }, []);

  // 5. Update the target sheet.
  var values = [...updatedValues, ...appendValues];
  targetSheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

修改后的跨工作表同步脚本 #

function UpdateCrossSheet() {
  // 1. 替换为你的源表、目标表ID及对应标签页名称
  const SRC_SPREADSHEET_ID = "你的源工作表ID";
  const SRC_SHEET_NAME = "Source";
  const TARGET_SPREADSHEET_ID = "你的目标工作表ID";
  const TARGET_SHEET_NAME = "Target";

  // 2. 获取跨表对象
  const srcSheet = SpreadsheetApp.openById(SRC_SPREADSHEET_ID).getSheetByName(SRC_SHEET_NAME);
  const targetSheet = SpreadsheetApp.openById(TARGET_SPREADSHEET_ID).getSheetByName(TARGET_SHEET_NAME);

  // 3. 读取表数据（跳过表头，A2:H列）
  const getSheetData = (sheet) => {
    const lastRow = sheet.getLastRow();
    return lastRow <= 1 ? [] : sheet.getRange(2, 1, lastRow - 1, 8).getValues();
  };
  const srcValues = getSheetData(srcSheet);
  const targetValues = getSheetData(targetSheet);

  // 4. 构建工单编号映射，快速匹配
  const buildIdMap = (data) => data.reduce((map, row) => {
    map[row[0]] = row.slice(1);
    return map;
  }, {});
  const srcIdMap = buildIdMap(srcValues);
  const targetIdMap = buildIdMap(targetValues);

  // 5. 更新目标表已有工单数据
  const updatedRows = targetValues.map(row => srcIdMap[row[0]] ? [row[0], ...srcIdMap[row[0]]] : row);

  // 6. 筛选源表新增工单
  const newRows = srcValues.filter(row => !targetIdMap[row[0]]);

  // 7. 写入目标表
  const allRows = [...updatedRows, ...newRows];
  if (allRows.length > 0) {
    targetSheet.getRange(2, 1, targetSheet.getLastRow() - 1, 8).clearContent();
    targetSheet.getRange(2, 1, allRows.length, allRows[0].length).setValues(allRows);
  }
}

关键修改说明 #

• 跨表访问：用SpreadsheetApp.openById()直接通过工作表ID打开目标表，替代原脚本仅能访问当前活跃表格的getActive()
• 数据读取优化：改用行列索引方式读取数据，避免原脚本字符串拼接导致的Range格式错误
• 空表兼容：增加空表（仅表头）处理逻辑，防止运行报错
• 逻辑拆分：将数据读取、映射创建拆分为独立函数，提升代码可读性

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