如何在Google Apps Script中复制单元格格式和值而非公式并解决超时

我有一个包含约70个工作表的Google表格文件，需要为每个工作表生成同名的独立文件，且仅复制单元格的格式和值（而非公式）。参考现有方案写的脚本因超出最大执行时间无法完成，请问有解决办法吗？

原脚本代码：

function copyEntireSpreadsheet() {
  var id = "###"; // Please set the source Spreadsheet ID.

  var ss = SpreadsheetApp.openById(id);
  var srcSheets = ss.getSheets();
  var tempSheets = srcSheets.map(function(sheet, i) {
    var sheetName = sheet.getSheetName();
    var dstSheet = sheet.copyTo(ss).setName(sheetName + "_temp");
    var src = dstSheet.getDataRange();
    src.copyTo(src, {contentsOnly: true});
    return dstSheet;
  });
  var destination = ss.copy(ss.getName() + " - " + new Date().toLocaleString());
  tempSheets.forEach(function(sheet) {ss.deleteSheet(sheet)});
  var dstSheets = destination.getSheets();
  dstSheets.forEach(function(sheet) {
    var sheetName = sheet.getSheetName();
    if (sheetName.indexOf("_temp") == -1) {
      destination.deleteSheet(sheet);
    } else {
      sheet.setName(sheetName.slice(0, -5));
    }
  });
}

原脚本的核心瓶颈在于一次性处理所有70个工作表，且先在原文件内创建大量临时表再整体复制，导致执行时间超限。以下是两种针对性优化方案：

方案1：分批次为每个工作表生成独立文件（推荐） #

直接为每个源工作表单独创建新文件，只保留值和格式，避免一次性操作大量表。同时加入分批次处理逻辑，支持断点续跑，避免超时。

function copySheetsToIndividualFiles() {
  var sourceId = "###"; // 替换为源表格ID
  var sourceSs = SpreadsheetApp.openById(sourceId);
  var sheets = sourceSs.getSheets();
  
  // 从PropertiesService获取已处理的工作表索引，支持断点续跑
  var properties = PropertiesService.getScriptProperties();
  var processedIndex = parseInt(properties.getProperty("processedIndex")) || 0;
  
  // 每次处理5个工作表，可根据实际情况调整数量
  var batchSize = 5;
  var endIndex = Math.min(processedIndex + batchSize, sheets.length);
  
  for (var i = processedIndex; i < endIndex; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getSheetName();
    
    // 创建新的空白表格
    var newSs = SpreadsheetApp.create(sheetName);
    var newSheet = newSs.getActiveSheet();
    
    // 复制源工作表的格式和值到新表格
    sheet.getDataRange().copyTo(newSheet.getRange(1,1), {
      formatOnly: false,
      contentsOnly: true,
      copyFormat: true
    });
    
    // 重命名新表格的工作表（可选，确保和源表同名）
    newSheet.setName(sheetName);
    
    SpreadsheetApp.flush(); // 强制刷新操作，避免缓存堆积
  }
  
  // 更新已处理索引
  if (endIndex < sheets.length) {
    properties.setProperty("processedIndex", endIndex);
    SpreadsheetApp.getUi().alert(`已完成${endIndex}/${sheets.length}个工作表处理，请再次运行脚本继续`);
  } else {
    properties.deleteProperty("processedIndex");
    SpreadsheetApp.getUi().alert("所有工作表已处理完成");
  }
}

方案优势： #

• 单次仅处理少量工作表，大幅降低单轮执行时间，避免超时
• 支持断点续跑，即使中途中断也能从上次进度继续
• 每个工作表独立生成文件，更符合需求场景

方案2：优化原逻辑生成单份包含所有值的表格 #

如果你的需求是生成一份包含所有工作表值和格式的表格（而非独立文件），可优化原脚本的临时表处理逻辑，减少不必要的操作：

function copySpreadsheetWithValuesOnly() {
  var sourceId = "###"; // 替换为源表格ID
  var sourceSs = SpreadsheetApp.openById(sourceId);
  var tempSs = SpreadsheetApp.create("Temp_Sheet_Values");
  
  sourceSs.getSheets().forEach(function(sheet) {
    var copiedSheet = sheet.copyTo(tempSs);
    var dataRange = copiedSheet.getDataRange();
    // 直接复制值和格式，替换公式
    dataRange.copyTo(dataRange, {contentsOnly: true, copyFormat: true});
    SpreadsheetApp.flush();
  });
  
  // 重命名最终表格并删除临时表标识
  var finalName = sourceSs.getName() + " - " + new Date().toLocaleString();
  DriveApp.getFileById(tempSs.getId()).setName(finalName);
  
  SpreadsheetApp.getUi().alert(`已生成仅含值的表格：${finalName}`);
}

方案优势： #

• 避免在源表格内创建临时表，减少对源文件的操作
• 直接在临时表格内处理所有工作表，逻辑更简洁

• 调整batchSize参数：根据每个工作表的大小（行数/列数）调整单次处理的工作表数量，表格越大，batchSize应越小
• 关闭不必要的通知：如果不需要弹窗提示，可移除SpreadsheetApp.getUi().alert相关代码
• 避免高峰时段执行：Google Apps Script在高峰时段可能存在资源限制，尽量在非高峰时段运行脚本

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