Google脚本超时 [英] Google Script timeouts

问题描述

问题:

导入功能importXLSXtoGsheet()在可以处理所有52个XLSX文件之前超时，我收到错误消息: Exception: Time-out: https://www.googleapis.com/batch/drive/v3 at [unknown function](Code:63) at Do(Code:8) at importXLSXtoGsheet(Code:71)

The import function importXLSXtoGsheet() times out before it can process all 52 XLSX files, I received the error: Exception: Time-out: https://www.googleapis.com/batch/drive/v3 at [unknown function](Code:63) at Do(Code:8) at importXLSXtoGsheet(Code:71)

如果我使用importXLS文件夹中的1个文件运行该函数，则它可以正常工作.

If I run the function with 1 file in the importXLS folder, it works correctly.

脚本说明:

我有52个文件夹，每个文件夹包含一个电子表格文件.
每个文件夹与不同的同事共享. 白天，人们会对文件进行更改.

I've got 52 folders, each containing one spreadsheet file.
Each folder is shared with different colleagues. During the day, people make changes to the files.

1. 最终，使用功能collectAndExportXLS将所有文件收集在一个文件夹(gsheetFolder)中并转换为XLSX文件.
   

1. At the end of the day, all files are collected in one folder (gsheetFolder) and converted to XLSX files, using the function collectAndExportXLS.
   

这些文件在晚上(使用批处理脚本和驱动器同步)复制到本地服务器，这将更新文件中的其他信息，并复制回importXLSXfolder.

These files are copied to a local server in the evening (using batch script and drive sync) which updates other information in the file and are copied back to the importXLSXfolder.

1. 早晨，importXLSXtoGsheet函数运行，并将importXLSXfolder文件夹中的所有XLSX文件转换为gsheetFolder中的Gsheet文件.
2. 运行sortGsheetFiles之后，将所有Gsheet文件排序并移动到52个文件夹之一中(使用当前电子表格中的数组列表).

1. In the morning the importXLSXtoGsheet function runs and converts all XLSX files in the importXLSXfolder folder to Gsheet files in the gsheetFolder.
2. After that sortGsheetFiles runs, sorting and moving every Gsheet file in one of the 52 folders (using an array list from the current spreadsheet).

其他操作包括使用deleteFolder功能清理文件夹.

Other actions include cleaning the folders with the deleteFolder function.

脚本:

var gsheetFolder = '###';
var XLSXfolder = '###';
var importXLSXfolder = '###';

// Modified
function deleteFolder(folderId) {
  var url = "https://www.googleapis.com/drive/v3/files?q='" + folderId + "'+in+parents+and+trashed%3Dfalse&fields=files%2Fid&access_token=" + ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  var obj = JSON.parse(res.getContentText());
  var reqs = obj.files.map(function(e) {return {method: "DELETE", endpoint: "https://www.googleapis.com/drive/v3/files/" + e.id}});
  var requests = {batchPath: "batch/drive/v3", requests: reqs};
  if (requests.requests.length > 0) BatchRequest.Do(requests);
}

// Added
function deleteFiles(files) {
  var reqs = files.map(function(e) {return {method: "DELETE", endpoint: "https://www.googleapis.com/drive/v3/files/" + e.id}});
  var requests = {batchPath: "batch/drive/v3", requests: reqs};
  if (requests.requests.length > 0) BatchRequest.Do(requests);
}

// Added
function getValuesFromSpreadsheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  return sheet.getRange("A2:B53").getValues();
}

// Modified
function sortGsheetFiles() {
  var url = "https://www.googleapis.com/drive/v3/files?q='" + gsheetFolder + "'+in+parents+and+mimeType%3D'" + MimeType.GOOGLE_SHEETS + "'+and+trashed%3Dfalse&fields=files(id%2Cname)&access_token=" + ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  var obj = JSON.parse(res.getContentText());
  var values = getValuesFromSpreadsheet();
  var reqs = values.reduce(function(ar, e) {
    for (var i = 0; i < obj.files.length; i++) {
      if (obj.files[i].name == e[0]) {
        ar.push({
          method: "PATCH",
          endpoint: "https://www.googleapis.com/drive/v3/files/" + obj.files[i].id + "?addParents=" + e[1] + "&removeParents=" + gsheetFolder,
        });
        break;
      }
    }
    return ar;
  }, []);
  var requests = {batchPath: "batch/drive/v3", requests: reqs};
  if (requests.requests.length > 0) BatchRequest.Do(requests);
  deleteFolder(importXLSXfolder);
}

