It would be fantastic if all data users knew how to write SQL and were able to pull the data they needed when they needed it, without technical support. In my experience however, most data end users need some last mile reporting solution to avoid request support every-time a fresh slice of data is needed. One very simple last mile solution is to connect Google Big Query queries to Google Sheets and provide the user with a simple parameter interface to pull data directly in a format they understand. In this article I will demonstrate how to set this up.
If you would prefer to see the solution in video form, please find a link to the YouTube video demonstrating this method below.
https://www.youtube.com/watch?v=OLw_4oB_fXs
To get started open Google sheets when logged in to your Google Cloud Platform, go to Data, Data Connectors and Connect to Big Query.
This will open a dialogue box where you can either choose a table or write a query, in this case I am going to write a query as it gives me more flexibility.
Im copying a custom query which I previously tested in the BigQuery Google Cloud Interface. This will show me sales from the Iowa Liquor dataset from a single store for one month.
Once connected a number of options will appear, including metrics, calculated fields and pivot tables. Im going to create a pivot table.
The pivot table below contains profit by Item and vendor.
The beauty of connected sheet is that I can now turn this to a dynamic report driven by parameters define by the user.
On another sheet is the workbook I can entered some variables. I can now go in to my connection setting and make these parts of the query.
In the parameter section of the connection setting screen click the Add parameter button, here you can define parameters and add them to the query.
Once the parameter is added you can insert it in to the query by adding a @ symbol before it. Im using it below to designate my month and year for the filter.
Back in the workbook all the user need to do is change the values and refresh the query.
I would suggest hiding the connected sheet from the end user only allowing them to change text based parameter and refresh.
So there you have it a nice functionality from Google Big Query and Google Sheet to automate reporting. If you have any questions feel free to comment down below.