Google Apps Script导入CSV文件时随机超时异常解决咨询

针对你遇到的Service timed out: Spreadsheets随机超时问题，核心原因是Spreadsheet服务操作的资源占用或执行时长超限，结合你的代码，可通过以下优化手段解决：

核心优化点及代码修改 #

1. 修复全局变量问题，避免上下文混乱 #

原代码中sheet是全局变量，跨函数调用时易引发上下文冲突，改为局部变量并通过返回值传递：

// 修改writeDataToSheet，返回新建的sheet对象
function writeDataToSheet(data) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.insertSheet(); // 改为局部变量
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  return sheet; // 返回sheet供后续操作使用
}

// 修改OpenFiles函数，接收返回的sheet
function OpenFiles(files) {
  for (var i = 0; i < files.length; i++) { // 声明局部变量i
    var file = files[i];
    var contents = Utilities.parseCsv(file.getBlob().getDataAsString());
    var sheet = writeDataToSheet(contents); // 接收返回的sheet
    var sheetName = sheet.getRange("A10").getValue(); // 单个单元格用getValue()更高效
    sheet.setName(sheetName);
    // 每处理3个文件刷新一次，释放资源
    if (i % 3 === 0) {
      SpreadsheetApp.flush();
    }
  }
}

2. 添加超时重试机制 #

针对随机超时，在高风险操作（如setValues）周围添加重试逻辑：

function writeDataToSheet(data) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.insertSheet();
  var maxRetries = 3;
  var retryCount = 0;
  
  while (retryCount < maxRetries) {
    try {
      sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
      break; // 成功则退出循环
    } catch (e) {
      if (e.message.includes("Service timed out") && retryCount < maxRetries - 1) {
        retryCount++;
        Utilities.sleep(1000); // 重试前等待1秒，避免频繁请求
      } else {
        throw e; // 非超时异常或重试耗尽，抛出原错误
      }
    }
  }
  return sheet;
}

3. 分批处理超大CSV数据 #

若CSV行数极多（如超1万行），一次性写入易超时，拆分数据块分批写入：

function writeDataToSheet(data) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.insertSheet();
  var batchSize = 1000; // 每批写入1000行
  var totalRows = data.length;
  
  for (var startRow = 0; startRow < totalRows; startRow += batchSize) {
    var endRow = Math.min(startRow + batchSize, totalRows);
    var batchData = data.slice(startRow, endRow);
    sheet.getRange(startRow + 1, 1, batchData.length, batchData[0].length).setValues(batchData);
    SpreadsheetApp.flush(); // 每批写入后刷新资源
  }
  return sheet;
}

4. 改用Drive API直接导入CSV（高效替代方案） #

手动解析CSV再写入效率较低，可调用Drive API直接将CSV转换为Sheets工作表：
首先在脚本编辑器中启用Drive API（资源 > 高级Google服务 > 启用Drive API），然后修改代码：

function importCsvToSheets(file) {
  var ss = SpreadsheetApp.getActive();
  // 调用Drive API将CSV导入为临时工作表
  var resource = {
    title: file.getName(),
    parents: [{id: ss.getId()}],
    mimeType: "application/vnd.google-apps.spreadsheet"
  };
  var importedFile = Drive.Files.insert(resource, file.getBlob(), {convert: true});
  // 将临时工作表复制到当前Spreadsheet
  var importedSs = SpreadsheetApp.openById(importedFile.id);
  var importedSheet = importedSs.getSheets()[0];
  importedSheet.copyTo(ss);
  // 删除临时文件
  Drive.Files.remove(importedFile.id);
  // 重命名工作表
  var newSheet = ss.getSheets()[ss.getSheets().length - 1];
  var sheetName = newSheet.getRange("A10").getValue();
  newSheet.setName(sheetName);
}

// 修改OpenFiles函数
function OpenFiles(files) {
  for (var i = 0; i < files.length; i++) {
    importCsvToSheets(files[i]);
    if (i % 2 === 0) {
      SpreadsheetApp.flush();
      Utilities.sleep(500);
    }
  }
}

额外注意事项 #

• Google Apps Script单脚本执行时长上限为6分钟，若文件数量过多，可使用时间驱动触发器分批处理，比如每次处理5个文件，间隔10分钟执行。
• 避免在循环中频繁调用Spreadsheet服务的读写操作，尽量批量处理后一次性写入。

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