If you need to export SQL query results from BigQuery to Google Sheets (and you don’t want to mess around with an app script), you should consider one of the following ready-to-use options:
- BigQuery web UI
- BigQuery data connector
- Google Sheets add-on
How to export BigQuery data to Google Sheets
BigQuery web UI
The simplest solution is to save the SQL query results to a spreadsheet using the web UI. After you’ve run your query, click the Save Results button and choose where to save the results.
The drawback of this option is that you can only export up to 16,000 rows to Google Sheets and there is no automation. So, every time you want to refresh the data, you’ll have to do it manually.
BigQuery data connector
If your G Suite account type is Enterprise, Enterprise for Education, or Essentials, you can access your BigQuery data from a spreadsheet via a BigQuery data connector.
The obvious drawback of the native BigQuery connector is that not every user can afford it. Besides, it only allows you to pull up to 10,000 rows from BigQuery. At the same time, you can automate data refresh on a schedule, but you’ll need to write some script for that. 🙁 So, coding skills are required here.
Google Sheets add-on
It’s simple to use (no coding at all) and lets you entirely automate imports of SQL queries from BigQuery into Google Sheets. Here are some other benefits you can expect from Coupler.io:
- Import of more than 15,000 rows from BigQuery
- Automated data refresh with a selectable schedule
- One tool to export data from BigQuery, as well as other sources, such as Airtable, Xero, Pipedrive, etc.
Let’s see how it works.
How to connect BigQuery to Google Sheets
- Install Coupler.io from the G Suite Marketplace.
- Set up a BigQuery importer, which will act as the BigQuery – Google Sheets connector.
- Run the BigQuery importer to import data into Google Sheets.
Find the add-on on the G Suite Marketplace or install it via this direct link.
Set up a BigQuery importer
An importer is a connector between Google Sheets and a chosen data source. Since you need to import data from BigQuery, you’ll need to set up a BigQuery importer. For this, open Coupler.io in the Add-ons tab of your spreadsheet, click on the +Add Importer button and select BigQuery.
The BigQuery importer will open, and you’ll need to fill out the following fields:
- Title – add the name of your BigQuery Google Sheets connector.
- Sheet Name – add the name of the sheet that will be receiving data.
- Google Project ID – insert the unique ID of your project. Example:
You can find your project ID in many places:
Drop-down menu to select projects
Navigation menu => IAM & Admin => Settings
- SQL Query – insert the query statement to import the query results. Example:
SELECT last_name, email FROM 'secret-project-282206.Test.Table1' LIMIT 10
This field is required, so you can’t leave it blank.
- Key File – insert the key to your service account in JSON format.
How to create a key for a service account in BigQuery
Step 1: Go to Navigation menu => IAM & Admin => Service Accounts
Step 2: Click Create Service Account.
It’s better to create a new account even if you have one, since you’ll need to provide specific Service account permissions.
Step 3: Add the name of your Service Account (required) and then click Create.
Step 4: On the Service account permissions page, add two roles:
– BigQuery Data Viewer – access to view datasets and all of their contents
– BigQuery Job User – access to run jobs
Click Continue and then click Done.
Step 5: We can now get to the creation of a private key. Click on the three dots of your service account and choose Create key.
Step 6: Select JSON as the key type and click Create. Your private key will be downloaded as a JSON file to your computer.
Step 7: Open the JSON file, copy the entire string and paste it into the Key File field. Here is an example of a JSON key string:
Automatic data refresh
This feature allows you to schedule automatic data imports from BigQuery. Choose the frequency to enable automatic data refresh. If you don’t need it, choose Never or toggle off.
The Settings section is optional. You can learn more about it in the Coupler.io knowledge base.
Once all the parameters are set up, click Add Importer to save your BigQuery to Google Sheets connector. After that, run it to import data into the spreadsheet, as follows:
Use case: Create an SQL query for a BigQuery dataset and import the results into Google Sheets
Let’s check out this BigQuery to Google Sheets connector in practice. We have a BigQuery dataset for 1000 rows:
What we need is to import the data filtered by the following conditions:
- Include four columns: Applicant_Name, Position, Stage_Name, and Applicant_Status
- Filter data by the applicant status “open“
- Filter data by three countries: Poland, Brazil, and Ukraine
The SQL Tutorial for Beginners on the Railsware YouTube channel helped us create a correct query:
SELECT Applicant_Name, Position, Stage_Name, Applicant_Status FROM `project-for-coupler.Applicants.Applicants` WHERE Applicant_Status="open" AND Country IN ("Poland", "Brazil", "Ukraine")
To import the query results into a spreadsheet, we updated the BigQuery importer that we set up before, and inserted this SQL Query into the respective field. Here’s what we got:
To wrap up
It did not take much time to set up a connector and pull data from BigQuery to Google Sheets. The trickiest thing in this integration was creating a service account key, but it’s quite doable. On the other hand, you won’t have to mess around with any script or coding at all. So, this BigQuery to Google Sheets tutorial should be quite handy for different data enthusiasts. Good luck!Back to Blog