AppScript需求：基于Name字段同步源表到目标表并更新变更

问题描述 #

我有一段将source_sheet数据复制到target_sheet的AppScript，涉及Team、Name、Age、Score四列，期望以Name作为唯一标识符检测数据变更并同步，但当前脚本仅能新增行，无法更新已有数据（如Score字段修改后不生效）。

原代码：

function copy_rows() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("source_sheet");
  const targetSheet = ss.getSheetByName('target_sheet');
  const targetLastRow = getColumnHeight(9, targetSheet, ss);
  let lastRow = sourceSheet.getLastRow();
  let sourceRange = sourceSheet.getRange(2, 3, lastRow, 4);

  Logger.log(lastRow);
  let targetCounter = 1;

  let targetColumnUniqueIdentifier = targetSheet.getRange(1, 9, targetLastRow + 1, 1).getValues();
  let existingValuesInTargetSheet = targetColumnUniqueIdentifier.flat();

  for (var i = 0; i < sourceRange.getValues().length; i++) {
    let rowValues = sourceRange.getValues()[i];
    let id = rowValues[1]; // Unique identifier is in 1st array

    if (existingValuesInTargetSheet.indexOf(id) === -1) {
      targetSheet.getRange(targetLastRow + targetCounter, 8, 1, 4).setValues([rowValues]);
      targetCounter++;
      existingValuesInTargetSheet.push(id);
    } else {
      // Check for updates in Columns C, E, and F
      let sourceID = rowValues[1]; // Get the source ID from column 2
      let targetID = rowValues[3]; // Get the target ID from column 4
      let sourceValues = sourceSheet.getRange(i + 2, 3, 1, 3).getValues()[0]; // Get the values in Columns C, E, and F from the source sheet
      let targetValues = targetSheet.getRange(targetLastRow + targetCounter, 8, 1, 3).getValues()[0]; // Get the values in Columns H, J, and K from the target sheet

      // Compare source and target values and update target sheet if necessary
      if (JSON.stringify(sourceValues) !== JSON.stringify(targetValues)) {
        targetSheet.getRange(targetLastRow + targetCounter, 8, 1, 3).setValues([sourceValues]);
      }
    }
  }
}

问题分析 #

• 目标行定位错误：更新逻辑中用targetLastRow + targetCounter定位目标行，这个位置是新增行的起始位置，并非已有Name对应的行，导致永远找不到需要更新的记录。
• API调用冗余：循环内多次调用getRange()和getValues()，不仅降低执行效率，还容易因上下文错误导致数据读取偏差。
• 数据匹配逻辑混乱：targetID = rowValues[3]属于无效逻辑，未从目标表中匹配对应Name的行数据，无法进行有效对比。

修复后的代码 #

function syncData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("source_sheet");
  const targetSheet = ss.getSheetByName("target_sheet");

  // 批量读取源表数据（第2行开始，C-F列：Team、Name、Age、Score）
  const sourceData = sourceSheet.getRange(2, 3, sourceSheet.getLastRow() - 1, 4).getValues();
  // 批量读取目标表全量数据
  const targetData = targetSheet.getDataRange().getValues();
  // 建立Name到目标行号的映射（目标表Name在第9列，对应数组索引8）
  const nameToRowMap = {};
  targetData.forEach((row, index) => {
    const name = row[8];
    if (name) nameToRowMap[name] = index + 1; // 行号从1开始计数
  });

  let targetLastRow = targetSheet.getLastRow();

  sourceData.forEach(row => {
    const [team, name, age, score] = row;
    if (!name) return; // 跳过空Name的无效行

    if (nameToRowMap[name]) {
      // 存在对应Name，检查字段是否需要更新
      const targetRow = nameToRowMap[name];
      const targetRowData = targetSheet.getRange(targetRow, 8, 1, 4).getValues()[0];
      // 对比Team、Age、Score字段
      if (targetRowData[0] !== team || targetRowData[2] !== age || targetRowData[3] !== score) {
        targetSheet.getRange(targetRow, 8, 1, 4).setValues([row]);
      }
    } else {
      // 无对应Name，新增行
      targetLastRow++;
      targetSheet.getRange(targetLastRow, 8, 1, 4).setValues([row]);
      nameToRowMap[name] = targetLastRow;
    }
  });
}

// 补充原脚本依赖的getColumnHeight函数（处理空行场景）
function getColumnHeight(col, sheet, ss) {
  const range = sheet.getRange(1, col, sheet.getMaxRows(), 1);
  const values = range.getValues();
  for (let i = values.length - 1; i >= 0; i--) {
    if (values[i][0] !== "") return i + 1;
  }
  return 0;
}

关键改动说明 #

1. 批量数据读取：一次性读取源表和目标表的全量数据，减少API调用次数，提升执行效率。
2. Name映射表：通过nameToRowMap快速定位目标表中对应Name的行号，避免低效的循环查找。
3. 精准更新逻辑：找到目标行后直接对比核心字段，有差异则更新整行数据。
4. 空行过滤：跳过源表中Name为空的行，避免无效数据同步。

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