What are the options to export BigQuery to Google Sheets
There are 4 ways to export BigQuery to Google Sheets:
- Save query results to Google Sheets – it’s a manual method that allows you to export the results of SQL queries from BigQuery to Google Sheets.
- BigQuery Google Sheets integration – it’s a ready-to-use BigQuery connector designed by Coupler.io. It allows you to automate exports of queries from BigQuery to Google Sheets on a custom schedule. You can check this method right away on our YouTube channel.
- Connected Sheets – it’s a brand new built-in BigQuery to Google Sheets connector that allows you to explore BigQuery tables in Google Sheets.
- Google Apps Script or another custom code solution – it’s a way for data engineers or software developers to build a custom BigQuery to Google Sheets connector using their coding skills.
All these methods are workable and suitable for specific needs or tasks. Which one is best for your particular business or project? This is what we’re going to find out by exploring each option. Oh, sorry, we won’t dive into the code-based option since it would require more than one article to cover all the details. So, let’s focus on the no-code ways to connect BigQuery to Google Sheets.
Connect BigQuery to Google Sheets with Coupler.io
Coupler.io is an all-in-one reporting automation solution to automate data exports. It lets you connect BigQuery to Google Sheets and automate data refresh on a schedule. It takes just a few minutes to set up the integration.
1. Collect your BigQuery data
To connect BigQuery to Google Sheets, click Proceed in the widget below.
- Create a Coupler.io account for free (no card required) and follow the wizard instructions to connect your BigQuery account. You’ll need a Google Cloud JSON key file. Add it to set up a connection between Coupler.io and your BigQuery project.
- Enter the SQL query to export data from BigQuery to Google Sheets.
In our example, the SQL query to export an entire table looks as follows:
SELECT * FROM `couplerio-demo.CSV.csv_data`
2. Transform and organize data
As Coupler.io is an ETL solution, it allows you to perform various data transformations:
- Sort and filter data
- Hide, rename and rearrange columns
- Split and merge columns
- Use formulas and perform calculations
- Blend or join data from several accounts or apps
For example, you can add multiple queries from different BigQuery tables, datasets, or projects, and consolidate this data in Google Sheets.
After this, follow the onscreen instructions to connect your Google account. Then, select the spreadsheet and the sheet where you want to load your data.
In a similar way, you can export BigQuery to JSON.
3. Schedule updates
To automate BigQuery export to Google Sheets, you need to complete one more step.
Coupler.io allows you to schedule automatic data refresh of your exported data at a custom frequency. It does not let you refresh the dataset in real-time, but you can set it to every 15 minutes! Toggle on the Automatic data refresh and configure the schedule:
- Choose interval
- Choose days of the week
- Choose time preferences
- Choose time zone
In the end, click Run importer to launch your integration. As a result, the BigQuery export to Google Sheets has successfully pulled 127,072 rows!
Save query results from BigQuery to Google Sheets
Let’s start with the simplest solution, which is to manually 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.
In a moment, a newly created Google Sheets document with your BigQuery data will open in a new tab of your browser.
This option only allows you to export data in the Google Sheets files up to 10 MB. The main drawback is that there is no automation. So, every time you want to refresh the data, you’ll have to manually export data again and again, flooding your Google Drive with newly created spreadsheets. So, let’s take a look at a more advanced way to export BigQuery to Google Sheets.
BigQuery to Google Sheets with Connected Sheets
Connected Sheets, a former Sheets data connector, is a built-in solution to explore BigQuery data in Google Sheets. It allows you to analyze BigQuery data without exporting it to Google Sheets. However, the latter is also possible if you need to get a table or query available in Google Sheets format. In addition, Connected Sheets supports data refresh on a schedule, which means you no longer need to manually export data from BigQuery. Let’s see how it works.
Export BigQuery tables to Google Sheets with Connected Sheets
You can open an entire BigQuery table in Google Sheets. For this, click the three dots next to the desired table, and select Open with => Connected Sheets.
Or, you can open your table in a new tab, then click Export => Explore with Sheets.
In a moment, a Google Sheets doc will open in a new tab and you’ll get a message that your BigQuery table is connected to Google Sheets.
Click Get started and you’ll see your BigQuery data in Google Sheets.
Keep in mind that the data is not actually imported into Google Sheets. For now, you can manipulate it in different ways, such as by building charts or pivot tables. If you need to export table data to Google Sheets, click Extract.
Select whether you want to extract data to a new or existing sheet, then confirm the extraction by clicking Apply. Here is what the exported data looks like.
To the right of the sheet, you will see the Extract editor, which allows you to select columns to synchronize, filter & sort data, as well as change the row limit.
Note: You won’t be able to extract more than 25,000 rows.
Export SQL queries from BigQuery to Google Sheets with Connected Sheets
If you only need to export an SQL query from BigQuery to Google Sheets, the Connected Sheets functionality can do this as well. Run your query, then go to the query results and click Explore Data => Explore with Sheets.
What happens next you already know: a new Google Sheets doc will open in a new tab, and you’ll get a message that your BigQuery data is connected to Google Sheets.
After this, you can work with this query data and even extract it if needed. And the most valuable thing is that you can automate the refresh of your data exported from BigQuery to Google Sheets!
How to automate BigQuery export to Google Sheets with Connected Sheets
Whether you connected your BigQuery table or SQL query with Google Sheets, you can automate its refresh on a schedule. For this, click the Schedule refresh button. The Refresh options panel will open to the right, where you can customize the schedule, namely:
- Repeat frequency – to refresh every [number of] hours, days, weeks, or months.
- Start date – the date and time range when the automatic refresh starts.
Click Save and enjoy your automated BigQuery export to Google Sheets.
Connected Sheets limitations
Connected Sheets seems to be an ideal option to connect BigQuery to Google Sheets. However, it has a few crucial limitations that may push you to search for an alternative Google Sheets connector:
- You are limited to 25,000 rows for data extraction
- The highest refresh frequency is every hour.
- Stakeholders must have access to the connected BigQuery project in order to be able to manipulate the data in Google Sheets. This means that if you do not have the respective permissions to the BigQuery project connected to Google Sheets, then you won’t be able to create pivot tables, add charts and perform other functions provided by Connected Sheets.
So, you may want to consider a solution without these limitations like connecting BigQuery to Google Sheets using Coupler.io.
Which is the best option to link BigQuery to Google Sheets?
Each method to send data from Google BigQuery to Google Sheets is good for specific needs. If you do not plan to make recurring exports or you tend to export different data with every export, then you can go with one-time manual export from BigQuery to Google Sheets. It works fast and won’t require any configuration or setup.
For recurring exports, Connected Sheets seems to be a good option. However, you should keep in mind the limitations that we mentioned above. Moreover, if you operate not only in BigQuery and Google Sheets as source/destination apps, then you’ll need to use other tools for data integration and automation. Therefore, in this case, it would be better to have an all-in-one solution, which Coupler.io definitely is.
Why should you consider a BigQuery – Google Sheets integration by Coupler.io?
BigQuery Google Sheets integration by Coupler.io is free of limits associated with Connected Sheets. In addition, it can provide you with scalability – you can export data not only from BigQuery to Google Sheets, but expand the list of sources with marketing, accounting, and project management apps. It supports Google Analytics 4, Google Ads, MySQL, Airtable, and many other sources. Not to mention that you can transfer data in the opposite direction – from Google Sheets to a BigQuery data warehouse. All this can significantly optimize your workflows.
So, consider the different options that we’ve mentioned and how they relate to your needs, and pick the option that works for you best. Good luck with your data!
Automate data export with Coupler.io
Get started for free