跨Google Sheets复制工作表遇阻，求完整格式复制方案

问题背景 #

• 跨文件使用copyTo()方法时触发报错：Exception: Target range and source range must be on the same spreadsheet
• 源文件体积7Mb，包含959个带格式图表的工作表，需拆分处理，但受限于Google Apps Script 6分钟运行时长限制
• 现有方案仅支持复制值或同文件内操作，自行编写的代码无法复制公式、边框，行高列高匹配存在问题，且授权流程繁琐

方法1：利用Sheets API批量更新实现完整复制（推荐） #

该方法可完整复制公式、边框、格式、行高列高及图表，效率更高，适合批量处理场景。

操作步骤 #

1. 在脚本编辑器中开启Google Sheets API：点击「资源」>「高级Google服务」，找到Sheets API并启用
2. 通过API获取源工作表的完整数据（含格式、公式、边框等所有属性）
3. 调用批量更新接口将数据写入目标文件

示例代码 #

function copySheetCrossFile(srcSpreadsheetId, srcSheetName, destSpreadsheetId) {
  // 获取源工作表基础信息
  const srcSheet = SpreadsheetApp.openById(srcSpreadsheetId).getSheetByName(srcSheetName);
  const srcSheetId = srcSheet.getSheetId();
  
  // 调用Sheets API拉取源工作表全量数据
  const srcData = Sheets.Spreadsheets.get(srcSpreadsheetId, {
    ranges: [srcSheetName],
    includeGridData: true,
    fields: "sheets(data,properties,merges,bandedRanges,conditionalFormats,filterViews)"
  });
  
  // 创建目标工作表
  const createSheetRequest = {
    addSheet: {
      properties: {
        title: srcSheetName,
        gridProperties: {
          rowCount: srcSheet.getMaxRows(),
          columnCount: srcSheet.getMaxColumns()
        }
      }
    }
  };
  const createResponse = Sheets.Spreadsheets.batchUpdate({requests: [createSheetRequest]}, destSpreadsheetId);
  const destSheetId = createResponse.replies[0].addSheet.properties.sheetId;
  
  // 组装批量更新请求
  const updateRequests = [];
  
  // 复制单元格数据、公式、格式
  srcData.sheets[0].data.forEach(gridData => {
    updateRequests.push({
      updateCells: {
        range: {
          sheetId: destSheetId,
          startRowIndex: gridData.startRowIndex,
          endRowIndex: gridData.endRowIndex,
          startColumnIndex: gridData.startColumnIndex,
          endColumnIndex: gridData.endColumnIndex
        },
        rows: gridData.rowData,
        fields: "userEnteredValue,userEnteredFormat,formattedValue,dataValidation,note,textFormatRuns"
      }
    });
  });
  
  // 复制合并单元格
  if (srcData.sheets[0].merges) {
    srcData.sheets[0].merges.forEach(merge => {
      updateRequests.push({
        mergeCells: {
          range: {
            sheetId: destSheetId,
            startRowIndex: merge.startRowIndex,
            endRowIndex: merge.endRowIndex,
            startColumnIndex: merge.startColumnIndex,
            endColumnIndex: merge.endColumnIndex
          },
          mergeType: "MERGE_ALL"
        }
      });
    });
  }
  
  // 复制带状格式（斑马线）
  if (srcData.sheets[0].bandedRanges) {
    srcData.sheets[0].bandedRanges.forEach(band => {
      band.range.sheetId = destSheetId;
      updateRequests.push({
        addBandedRange: {bandedRange: band}
      });
    });
  }
  
  // 复制条件格式
  if (srcData.sheets[0].conditionalFormats) {
    srcData.sheets[0].conditionalFormats.forEach(cf => {
      cf.ranges.forEach(range => range.sheetId = destSheetId);
      updateRequests.push({
        addConditionalFormatRule: {rule: cf}
      });
    });
  }
  
  // 复制列宽
  for (let i = 1; i <= srcSheet.getMaxColumns(); i++) {
    updateRequests.push({
      updateDimensionProperties: {
        range: {
          sheetId: destSheetId,
          dimension: "COLUMNS",
          startIndex: i-1,
          endIndex: i
        },
        properties: {pixelSize: srcSheet.getColumnWidth(i)},
        fields: "pixelSize"
      }
    });
  }
  
  // 复制行高
  for (let i = 1; i <= srcSheet.getMaxRows(); i++) {
    const rowHeight = srcSheet.getRowHeight(i);
    if (rowHeight > 0) {
      updateRequests.push({
        updateDimensionProperties: {
          range: {
            sheetId: destSheetId,
            dimension: "ROWS",
            startIndex: i-1,
            endIndex: i
          },
          properties: {pixelSize: rowHeight},
          fields: "pixelSize"
        }
      });
    }
  }
  
  // 执行批量更新
  Sheets.Spreadsheets.batchUpdate({requests: updateRequests}, destSpreadsheetId);
}

批量优化建议 #

• 分批次处理工作表：每次处理10-20个工作表后暂停，通过时间驱动触发器继续执行，避免超时
• 跳过空工作表，减少无效操作

