Google Apps Script报错：访问外部Spreadsheet时服务超时求助

手动运行Google Apps Script脚本时频繁触发报错：Exception: Service Spreadsheets timed out while accessing document with id，错误出现在代码中两个console.log语句之间。已尝试添加SpreadsheetApp.flush()但问题仍未解决，脚本核心需求是访问外部电子表格收集数据并发送邮件。

报错代码 #

function Email(linenumber) {
  // Fonction qui email la donnée de la ligne linenumber de l'onglet d'Email

  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();


  // Obtenir les informations depuis l'onglet "Email" sur le travail à mener
  var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");
  var emailData = emailSheet.getDataRange().getValues();

  // Récupère le header de l'onglet Email
  headsEmailData = emailData[0];

  // Récupère les indexs des colonnes de l'onglet Email
  var idxObjectEmail = headsEmailData.indexOf("objectEmail");
  var idxSheetUrl = headsEmailData.indexOf("sheetUrl");
  var idxTablesEmail = headsEmailData.indexOf("tablesEmail");
  var idxAttachmentsEmail = headsEmailData.indexOf("attachmentsEmail");
  var idxAttachmentsType = headsEmailData.indexOf("attachmentsType");
  var idxBodyEmail = headsEmailData.indexOf("bodyEmail");
  var idxRecipientsEmail = headsEmailData.indexOf("recipientsEmail");
  var idxNoreplyEmail = headsEmailData.indexOf("noreplyEmail");
  var idxLastEmailDate = headsEmailData.indexOf("lastEmailDate");
  var idxLastEmailLength = headsEmailData.indexOf("lastEmailLength");
  var idxStartHour = headsEmailData.indexOf("startHour");
  var idxEndHour = headsEmailData.indexOf("endHour");
  var idxReplyTo = headsEmailData.indexOf("replyTo");



  // Parcourir la ligne linenumber ligne dans l'onglet "Email"
  var objectEmail = emailData[linenumber][idxObjectEmail];
  var sheetUrl = emailData[linenumber][idxSheetUrl];
  var attachmentsType = emailData[linenumber][idxAttachmentsType];
  var bodyEmail = emailData[linenumber][idxBodyEmail];
  var recipientsEmail = emailData[linenumber][idxRecipientsEmail];
  var noreplyEmail = emailData[linenumber][idxNoreplyEmail];
  var attachmentsEmail = emailData[linenumber][idxAttachmentsEmail].split(",");
  var tablesEmail = emailData[linenumber][idxTablesEmail].split(",");
  var replyTo = emailData[linenumber][idxReplyTo];

  // Extraire l'ID de la Google Sheet à partir du lien
  var sourceSpreadsheetId = extractSpreadsheetId(sheetUrl);
  var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);

  // Initialize email body
  var bodyEmail = bodyEmail + "<br>";

  
  //Ajout de emailOptions : Une variable emailOptions est créée pour stocker les options de l'email, avec htmlBody initialisé. Condition if pour noreplyEmail : Si noreplyEmail est égal à "TRUE" (indépendamment de la casse), noReply est ajouté à emailOptions. Appel de GmailApp.sendEmail : La fonction est appelée avec emailOptions comme argument.

  var emailOptions = { htmlBody: bodyEmail, attachments: [] };

  // Mise en place du no reply
  if (noreplyEmail.toString().toUpperCase() === "TRUE") {
    emailOptions.noReply = true;
  }

  if (replyTo) {
    emailOptions.replyTo = replyTo;
  }

  // ajout des PJ
  if (attachmentsEmail!=''){
    for (var j = 0; j < attachmentsEmail.length; j++) {
    var fileSheetName = attachmentsEmail[j].trim();
    var fileSheet = sourceSpreadsheet.getSheetByName(fileSheetName);

    // Vérifie la présence de l'onglet source
    if(fileSheet==null){
    SpreadsheetApp.getUi().alert("Un onglet de attachmentsEmail n'est pas présent dans le fichier source : "+fileSheet, SpreadsheetApp.getUi().ButtonSet.OK);
    return;
    }
    else {
        //Vérifie la présence de l'onglet
        var sheetTemp = sourceSpreadsheet.getSheetByName(fileSheetName+"_temp");
        if (!sheetTemp) {
          sheetTemp=sourceSpreadsheet.insertSheet(fileSheetName+"_temp");        
        }
console.log(fileSheetName+" vérification de la présence de l'onglet");
        SpreadsheetApp.flush();
        var totalRows = fileSheet.getLastRow();
        SpreadsheetApp.flush();
        var totalCols = fileSheet.getLastColumn();
        SpreadsheetApp.flush();
        var sourceRange = fileSheet.getRange(1,1,totalRows,totalCols);
        SpreadsheetApp.flush();
console.log(fileSheetName+" range récupéré");
        var targetRange = sheetTemp.getRange(1,1);
        sourceRange.copyTo(targetRange);
        sourceRange.copyTo(targetRange,SpreadsheetApp.CopyPasteType.PASTE_COLUMN_WIDTHS,false);
        sourceRange.copyTo(targetRange,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
        SpreadsheetApp.flush();
        var url = "https://docs.google.com/spreadsheets/d/"+sourceSpreadsheetId+"/export"+"?format=xlsx"+"&gid="+sheetTemp.getSheetId();                                      
        var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
        var blob = UrlFetchApp.fetch(url, params).getBlob().setName(fileSheetName+".xlsx");
        emailOptions.attachments.push(blob);
        sourceSpreadsheet.deleteSheet(sheetTemp);
    }
    }
  }


  GmailApp.sendEmail(recipientsEmail, objectEmail, "", emailOptions);

  }
}

