google apps script Spreadsheet and Bigquery querying

Question

Following the example from

https://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs#L26-L28

I was trying to run a query in bigquery, but I am getting "Invalid table name: my_db:my_dataset.my_table [Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]. (line 68, file "bigquery")

What do I need to do to get this to work?

rtbf rtbf · Accepted Answer · 2018-10-04T19:23:44

If you want to run following query:

SELECT TOP(word, 300) AS word, COUNT(*) AS word_count ' +
      'FROM publicdata:samples.shakespeare WHERE LENGTH(word) > 10;'

in BigQuery console, which is provided in Legacy Dialect, ensure that you have enabled option: Use Legacy SQL in Options

Or you could migrate query from LEGACY to STANDARD SQL:

 SELECT APPROX_TOP_COUNT(word, 300) AS word, COUNT(*) AS word_count 
    FROM `publicdata.samples.shakespeare` WHERE LENGTH(word) > 10;

Additionally if you want to run STANDARD SQL queries from Google App Script add flag useLegacySql as below:

var job = {
configuration: {
  query: {
    query: 'SELECT APPROX_TOP_COUNT(word, 300) AS word, COUNT(*) AS word_count +
        FROM `publicdata.samples.shakespeare` WHERE LENGTH(word) > 10;',
    useLegacySql: false
    }

google apps script Spreadsheet and Bigquery querying

1 Answers