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


This article improves the Google Apps Script engineering featured in earlier Bit Vectors articles herehere, and here to add file save and print features to a Google Apps Script application. This article will also discuss the potential of Google Apps Script application sharing.

Part one showed the sample application user experience.

Part two described the application configuration.

Part three here 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.

Three files comprise the demo application. BigQueryDemoApp.html more or less clones the BigQueryDemoApp file of the earlier Bit Vectors article, with some small differences. First, the form RESET button calls a revised #RESET jQuery function

1. The form RESET button calls this #RESET jQuery function
that only resets the web form controls. The original version called a Google Apps Script function that also reset the spreadsheet tied to the original application. Second, this file places slightly different controls with slightly different form behavior on the web page itself.

The file has three functions. When the application first loads, it runs the

2. The doGet() function
doGet() function. The doGet() function first calls the


located in the file to build a "scratchpad" Google sheets. The Google Drive of the active Google account owns this file. That called function returns the file ID of the created file, and the next line in doGet() saves that value as a script property. We'll look at the


in more detail shortly. Finally, doGet() calls the


function, which points to the BigQueryDemoApp.html file. This called function loads the application web page; the createHtmlOutputFromFile() function helps enable potential application sharing. We'll look at potential application sharing more closely in Part Four.

To run a query, the user makes zero or more picks from the web form controls and clicks the  SUBMIT  button. Just like the application described in this Bit Vectors article, the onclick() of this control calls the BigQueryDemoApp.html file


function. This function gathers the picked form control values into both an array and a separate BigQuery query string

3. The last line of submitParams() calls
returnFormParams() in
passing these parameters to the returnFormParams() function. In

4. The returnFormParams() function
in the file
the called returnFormParams() function first retrieves the "scratchpad" Google sheet file created when the application loaded. The function places the web form parameter values, passed in the arrayParam parameter, on the sheet, and then calls the function runQuery(), passing the assembled BigQuery query string with the queryString string parameter. The runQuery() function queries the BigQuery datasource, ultimately returning the result set back to the writeWebForm() function located in the HTML file. The writeWebForm() function writes the result set values on the web form and enables all the web form buttons. This feature space matches the application described in the earlier Bit Vectors article fairly closely.

At this point, the jQuery in the HTML file enables the web form SAVE button. The SAVE button calls the savePrintSheet() function

5. The savePrintsheet() function
in the HTML file. Because the finished application provides the quantiles function, the savePrintSheet() function has to handle a result set of variable size. The savePrintSheet() function calculates the size as the firstBlankRow variable and passes this value to the funcSaveSheet() function at line 72 in the file statement.

Because of the jQuery enable control structure, the user can click  SAVE / PRINT  only after the application returns a result set after a run. At this point, when the user clicks  SAVE / PRINT , the funcSaveSheet() function

6. The funcSaveSheet() function - first part
will have the raw result set available on the scratchpad Google sheet file. The function first calls returnScratchpadFile() out in

7. The returnScratchpadFile() function to open the scratchpad spreadsheet, returning its ID value. Next, funcSaveSheet() calls formatScratchpadSpreadsheet() in to format the sheet. Although the returnFormParams() function could have called formatScratchpadSpreadsheet(), that would become a wasted call if the user does not save or print the result set. Therefore, the call happens here.

The funcSaveSheet() function places the first sheet of the scratchpad spreadsheet into a variable, and then places the URL of that spreadsheet into another variable. A Google Sheet file defaults to one thousand rows and because a result set in this software could have as few as six rows, funcSaveSheet() uses the firstBlankRow parameter at line 209 to hide spreadsheet rows starting from firstBlankRow

8. The funcSaveSheet() function - second part
down to row one thousand. This matters because otherwise, a finished file with six rows would have a lot of blank, wasted space. String variable url_ext has the parameters needed to export the result set as a finished PDF; the response variable holds the content of the sheet formatted with those url_text parameters. Line 230 returns the response variable content, transformed into a finished PDF file through the functions in the statement.

After the call to funcSaveSheet(), the file statement at line 72 then calls the saveSheetWithCloudPrint() function

9. The saveSheetWithCloudPrint() function
located back in the HTML file. This function uses the cloudprint.Gadget object to build a new Cloud Print dialog box

10. Save or print the finished result set PDF
with the cloudprint.Gadget object. The Gadget object receives the finishedDoc PDF file as the finishedDoc parameter in the line 88 call.

Google does offer the Drive Picker. In this application, the Drive Picker would have become the natural choice for the file save feature, except for one problem. I never found a way to pass a finished PDF file to a Drive Picker object as the file content to save. This has most likely occurred to Google and they will eventually offer it as a feature. For now, this became "a bridge too far". Of course, while the cloudprint.Gadget object shown here can save a file, it will only save files in the root directory of a Google Drive. However, the overall flexibility of this approach outweighs this flaw. In the future, Google will probably enable directory drill-downs in the cloudprint.Gadget object as well.

Part three here described the application engineering. Part four 
will explore how Google Apps Script application sharing could work for this application, and why we should avoid it at this time.