App Script跨表格导入数据时触发Spreadsheets服务错误

核心问题根源 #

你遇到的Exception: Service error: Spreadsheets主要是因为循环中多次调用getRange()和getValues()触发了Google Apps Script的服务调用限制——Google对Spreadsheet服务的调用频率和次数有配额，循环逐行读取数据会快速耗尽配额，导致服务报错。此外，代码还存在变量未声明、数据范围处理不严谨的问题。

修复后的完整代码 #

const pasteSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const uploadFolder = DriveApp.getFolderById('someID');
obtainAndImportData(uploadFolder);

function obtainAndImportData(uploadFolder) {
  try {
    const internalFiles = uploadFolder.getFiles();
    while (internalFiles.hasNext()) {
      const file = internalFiles.next();
      const fileID = file.getId();
      const copySheet = SpreadsheetApp.openById(fileID).getSheets()[0];
      
      // 获取源表有效行数（从第3行开始取数据）
      const lastRow = copySheet.getLastRow();
      if (lastRow < 3) continue; // 没有可导入的数据时跳过
      
      // 一次性读取B3:P[lastRow]的所有数据，避免循环调用
      const allRows = copySheet.getRange(`B3:P${lastRow}`).getValues();
      
      // 获取目标表的起始写入行
      const targetStartRow = pasteSheet.getLastRow() + 1;
      if (allRows.length === 0) continue;
      
      // 写入数据：确保行列数匹配
      const targetRange = pasteSheet.getRange(targetStartRow, 1, allRows.length, allRows[0].length);
      targetRange.setValues(allRows);
    }
  } catch (err) {
    console.error('导入失败:', err.message);
  }
}

关键优化点 #

• 批量读取数据：替换循环逐行读取为一次性读取B3:P[lastRow]的所有数据，把Spreadsheet服务调用从N次减少到1次，彻底避免服务配额触发的错误。
• 严谨的边界处理：检查源表是否有足够的数据（lastRow < 3时跳过），避免读取空范围导致的异常；写入前检查allRows是否为空，防止调用allRows[0].length时报错。
• 变量规范：所有变量添加const/let声明，避免全局变量污染，提升代码稳定性。
• 错误捕获优化：合并外层和内层的try-catch，统一捕获并打印错误信息，便于排查问题。

额外排查建议 #

如果修复后仍报错，检查以下几点：

• 源文件（xlsx转Google Sheets）是否存在合并单元格、隐藏行/列：这类格式问题可能导致getLastRow()获取的行数不准确，建议先清理源表格式，确保数据区域连续。
• 权限验证：确认脚本拥有源文件和目标文件的编辑权限，若源文件是共享文件，需确保当前账号有编辑权限。

内容的提问来源于stack exchange，提问作者Rothman Mariño