Google Apps Script for BigQuery (Standard SQL) - NaN

Question

Facing some issues with my apps script that seems to run, but only selected lines and i'm not sure why. Was wondering if it could be an issue with standard SQL and some parameters. This is my code

function runQuery() {
  var configuration = {
    query: {
    useQueryCache: false,
    destinationTable: {
          projectId: "projectA",
          datasetId: "datasetA",
          tableId: "NewTable"
        },
    writeDisposition: "WRITE_TRUNCATE",
    createDisposition: "CREATE_IF_NEEDED",
    allowLargeResults: true,
    useLegacySql: false,
    query: "WITH a AS" + 
      "(SELECT Date, Month, Quarter, Week, Year FROM Dataset.Table1`)," + 
      "b AS "+
      "(SELECT * FROM `Dataset.Table2`)," +
      "c AS "+
      "(SELECT * FROM `Dataset.Table3`) " + 
      "SELECT Date, Month, Quarter, Week, Year, .... FROM a" + 
      "LEFT JOIN b ON a.x = b.x LEFT JOIN c ON a.x = c.x"
    }
  };

  var job = {
    configuration: configuration
  };

  var jobResult = BigQuery.Jobs.insert(job, "projectA");
  Logger.log(jobResult);
}

(Have changed the variables and table names to blank out the contents, but the general structure of the code has been shown)

The SQL code itself works on BigQuery, so the issue is not with an error in the query.

When I attempt to run this, the log on BigQuery seems to show that the code executed is only the last 2 lines of the code, with a NaN in front

NaNSELECT Date, Month, Quarter, Week, Year, .... FROM a LEFT JOIN b ON a.x = b.x LEFT JOIN c ON a.x = c.x

Appreciate any help on this. Thanks

could it be the space missing here query: "WITH a AS" + "(SELEC ? and in the subsequent concat operators too — FKrauss
@FKrauss are you referring to the space after AS? Have tried adding that but it doesn't work as well. :( — sharkorama
Not only that one, that are many that you're concatenation and not spacing with other characters — FKrauss
just tried with the spaces, and it didn't make a difference unfortunately. The last 2 lines worked without the spaces. — sharkorama
Have you tried to print "query" to see the value which is inside? It should be the whole query sentence, right? I mean something like " console.log(configuration.query.query);" in the original one since this one was rewrote. — Temu

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2018-10-28T20:57:27

I think, you are missing back-tick in below line

  "(SELECT Date, Month, Quarter, Week, Year FROM Dataset.Table1`)," +

It should be

  "(SELECT Date, Month, Quarter, Week, Year FROM `Dataset.Table1`)," +

Google Apps Script for BigQuery (Standard SQL) - NaN

1 Answers