How to send data from Google BigQuery to Google Sheets and Excel

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, or from other Google services in our previous article. Today, we’ll tell you how to upload data from BigQuery to your all-time favorites — Google Sheets and 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.

The easy way to load data into Google Sheets is using the BigQuery Reports add-on from OWOX BI. You can install it for free directly from the Add-ons menu in Google Sheets, or you can download it from the Chrome Web Store.

1. Once you’ve installed the OWOX BI BigQuery Reports add-on, you’re ready to load data from storage. To do so, go to the Add-ons menu in Google Sheets, hover your mouse over OWOX BI BigQuery Reports, and select Add a new report.

1. In the add-on menu that appears on the right side of the screen, specify the name of your GBQ project. You can then either create a new SQL query for the selected project or select a previously used query from the drop-down list.

1. If necessary, define dynamic parameters for your query. Then click Add & Run.
2. Your data is ready! Data from GBQ will be uploaded to a new sheet in Google Sheets.

Advantages of the OWOX BI BigQuery Reports add-on:

• You can load data from BigQuery to Google Sheets and vice versa.
• You control access to your data.
• You can share data with colleagues in one click from Google Sheets.
• You get access to a simple query editor.
• Reports are automatically updated.

You can learn more about setting up the OWOX BI BigQuery Reports connector and automating reports in Google Sheets based on information from Google BigQuery on the OWOX blog.

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. Detailed instructions can be found on YouTube: How to Connect Google BigQuery to Microsoft Excel.

Now you know how to load data from Google BigQuery cloud storage to Google Sheets and Excel. If you have any questions, ask in the comments below:)

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

Google BigQuery Features — Detailed Review

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