Google Apps Script如何优化复制标记行到非连续目标行的运行速度

API 探路者

2026-10-6

函数目标

TOSync值为TRUE的行，需根据源表中存储的rowid复制粘贴到目标表，作为复制源的当前工作表支持筛选和排序操作。

*源表（Source sheet）*包含取值为true/false的复选框，值为true即标记该行需要同步/复制到目标表。
Row ID对应目标表的行号。

触发函数后，需将所有标记为TRUE/勾选的行，复制到目标表对应的行索引位置。
以上图为例：

第一个标记行的Row ID为1741
函数将复制源表（trackerSheet）对应的数据范围
粘贴到目标表的第1741行

遇到的问题

当前数据量超过2000行，现有代码运行速度非常慢，逻辑为逐行遍历，将源表（trackersheet）数据逐行复制粘贴到目标表（mmics或bau sheet）。
目标表的写入行不是连续区间（如1-100、500-600），而是随机分散的行号（如1、3、8、54、798等）。
请问实现该需求的最快方案是什么？
现有代码如下：

function copyToDestination(){
/****   配置外部/目标表    ******/
    const toSyncDataRange   = trackerSheet.getRange(firstRowTrackerData,colToSync,dataRowsCount,1).getValues();
    const dataRowsCount    = lastRowTrackerData - firstRowTrackerData;
    const sourceSpreadSheet = SpreadsheetApp.openById(DataSourceSSId);  
    const BAUSheetName      = sheet.getRange("BAUSheetName").getValue();
    const MMICSSheetName    = sheet.getRange("MMICSSheetName").getValue();
    const mmicsSheet        = sourceSpreadSheet.getSheetByName(MMICSSheetName);
    const bauSheet          = sourceSpreadSheet.getSheetByName(BAUSheetName);


    //trackerSheet为源表

    let toUpdate = false;
    //遍历ToSync列检查TRUE值
    for (let i = 0; i < dataRowsCount; i++) {   //此处可优化
        if(toSyncDataRange[i][0] === true){     //如果toSync被勾选或为TRUE
            let currentRow = i + 1 + TrackerHeaderRow; //+1因为索引从0开始，trackerheaderrow为表头行
            
            //需要复制到目标表的数据范围
            const editableData      = trackerSheet.getRange(currentRow,colFirstEditableTracker,1,numColsTrackerData).getValues();

            //获取源表内的rowId
            const rowId     = trackerSheet.getRange(currentRow,colRowId).getValue();
            const source    = trackerSheet.getRange(currentRow,colSourceTracker).getValue(); 
            
            //如果B列source为ICS，使用MMICS表，否则用BAU表
            if(source === "ICS"){
              const mmics = mmicsSheet.getRange(rowId,1,1,numColsTrackerData);//.setValues(editableData);
              mmics.setDataValidation(null);
              mmics.setValues(editableData); //此处可优化
            }
            else if(source === "BAU"){
              const bau = bauSheet.getRange(rowId,1,1,numColsTrackerData);//.setValues(editableData);
              bau.setDataValidation(null);
              bau.setValues(editableData); //此处可优化

            }                   

            trackerSheet.getRange(currentRow,colLastUpdated,1,1).setValue("UPLOADED");  //此处可优化
            toUpdate = true;      
        }
    }

    if(toUpdate){             
        SpreadsheetApp.getActive().toast("变更已成功上传至原始源表...");
    }
}

最优实现方案

核心优化逻辑是最大化减少Google Apps Script的表格IO次数，GAS中读写表格的操作远慢于内存运算，原代码逐行读写的方式在数据量超过千行后必然卡顿，优化步骤如下：

一次性读取源表所有需要的列数据（同步标记、RowID、来源类型、待复制内容、更新标记列），全部在内存中处理筛选，避免循环内调用getRange
分别统计两个目标表需要写入的行号与对应内容，以及源表需要更新为UPLOADED的行位置，批量操作
针对分散行写入的场景，优先采用「读取目标表全量数据→内存覆盖对应行→一次性写回」的方案，性能比逐行写入高10~100倍
若需要清除目标行的数据验证规则，可通过RangeList批量选中所有目标行后一次性清除，无需逐行操作

优化后代码如下：

