How to Use the BigQuery Connector for Excel

Generating reports manually requires time and can detract from your many other priorities, yet a data-driven organization has to sustain its reporting workflow to make key decisions.

While most of work revolves around data integration with cloud platforms and large datasets, there are use cases where Microsoft Excel is a viable solution for certain companies. If Microsoft Excel is one of your reporting tools and rely on BigQuery as a data source, you can escape the plight of manual reporting by using the BigQuery Connector for Excel. Learn about this dynamic BigQuery feature that will enable you to automate reports while requiring only minimal changes to the Excel file(s) you already have.

Advanced Solution for Reporting in Excel

There are many reasons some of your stakeholders in your organization may be using Excel for reporting as part of your reporting workflow. Excel remains a strong option for fast answers to business questions with minimal effort, and maybe your organization’s procurement requirements or other procedures make it difficult for you to use an enterprise-level reporting solution.

Here’s some good news: if you do your reporting in Excel, you, too, can implement advanced reporting solutions. Using the BigQuery Connector for Excel, you can automate your BigQuery data export and import it dynamically into your existing reports in Excel.

BigQuery Connector for Excel

BigQuery Connector for Excel is a great tool for automating existing reports. It’s super easy to use and maintain and does not require expert knowledge of BigQuery – you just need to have the required queries and you are set to go.

You can use this tool to run queries from within Excel and propagate the data to the desired cells connected to any charts. The BigQuery connector works with Microsoft Excel 2007 and higher, for both the Windows and Mac versions of Microsoft Excel. Also, it supports both legacy and standard SQL.

How to use BigQuery Connector for Excel

To be able to connect and execute BigQuery queries in Excel you only need the following:

IQY file (the connector)

You can download the IQY file here using your Google Analytics account.

GCP Project ID

The GCP project ID containing the tables or views used in BigQuery queries.

Unique key

You can generate the unique key through the same link above and specify expiration time (1 or 12 hours; or 1,2,7, or 30 days).

Query

You need to import a connector for each query. If the query script exceeds 256 characters long Excel will not run it, even if using merged cells. Strategies for overcoming this limitation are integrated into the example below.

Example

Here are some screens from a working example.

First place the project ID, Unique key and any number of queries in different cells as below:

As you can see, in the above screenshot each query is less than 256 characters so each query is in one cell (we are using BigQuery views in this case). If the query exceeds 256 characters it will need to be split into different cells with 256 characters long maximum in each cell as shown below.

To import the BigQuery Excel connector click on Data > Get External Data > Existing Connections (or Run Saved Query for Mac) then click Browse for More and navigate to the IQY file directory.

The connector has four prompts as below

1. 1. Import Data: to map the cells where the query outcome will be placed in the Excel sheet.

1. 1. Enter a query: reference to the BigQuery script cell(s)

1. 1. Enter a project ID: reference to the GCP project ID

1. 1. Enter your connector key: reference to the unique ID

Once all steps are completed, the connector will execute the query and retrieve the data to the mapped cells which could then be used as a source for different charts.

Refreshing the Data

Data can be refreshed in two ways:

Use any of the three inputs as trigger to refresh the data if changed.

1. Project ID
2. Unique key
3. Query (unless the query is split into several cells in this case it can’t be used as a refresh trigger)

Right-click on any of the BigQuery output cells and click Refresh.

Right-click on any cell and open “Data Range Properties” window:

The “Refresh every ___” option will prompt the connector to refresh the data every given number of minutes.

The “Refresh data when opening the file” option will execute the query once the user opens the Excel file. (This option is not recommended if the file has several connectors as it tends to take more time to load and sometimes crashes).

“Enable background refresh” option will enable the user to interact with the Excel file while the connector executes the query in the background. If not checked interactions will be locked until the data is retrieved.

“If the number of rows in the data range changes upon refresh” option has two main difference (Insert and Overwrite):

Insert: Will insert the new data and shift unrelated data to the left side.

Overwrite: Will overwrite existing data leaving unrelated data unchanged unless it is in the retrieved data cell range. (This is recommended especially if there are charts connected to the data, to not mess up the cells reference).

Gotchas

Rows/records: Excel can hold, at maximum, about 1 million records. This means that the BigQuery connector will not work if the query returns more than 1 million records.

Columns: There are no issues regarding number of columns as long as it stays within the 16K Excel columns limit.

Timeout: The connector must run the query and download the data in under 1 minute, or it will hit the web connector timeout limit and crashes.

If your data volume causes them to hit the limitations described above, you will need to consider switching to a more powerful report solution.