Accessing BigQuery Saved Project Query from Script

Question

I’m trying to use Google Apps Scripts to wrangle and remodel BigQuery data via a series of select / insert statements

My 15+ Select statements are complicated and for maintainability ideally belong inside of BigQuery (as opposed to embedded within JavaScript). I would like to reference the Saved Project Query from BigQuery from within Google Scripts in place of the embedded query. So the following query statement

body = 
{
    configuration: 
    {
        query: 
        {
            query: 'SELECT foo, bar FROM FooBarProjcet;',
            writeDisposition:'WRITE_APPEND',
            destinationTable: 
            {
                projectId: projectId,
                datasetId: datasetId,
                tableId: 'TestScript'
            }
        },
    }
}

would become something like

    body = 
{
    configuration: 
    {
        query: 
        {
            query: BigQuery.ProjectQuery.FooBarSelect,
            writeDisposition:'WRITE_APPEND',
            destinationTable: 
            {
                projectId: projectId,
                datasetId: datasetId,
                tableId: 'TestScript'
            }
        },
    }
}

Is this possible? Are the BigQuery saved project queries exposed to the APi?

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-01-23T16:03:05

From what I know - Saving and Sharing Query designed for sharing queries between users via Query Link and not for executing that Link anyhow.
So in your case you can save your queries as views and then use them as below

   query: 
    {
        query: 'SELECT * FROM YourView',
        writeDisposition:'WRITE_APPEND',
        destinationTable: 
        {
            projectId: projectId,
            datasetId: datasetId,
            tableId: 'TestScript'
        }
    },

Accessing BigQuery Saved Project Query from Script

1 Answers