如何用脚本从Staff工作表动态列表更新PatternToApply静态列表？

需求说明 #

• 源工作表Staff（动态更新的人员列表）与目标工作表PatternToApply（静态列表）需实现以下同步逻辑：
  • 源表新增名称时，复制到目标表并在对应行下方插入一行空行
  • 目标表存在但源表没有的名称，删除对应行及下方的空行

原代码问题分析 #

1. 错误使用源表行数读取目标表数据，导致目标表数据读取不完整或越界
2. 按行索引直接对比内容，未考虑源表与目标表名称顺序可能不一致的情况
3. 二维数组直接用==比较，未正确提取单元格值（需取data[i][0]）
4. 插入行方法insertRowAfter()缺少指定行参数，存在未定义变量使用的语法错误
5. 删除逻辑未处理行索引偏移问题，批量删除时会导致后续行定位错误

修正后的脚本 #

function UpdateAgList() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('Staff');
  const targetSheet = ss.getSheetByName('PatternToApply');

  // 获取源表非空名称集合（跳过表头）
  const sourceData = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, 1).getValues()
    .flat()
    .filter(name => name !== '');
  const sourceNames = new Set(sourceData);

  // 获取目标表现有数据（跳过表头）
  const targetLastRow = targetSheet.getLastRow();
  const targetRange = targetSheet.getRange(2, 1, targetLastRow - 1, 1);
  const targetData = targetRange.getValues();
  const startTargetRow = targetRange.getRow();

  // 1. 批量删除目标表中不存在于源表的名称行及对应空行
  const rowsToDelete = [];
  for (let i = 0; i < targetData.length; i++) {
    const name = targetData[i][0];
    if (name !== '' && !sourceNames.has(name)) {
      // 标记名称行
      rowsToDelete.push(startTargetRow + i);
      // 标记名称下方的空行（如果存在）
      if (i + 1 < targetData.length && targetData[i + 1][0] === '') {
        rowsToDelete.push(startTargetRow + i + 1);
        i++; // 跳过已标记的空行
      }
    }
  }
  // 倒序删除避免索引偏移
  rowsToDelete.sort((a, b) => b - a).forEach(row => targetSheet.deleteRow(row));

  // 2. 批量添加源表新增的名称
  const currentTargetNames = new Set(
    targetSheet.getRange(2, 1, targetSheet.getLastRow() - 1, 1).getValues()
      .flat()
      .filter(name => name !== '')
  );
  const namesToAdd = sourceData.filter(name => !currentTargetNames.has(name));

  namesToAdd.forEach(name => {
    const newRow = targetSheet.getLastRow() + 1;
    targetSheet.getRange(newRow, 1).setValue(name);
    // 在新名称行下方插入空行
    targetSheet.insertRowAfter(newRow);
  });
}

脚本关键说明 #

• 使用Set存储名称，实现高效的存在性检查，提升同步效率
• 删除操作采用倒序执行，避免删除行导致后续行索引错乱
• 保留目标表原有格式：每个名称行下方对应一行空行
• 自动过滤空值与表头，只处理有效数据行
• 支持源表与目标表名称顺序不一致的场景

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