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

Downloads
590.48 Kb


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.

Before you can upload data from BigQuery, you must collect and merge it into data storage. If you haven’t done so yet, use OWOX BI for this task. 

How to import data from Google BigQuery to Google Sheets

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.

Download add-on
Google Sheets menu
  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.
adding 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.
a new SQL query
  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.

How to import data from Google BigQuery to Excel

  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.
unique key menu

If necessary, you can always create a new key.

creating of a new key

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

Revoke key
  1. 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
Excel spreadsheet

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.

  1. Next, download the IQY file below.
Download IQY file
  1. 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.
Existing connections
  1. The first time you connect, you’ll need to specify where your data will appear. Choose a cell in your current worksheet.
Specify the place for your data
  1. In the following windows, specify the values of the cells containing your query, project ID, and connector key.
Specify the values

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.

Short conclusions

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.

SUBSCRIBE TO NEWSLETTER

Useful links