Clever Geek Handbook

Run saved bigquery from Google Apps Script?

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

  • Run your BigQuery query until you get it right.
  • Copy / paste into a text editor to insert new line ink
  • Run it in your apps script and hope it works.
  • Go back to BigQuery and repeat 1-3 if something doesn't work.

Is there a way to just save the query using BigQuery's save function and then call that particular query from within a script?

+3


source to share


4 answers


His workaround ...

try to save as view and do a simple



Select * from MyView
+2


source


If you query BigQuery for data, regularly generate reports and visualize results, you'll enjoy a free Google Sheets add-on .

This will simplify your workflow.

Benefits:



  • Analysts and developers can create generic SQL queries using predefined variables,
  • Requests are saved for future use,
  • Variables allow you to change the result without editing the SQL syntax,
  • Visualize, manage and share data taking advantage of Google Sheets,
  • Everything works 100% in Google Cloud and is completely safe: data from Google BigQuery is directly inserted into Google Sheets.

Any suggestions and comments are appreciated.

0


source


In my blog, I wrote an article that shows how to execute a BigQuery project using the Google Apps Script app, owned by "Google Drive Spreadsheet". This article describes a simple sample application available in my GitHub repository. This is an example application

1) creates a web page that requests BigQuery directly

2) shows a set of results on a web page

3) displays the result set in a spreadsheet

The sample solution has two small files - this is it. The BigQuery project is probably more complex than the one shown in the article , but the main idea of ​​the article / sample should apply to your situation.

0


source


The BigQuery team definitely has some kind of secret sauce here, as they do have this functionality in the Native BigQuery UI - you can save and then reuse it from that UI

Using the saved query programmatically, I can recommend this route:

  • execute the request anyway and get the corresponding jobid
  • run assignments / get ( https://cloud.google.com/bigquery/docs/reference/v2/jobs/get ) for that specific task in your script to get configuration.query.query file which consists of request text
  • then just use it the way you need it.
  • if wrapped correctly, it can be quite useful: 0)
0


source






More articles:


All Articles