在Google Apps Scripts中收到401 'truncated server‘响应
我有这个代码。
function method3()
{
var spreadsheetID = '1BGi80ZBoChrMXGOyCbu2pn0ptIL6uve2ib62gV-db_o';
var sheetName = 'Form Responses 1';
var queryColumnLetterStart = 'A';
var queryColumnLetterEnd = 'C';
var query = 'select * where B = "8"';
// don't provide last row in range selection
var qvizURL = 'https://docs.google.com/spreadsheets/d/' + spreadsheetID + '/gviz/tq?tqx=out:json&headers=1&sheet=' + sheetName + '&range=' + queryColumnLetterStart + ":" + queryColumnLetterEnd + '&tq=' + encodeURIComponent(query);//(myQuery);
Logger.log('qvizURL: ' + qvizURL);
options = {muteHttpExceptions: true};
// fetch the data
Logger.log(ScriptApp.getOAuthToken());
var ret = UrlFetchApp.fetch(qvizURL, { headers: {Authorization: 'Bearer ' + ScriptApp.getOAuthToken()}}).getContentText();
Logger.log('ret: ' + ret);
var obj1 = JSON.parse(ret.replace("/*O_o*/", "").replace("google.visualization.Query.setResponse(", "").slice(0, -2));
Logger.log('obj1:');
Logger.log(obj1);
var data = obj1.table.rows;
Logger.log('#rows: ' + data.length);
for(var i=0;i<data.length;i++) {
values.push({ts:obj.table.rows[i].c[0].f, cs:obj.table.rows[i].c[1].v, or:obj.table.rows[i].c[2].v})
Logger.log(i+': ' + values[i][ts] + ' || ' + values[i][cs] + ' || ' + values[i][or] + ' || ');
}
}记录器：
18 feb. 2021 22:33:44 Informatie qvizURL: https://docs.google.com/spreadsheets/d/1BGi80ZBoChrMXGOyCbu2pn0ptIL6uve2ib62gV-db_o/gviz/tq?tqx=out:json&headers=1&sheet=Form Responses 1&range=A:C&tq=select%20*%20where%20B%20%3D%20%228%22
18 feb. 2021 22:33:44 Informatie ya29.A0AfH6SMBGpL2mxU7DO5p8RQfCXKP1w13wmU6aBTVZSCjfO-uj_xzkYQziMhnXATEdGREibJk9cATEGioTfQG4aGsNq7Tm05_oD0z1HKu1v4ozBF_B2XegyQ-NuXBJFmJWTX5WEpTOm0RDTlfY6uw8lK3R5HTV
18 feb. 2021 22:33:44 Fout Exception: Request failed for https://docs.google.com returned code 401. Truncated server response: <HTML>
<HEAD>
<TITLE>Unauthorized</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000">
<H1>Unauthorized</H1>
<H2>Error 401</H2>
</BODY>
</HTML>
(use muteHttpExceptions option to examine full response)
at method3(QueryTable:16:27)已尝试添加“muteHttpExceptions:true”。但这只显示了我获取的url中的HTML。被截断了并且没有显示任何线索。
我需要在某个地方授权这个UrlFetch吗？
*更新*
所有的东西都是用同一个账号创建的。
这是清单的初始版本(appscript.json)：
{
"timeZone": "America/New_York",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"}然后，我在https://developers.google.com/apps-script/concepts/scopes#viewing_scopes上阅读时添加了oauthScopes
{
"timeZone": "America/New_York",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.readonly",
"https://www.googleapis.com/auth/userinfo.email"
]
}结果是："Exception:您没有权限调用UrlFetchApp.fetch。需要的权限: googleapis.com/auth/script.external_request“。
所以我加了一个
"https://www.googleapis.com/auth/script.external_request"这将产生初始错误消息: Exception: Request failed for https://docs.google.com，返回代码401。截断的服务器响应：...
所以我猜我需要对oauthScopes进行更多的微调...我看了https://developers.google.com/workspace/add-ons/concepts/gsuite-scopes#editor_scopes，但不知道该怎么做...
回答 1
Stack Overflow用户
发布于 2021-02-22 18:07:09
您的凭据无效，请改用Sheets高级服务
401 (未授权)状态代码指示尚未应用请求，因为它缺少目标资源的有效身份验证凭据。
另一方面，接收到不足以获得访问权限的有效凭据的服务器应该响应403 (禁止)状态代码。
正如我在您的代码中看到的，您希望通过获得JSON格式的电子表格范围来迭代您的工作表数据。我建议您在Google Apps脚本中使用Sheets高级服务，并进行以下更改：
单击服务+并添加工作表API V4。
通过Google Apps脚本中的Sheets API获取值，然后过滤
function SheetsAdvancedService()
{
var spreadsheetID = 'ID';
var sheetName = 'Form Responses 1';
var queryColumnLetterStart = 'A';
var queryColumnLetterEnd = 'C';
var sheetRangeResponse = Sheets.Spreadsheets.Values.get(spreadsheetID, `${sheetName}!${queryColumnLetterStart}:${queryColumnLetterEnd}`);
if (sheetRangeResponse.values) {
var filteredData = sheetRangeResponse.values.filter(row => row[1] == 8) // select * where B = "8"
Logger.log(filteredData);
// Handle data ...
// Or use forEach instead of filter for efficiency
} else {
Logger.log("No values");
}
}或者使用SheetsApp，然后使用filter
function SheetsThroughGoogleAppsScript()
{
var spreadsheetID = 'ID';
var sheetName = 'Form Responses 1';
var ss = SpreadsheetApp.openById(spreadsheetID);
var sheets = ss.getSheetByName(sheetName);
var range = sheets.getDataRange().getValues();
if (range) {
var filteredData = range.filter(row => row[1] == 8) // select * where B = "8"
Logger.log(filteredData);
// Handle data and filter columns ...
// Or use forEach instead of filter for efficiency
} else {
Logger.log("No values");
}
}参考文献
https://stackoverflow.com/questions/66268685
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号