这个解决方法怎么样?
实验:
此实验使用您共享的示例电子表格。
当 UrlFetchApp 直接调用 Sheets API 的端点时,如果响应大小大于 50 MB(52,428,800 Byte),则返回小于 50 MB 的响应。 50 MB 的大小是由于 UrlFetchApp 的限制。另一方面,在Advanced Google Service中,它无法确认这种情况,因为超过限制时就会发生错误。因此,通过使用 UrlFetchApp,可以确认您的情况的错误原因。所以首先,我使用以下脚本确认了这一点。
var spreadsheetId = "#####";
var range = "'Copie de Feuille 1'!A1:JE1000";
var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + range + "?majorDimension=ROWS&valueRenderOption=FORMULA";
var res = UrlFetchApp.fetch(url, {headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}});
Logger.log(res.getContentText().length.toString())
var values = JSON.parse(res.getContentText());
当上面的脚本运行时,52428450
被返回。并且在最后一行出现“未终止的字符串文字”错误。这意味着该对象是不完整的。从这个结果可以发现,一次调用values.get无法检索到范围为'Copie de Feuille 1'!A1:JE1000
。这与以下情况相同我--我的评论 https://stackoverflow.com/questions/52900410/undocumented-sheet-api-limitation-problems?noredirect=1#comment92714210_52900410.
在您的示例电子表格中,发现错误发生的范围边界是'Copie de Feuille 1'!A1:JE722
。当尝试从范围中检索值时'Copie de Feuille 1'!A1:JE723
,出现错误。检索值的大小'Copie de Feuille 1'!A1:JE722
是 52,390,229 字节。这小于 50 MB(52,428,800 字节)。尺寸从'Copie de Feuille 1'!A1:JE723
是 52,428,450 字节,与以下值相同'Copie de Feuille 1'!A1:JE1000
。由此发现超出了UrlFetchApp的限制。
解决方法:
为了避免此错误并检索所有值,作为一种解决方法,我认为我想建议它分割从电子表格检索值的范围。但在你的问题中,你想要速度。所以我想提出以下示例脚本。
- 创建请求。
- Fetch the created requests using
UrlFetchApp.fetchAll()
.
- By
UrlFetchApp.fetchAll()
,每个请求都可以通过异步处理来工作。
- 在高级 Google 服务的 Sheets API 中,无法使用此功能。另外,在values.batchGet中,由于所有检索到的值都超出了限制,因此会发生错误。
- 由此,使用的工艺成本
UrlFetchApp.fetchAll()
变得低于高级 Google 服务的 Sheets API。
示例脚本:
var ranges = ["'Copie de Feuille 1'!A1:JE500", "'Copie de Feuille 1'!A501:JE1000"]; // This was used from the shared spreadsheet. So please modify this for your environment.
var token = ScriptApp.getOAuthToken();
var requests = ranges.map(function(e) {
return {
method: "get",
url: "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetId + "/values/" + e + "?majorDimension=ROWS&valueRenderOption=FORMULA",
headers: {Authorization: "Bearer " + token},
muteHttpExceptions: true,
}
});
var res = UrlFetchApp.fetchAll(requests);
var values = res.reduce(function(ar, e) {
Array.prototype.push.apply(ar, JSON.parse(e.getContentText()).values);
return ar;
}, []);
Logger.log(values.length) // 1000
Logger.log(values[0].length) // 265
Note:
- 如果您想使用该脚本,请确认在 API 控制台启用了 Sheets API。
- 合并数组时,如果出现数组限制错误,请使用各个数组,不要合并数组。
- 当使用高级Google服务的Sheets API时,在以下范围内也不会发生错误
'Copie de Feuille 1'!A1:JE722
错误发生在'Copie de Feuille 1'!A1:JE723
。这个结果和下面的结果是一样的UrlFetchApp
.
参考:
- Google 服务配额 https://developers.google.com/apps-script/guides/services/quotas#current_limitations
- 获取全部(请求) https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetchAll(Object)