错误“无效参数:"在日期为'yyy-MM-dd'的地方使用UrlFetchApp.fetch(url)时 [英] error "Invalid argument:" when using UrlFetchApp.fetch(url) with where date 'yyy-MM-dd'

问题描述

我使用

UrlFetchApp类

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.

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 of https://www.googleapis.com/auth/drive.readonly, please use // DriveApp.createFile() instead of // DriveApp.getFiles()

References:

• encodeURIComponent()
• Class UrlFetchApp

这篇关于错误“无效参数:"在日期为'yyy-MM-dd'的地方使用UrlFetchApp.fetch(url)时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！