针对跨表格访问超时问题，可从以下几个方向优化：

• 删除临时表操作，直接导出原工作表：
  创建、复制、删除临时表是高开销操作，可直接导出原工作表生成附件，大幅减少对外部表格的API调用：

  // 替换原临时表相关代码块
  if (attachmentsEmail!=''){
    for (var j = 0; j < attachmentsEmail.length; j++) {
    var fileSheetName = attachmentsEmail[j].trim();
    var fileSheet = sourceSpreadsheet.getSheetByName(fileSheetName);
  
    if(fileSheet==null){
      SpreadsheetApp.getUi().alert("Un onglet de attachmentsEmail n'est pas présent dans le fichier source : "+fileSheetName, SpreadsheetApp.getUi().ButtonSet.OK);
      return;
    }
    // 直接导出原工作表
    var url = "https://docs.google.com/spreadsheets/d/"+sourceSpreadsheetId+"/export?format=xlsx&gid="+fileSheet.getSheetId();                                      
    var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
    var blob = UrlFetchApp.fetch(url, params).getBlob().setName(fileSheetName+".xlsx");
    emailOptions.attachments.push(blob);
    }
  }
  

• 添加超时重试机制：
  对访问外部表格的关键操作包裹重试逻辑，配合指数退避等待，降低单次超时影响：

  function withRetry(func, maxAttempts = 3) {
    let attempts = 0;
    while (attempts < maxAttempts) {
      try {
        return func();
      } catch (e) {
        attempts++;
        if (attempts >= maxAttempts) throw e;
        Utilities.sleep(1000 * attempts); // 每次重试等待时间递增
      }
    }
  }
  
  // 使用示例：获取外部工作表时添加重试
  var fileSheet = withRetry(() => sourceSpreadsheet.getSheetByName(fileSheetName));
  

• 减少外部表格API调用次数：
  提前一次性获取外部表格的所有工作表信息，避免循环内重复查询；合并getLastRow()、getLastColumn()这类操作，减少单次循环内的API调用量。

• 拆分任务批量处理：
  如果attachmentsEmail包含多个工作表，可拆分任务，单次只处理2-3个附件，避免长时间占用外部表格连接。

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