How to Connect BigQuery to Excel & Automate Data Sync

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. However, this is not a realistic option, especially when working on dynamic datasets. Learn how to connect BigQuery to Excel and automate data sync between the platforms using Coupler.io.

Options to connect BigQuery to Excel

Here are the three most actionable ways to connect BigQuery to Excel:

• Coupler.io – The reporting automation and data integration platform that supports automatic data refresh. It allows you to not only connect BigQuery to Excel but also work with your data on the go before it’s loaded to Excel. So, you can use it even if you are not very proficient in SQL. Just use select * for a table in BigQuery and organize your query in the Coupler.io UI.
• 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

To connect BigQuery to Excel and automate the data importing, create a new Coupler.io account and log into the dashboard. Or just use the form below to get started right away for free. After that, complete three steps.

Step 1. Select data from BigQuery

• 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.

SELECT * FROM `couplerio-demo.ga4.ga4data` LIMIT 1000

Step 2. Refine your query

Once Coupler.io preloads your query, you have the following options to refine it:

Step 3. Load query to Excel

• 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 or create a new one

And of course, enable the Automatic Data Refresh to always have up-to-date records from BigQuery in Excel. Coupler.io 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.

Download the latest version of the ODBC driver and install it on your computer. Once the driver is installed, follow these steps to connect your BigQuery account:

• 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.

Why connect BigQuery to Excel?

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.

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.