I'm currently working on a code snippet that looks like this.
function method3()
{
var spreadsheetID = '1BGi80ZBoChrMXGOyCbu2pn0ptIL6uve2ib62gV-db_o';
var sheetName = 'Form Responses 1';
var queryColumnLetterStart = 'A';
var queryColumnLetterEnd = 'C';
var query = 'select * where B = "8"';
// Omitted the 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};
// Fetching 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] + ' || ');
}
}
Logger:
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)
I also tried adding 'muteHttpExceptions:true', but unfortunately, it only displayed truncated HTML from the fetched URL without providing any useful insights.
Do I need to authorize something for this UrlFetch function?
**** update ***
All aspects mentioned were created using one single account.
This was the original version of the manifest file (appscript.json):
{
"timeZone": "America/New_York",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"}
Later, I included oauthScopes as suggested on https://developers.google.com/apps-script/concepts/scopes#viewing_scopes
{
"timeZone": "America/New_York",
"dependencies": {
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.readonly",
"https://www.googleapis.com/auth/userinfo.email"
]
}
This resulted in an error message saying: "Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions: googleapis.com/auth/script.external_request".
Therefore, I added
"https://www.googleapis.com/auth/script.external_request"
However, this caused the initial error message again: Exception: Request failed for https://docs.google.com returned code 401. Truncated server response: ...
It seems like I need to further refine the oauthScopes settings... I browsed through https://developers.google.com/workspace/add-ons/concepts/gsuite-scopes#editor_scopes, but I'm unsure about what steps to take next...