Run a saved bigquery query from Google Apps Script?

4
votes

We frequently use Google Apps script to run BigQuery queries and put them into a Google Sheet. However, the workflow is annoying:

  1. Run the query in BigQuery until you get it right.
  2. Copy/paste to a text editor to put in the newline slashes
  3. Run it in apps script and hope it works
  4. Go back to BigQuery and repeat 1-3 if something doesn't work.

Is there some way to just save a query using BigQuery's save function, and then call that specific query from a script?

4
google-apps-scriptgoogle-bigquery

4 Answers

3
votes

Its a workaround...

try saving as a view, and execute a simple 

Select * from MyView
0
votes

If you query data in BigQuery, build reports and visualize results on a regular basis, you will enjoy our free Add-on for Google Sheets.

It will make your workflow easier.

Benefits:

  1. Analysts and developers can create shared SQL-queries with pre-defined variables,
  2. Queries are saved for future use,
  3. Variables allow modifying the result without editing SQL-syntax,
  4. Visualize, manipulate and share data enjoying the benefits of Google Sheets,
  5. Everything works 100% in the Google Cloud and absolutely safe: data from Google BigQuery is directly inserted to Google Sheets.

Any suggestions and comments are welcome.

0
votes

At my blog, I wrote an article that shows how to front-end a BigQuery project with a Google Apps Script application "owned" by a Google Drive spreadsheet. The article describes a simple example application available at my GitHub repository. This sample application

1) builds a web page that directly queries BigQuery

2) shows the result set on the web page

3) mirrors the result set on the spreadsheet

The sample solution has two small files - that's it. You probably have a BigQuery project more complicated than the one featured in the article, but the main idea of the article / sample should extend to your situation.

0
votes

BigQuery Team definitely has some secret sauce here as they actually have this functionality in their Native BigQuery UI – you can save and then reuse it from within that UI

As of using saved query programmatically I can recommend this route:

  1. run your query anyhow and get respective jobid
  2. do jobs/get (https://cloud.google.com/bigquery/docs/reference/v2/jobs/get) for that specific jobid within your script to get configuration.query.query that consists of query text
  3. then just use it the way you need
  4. if to properly wrap it – can be quite useful :0)