How You Can Connect BigQuery to Excel for Seamless Data Management

Google BigQuery (GBQ) doesn’t require additional maintenance costs and processes your data in less than a minute. You can learn about uploading data to GBQ from CSV and JSON files, using the BigQuery API, Google Analytics 4, or from other Google services in our previous article. You can also learn more about setting up the OWOX BI BigQuery Reports Add-on and automating reports in Google Sheets based on information from Google BigQuery. Today, we’ll tell you how to upload data from BigQuery to your all-time favorite – Excel.

To learn more about the benefits and ease of use of Google’s cloud-based data storage, read our detailed overview of BigQuery features. You can also watch the video “What is BigQuery?” by DevBytes, and read about five reasons why building reports is better in BigQuery.

Note: This post was originally published in Feb 2020 and was completely updated in March 2024 for accuracy and comprehensiveness.

BigQuery, developed by Google, is a cloud-based big data analytics platform tailored for large-scale enterprises. It enables the swift querying of vast datasets, often comprising thousands of rows, thanks to its powerful processing capabilities. By integrating BigQuery with your applications, you gain the ability to ingest and analyze extensive data volumes in real time, facilitating up-to-the-minute insights for business initiatives.

Signing up for an account grants access to BigQuery's features, with the option of a monthly flat-rate payment plan. This plan offers businesses a predetermined data import and querying capacity, supporting multiple concurrent project analyzes.

Connecting BigQuery to Microsoft Excel opens up enhanced capabilities for businesses by leveraging Excel's robust data analytics and visualization tools alongside BigQuery's scalable cloud data warehouse for handling vast datasets in real time. This integration offers several key benefits:

• Seamless Access to Extensive Datasets: By linking BigQuery with Excel, businesses can effortlessly access and extract sizable datasets in real-time, bypassing any performance bottlenecks.
• Advanced Data Analysis and Visualization: Utilizing Excel's comprehensive data analysis and visualization capabilities allows for deeper insights from BigQuery data, enhancing decision-making processes.
• Improved Collaboration: Excel's user-friendly format facilitates easier sharing and collaboration. Connecting to BigQuery enables users to extract necessary data into Excel, simplifying teamwork and data sharing.
• Streamlined Data Reporting: Establishing a connection between BigQuery and Excel paves the way for automating routine data reporting, ensuring timely and accurate data retrieval from BigQuery.
• Expanded Integration Options: BigQuery's compatibility with various data sources, including Google Analytics and Google Ads, means that if you connect Google BigQuery with Excel, it allows for direct and efficient data transfers from these platforms into Excel for further analysis.

This synergy not only amplifies data handling efficiency but also enriches the analytical outcomes available to businesses, making it a strategic move for leveraging data-driven insights.

Connecting Google BigQuery with Microsoft Excel opens up a box of opportunities for data analysts and professionals to harness the combined power of BigQuery's extensive data analytics capabilities and Excel's versatile data manipulation and visualization tools.

There are several methods available to establish this connection, each tailored to meet different needs and technical requirements. From utilizing Power Query – a feature within Excel that simplifies data import and transformation – to leveraging Directly Import data from Google BigQuery to Excel Using IQY file for a more direct data import, the choice of method depends on factors such as the need for real-time data updates, ease of setup, and specific project requirements.

This introduction explores the various approaches to seamlessly import data from BigQuery into Excel, enhancing data analysis and reporting workflows.

1. To import data from Google BigQuery to Excel, first of all, you need a unique key to run queries against BigQuery. You can create this key any time you like, but remember that it has an expiration date.

If necessary, you can always create a new key.

You can also expire your current key using the Revoke Key button or in your Google profile settings.

2. In Excel, create a new worksheet and provide the following information in the format shown in the screenshot below:

• Your project ID
• Your unique key
• Your query

Note that if your query exceeds 256 characters, Excel won’t run it. In this case, you should split your query into parts and insert them into adjacent cells.

3. Next, download the IQY file below.
4. In the Data tab in Excel, select Existing Connections. In the window that appears, click Browse for more and select the IQY file you just downloaded.

5. The first time you connect, you’ll need to specify where your data will appear. Choose a cell in your current worksheet.

6. In the following windows, specify the values of the cells containing your query, project ID, and connector key.

It’s ready! Your BigQuery data will now appear in Excel.

As an alternative way to connect to Excel, you can use the Magnitude Simba ODBC driver for BigQuery, which is explained down below. Detailed instructions can be found on YouTube: How to Connect Google BigQuery to Microsoft Excel.

Connecting BigQuery to Excel using the API involves a technical process that leverages BigQuery's REST APIs for integration with external applications, necessitating a good grasp of programming and API usage. Given its technical nature, this method is best suited for those with engineering or analytical expertise, offering a powerful way to leverage their skills for advanced data integration tasks. Here's how to establish this connection:

1. Begin by accessing your Google Cloud Platform (GCP) account, navigate through the menu to 'APIs & Services' > 'Library', search for the BigQuery API, and enable it.

2. Create a service account by going to 'IAM & Admin' > 'Service Accounts' in your GCP console. Fill in the necessary details for the account and assign it a role with permissions for BigQuery, such as BigQuery Data Editor or BigQuery Data Viewer, to ensure it has the required access.

3. Inside your service account, navigate to the 'Keys' section, add a new key, select the JSON format, and download it. This key will authenticate your API requests.

