Google Apps Script优化：仅参考单元格为空时更新目标表格

问题背景 #

现有跨表格更新数据的Google Apps Script会全量更新所有匹配单元格，哪怕数据无变化，导致运行卡顿（需更新约5万个单元格）。需要调整逻辑：仅当源表B-G列对应单元格为空时，才将I-N列的对应数据同步到目标表格（例如B2有值则跳过I2，C2为空则同步J2）。

原代码 #

function updateShadowSKU() {
  var source      = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var destRange   = SpreadsheetApp.openById('104t29KUBGvglxmU-ILN0ntiNnZDkAkA4Lkm5jRoLJxk').getSheetByName('Sheet1').getDataRange();
  var destValues  = destRange.getValues();
  var destHeaders = destValues[0];
  var destIds     = destValues.map(e=>e[0]);
  var values      = source.getDataRange().getValues().map(e=>e.filter((f,i)=>!i||i>8));
  var colMap      = values[0].map(e=>destHeaders.indexOf(e));
  Logger.log(colMap);
  values = values.map((e,i,arr)=>e.map((f,j)=>[e[0],colMap[j],f,i,j])).flat().filter(e=>e[0]&&e[1]&&e[2]&&e[3]&&e[4]);
  Logger.log(values);
  if(!values.length){Logger.log('No Changes to make');return}
  values = values.map(e=>[destIds.indexOf(e[0]),e[1],e[2]]);
  
  Logger.log(values.length +' changes to make');
  Logger.log(values);

  values.forEach(function (e){try{destValues[e[0]][e[1]]=e[2]}catch(err){}});
  destRange.setValues(destValues);
}

优化后代码 #

function updateShadowSKU() {
  var source = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var destSheet = SpreadsheetApp.openById('104t29KUBGvglxmU-ILN0ntiNnZDkAkA4Lkm5jRoLJxk').getSheetByName('Sheet1');
  var destRange = destSheet.getDataRange();
  var destValues = destRange.getValues();
  var destHeaders = destValues[0];
  var destIds = destValues.map(row => row[0]);
  
  // 获取源表完整数据：保留B-G列（标记列）和I-N列（待更新列）
  var sourceData = source.getDataRange().getValues();
  // 待更新列（I-N）的标题，用于匹配目标表列
  var updateHeaders = sourceData[0].slice(8, 14);
  // 建立源表头到目标表列索引的映射
  var colMap = updateHeaders.map(header => destHeaders.indexOf(header));

  var updateQueue = [];

  // 遍历源表数据（跳过表头）
  for (let rowIdx = 1; rowIdx < sourceData.length; rowIdx++) {
    var row = sourceData[rowIdx];
    var skuId = row[0];
    if (!skuId) continue; // 跳过无ID的行

    // 找到目标表中对应的行索引
    var destRowIdx = destIds.indexOf(skuId);
    if (destRowIdx === -1) continue; // 目标表无对应ID，跳过

    // 遍历B-G列（索引1-6），对应I-N列（索引8-13）
    for (let flagCol = 1; flagCol <= 6; flagCol++) {
      // 标记列为空时，才更新对应的数据列
      if (!row[flagCol]) {
        var dataColIdx = 8 + (flagCol - 1); // 对应I-N列的索引
        var targetColIdx = colMap[flagCol - 1]; // 目标表中对应的列索引
        
        // 检查目标列是否存在，且数据有变化（可选优化）
        if (targetColIdx !== -1 && row[dataColIdx] !== destValues[destRowIdx][targetColIdx]) {
          updateQueue.push([destRowIdx, targetColIdx, row[dataColIdx]]);
        }
      }
    }
  }

  if (updateQueue.length === 0) {
    Logger.log('No Changes to make');
    return;
  }

  Logger.log(`${updateQueue.length} changes to make`);
  Logger.log(updateQueue);

  // 批量更新目标表数据
  updateQueue.forEach(item => {
    try {
      destValues[item[0]][item[1]] = item[2];
    } catch (err) {
      Logger.log(`Update failed for row ${item[0]}, column ${item[1]}: ${err}`);
    }
  });

  destRange.setValues(destValues);
}

关键优化点 #

• 条件过滤逻辑：新增对B-G标记列的判断，仅当标记为空时才处理对应I-N列的数据，避免无效更新
• 数据变化校验：增加源表与目标表数据对比，仅当数据实际不同时才加入更新队列（可根据需求移除）
• 清晰的索引映射：明确标记列与待更新列的对应关系，避免逻辑混乱
• 错误日志优化：新增错误详情日志，便于排查更新失败的情况

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