Export query results data from Bigquery to Google Sheets

Google+Tweet about this on TwitterShare on FacebookShare on LinkedIn

This post will contain a simple JS function helping you to export data from Bigquery to Sheets. In fact, we’re going to export a resulting table after querying our GBQ tables. We’re going to need Google Bigquery API and Apps Script.

My function here is nothing more than a modified version of the original code (changed according to my needs) demonstrating how you can use Bigquery API with your apps. What I want to share with you is how you can inject dynamic parameters into your SQL query which allow you to automate the upload process.

For instance, you can automatically export tables containing data for the past week every Monday without needing to tweak dates in the body of your SQL-query.

The function

The code below contains extensive comments. However, I’ll try to briefly explain the logic of my own additions right after it.

Comments

Wow, that’s a lot of code. But don’t worry, we’ll get through it! Let us start with my additions to the original code.

queryDates function

This immediately invoked function expression returns an array containing two items. These items are strings representing the first and the last dates in previous month:

export data from bigquery to sheets: queryDates

I use this variable as a dynamic parameter in my SQL code:

Thus, in November I automatically query data for October, in December for November and so on…

getPastDate function

This function takes one argument [n] and returns a string date (yyyy-mm-dd) according to the following formula (today’s date – n) where n = number of days.

Files array

This array contains two objects which in turn contain values for getting different event data. Consequently, using one single function (and a loop inside it) I can repeat my SQL query for different event categories and actions. For instance, first I get ids of all users that purchased Google T-Shirt in October and put the result table in file1. Next, I get ids of those who clicked on Yandex promo in October and put the table in file2. All this happens inside a single function.

getPrevMonth function

Ahh, this one’s easy. The function simply returns a string containing the name of the previous month and a year it belonged to. n = number of current month. Remember that month count in JS starts with zero!

I use this one for naming the tab in my Google Sheet file.

Usage

Certainly, you’ve already guessed that a copy-paste approach won’t work here. Surely, you can copy the code but please do remember that you will have to tune it according to your tasks.

I’d recommend starting with a original variant (see links above). Once you understand how it works, you can easily implement my functions described here of write your own solutions.

After making sure your runQuery function works as expected you can easily automate your data export using time-driven triggers.

By the way

Here’s some info on other methods of exporting data stored in GBQ

Should any questions arise, the comment section is…. ahhh you know it.

 

Google+Tweet about this on TwitterShare on FacebookShare on LinkedIn