解决Google Sheets Apps Script处理6500+行时的执行超时问题

问题描述 #

我有一段从图片URL提取文本的代码，处理50行数据时运行正常，但处理6500+行数据时，大约执行到第200行就因执行超时停止。我尝试拆分数据后可正常运行，但期望在单个在线文件中完成全部7000行数据的处理。

原代码：

function validar() {
  var sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GetTextFromImage");
  var lrow = sh1.getLastRow();
  
  try {
    for(var i = 3;i<=lrow;i++){
    var url = sh1.getRange(i,7).getValue();
    var imageBlob = UrlFetchApp.fetch(url).getBlob();
    var resource = {
      title : imageBlob.getName(),
      mimType : imageBlob.getContentType(),

    }
    var options = {
    ocr : true
    }
    
    var docFile = Drive.Files.insert(resource,imageBlob,options);
    var doc = DocumentApp.openById(docFile.id);
    var text = doc.getBody().getText();
    var newText = text.replace(/ /g, "");
    var finalText = newText.replace(/\n/g,"")
       

    sh1.getRange(i,8).setValue(finalText);
  
   

    Drive.Files.remove(docFile.id);
    }
  } catch(error) {
  return;
}

核心优化思路 #

原代码的主要问题是每次循环单独读写单元格，频繁与服务器交互，加上Google Apps Script单次执行有6分钟时间限制，导致处理大量数据时超时。通过以下方式优化：

• 批量读写数据，大幅减少服务器交互次数
• 记录处理进度，超时后可从断点续接处理
• 修复代码拼写错误（mimType改为mimeType）
• 优化错误处理，单个行处理失败不中断整个脚本

修改后的代码 #

function processImageOCR() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GetTextFromImage");
  const lastRow = sheet.getLastRow();
  // 获取上次处理的行号，默认从第3行开始
  const startRow = PropertiesService.getUserProperties().getProperty('lastProcessedRow') || 3;
  // 每次批量处理的行数，可根据实际情况调整
  const batchSize = 50;
  const endRow = Math.min(parseInt(startRow) + batchSize - 1, lastRow);
  
  // 批量读取所有待处理的URL
  const urlRange = sheet.getRange(startRow, 7, endRow - startRow + 1, 1);
  const urls = urlRange.getValues();
  const results = [];

  for (let i = 0; i < urls.length; i++) {
    const currentRow = parseInt(startRow) + i;
    const url = urls[i][0];
    let finalText = "";

    try {
      if (!url) {
        results.push([""]);
        continue;
      }

      // 获取图片Blob并执行OCR
      const imageBlob = UrlFetchApp.fetch(url).getBlob();
      const resource = {
        title: imageBlob.getName(),
        mimeType: imageBlob.getContentType()
      };
      const options = { ocr: true };

      const docFile = Drive.Files.insert(resource, imageBlob, options);
      const doc = DocumentApp.openById(docFile.id);
      const text = doc.getBody().getText();
      // 清理文本格式
      finalText = text.replace(/ /g, "").replace(/\n/g, "");

      // 删除临时生成的文档
      Drive.Files.remove(docFile.id);
    } catch (error) {
      console.log(`第${currentRow}行处理失败: ${error.message}`);
      finalText = "处理失败";
    }

    results.push([finalText]);
  }

  // 批量写入处理结果
  if (results.length > 0) {
    const resultRange = sheet.getRange(startRow, 8, results.length, 1);
    resultRange.setValues(results);
  }

  // 更新处理进度
  if (endRow < lastRow) {
    PropertiesService.getUserProperties().setProperty('lastProcessedRow', endRow + 1);
  } else {
    // 全部处理完成，清除进度记录
    PropertiesService.getUserProperties().deleteProperty('lastProcessedRow');
    SpreadsheetApp.getUi().alert("所有数据处理完成！");
  }
}

// 重置处理进度（如需重新开始处理）
function resetProgress() {
  PropertiesService.getUserProperties().deleteProperty('lastProcessedRow');
  SpreadsheetApp.getUi().alert("进度已重置，将从第3行开始处理");
}

使用说明 #

1. 首次执行：直接运行processImageOCR函数，从第3行开始批量处理数据
2. 超时续接：脚本因超时停止后，再次运行processImageOCR会自动从上次中断的行号继续处理
3. 重置进度：如果需要从头开始处理，运行resetProgress函数即可
4. 自动执行（可选）：可创建时间驱动触发器（如每5分钟执行一次），让脚本自动续接处理，无需手动重复运行

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