Google BigQuery is a fully-managed data warehouse where you can store, analyse data assess the data. It gives you a bird’s eye view of your data. For a more granular analysis, though, Google Sheets would serve a better purpose. A worm’s eye view from Google Sheets combined with the bird’s eye view from BigQuery will give a more complete view of the picture thus minimising the chance of minute but critical trends being overlooked.
Another benefit of connecting BigQuery data to Google Sheets is the ability to easily share the data and collaborate with team members and clients.
We can transfer data from BigQuery to Google Sheets by connecting them using a Google Sheets extension–Amigo–and retrieve the data automatically and periodically.
Let’s learn how to connect BigQuery to Google Sheets and import BigQuery data to Google Sheets using Amigo without coding or knowledge of API. And automate the data transfer from BigQuery to Google Sheets.
Steps to connect and transfer data to BigQuery to Google Sheets
Install the Amigo add-on to connect BigQuery to Google Sheets
The Amigo add-on acts as a bridge allowing you to move loads of data from BigQuery to Google Sheets in seconds.
Get it from the Google Workspace Marketplace by clicking the link given below.
Alternatively, you can get it from within Google Sheets itself.
- Open a Google Sheets document
- Go to Extensions→Add-ons→Get add-ons
- Search for Amigo and install
Once the installation is complete, the Amigo add-on should appear in your Google Sheets Extensions list.
Connect BigQuery to Google Sheets
Open Google Sheets and launch Amigo by clicking Extensions→Amigo: Data Exports and Reports→Connect to connect BigQuery to Google Sheets.
From the list of available sources to connect select BigQuery and click Import from Tables.
Enter the project ID of the project you want to connect. Then click Authorize to connect BigQuery to Google Sheets.
The project ID can be found in Google API Console.
After connecting BigQuery to Google Sheets, we can retrieve data from BigQuery to Google Sheets with just a few clicks and automate the data transfer.
The connection will be added to the sources list; clicking it will take us to the BigQuery report creation dashboard in Amigo where we can select and customise the data to export to Google Sheets.
Transfer data from BigQuery to Google Sheets
On the dashboard to the left is a list of data tables. Click on any one of them to preview.
All metrics are selected automatically. Unselect those that you don’t require and set a limit to the number of items to import with the limit button.
Name the report if you need to and then click the IMPORT button. In a few seconds the data from BigQuery will be imported into Google Sheets.
If you want the data to be updated periodically and automatically pull data from BigQuery to Google Sheets, set a refresh schedule by clicking the Yes, set a refresh timing. Or you can set it up later. To learn more on how ot set a refresh schedule and set up alerts to receive the report in your inbox or Slack, click here.
Amigo can connect Google Sheets to multiple sources and allows you to import data automatically. It is a convenient and versatile tool to import and export data to Google Sheets. Visit our blog to discover more.
FAQs
How do I connect BigQuery to Google Sheets?
Connect BigQuery to Google Sheets with the Amigo addon in three steps.
- Launch Amigo
- Select BigQuery and click “Import from tables”
- Connect by entering the project ID.
How do I automatically get data from BigQuery to Google Sheets?
To automatically retrieve data from BigQuery to Google Sheets, simply set a refresh schedule and specify the time. Amigo will then automatically retrieve data from BigQuery and update the data in Google Sheets at the time and frequency specified.
Some related articles you may find useful:
Importing MySQL Data to Google Sheets
Import Data From Amazon Redshift To Google Sheets