方法2：改进原有脚本，补充缺失复制项 #

针对现有代码的不足，补充公式、边框复制逻辑，并修正行高列宽的复制范围。

改进后代码 #

function CopyTable(srcSheet, destSheet, copyRange){
  destSheet.clear();

  var srcRange = srcSheet.getRange(copyRange); 
  var destRange = destSheet.getRange(copyRange);

  // 复制公式
  var formulas = srcRange.getFormulas();
  destRange.setFormulas(formulas);

  // 复制边框
  var borders = srcRange.getBorders();
  destRange.setBorders(
    borders.top, borders.bottom, borders.left, borders.right,
    borders.vertical, borders.horizontal
  );

  // 原有格式复制逻辑保留
  var values = srcRange.getValues();
  var background = srcRange.getBackgrounds();
  var fontColor = srcRange.getFontColors();
  var fontFamily = srcRange.getFontFamilies();
  var fontLine = srcRange.getFontLines();
  var fontSize = srcRange.getFontSizes();
  var fontStyle = srcRange.getFontStyles();
  var fontWeight = srcRange.getFontWeights();
  var textStyle = srcRange.getTextStyles();
  var horAlign = srcRange.getHorizontalAlignments();
  var vertAlign = srcRange.getVerticalAlignments();
  var bandings = srcRange.getBandings();
  var mergedRanges = srcRange.getMergedRanges();

  destRange.setValues(values);
  destRange.setBackgrounds(background);
  destRange.setFontColors(fontColor);
  destRange.setFontFamilies(fontFamily);
  destRange.setFontLines(fontLine);
  destRange.setFontSizes(fontSize);
  destRange.setFontStyles(fontStyle);
  destRange.setFontWeights(fontWeight);
  destRange.setTextStyles(textStyle);
  destRange.setHorizontalAlignments(horAlign);
  destRange.setVerticalAlignments(vertAlign);

  // 复制富文本链接
  var sourceValues = srcRange.getRichTextValues();
  var targetValues = destRange.getRichTextValues();
  for (var i = 0; i < sourceValues.length; i++) {
    for (var j = 0; j < sourceValues[0].length; j++) {
      var sourceLinkUrl = sourceValues[i][j].getLinkUrl();
      
      if (sourceLinkUrl != null) {
        targetValues[i][j] = SpreadsheetApp.newRichTextValue()
          .setText(targetValues[i][j].getText())
          .setLinkUrl(sourceLinkUrl)
          .build();
      }
    }
  }
  
  // 复制带状格式
  for (let i in bandings){
    let srcBandA1 = bandings[i].getRange().getA1Notation();
    let destBandRange = destSheet.getRange(srcBandA1);

    destBandRange.applyRowBanding()
    .setFirstRowColor(bandings[i].getFirstRowColor())
    .setSecondRowColor(bandings[i].getSecondRowColor())
    .setHeaderRowColor(bandings[i].getHeaderRowColor())
    .setFooterRowColor(bandings[i].getFooterRowColor());
  }

  // 复制合并单元格
  for (let i = 0; i < mergedRanges.length; i++) {
    destSheet.getRange(mergedRanges[i].getA1Notation()).merge();
  }
 
  // 修正列宽复制范围
  var startCol = srcRange.getColumn();
  var numCols = srcRange.getWidth();
  for (let i = startCol; i < startCol + numCols; i++) {
    let width = srcSheet.getColumnWidth(i);
    destSheet.setColumnWidth(i, width);
  }
 
  // 修正行高复制范围
  var startRow = srcRange.getRow();
  var numRows = srcRange.getHeight();
  for (let i = startRow; i < startRow + numRows; i++){
    let height = srcSheet.getRowHeight(i);
    destSheet.setRowHeight(i, height);
  }
}

方法3：文件复制+删除多余工作表（适合批量拆分） #

若仅需拆分整个文件，可直接复制源文件后删除多余工作表，效率最高且能完整保留所有内容。

示例代码 #

function splitSpreadsheet(srcId, destFolderId, sheetsPerFile) {
  const srcSpreadsheet = SpreadsheetApp.openById(srcId);
  const sheets = srcSpreadsheet.getSheets();
  const totalSheets = sheets.length;
  
  for (let i = 0; i < totalSheets; i += sheetsPerFile) {
    // 复制整个源文件到目标文件夹
    const destFile = DriveApp.getFileById(srcId).makeCopy(`拆分文件_${Math.floor(i/sheetsPerFile)+1}`, DriveApp.getFolderById(destFolderId));
    const destSpreadsheet = SpreadsheetApp.openById(destFile.getId());
    const destSheets = destSpreadsheet.getSheets();
    
    // 删除不需要的工作表
    const keepIndices = Array.from({length: sheetsPerFile}, (_, k) => i + k).filter(idx => idx < totalSheets);
    destSheets.forEach((sheet, idx) => {
      if (!keepIndices.includes(idx)) {
        destSpreadsheet.deleteSheet(sheet);
      }
    });
  }
}

优点 #

• 完整保留所有格式、公式、图表，无需逐个复制
• 操作简单，效率远高于单个工作表复制
• 仅需一次授权，流程简化

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