4. Depending on the programming language you're using, install the BigQuery client library to facilitate communication with the BigQuery API.

5. Use your chosen programming language to make an API request to BigQuery, extract the desired data, and process it into a format suitable for Excel. For example, in Python, you would use the google.cloud.bigquery client to fetch data, load it into a Pandas DataFrame, and then export it to an Excel file using the DataFrame.to_excel() method.

Here's a simplified Python code example for fetching data from BigQuery and exporting it to Excel:

This code snippet outlines fetching data from BigQuery, converting it to a CSV format, downloading it, and then exporting it to Excel. Modify the code according to your specific requirements. For comprehensive information, consult the BigQuery REST API documentation.

Power Query enhances Excel by enabling connections to various external data sources, a capability seamlessly integrated into Excel under the "Get & Transform Data" section in the Data tab.

For this example, we'll utilize the ODBC driver as a BigQuery Excel connector, one of the data source options supported by Excel's Get Data feature, to connect to Google BigQuery. Google, in partnership with Simba – a leader in data connectivity solutions, provides ODBC and JDBC drivers for connecting BigQuery with Excel and also other applications.

To connect your BigQuery account to Excel, first, ensure you download and install the most recent version of the Google BigQuery ODBC driver on your system. Following the installation, proceed with the steps below to establish a connection with your BigQuery account.

1. Download and install the latest ODBC driver for Google BigQuery.

2. Open the ODBC Data Sources app by searching for "ODBC" in the start menu search bar.

3. Within the app, navigate to the System DSN tab, select "Google BigQuery," and click on the "Configure" button.
4. In the Data Source Name (DSN) setup, choose "User Authentication" as the OAuth Mechanism from the dropdown menu and click on the "Sign In…" button to initiate access to BigQuery data.

5. Authenticate access by logging into your Google account and allowing the connection when prompted.

6. After successful authentication, a success message will appear in your browser. Close the browser and return to the DSN configuration window, where the Refresh Token should now appear.
7. Choose the specific project and dataset in BigQuery that you wish to export to Excel and save your configuration by clicking "OK."
8. Save the DSN configuration with another click on "OK."

After setting up the ODBC driver as a BigQuery connector for Excel on your system, you're ready to utilize it as a data source for exporting data from BigQuery into Excel. Begin by opening a new Excel worksheet, and then proceed with the following steps to establish a connection between BigQuery and Excel.

Here's how you can load the data from BigQuery to Excel:

1. In a new Excel worksheet, select the cell where you wish to import BigQuery data. Navigate to Data > Get Data > From Other Sources, and choose "From ODBC."

2. Select "Google BigQuery" as the Data Source Name (DSN) and use the Advanced Options to input the SQL Query for the data you wish to load into Excel. Confirm your custom query by clicking "OK."

3. Before executing the query in Excel, it's wise to first run and verify it within the BigQuery interface to ensure there are no errors.

4. Once the query executes successfully, the queried data will be displayed. Proceed by clicking on "Load" to import the data from BigQuery into Excel.

You've now adeptly navigated the process of connecting BigQuery to Excel via ODBC and have successfully imported your data into Excel.

Automatically updating data from BigQuery in Excel is a powerful feature that ensures your analysis is always based on the most current information. After you have successfully imported data from BigQuery into Excel, it's crucial to maintain the freshness of this data to reflect any changes or updates that occur in the BigQuery database.

Excel facilitates this through its ability to refresh data both manually and automatically. Manual refreshes are useful for ad-hoc updates, while automatic refreshes are ideal for maintaining up-to-date data without constant manual oversight. Setting up automatic data refreshes can save significant time and effort, allowing you to focus on analysis rather than data management.

This feature is especially beneficial in dynamic environments where data changes frequently, ensuring that your Excel workbook reflects the latest data with minimal effort. After importing data from BigQuery into Excel, ensuring the data remains current is essential.

Manually refreshing data in Excel involves a simple process to ensure your workbook contains the latest information from BigQuery. Navigate to the Data tab and locate the refresh icon positioned above the "Refresh All" option within the "Queries & Connections" section.

Clicking this icon initiates an immediate data update. Alternatively, for a quicker method, you can utilize the shortcut key Alt + F5. This action forces Excel to fetch and update the imported data, ensuring your analysis is based on the most current dataset available.

For continuous data updates without manual intervention, Excel allows you to set up automatic data refresh at specified intervals. Here’s how you can activate this feature:

1. Navigate to Data > Queries & Connections > Refresh All, then choose the "Connection Properties" option at the end of the list.

2. In the Query Properties menu, check the "Refresh every" box and specify your desired auto-refresh interval in minutes in the adjacent field.

3. Click "OK" to apply and save your settings.

With these steps, Excel will automatically refresh and update the BigQuery data at the intervals you've set, ensuring your data analysis remains up-to-date without needing manual refreshes.

Now you know how to load data from Google BigQuery cloud storage to Excel.

And if you want to learn more about GBQ and other analytics tools, subscribe to our newsletter. Every month you’ll get useful tips for modern marketers and analysts.

🚀 BigQuery data structure in Google: How to get started with cloud storage

☕️ Top 6 BigQuery Visualization Tools

🚀 Automate Reports in Google Sheets Using Data from Google BigQuery

🚀 Standard SQL in Google BigQuery: Advantages and Examples of Use in Marketing

▶️ Google Developers YouTube channel

▶️ Google Cloud Platform YouTube channel