Even though we live on BigQuery, Google Sheets can still be an extremely helpful way to visualize our data.
We use it anytime we're generating a task list from our BigQuery data, or sharing specific raw data for review with a non-BigQuery user.
We use these three methods to pull data from BigQuery into Google Sheets:
For BigQuery Users: Export Results from a Query Run
For this step, some knowledge of BigQuery and SQL may be required. Check our docs on Writing SQL in BigQuery for a better reference.
After running a query from the BigQuery query editor, you can export the results directly into a Google Sheets table.
After you run the query, click on the SAVE RESULTS option below the query editor.
A new window will pop up with several options on how to save the results. Among the potential options you'll see:
Export to Google Sheets
Save CSV file directly to Google Drive
Download a local CSV File
Copy to Clipboard
The first option will create a Google Sheet for you.
Alternatively, you can download a CSV and import it manually.
Our favorite option, is Copy to Clipboard, which allows you to paste query results directly into a Sheet, without creating a new Sheet.
Choose the option you prefer, and don't forget to click 'Save' to activate it (it's simple, but we always forget that step).
DISCLAIMER: This option has a size limit of 16k rows or 1GB of data.
For Data Studio Users: Export to Sheets from a Data Studio Report
From any chart in Data Studio, you can export the underlying data directly to Google Sheets.
All of the Data Studio report templates provided by query.recipes include an explicit 'Export' report page expedite this process, which usually is used to generate some type of task or action list.
This is helpful for non-BigQuery users, who may not be comfortable writing SQL queries to export data.
We'll be using the Export page from the Website Quality Audit dashboard for reference - you can export data to Sheets following these steps:
Navigate to the Export page (or any report page + widget you'd like to export).
Pick the table or chart for export (you can export data from one at a time), and hover over to the hamburger menu to the top right of the table.
On click of the menu, you'll see export options appear. Select Export to Sheets, and away you go.
For Google Apps Script Users: The CIFL Sheets <> BigQuery Connector
A while back, we on the CIFL built a Sheets to BigQuery connector for internal use.
It's an open-source script built in Google Sheets (on Google Apps Script), and allows you to easily:
Upload data from Sheets to BigQuery
Query it back down from BigQuery to Sheets
The Getting Started tab of the Sheet contains detailed instructions on how to use it, including this video walkthrough of the setup:
We've got our eyes on Google's Connected Sheets beta, which will allow you to visualize BigQuery tables as pivot tables, directly within a Google Sheet.
Our team has been testing it since the alpha in 2019 and loving it, and are hoping that it'll be more widely available soon (currently available for only Enterprise-level users).