Search code examples
google-apps-scriptgoogle-sheetsgoogle-visualizationurlfetchgoogle-query-language

error "Invalid argument:" when using UrlFetchApp.fetch(url) with where date 'yyy-MM-dd'

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);
}

Solution

  • 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: