How to call Google Big Query non-legacy SQL using Google App Script?

Question

I want to use Google App Scripts to trigger a Google Big Query query as per the docs here:

https://developers.google.com/apps-script/advanced/bigquery#reference

Since my query uses the new DML functionality it needs to use non-legacy SQL.

Is this possible using Google App Script? How do I specify non-legacy SQL?

Answer 1

I don't believe that you need to use #StandardSQL in the query text. Using the sample code on https://developers.google.com/apps-script/advanced/bigquery#reference as a reference, it should be sufficient to change:

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

to:

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

Specifically, you need to add the useLegacySql key to the request.

Answer 2

Very Minor correction in the accepted answer. (Semicolon needed, not equals symbol)

It should be

useLegacySql : false

And not

useLegacySql = false