Analyzing BigQuery via Excel and Google Sheets
Both MS Excel and Google Sheets offer ways to connect directly to BQ data, to run queries, to pull data back to Excel/Sheets and allow further analysis via options such as pivot tables, charts and drilling up/down.
MS Excel
The link provided below details instructions for setting up an external datasource to BQ from Excel. Basically, we would need to provide Excel with the project ID, the query to run and the authorization key for it to run the query and pull the data. https://cloud.google.com/bigquery/docs/bigquery-connector-for-excelProviding project and query information
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/Screen-Shot-2018-06-29-at-11_57_09-AM.png?width=1159&height=311&name=Screen-Shot-2018-06-29-at-11_57_09-AM.png)
Analyzing retrieved data with pivot tables
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/Screen-Shot-2018-06-29-at-11_58_28-AM.png?width=1161&height=484&name=Screen-Shot-2018-06-29-at-11_58_28-AM.png)
Analyzing the pivot data further
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/Screen-Shot-2018-06-29-at-11_59_31-AM.png?width=1131&height=522&name=Screen-Shot-2018-06-29-at-11_59_31-AM.png)
Google Sheets
BigQuery allows ad-hoc queries to run via BigQuery UI and allows saving results data as Google Sheets directly in the user's Google drive. Via Google Sheets, the users can then pivot and perform analysis just like in Excel.Exporting data to Sheets
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/Screen-Shot-2018-06-29-at-12_00_36-PM.png?width=1130&height=551&name=Screen-Shot-2018-06-29-at-12_00_36-PM.png)
Analyzing via Sheets (sample view):
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/Screen-Shot-2018-06-29-at-12_01_38-PM.png?width=1131&height=443&name=Screen-Shot-2018-06-29-at-12_01_38-PM.png)
Sheets add-ons:
Add-ons for Sheets, provided by third parties, allow for easy integration with BQ, as well as running queries directly, extracting results and analyzing and visualizing data. A few popular Google Sheets add-ons for BigQuery:- OWOX BI (100% Google Cloud Platform)
- SuperMetrics
- Insight Metrix IM Funnel
Summary:
Sheets/Excel can be considered for any daily/periodic reports that require authorized users to run predefined queries, pull data and if needed, analyze further via drill down and drill up capabilities using pivot tables. For more complicated query analysis or for queries that could return very large data results, we could consider Looker, Tableau, or DataStudio which can all handle better visualization along with drill-through capabilities. BI tools can handle large results better and as well, allow dynamic drill down/up and drill through capabilities.Share this
Next story
The night of the living files →
You May Also Like
These Related Stories
Amazon database migration service - first try
![](https://www.pythian.com/hubfs/Imported_Blog_Media/Screen-Shot-2016-03-16-at-1_26_40-PM.png)
Amazon database migration service - first try
Mar 28, 2016
6
min read
Favorite way: migrating to exadata
Favorite way: migrating to exadata
Sep 9, 2015
1
min read
Bulk Loading Options for Cassandra
Bulk Loading Options for Cassandra
Sep 24, 2012
3
min read
No Comments Yet
Let us know what you think