Google BigQuery is a robust cloud data warehouse that centralizes data management and processing. The platform offers a lightning-fast query engine to pull required data from terabytes of datasets in seconds. This raw data can then be processed, analyzed, or shared through tools such as Microsoft Excel.
You can do it manually by exporting the query results into a CSV file. But, this is not a realistic option, especially when working on dynamic datasets. Continue reading to learn how to connect BigQuery to Excel and automate data sync between the platforms.
Microsoft Excel is a popular spreadsheet tool with excellent data analytics and visualization features. On the other hand, BigQuery is a powerful cloud data warehouse to store and manage large datasets and access the data in real-time. Therefore, connecting BigQuery to Excel can unlock new possibilities for your business, such as:
Access to large datasets – Connecting BigQuery to Excel enables you to access large datasets and extract data in real-time without any performance issues.
Rich data analysis & visualization – You can use Excel’s data analysis and visualization features to get more insights out of the data pulled from BigQuery.
Ease of collaboration – Excel spreadsheets are comparatively easy to share and collaborate with others. You can connect BigQuery to Excel, pull the required data, and easily share it with others.
Automate data reporting – You can automate regular report generation by connecting BigQuery to Excel to automatically pull the required data from BigQuery.
Other integration possibilities – BigQuery supports many data sources, such as Google Analytics and Google Ads. Connecting BigQuery to Excel can enable you to pull data directly from other integrations to Excel quickly.
Options to connect BigQuery to Excel
Here are the three most actionable ways to connect BigQuery to Excel:
Coupler.io – The most feasible method to connect BigQuery to Excel using an ETL tool by Coupler.io that supports automatic data refresh for real-time data sync.
Power Query – You can connect BigQuery to Excel using Power Query, the ETL add-in, with the help of the ODBC driver.
BigQuery API – Programmatic method to load and access data from BigQuery to Excel. Suitable for developers with coding expertise.
Now, let’s go through each of the methods one by one.
Automate BigQuery to Excel connection using Coupler.io
Coupler.io is an easy-to-use data analytics and integration platform that automates data flow and reduces manual tasks. It provides an ETL solution to integrate BigQuery with Excel, with scheduled auto-refresh to stay up-to-date with the latest data.
Furthermore, the platform offers a data experts service for more advanced data automation and analytics tasks. With it, you can build activity monitors to make solid business decisions using the latest data. Here’s an example of connecting BigQuery to Excel using Coupler.io.
Steps to connect BigQuery to Excel using the ETL tool by Coupler.io
To connect BigQuery to Excel and automate the data importing, create a new Coupler.io account and log into the dashboard. Add a new importer and select BigQuery in the source and Microsoft Excel in the destination. This configuration will let you move data from BigQuery to Excel. However, you can also reverse the data flow from Excel to BigQuery using Coupler.io.
Source
In the Source Account, connect to your BigQuery project by uploading the .json key file generated from BigQuery. Learn how to get it.
Enter a custom SQL query to export specific data from BigQuery to Excel.
Click on Continue to proceed further.
Destination
Connect your Microsoft OneDrive account in the Account.
Select the Workbook in your OneDrive where you want to export data from BigQuery.
Next, select the worksheet where you want to import the data.
Click on Jump to Schedule Settings to jump to the next section.
Or you can also configure these optional settings for the destination:
Specify the cell address of the destination worksheet.
In the Import Mode, you can choose from the following options:
Append: Old data will remain as it is. Coupler.io will append new data to the worksheet.
Replace: The old data will be replaced by a fresh one.
You can also add a date and time column to keep track of the last update and skip data importing if nothing has changed.
Click on Continue to proceed to the next section.
Schedule
Enable the Automatic data refresh option to auto-update data in Excel from BigQuery at regular intervals.
Select the appropriate interval, weekdays, and timing, and click on Save and Run.
On successful BigQuery export to Excel, a success message will be displayed on the screen. Here is what the data exported from BigQuery to Excel will look like:
Coupler.io did a fantastic job here! 👏 It pulled the required data from BigQuery directly into the Excel worksheet. Furthermore, it will update the worksheet regularly with new data per the configuration.
How to connect BigQuery to Excel Using Power Query?
Power Query helps you connect Excel to external data sources and import data from them. It used to be a separate tool. It has been fully integrated into Excel since the 2016 version, under the Get & Transform Data section in the Data tab.
Google offers ODBC & JDBC drivers to connect BigQuery to other applications. These drivers are provided in collaboration with Simba, a leading data connectivity solutions provider. In this example, we will use the ODBC driver as ODBC is one of the supported data sources by Excel using the Get Data feature.
In the start menu search bar, look for ODBC and click on the ODBC Data Sources app.
In the app, click on the System DSN tab, select Google BigQuery,and click the Configure button.
On the Data Source Name (DSN) setup, select OAuth Mechanism as User Authentication from the dropdown and click the Sign In… button to allow access to the BigQuery data.
You will be redirected to the Google login page. Log into your Google account and authenticate the access by clicking on Allow.
On successful authentication, your browser will show a success message.
Close the browser and return to the previous window, where you’ll find the Refresh Token added to the DSN configuration.
Select the project and dataset you want to export from BigQuery to Excel and click OK to save the configuration.
Lastly, click OK to save the configuration.
Now, take a moment to congratulate yourself as you’ve successfully configured ODBC on your computer. You’re already halfway to connecting BigQuery to Excel!
Steps to load data from BigQuery to Excel
Once you have configured the ODBC driver on your computer, you can use ODBC as a data source to perform BigQuery export to Excel. Create a new Excel worksheet, and follow these steps to connect BigQuery to Excel:
Select the cell where you want to import data from BigQuery, go to Data > Get Data > From Other Sources,and select From ODBC.
Select Google BigQuery as the Data source name (DSN) and expand the Advanced Options to enter the SQL Query for the data you want to load to Excel. Enter your custom query and click on OK to proceed.
Bonus Tip: Run and check the query in the BigQuery interface before entering it in Excel to avoid issues.
On successful execution of the query, the data will be displayed on the screen. Click on Load to export data from BigQuery to Excel.
Cheers! You’ve successfully exported BigQuery to Excel.
Set up auto-refresh data in Excel from BigQuery
Once you’ve loaded the data from BigQuery to Excel, you can refresh the data to update it.
To manually refresh the data, switch to the Data tab and click on the icon above the Refresh All option in the Queries & Connections section. (Shortcut Key: Alt + F5)
Furthermore, you can configure the auto-refresh functionality at regular intervals. Simply follow these steps to do that:
Go to Data > Queries & Connections > Refresh All and click on the last Connection properties option.
Next, in the Query Properties menu, tick against the Refresh every option, and enter the auto-refresh interval (in minutes) in the field next to it. Finally, click on OK to save the changes.
Now, Excel will auto-refresh the connection and update the data from BigQuery at regular intervals, which is 10 minutes in the above example.
How to connect BigQuery to Excel via API?
Connecting BigQuery to Excel via API is a highly technical method that requires extensive coding skills and knowledge of APIs. BigQuery offers REST APIs to connect the platform with other tools and services, which can be enabled from the Google Cloud Console.
To connect BigQuery to Excel via API, follow these steps:
Enable BigQuery API: In your Google Cloud Platform, click on the hamburger menu at the top-left corner, and go to APIs and Services > Library. Next, find the BigQuery API using the search box, and enable it.
Create a Service Account: Next, you must create a service account to authenticate the API requests. Go to IAM and Admin > Service Accounts and create a service account. Enter service account details, and select the BigQuery Data Editor or BigQuery Data Viewer role to grant access to the project.
Generate a Private Key: Open the service account, and go to the Key tab. Click on Add Key > Create New Key and create a new JSON key.The .json file will start downloading.
Install the BigQuery API Client Library: Now, install the BigQuery API client library for the programming language you use to interact with the API.
Make API Request to Export Data from BigQuery: Finally, make an API request from the programming language of your preference to get data from BigQuery, and convert it to an Excel file. Here’s an example of Python code to do that:
from google.cloud import bigquery
import pandas as pd
client = bigquery.Client.from_service_account_json('path/to/service_account.json')
# Set up the job configuration
job_config = bigquery.ExtractJobConfig()
job_config.destination_format = 'CSV'
job_config.print_header = False
# Set up the destination URI
bucket_name = 'my-bucket'
destination_uri = f'gs://{bucket_name}/my-table.csv'
# Export the table
table_ref = client.dataset('my-dataset').table('my-table')
extract_job = client.extract_table(
table_ref, destination_uri, job_config=job_config
)
# Download the exported CSV file and load it into a Pandas dataframe
bucket = client.get_bucket(bucket_name)
blob = bucket.blob('my-table.csv')
csv_string = blob.download_as_string()
df = pd.read_csv(BytesIO(csv_string))
# Save the dataframe to an Excel file
df.to_excel('my-table.xlsx')
In the above code, we’re making an API request using the Python library to get data from BigQuery, which is then loaded into Pandas DataFrames (2-dimensional data structure in Python.) Lastly, the to_excel function converts it to an Excel file.
Remember, this is just a general example of loading data from BigQuery to Excel via API. You may need to modify the code as per your expectations. For more details, you can refer to the BigQuery REST API Documentation.
Considering the technical complexities, it is not a convenient method unless you are an experienced engineer or analyst.
Connect BigQuery to Excel: Which is the best method?
BigQuery-Excel integration synergizes the powerful data processing capabilities of BigQuery with Excel’s rich analytics and visualization features. As discussed above, there are multiple ways to connect BigQuery to Excel.
The Power Query method may appear straightforward here but lacks automated real-time data sync. You need to refresh the data manually to stay updated and keep the Excel window active to make the auto-refresh feature work.
Connecting BigQuery to Excel using Coupler.io is the most efficient method of all. It regularly enables real-time data sync between BigQuery and Excel to help you stay updated with fresh data without any manual effort.
A content manager at Coupler.io whose key responsibility is to ensure that the readers love our content on the blog. With 5 years of experience as a wordsmith in SaaS, I know how to make texts resonate with readers' queries✍🏼
