JavaScript 如何仅将电子表格B中已修改的数据行追加到电子表格A

核心逻辑 #

针对从工作表B向工作表A增量追加数据的需求，核心判断逻辑如下：

• 取mcleod字段作为每行的唯一识别主键
• 首次运行（工作表A无数据）时直接写入所有B表有效行
• 非首次运行时，先将A表所有数据按主键生成快照映射，遍历B表数据时仅追加两类行：A表不存在对应主键的新行、主键存在但行内容发生修改的行
• 批量写入目标表，避免逐行追加的性能损耗

优化后可运行代码 #

// 数据源表（工作表B），请替换为实际表名
const datasheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("工作表B");
// 目标表（工作表A），请替换为实际表名
const currentsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("工作表A");

// 取B表所有有效数据（跳过表头行）
const pulledData = datasheet.getRange(2, 1, datasheet.getLastRow() - 1, datasheet.getLastColumn()).getValues();
// 待追加到A表的数组
const toAppend = [];

// 处理A表有数据的场景，生成主键-行快照的映射
const currentMap = new Map();
if (currentsheet.getLastRow() >= 2) {
  const currentData = currentsheet.getRange(2, 1, currentsheet.getLastRow() - 1, currentsheet.getLastColumn()).getValues();
  currentData.forEach(row => {
    // 按A表的字段顺序生成行快照
    const currentRowArr = [
      row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], 
      row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18]
    ];
    // 主键为第一个字段mcleod，值为行内容的序列化字符串
    currentMap.set(currentRowArr[0], JSON.stringify(currentRowArr));
  });
}

// 遍历B表数据，筛选需要追加的行
pulledData.forEach(row => {
  // 按字段映射规则生成和A表结构一致的行
  const pulledRowArr = [
    row[0], row[2], row[3], row[16], row[41], row[39], row[5], row[6], row[9],
    row[10], row[11], row[12], row[13], row[14], row[15], row[34], row[35], row[36], row[69]
  ];
  const rowKey = pulledRowArr[0];
  const rowSnapshot = JSON.stringify(pulledRowArr);
  // 主键不存在 或者 主键存在但内容有修改，就加入待追加数组
  if (!currentMap.has(rowKey) || currentMap.get(rowKey) !== rowSnapshot) {
    toAppend.push(pulledRowArr);
  }
});

// 批量写入到A表末尾
if (toAppend.length > 0) {
  currentsheet.getRange(
    currentsheet.getLastRow() + 1,
    1,
    toAppend.length,
    toAppend[0].length
  ).setValues(toAppend);
}

原参考代码问题说明 #

原有代码存在以下可优化点：

• 双重循环时间复杂度为O(n*m)，数据量大时性能极低，改为Map映射后时间复杂度降为O(n+m)
• 错误使用索引i[0]/j[0]进行对比，i和j是数组下标并非行数据本身
• modifiedData作用域定义错误，在循环内初始化导致每次循环都会重置数据
• 逐行调用appendRow接口，频繁调用Google Sheets接口会触发限流且运行速度慢

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