// Modified
function importXLSXtoGsheet(){
  deleteFolder(XLSXfolder);
  var url = "https://www.googleapis.com/drive/v3/files?q='" + importXLSXfolder + "'+in+parents+and+mimeType%3D'" + MimeType.MICROSOFT_EXCEL + "'+and+trashed%3Dfalse&fields=files(id%2Cname)&access_token=" + ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  var obj = JSON.parse(res.getContentText());
  var reqs = obj.files.map(function(e) {return {
      method: "POST",
      endpoint: "https://www.googleapis.com/drive/v3/files/" + e.id + "/copy",
      requestBody: {mimeType: MimeType.GOOGLE_SHEETS, name: e.name + ".xlsx", parents: [gsheetFolder]},
    }
  });
  var requests = {batchPath: "batch/drive/v3", requests: reqs};
  if (requests.requests.length > 0) BatchRequest.Do(requests);
  deleteFolder(importXLSXfolder);
}

// Modified
function ConvertBackToXLS(fileList) {
  var token = ScriptApp.getOAuthToken();
  var reqs1 = fileList.map(function(e) {return {
      method: "GET",
      url: "https://docs.google.com/spreadsheets/export?id=" + e.id + "&exportFormat=xlsx&access_token=" + token,
    }
  });
  var res = UrlFetchApp.fetchAll(reqs1);
  var reqs2 = res.map(function(e, i) {
    var metadata = {name: fileList[i].name, parents: [XLSXfolder]};
    var form = FetchApp.createFormData(); // Create form data
    form.append("metadata", Utilities.newBlob(JSON.stringify(metadata), "application/json"));
    form.append("file", e.getBlob());
    var url = "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart";
    return {url: url, method: "POST", headers: {Authorization: "Bearer " + token}, body: form};
  });
  FetchApp.fetchAll(reqs2);
}

// Modified
function collectAndExportXLS() {
  deleteFolder(gsheetFolder);
  var values = getValuesFromSpreadsheet();
  var reqs1 = values.reduce(function(ar, e) {
    if (e[0] && e[1]) {
      ar.push({
        method: "GET",
        endpoint: "https://www.googleapis.com/drive/v3/files?q='" + e[1] + "'+in+parents+and+trashed%3Dfalse&fields=files(id%2Cname)",
      });
    }
    return ar;
  }, []);
  var resForReq1 = BatchRequest.Do({batchPath: "batch/drive/v3", requests: reqs1});
  var temp = resForReq1.getContentText().split("--batch");
  var files = temp.slice(1, temp.length - 1).map(function(e) {return JSON.parse(e.match(/{[\S\s]+}/g)[0])});
  var fileList = files.reduce(function(ar, e) {return ar.concat(e.files.map(function(f) {return f}))}, []);
  ConvertBackToXLS(fileList);
  deleteFiles(fileList);
}

推荐答案

关于您的问题，我可以理解如下.

About your question, I could understand like below.

• 当使用52个文件运行importXLSXtoGsheet()时，会发生错误.
• 当importXLSXtoGsheet()运行的文件少于13个时，不会发生错误.
• importXLSXtoGsheet()以外的其他功能都可以正常工作.

• When importXLSXtoGsheet() is run with 52 files, the error occurs.
• When importXLSXtoGsheet() is run with less than 13 files, no error occurs.
• Other functions except for importXLSXtoGsheet() works fine.

如果我的理解是正确的，则作为一种解决方法，它确定一次处理文件的最大数量.当这反映到脚本的importXLSXtoGsheet()时，修改后的脚本如下.

If my understanding is correct, as one workaround, it decides the maximum number for processing the files once. When this is reflect to importXLSXtoGsheet() of your script, the modified script is as follows.

function importXLSXtoGsheet(){
  deleteFolder(XLSXfolder);
  var url = "https://www.googleapis.com/drive/v3/files?q='" + importXLSXfolder + "'+in+parents+and+mimeType%3D'" + MimeType.MICROSOFT_EXCEL + "'+and+trashed%3Dfalse&fields=files(id%2Cname)&access_token=" + ScriptApp.getOAuthToken();
  var res = UrlFetchApp.fetch(url);
  var obj = JSON.parse(res.getContentText());

  // I modified below script.
  var n = 10; // Maximum number.
  var files = [];
  var len = obj.files.length;
  for (var i = 0; i < len; i++) {
    files.push(obj.files.splice(0, n));
    len -= n - 1;
  }
  files.forEach(function(f) {
    var reqs = f.map(function(e) {return {
        method: "POST",
        endpoint: "https://www.googleapis.com/drive/v3/files/" + e.id + "/copy",
        requestBody: {mimeType: MimeType.GOOGLE_SHEETS, name: e.name + ".xlsx", parents: [gsheetFolder]},
      }
    });
    var requests = {batchPath: "batch/drive/v3", requests: reqs};
    if (requests.requests.length > 0) BatchRequest.Do(requests);
  });
  deleteFolder(importXLSXfolder);
}

注意:

• 在此示例脚本中，每个批处理请求处理10个文件.如果要更改此设置，请修改var n = 10;.

Note:

• In this sample script, 10 files are processed every batch request. If you want to change this, please modify var n = 10;.

这篇关于Google脚本超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！