google-apps-script - 谷歌脚本超时
问题描述
问题:
导入函数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)
如果我在 importXLS 文件夹中使用 1 个文件运行该函数,则它可以正常工作。
脚本解释:
我有 52 个文件夹,每个文件夹包含一个电子表格文件。
每个文件夹都与不同的同事共享。白天,人们对文件进行更改。
- 一天结束时,所有文件都收集在一个文件夹 (
gsheetFolder
) 中,并使用该功能转换为 XLSX 文件collectAndExportXLS
。
这些文件在晚上被复制到本地服务器(使用批处理脚本和驱动器同步),该服务器更新文件中的其他信息并被复制回importXLSXfolder
.
- 早上,该
importXLSXtoGsheet
函数运行并将文件importXLSXfolder
夹中的所有 XLSX 文件转换为gsheetFolder
. - 运行后
sortGsheetFiles
,对 52 个文件夹之一中的每个 Gsheet 文件进行排序和移动(使用当前电子表格中的数组列表)。
其他操作包括使用该deleteFolder
功能清理文件夹。
脚本:
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);
}
解决方案
关于你的问题,我可以理解如下。
- 使用
importXLSXtoGsheet()
52 个文件运行时,会发生错误。 - 当
importXLSXtoGsheet()
运行少于 13 个文件时,不会发生错误。 - 除了
importXLSXtoGsheet()
工作正常以外的其他功能。
如果我的理解是正确的,作为一种解决方法,它决定了一次处理文件的最大数量。当这反映到importXLSXtoGsheet()
您的脚本时,修改后的脚本如下。
修改后的脚本:
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;
.
推荐阅读
- php - 您能帮我将看似 json 的值转换为逗号分隔的值吗?
- python - How to find the longest sequence of equal elements in a sequence?
- .net - 目前正在尝试学习 Microsoft.net 核心,但它给出了这个错误值不能为空。(参数“连接字符串”)
- javascript - javascript字符串中的HTML注释的奇怪行为
- salesforce - Salesforce:根据文本列表评估潜在客户字段的复选框
- javascript - 解构对象反应来自firebase的本机钩子返回未定义
- json - How to access JSON object key and value in ReactJS
- apache-kafka - 为什么kafka消费者消费进程3次后不消费?
- azure - Azure 自动化/o365 Exchange 连接拒绝访问
- ms-access - 从 MS Access 升级到 SQL Server 后多值字段消失