function copyToDestination(){
  // 配置参数（请确保原有全局变量如trackerSheet、firstRowTrackerData等已正确定义）
  const dataRowsCount = lastRowTrackerData - firstRowTrackerData;
  // 一次性读取源表所有需要的列：同步标记列、RowID列、来源列、待复制数据区域、最后更新列
  const sourceAllData = trackerSheet.getRange(
    firstRowTrackerData, 
    Math.min(colToSync, colRowId, colSourceTracker, colFirstEditableTracker, colLastUpdated),
    dataRowsCount,
    Math.max(colToSync, colRowId, colSourceTracker, colFirstEditableTracker + numColsTrackerData -1, colLastUpdated) - Math.min(colToSync, colRowId, colSourceTracker, colFirstEditableTracker, colLastUpdated) + 1
  ).getValues();
  // 计算各列在读取数组中的相对索引
  const colOffset = Math.min(colToSync, colRowId, colSourceTracker, colFirstEditableTracker, colLastUpdated);
  const idxToSync = colToSync - colOffset;
  const idxRowId = colRowId - colOffset;
  const idxSource = colSourceTracker - colOffset;
  const idxFirstEditable = colFirstEditableTracker - colOffset;
  const idxLastUpdated = colLastUpdated - colOffset;

  // 初始化待写入数据容器
  const mmicsUpdates = {}; // key: 目标行号, value: 待写入数据数组
  const bauUpdates = {};
  const sourceUpdateArr = sourceAllData.map(row => [row[idxLastUpdated]]); // 初始化源表更新列数组，保留原有值
  let hasUpdate = false;

  // 内存遍历筛选需要同步的行
  for (let i = 0; i < dataRowsCount; i++) {
    if (sourceAllData[i][idxToSync] !== true) continue;
    hasUpdate = true;
    const rowId = Number(sourceAllData[i][idxRowId]);
    const sourceType = sourceAllData[i][idxSource];
    const editableData = sourceAllData[i].slice(idxFirstEditable, idxFirstEditable + numColsTrackerData);
    // 存入对应目标表的更新容器
    if (sourceType === "ICS") mmicsUpdates[rowId] = editableData;
    else if (sourceType === "BAU") bauUpdates[rowId] = editableData;
    // 标记源表更新状态
    sourceUpdateArr[i][0] = "UPLOADED";
  }

  if (!hasUpdate) return;

  // 处理目标表写入
  const sourceSS = SpreadsheetApp.openById(DataSourceSSId);
  const bauSheetName = sheet.getRange("BAUSheetName").getValue();
  const mmicsSheetName = sheet.getRange("MMICSSheetName").getValue();
  
  // 通用写入函数
  const writeToTarget = (sheetName, updates) => {
    const sheet = sourceSS.getSheetByName(sheetName);
    if (!sheet || Object.keys(updates).length === 0) return;
    // 读取目标表所有数据
    const maxRow = Math.max(...Object.keys(updates).map(Number));
    const targetData = sheet.getRange(1, 1, maxRow, numColsTrackerData).getValues();
    const rangeList = [];
    // 内存覆盖对应行
    Object.entries(updates).forEach(([rowId, data]) => {
      const rowIdx = Number(rowId) - 1;
      targetData[rowIdx] = data;
      const colSuffix = SpreadsheetApp.getActiveSpreadsheet().getRange(1, numColsTrackerData).getA1Notation().replace(/\d+/g, '');
      rangeList.push(`A${rowId}:${colSuffix}${rowId}`);
    });
    // 一次性写回所有数据
    sheet.getRange(1,1, maxRow, numColsTrackerData).setValues(targetData);
    // 批量清除数据验证
    sheet.getRangeList(rangeList).setDataValidation(null);
  };

  writeToTarget(mmicsSheetName, mmicsUpdates);
  writeToTarget(bauSheetName, bauUpdates);

  // 一次性更新源表的最后更新列
  trackerSheet.getRange(firstRowTrackerData, colLastUpdated, dataRowsCount, 1).setValues(sourceUpdateArr);

  SpreadsheetApp.getActive().toast("变更已成功上传至原始源表...");
}

该优化方案在2000行数据的场景下，运行耗时可从原来的数十秒降低到2~3秒以内。

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