本文介绍了错误“无效参数:"在日期为'yyy-MM-dd'的地方使用UrlFetchApp.fetch(url)时的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用
I use this sheet and I want to get the values where the date is > to '2020-07-21',the data source URL I used with html output is: https://docs.google.com/spreadsheets/d/1u78Qx5YIB2mektPyErz6xYtTXMLLtCapXlEpp63NTYI/gviz/tq?tqx=out:html&tq=select B where date '2020-07-21' > A &gid=0
the problem that when I se the data source URL in chrome browser, I could see the response data but when running that with the function UrlFetchApp.fetch(url) I receice an error.the code in script editor:
function myFunction() {
var url ="https://docs.google.com/spreadsheets/d/1u78Qx5YIB2mektPyErz6xYtTXMLLtCapXlEpp63NTYI/gviz/tq?tqx=out:csv&tq=select B where date '2020-07-21' > A &gid=0"
var response = UrlFetchApp.fetch(url).getContentText();
Logger.log(response);
}
解决方案
In your case, there are the following modification points.
Modification points:
- Please do the URL encode for
select B where date '2020-07-21' > A
. - Please request to the endpoint using the access token.
- In this case, because the data is retrieved, I think that the scope of
https://www.googleapis.com/auth/drive.readonly
can be used.
- In this case, because the data is retrieved, I think that the scope of
When these points are reflected to your script, it becomes as follows.
Modified script:
function myFunction() {
var query = "select B where date '2020-07-21' > A";
var url ="https://docs.google.com/spreadsheets/d/1u78Qx5YIB2mektPyErz6xYtTXMLLtCapXlEpp63NTYI/gviz/tq?tqx=out:html&tq=" + encodeURIComponent(query) + "&gid=0";
var params = {headers: {authorization: "Bearer " + ScriptApp.getOAuthToken()}};
var response = UrlFetchApp.fetch(url, params).getContentText();
Logger.log(response);
// DriveApp.getFiles() // This line is used for automatically detecting the scope of "https://www.googleapis.com/auth/drive.readonly". So please don't remove this line.
}
- When you run the script, the authorization dialog is opened. So please authorize the scopes. By this, the script is run.
Note:
- If your Spreadsheet is publicly shared, I think that
params
is not required. At that time, you can remove// DriveApp.getFiles()
. - If you want to use the scope of
https://www.googleapis.com/auth/drive
instead ofhttps://www.googleapis.com/auth/drive.readonly
, please use// DriveApp.createFile()
instead of// DriveApp.getFiles()
References:
这篇关于错误“无效参数:"在日期为'yyy-MM-dd'的地方使用UrlFetchApp.fetch(url)时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!