App Script BigQuery Standard SQL insert or update statement

Question

I'm looking to use the BigQuery Standard SQL dialect from within Google App Script. The following works in BigQuery when "SQLVersion : Use Legacy SQL" is unchecked;

INSERT INTO MyDataSet.MyFooBarTable (Id, Foo, Date) VALUES (1, 'bar', current_Date)

I can happily interrogate BigQuery from Google Script using Jobs and perform inserts via an Append job (effectively giving the same insert result). Is it possible to use the Standard SQL logic within script to perform the insert?

I have tried adding #standardSQL to the statement with no joy.

according to the docs jobs.query defaults to legacy if you do not explicitly set useLegacySql to false. — Spencer Easton

Graham Polley Graham Polley · Accepted Answer · 2017-02-08T08:52:08

You need to set the useLegacySql flag/parameter to false, to indicate that you want to use standard SQL, like so:

var job = {
configuration: {
  query: {
    query: 'INSERT INTO MyDataSet.MyFooBarTable (Id, Foo, Date) VALUES (1, \'bar\', current_Date);',
    useLegacySql: false
    }

}};

I tested this in my own GAS, and worked as expected.

App Script BigQuery Standard SQL insert or update statement

1 Answers