Export BigQuery reports to Google Spreadsheets and send them via email

Inhalt ausblenden

Google BigQuery is an ingenious tool to merge and process data. However, most people cannot work there directly because SQL knowledge is required for this. Looker would now be an alternative but also not always practical. Sometimes it should be the good old spreadsheet and an email to go with it. I offer a solution to this in this article.

The script was originally written for notifications about Google Merchant Center. However, with a few modifications, it can also be used to generate other reports from Google BigQuery.

What does the script do?

  1. Get data from a table or view into BigQuery.
  2. Export data to Google Spreadsheets
  3. Generate email and send data to configured emails.

Installing the script

The Apps Script consists of 2 parts.

  1. Code
  2. Config

So please put the parts in 2 „files“ each in Apps Scripts. This is how you separate configuration and function. This is esp. advantageous for updates and changes.

Then fill Config with life and execute the doPost or runQuery in code.

In the CONFIG variable the data for BigQuery, a spreadsheet, emails and email texts must be defined:

Conclusion

With the help of the script, reporting from BigQuery can be realized very easily.