使用数组与For循环跨表格复制数据：重发仍未解决

问题描述 #

这段代码逻辑上看似可行，但执行时会超时，且在循环处理第一个目标表格后抛出错误：

Exception: Service Spreadsheets failed while accessing document with id "Y". CopyCmpstrs @ Code.gs:33

推测核心原因是数据集过大（176列、多行数据），导致操作第二个目标表格时出现冻结，此前找到的仅处理同表格内数据的方案不适用当前跨表格复制的场景。

原代码 #

function CopyCmpstrs() {
  var DashboardSSID = "X";

  var PaymentSSID = "Y";
  var SalesSSID = "Z";
  var FormsSSID = "A";
  var ProductSSID = "B";
  var InvoiceSSID = "C";

  var TargetSSID = [PaymentSSID,SalesSSID,FormsSSID,ProductSSID,InvoiceSSID];

  var ssSource = SpreadsheetApp.openById(DashboardSSID);

  var CmpstrsSheet = ssSource.getSheetByName("Cmpstrs");
  var CmpstrsSheetName = CmpstrsSheet.getName();

  var RowCountSource = CmpstrsSheet.getRange(1, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  var CustomerData = CmpstrsSheet.getSheetValues(1,1,RowCountSource,176);

  var ArraySize = TargetSSID.length;

for (var i=0; i<= ArraySize; i++){
  var TargetSS = SpreadsheetApp.openById(TargetSSID[i]);
  var TargetSheet = TargetSS.getSheetByName(CmpstrsSheetName);

  var MaxRow = TargetSheet.getMaxRows();
  var MaxCol = TargetSheet.getMaxColumns();

  var TargetFullRange = TargetSheet.getRange(1,1,MaxRow,MaxCol);
  TargetFullRange.clearContent();

  var TargetRange = TargetSheet.getRange(1,1,RowCountSource,176);
  TargetRange.setValues(CustomerData);
  }
}

优化方案 #

1. 修复循环边界错误 #

原代码循环条件i<=ArraySize会导致数组越界（数组索引从0开始，长度为5时i=5对应不存在的元素），这是触发报错的直接原因，需改为i<TargetSSID.length。

2. 优化数据清除逻辑 #

原代码清除整个表格的内容（包括大量空行空列），耗时且无必要，只需清除源数据对应的区域即可。

3. 分块处理大数据集 #

一次性写入大量数据容易超时，将数据分成小块分批写入，降低单次操作的资源占用。

4. 加入刷新机制 #

每次写入后调用SpreadsheetApp.flush()，确保当前操作完成后再进行下一步，避免缓存导致的异常。

优化后的代码 #

function CopyCmpstrs() {
  const DashboardSSID = "X";
  const TargetSSID = ["Y", "Z", "A", "B", "C"];
  const sheetName = "Cmpstrs";
  const batchSize = 500; // 每次写入500行，可根据实际数据量调整

  // 获取源数据
  const ssSource = SpreadsheetApp.openById(DashboardSSID);
  const sourceSheet = ssSource.getSheetByName(sheetName);
  const lastRow = sourceSheet.getRange(1, 2).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  const sourceData = sourceSheet.getSheetValues(1, 1, lastRow, 176);

  // 遍历目标表格
  for (let i = 0; i < TargetSSID.length; i++) {
    try {
      const targetSS = SpreadsheetApp.openById(TargetSSID[i]);
      const targetSheet = targetSS.getSheetByName(sheetName);
      if (!targetSheet) continue; // 目标表格不存在指定sheet时跳过

      // 仅清除源数据对应的区域
      targetSheet.getRange(1, 1, lastRow, 176).clearContent();

      // 分块写入数据
      for (let startRow = 0; startRow < sourceData.length; startRow += batchSize) {
        const endRow = Math.min(startRow + batchSize, sourceData.length);
        const batchData = sourceData.slice(startRow, endRow);
        targetSheet.getRange(startRow + 1, 1, batchData.length, 176).setValues(batchData);
        SpreadsheetApp.flush(); // 刷新确保写入完成
      }
    } catch (e) {
      console.error(`处理表格${TargetSSID[i]}时出错: ${e.message}`);
    }
  }
}

额外高效方案 #

如果目标表格的结构和源表格完全一致，直接复制整个sheet到目标表格再替换原sheet，比逐行写入效率更高：

// 示例：复制sheet到目标表格
const copiedSheet = sourceSheet.copyTo(targetSS);
copiedSheet.setName(sheetName);
targetSS.deleteSheet(targetSheet); // 删除原sheet

内容的提问来源于stack exchange，提问作者Adam of City Compost