Google Apps Script跨表拷贝数据耗时3分钟，如何简化优化代码？

原代码核心问题 #

• 变量覆盖bug：所有复制操作复用了format/values等同名变量，最终粘贴的5个范围全是最后一个Outstanding(last month)的数据，前4个范围数据完全错误
• 文件名计算错误：curr_month + curr_year是数字加法，9+2021=2030，且getMonth()返回值为0-11（1月返回0），需要转字符串拼接才能得到你要的92021格式
• 无效范围读取：固定读取10000行的超大范围，大量空数据的读写会浪费数倍运行时间
• 服务调用次数过多：相同逻辑重复写10次，每次都重复调用SpreadsheetApp/DriveApp接口，GAS中跨服务调用是耗时的核心原因
• 函数名不匹配：newpaste里调用的copy()函数实际定义名为newcopy()，直接运行会报错

优化思路 #

1. 动态计算工作表实际数据行数，只读取有数据的范围，避免无效读写
2. 抽离范围复制粘贴的公共逻辑，减少重复代码和重复服务调用
3. 提前计算文件名，避免循环中重复创建Date对象
4. 用copyTo()方法批量复制范围的内容+格式，不用单独读取每个格式属性，减少至少80%的接口调用次数

优化后代码 #

// 预配置所有需要复制的范围规则
const COPY_RULES = [
  {sourceSheet: 'Budget', sourceRange: 'I7:J29', targetSheet: 'Budget', targetRange: 'I7:J29'},
  {sourceSheet: 'Billing(last month)', colStart: 'AA', colEnd: 'AH', targetSheet: 'Billing(last month)'},
  {sourceSheet: 'Billing(YE to last month)', colStart: 'AA', colEnd: 'AH', targetSheet: 'Billing(YE to last month)'},
  {sourceSheet: 'Outstanding(last month)', colStart: 'AA', colEnd: 'AN', targetSheet: 'Outstanding(last month)'},
  {sourceSheet: 'Outstanding(last month)', colStart: 'AA', colEnd: 'AN', targetSheet: 'Outstanding(Today)'}
]

// 单个文档复制逻辑
function newcopy(sourceSheetId, targetFolderId) {
  // 1. 计算文件名 修复之前的数字相加bug
  const d = new Date()
  const currMonth = d.getMonth() + 1 // 加1得到1-12的实际月份
  const currYear = d.getFullYear()
  const newFileName = `Billing ${currMonth}${currYear}`

  // 2. 复制原文档到目标文件夹
  const sourceFile = DriveApp.getFileById(sourceSheetId)
  const newFile = sourceFile.makeCopy(newFileName, DriveApp.getFolderById(targetFolderId))
  const targetSS = SpreadsheetApp.openById(newFile.getId())
  const sourceSS = SpreadsheetApp.openById(sourceSheetId)

  // 3. 批量复制所有范围
  COPY_RULES.forEach(rule => {
    const sourceSheet = sourceSS.getSheetByName(rule.sourceSheet)
    const targetSheet = targetSS.getSheetByName(rule.targetSheet)
    
    let sourceRange
    if (rule.sourceRange) {
      // 固定范围直接取
      sourceRange = sourceSheet.getRange(rule.sourceRange)
    } else {
      // 动态计算实际数据行数，只取有数据的范围
      const lastRow = sourceSheet.getLastRow()
      sourceRange = sourceSheet.getRange(`${rule.colStart}1:${rule.colEnd}${lastRow}`)
    }
    // 直接复制到目标范围，一次性带所有格式、数值，不用单独读每个属性
    sourceRange.copyTo(targetSheet.getRange(sourceRange.getA1Notation()), SpreadsheetApp.CopyPasteType.PASTE_ALL, false)
  })

  // 保存修改，避免缓存延迟
  SpreadsheetApp.flush()
}

// 批量执行10次
function batchCopy() {
  // 把你的10组原文档ID、目标文件夹ID按顺序放在这个数组里
  const taskList = [
    ["Sheet1_ID", "Folder1_ID"],
    ["Sheet2_ID", "Folder2_ID"],
    ["Sheet3_ID", "Folder3_ID"],
    ["Sheet4_ID", "Folder4_ID"],
    ["Sheet5_ID", "Folder5_ID"],
    ["Sheet6_ID", "Folder6_ID"],
    ["Sheet7_ID", "Folder7_ID"],
    ["Sheet8_ID", "Folder8_ID"],
    ["Sheet9_ID", "Folder9_ID"],
    ["Sheet10_ID", "Folder10_ID"]
  ]

  // 批量执行
  taskList.forEach(task => newcopy(task[0], task[1]))
}

额外性能提升建议 #

• 如果10个任务执行总时长超过6分钟触发GAS上限，可以把任务拆成多个触发器分批执行
• 可以开启V8运行时，代码执行速度比旧runtime快30%以上
• 不需要保留公式的话，可以把CopyPasteType改成PASTE_VALUES_AND_FORMATTING，跳过公式复制速度更快

内容的提问来源于stack exchange，提问作者Bobbie Kan HKMS