Front-end Google BigQuery with a Google Spreadsheet Part 2 - The Application User Experience
_________________________
This five part article shows how to set up a Google BigQuery project, and how to front-end that project with a sample Google Docs Spreadsheet web application. This application will work in all browsers and platforms, including the Apple iPad and iPhone. The article also explains the application engineering and configuration. Download the two application component files from the GitHub repository.
Part one explained how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.
Part two here will describe the application user experience.
Part three will explain the application engineering.
Part four will start with a brief introduction to the Google Docs script editor. Then, it will show how to tie the application code to both the Google Docs Spreadsheet and the BigQuery project.
Part five will show how to configure security for the sample application, and how to distribute the sample application to users.
I want to say thank you to Google Developer Advocate / Software Engineer Felipe Hoffa. At the Google Cloud Platform Roadshow here in Los Angeles, he took the time to discuss Google Apps Script development with me, and answered my questions about the associated security machinery.
____________________
For this walk-through, we'll assume an authorized user will run the demo application through a URL. Part four will show how to open the form and run the application from the Google Apps Script editor. Part five will show how to configure user authorization and application security.
An authorized user sees a web form
and the associated Google Docs Spreadsheet if that user has the spreadsheet URL:
Any BigQuery result set on this spreadsheet will mirror the result set on the form. Note that the workflow moves one way, from the form to the spreadsheet. The spreadsheet does not drive changes in the form.
The form has seven dropdowns to filter the BigQuery result set. The Number of Quantiles dropdown stays hidden by default; it becomes visible only for the Quantiles Employee Count pick in the Function for Select Clause dropdown.
The Reset button does three things. It resets all dropdowns, including the hidden Number of Quantiles dropdown, to their default values. It deletes any existing query results on the form. Lastly, it hides the Number of Quantiles dropdown and its associated labels.
When the user clicks the submit button, the application sends the query to BigQuery and shows the result set on the form itself, mirroring both the result set and the user's dropdown picks on the spreadsheet.
The first three functions in the Function for Select Clause dropdown all return single-value result sets. The Quantiles for Employee Count function opens the Number of Quantiles dropdown
and its associated labels. The quantiles function needs a number of quantiles parameter, and for the application, this dropdown ranges from two to one-hundred.
The form does not have a CLOSE button because Google does not support window code closure. The user must close the form from the browser tab.
To use the application, the user simply picks values from the dropdown and then clicks SUBMIT. Both the form and the spreadsheet show the BigQuery result set. For single-value functions, the spreadsheet grays out the quantiles parameter value. If the quantiles function returns an empty result set, the spreadsheet grays out the quantile number cell next to the information message in the quantile value cell. These screenshot pairs show the overall application behavior:
This described the application user experience. Part three will explain the engineering behind the application.
This five part article shows how to set up a Google BigQuery project, and how to front-end that project with a sample Google Docs Spreadsheet web application. This application will work in all browsers and platforms, including the Apple iPad and iPhone. The article also explains the application engineering and configuration. Download the two application component files from the GitHub repository.
Part one explained how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.
Part two here will describe the application user experience.
Part three will explain the application engineering.
Part four will start with a brief introduction to the Google Docs script editor. Then, it will show how to tie the application code to both the Google Docs Spreadsheet and the BigQuery project.
Part five will show how to configure security for the sample application, and how to distribute the sample application to users.
I want to say thank you to Google Developer Advocate / Software Engineer Felipe Hoffa. At the Google Cloud Platform Roadshow here in Los Angeles, he took the time to discuss Google Apps Script development with me, and answered my questions about the associated security machinery.
____________________
For this walk-through, we'll assume an authorized user will run the demo application through a URL. Part four will show how to open the form and run the application from the Google Apps Script editor. Part five will show how to configure user authorization and application security.
An authorized user sees a web form
 |
| 1. The BigQuery application web form |
 |
| 2. The BigQuery application Google Docs Spreadsheet |
The form has seven dropdowns to filter the BigQuery result set. The Number of Quantiles dropdown stays hidden by default; it becomes visible only for the Quantiles Employee Count pick in the Function for Select Clause dropdown.
The Reset button does three things. It resets all dropdowns, including the hidden Number of Quantiles dropdown, to their default values. It deletes any existing query results on the form. Lastly, it hides the Number of Quantiles dropdown and its associated labels.
When the user clicks the submit button, the application sends the query to BigQuery and shows the result set on the form itself, mirroring both the result set and the user's dropdown picks on the spreadsheet.
The first three functions in the Function for Select Clause dropdown all return single-value result sets. The Quantiles for Employee Count function opens the Number of Quantiles dropdown
 |
| 3. The QUANTILES EMPLOYEE COUNT function opens the Number of Quantiles dropdown / labels |
The form does not have a CLOSE button because Google does not support window code closure. The user must close the form from the browser tab.
To use the application, the user simply picks values from the dropdown and then clicks SUBMIT. Both the form and the spreadsheet show the BigQuery result set. For single-value functions, the spreadsheet grays out the quantiles parameter value. If the quantiles function returns an empty result set, the spreadsheet grays out the quantile number cell next to the information message in the quantile value cell. These screenshot pairs show the overall application behavior:
 |
| 4a. Form - parameters and BigQuery single-value result set |
 |
| 4b. Spreadsheet - mirrored parameters and single-valued result set from form. The application grayed out the number of quantiles cell. |
_________________
 |
| 5a. Form - parameters and BigQuery single-value function returning no value |
 |
| 5b. Spreadsheet - no-value message and parameters mirrored from form. |
_________________
 |
| 6a. Form - parameters and BigQuery quantiles function result set |
 |
| 6b. Spreadsheet - parameters and BigQuery quantiles function result set mirrored from form |
_________________
 |
| 7a. Form - parameters and zero-count BigQuery quantiles function result set |
 |
| 7b. Spreadsheet - parameters and zero-count BigQuery quantiles function result set mirrored from form. The application grayed out the quantile number cell. |
This described the application user experience. Part three will explain the engineering behind the application.