Error when trying to query BQ with Apps Script

Question

I am trying to get Apps Script to make a BQ query, however I get the following error:

Encountered " "FROM" "FROM "" at line 1, column 41. Was expecting: ")" ... (line 14, file "Code")

The query is working fine in BQ so I don't understand what's wrong here...

Here is the script:

  var datasets = [
    ['012345', "Country"]
    // other datasets will complete the list
  ]

  datasets.forEach(function(value) {
    var datasetId = value[0];
    var countryName = value[1];

    var queryDataset = BigQuery.Jobs.query(
      {'query' : 'SELECT "' + countryName + '" as country,' +
       'EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime) AT TIME ZONE "Europe/Paris") AS Hour,' +
       '(SELECT MAX(sourcePropertyInfo.sourcePropertyDisplayName)' +
       'FROM UNNEST(session.hits) AS hits) AS service,' +
       'IFNULL(SUM(totals.visits),0) as sessions,' +
       'IFNULL(SUM(totals.transactions),0) as transactions,' +
       'IFNULL(ROUND((SUM(totals.transactions)/SUM(totals.visits))*100,2),0) AS conversionRate' +
       'FROM `xx-135923.' + datasetId + '.ga_realtime_view` AS session' +
       'GROUP BY' +
       'Hour,' +
       'service' +
       'ORDER BY' +
       'Hour;',
       'defaultDataset' : {
       'datasetId': datasetId,
       'useLegacySql' : false
      }
      }, 'xx-135923');

Do you guys have any idea ?

Thanks for your help

Well, did you debug your constructed Apps Script query? If you get errors with a complex variable construction where syntax is extremely important, like a nested SQL call, its always a good idea to construct a complex query first and use try + catch to print the query used along with the error it generated. See my code in this answer for what I mean: stackoverflow.com/a/51085317/9337071 — tehhowch
– tehhowch, Commented Jun 28, 2018 at 15:09
Thanks for your help. I've created a variable to log the constructed query, which I then use in the BQ query. Also I've cleaned the query a bit by adding white spaces and escape sequences when I use double quotes in the string. The query is accurate when I use Logger.log on it, however I still get the same error. — kinzie
– kinzie, Commented Jun 28, 2018 at 15:41
Removing the "EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime) AT TIME ZONE "Europe/Paris") AS Hour," part apparently fixed it, although I have no idea why. Now I get a new error "Invalid table name: xx-135923:012345.ga_realtime_view" which I don't understand either, since the table is accurate. — kinzie
– kinzie, Commented Jun 28, 2018 at 16:18
Ok I found it. It was a stupid mistake : I placed the "'useLegacySql' : false" part in the "defaultDataset" object. So I was in fact querying in Legacy SQL instead of Standard. — kinzie
– kinzie, Commented Jun 28, 2018 at 16:29
@kinzie Could you write your comment as an answer. Thank you! — komarkovich
– komarkovich, Commented Jun 29, 2018 at 10:10

kinzie · Accepted Answer · 2018-06-29 12:36:39Z

2

Problem solved: it was a stupid mistake. I placed the "'useLegacySql' : false" part in the "defaultDataset" object. So I was in fact querying in Legacy SQL instead of Standard

answered Jun 29, 2018 at 12:36

kinzie

511 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Chiasson Over a year ago

I didn't change anything on mine and it appears to be using legacy SQL for the bq command line script. Therefore, I have to put the table name in square brackets rather than back quotes. Thanks for posting this.

Collectives™ on Stack Overflow

Error when trying to query BQ with Apps Script

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related