If you need to export SQL query results from BigQuery to Google Sheets (and you don’t want to mess around with the Apps Script), you should consider one of the following ready-to-use options:
- BigQuery web UI
- BigQuery data connector
- BigQuery integration by Coupler.io (web app and Google Sheets add-on)
If you don’t have time for reading about all the options, check out the video on how to connect BigQuery to Google Sheets using Coupler.io web app.(below in the text, we’ll introduce the flow for the Coupler.io add-on)
BigQuery export to Google Sheets – What options are available
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. Read more about querying options and settings in our BigQuery tutorial.
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.
BigQuery – Google Sheets integration
Coupler.io is a solution to integrate different data sources, including BigQuery, with Google Sheets for data import. Learn more about which Google Sheets integrations are available.
You can use Coupler.io directly from your browser or as a 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 this integration with BigQuery works in the example of the add-on.
Connect BigQuery to Google Sheets
Find Coupler.io on the Google Workspace Marketplace or install it via this direct link.
After that, open your Google Sheets document, go to the Add-ons menu => Coupler.io => Open dashboard. Then click on the +Add Importer button and select BigQuery.
BigQuery to Google Sheets – How to set up
Complete the following steps to configure the BigQuery importer:
Step 1: Title
Add the name of your BigQuery Google Sheets connector.
Step 2: Source
Click the Connect button to connect BigQuery to Google Sheets. In the open window, you’ll need to select a .json key file generated for a specific BigQuery project.
How to get Google BigQuery credentials .json key file
1. Open the Google Cloud Platform Console, go to Navigation menu => IAM & Admin => Service Accounts
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.
3. Add the name of your Service Account (required) and then click Create.
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.
5. We can now get to the creation of a private key. Click on the three dots of your service account and choose Manage keys.
6. Click Add key and select Create new key.
7. Select JSON as the Key type and click Create. Your private key will be downloaded as a JSON file to your computer.
Now you can select this .json key file to connect BigQuery to Google Sheets. Click Save once you’ve selected it.
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. Read our BigQuery SQL tutorial to learn more about SQL queries.
Step 3: Destination
Connect a Google account you want to import data to. You’ll need to sign in to the chosen account and confirm the access of rights granted to Coupler.io.
In this section, you can also change the name of the sheet that will be receiving data from BigQuery. If you click Show advanced, you’ll be able to choose the first cell where the data will be imported.
Step 4: Settings
In the Settings section, you can enable the Automatic data refresh functionality. This will allow you to automate data imports from BigQuery on a custom schedule, such as every hour, every Monday, etc. To do this, toggle it on and choose the frequency you want. For other options hidden under the Show advanced button, read the Coupler.io knowledge base.
Once all the parameters are set up, click Save & Run to save your BigQuery to Google Sheets connector and run the initial import. Welcome your data in your spreadsheet.
In a similar way, Coupler.io allows you to load your data from Google Sheets to BigQuery.
How it works: SQL query for BigQuery export to 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 export the query results from BigQuery to Google Sheets, we inserted this SQL Query into the SQL query field and ran the importer:
Do I need to export BigQuery to Google Sheets on a schedule?
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, and your data can be exported on your custom schedule. So, this BigQuery to Google Sheets integration should be quite handy for different data enthusiasts. Good luck!Back to Blog