Front-end Google Cloud Products With a Google Apps Script Application - Part 1


In an earlier article, I showed how to front-end Google BigQuery with a Google Docs Spreadsheet / Google Apps Script application. Later articles generalized the engineering: this article described a Google Cloud SQL front-end application and this article explained how to integrate BigQuery and Cloud SQL to build BigQuery stored procedure functionality. This engineering works well enough, but it has some drawbacks:

First, it offers no way to save a result set as a finished file.

Second, it offers no way to print a result set.

Third, the sample applications rely on a container‑based architecture - in other words, the application script files are bound to a Google Sheets file as components of that file.

This article describes a sample Google Apps Script application that front-ends a BigQuery project. However, the application also offers file save and print features. Additionally, while its engineering relies on Google Sheets, it breaks the Google Sheets dependency of the earlier article samples.

Lastly, this article will explore application sharing - the idea that the application can serve more than one user. We'll see that although the engineering described in this article is not recommended for that use case, the engineering can become the basis for future shared production applications if Google Apps Script technology evolves as it ideally will.

Part one here will show the sample application user experience.

Part two will describe the application configuration.

Part three will explain the application engineering.

Part four will explore how Google Apps Script application sharing presently works and will explain why this is not yet ready for production, based on the present state of the Google Apps Script product. It will also discuss suggested feature enhancements to the Google Apps Script product that would make application sharing possible.

Download the three application component files from the GitHub repository.

The application described in this article works a lot like the application described in this earlier Bit Vectors article. It front-ends the "Complete ZIP Code Totals Filefrom the U.S. Census Bureau, placed in one table in a Google BigQuery project. The user starts with a URL pointing to the application to open this

1. The BigQuery application web form
form. The form disables the SAVE / PRINT button at this point. The user picks zero or more values in the dropdowns and clicks SUBMIT. When the application shows the result set

2. The application result set
the SAVE / PRINT button enables. The user can click the RESET button to reset the form back to its original state, or (s)he can click the SAVE / PRINT button to open the

3. The Print / Save dialog
print / save dialog. With this dialog, the user can print a spreadsheet-formatted result set through the Google Cloud Print! link. This will open dialog boxes to print the result set through a printer. If the active Google account running the application has access rights to one or more printers configured earlier through Google Cloud Print!, printing can happen through it / them. This dialog can also configure a new printer. The user can also click

     Save to Google Drive

and then

     Advanced options...

to open this second
4. Save the output in the Google Drive of the user
dialog. The user types a file name in the

     Google Drive File Name

textbox and clicks  Print . The application saves the spreadsheet equivalent of the form - with the result set and all the control picks - as a PDF placed in the Google Drive

5. The output file saved in Google Drive
"owned" by the active Google account. The application saved the file - named "Application Output" here - as a conventional PDF

6. The Application Output PDF file
and the user can copy / rename / share / etc. the file. The Cloud Print dialog above defaults the file save to the root directory. It does not handle directory drill-downs. Google explains that the Cloud Print dialog is in beta; hopefully, it will soon offer directory drill-down flexibility.
Part one showed the application user experience. Part two will describe the application configuration.