As BigQuery becomes more popular many of you are asking a simple question: How does a person get data from BigQuery to Google Sheets on a regular basis?
There are a couple options so let’s look at them on by one.
Native Connector from Google
If you are running “Enterprise Plus” or “G Suite Enterprise for Education” account, you can use a native feature in Google Sheets. Go simply to Data tab and locate this menu item:
You’ll see:
Then add data connection:
Then you’ll likely want to write a custom query:
Then insert your SQL statement:
You can also play with a couple options under Query settings and Parameters. Under Schema, you can list the columns in the table you are about to query.
When I run my query, I get the results in a bit after pressing “Insert results”:
You can then also schedule regular refreshes after pressing “Refresh options”:
Comments about the Native Google BigQuery Connector
While the configuration process is pretty slick, the BigQuery data is imported into a sheet which behaves differently then a regular sheets. For example, you cannot really link to 1 cell within the query results. You can only run aggregation functions over the entire columns – so for example the SUM function.
Another problem is very simple – not everyone has the “Enterprise Plus” or “G Suite Enterprise for Education” Gapps account. So what are the options in the situation when you cannot use the native Google connector?
BigQuery Connector inside Supermetrics for Google Sheets
You can use Supermetrics for Google Sheets which includes BigQuery among many others connector. The problem here is the Supermetrics for Google Sheets is a paid product. Still, if you are spending thousands of dollars per month for example on advertising, spending pennies on a proper analytics tools does no hurt too much. You can get Supermetrics here:
The setup is also quite simple. After you install the extension into your Google Sheet, you need to launch it:
A new pane will open on the right side if your Google Sheet:
Under “Data source”, select “Google BigQuery”, then select a dataset (select any, it does not really matter) and enter your query:
It’s important to start the query with “#standardSQL” as Supermetrics need to tell BigQuery which type of syntax to use. If you don’t use the “#standardSQL” command, your query will fail.
Then run the query and get the results:
And as a final step, you’ll want to schedule your query:
You can do daily, weekly, monthly. With Super Pro account, you can also do hourly refreshes. Results can be emailed to you as PDF, HTML, CSV… There are many options.
Comments About Supermetrics Connector
The beauty here is that the results are standard Google Sheets cells and values. There are no limitations when it comes to referencing individual cells or creating custom formulas. The only limit here is the general 5 million cells/spreadsheet limit.
You also don’t have to have any special Gapps account – it will work even with a most basic @gmail account.
The downside is that, as I said, Supermetrics for Google Sheets is a paid product. You can learn more about it here:
Google Apps Scripts
The last and the least preferred option is to use a custom Google Apps Script which is a very complicated and unreliable solution. The benefit is that’s it’s free as long as you can put your script together. I’ve been through this long time ago and I swapped to Supermetrics because I did not want to keep losing time on configuring a script for every individual spreadsheet which is pain.
I am not gonna describe this route, I just want you to know that this option exists and you can try to figure it out based on this article.
Conclusion
Even though this is a quite basic article, I hope it helped you to get more insights into getting data from BigQuery to Google Sheets.