Front-end Google Cloud products with a Google Apps Script application - Part 4

_________________________

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 explained the application engineering.

Part four here 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.
____________________

In parts one through three, we saw how the application works and the engineering behind it. All this assumed that one Google account owned and used everything - the cloud data resources, the Google Drive assets, the web application, etc. At the single-account level, everything works well. Eventually, though, we might want to share the application with other Google accounts, so that the owners of those accounts can use it themselves, each with reliable security and privacy. Google Apps Script actually does support application sharing. Unfortunately, this sharing has security, privacy, and performance drawbacks and because of these flaws, Google Apps Script won't work. Ideally, the Google Apps Script product will evolve to make this possible. Here in part four, we'll look at the situation more closely.

In the Google Apps Script editor, click the "cloud" Deploy as Web App...

1. Set app access to anyone.
icon to open the "Deploy as web app" dialog box. The lower "Who has access to the app;" dropdown defaults to "Only myself". Change this to "Anyone", and any actively logged in Google account can use the application. To do so, copy the URL in the "Current web app URL:" textbox and send it out. Those people can drop that URL in a browser and proceed. From their perspectives, they will have the same user experience described in part one. Unfortunately, this has huge problems.

First, the application owner has no way to restrict access to a list of guest users, by Google account ID. Only the app owner can use it, or anyone can use it. In Figure 1, the  Share  icon at the top right controls access to the application code. It does not control access to the executable. The Google Apps Script product should offer granular share control of the executable.

Second, although a guest can certainly save a result set on the Google Drive of that guest, the app will build the scratchpad spreadsheets it needs on the Google Drive of the application owner. This creates many problems. First, if multiple guest users use the application at the same time, they would collide because they would use the same scratchpad spreadsheet. The

     returnScratchpadFileCollection()

function in utilities.gs names the scratchpad files in part with a date / time stamp. An ideal solution would name these files with the ID of the Google account, owner or guest(s), running the app. For each individual user, the email address of the account would become a unique value. Because the scratchpad files exist in the Google Drive of the application owner, the application could not see these email addresses and the date / time stamp became necessary. Additionally, in an ideal solution the scratchpad file(s) mapped to a guest would ideally exist in the Google Drive of that guest - not that of the application owner. This would guarantee privacy for the guest users. As I tried to solve this problem, I built the now commented code at the top of the

     returnScratchpadFileCollection()

function. Unfortunately, I did not succeed, but I decided to include this code in the file.

Third, a Google Apps Script application has a six minute execution time limit. Google Apps Script offers a sleep function, but this has a time limit itself. An ideal solution would more flexibly pause, or sleep, the front-end application while the data layer proceeds. Then, when the data layer returns the result set, the front-end application would wake up. This way, a Google Apps Script application could reliably front-end a BigQuery query that lasts longer than six minutes.

Through the Apps Marketplace, Google does offer an app distribution option to a restricted domain that might solve some of the identified distribution issues. However, this approach involves a lot of configuration and overhead, beyond the Google Apps Script environment.
_______________

Part four here described the drawbacks of shared execution of a Google Apps Script application, and the product enhancements that would help solve those drawbacks. Hopefully, Google will make these enhancements. When they do, Google Cloud will become the dominant cloud space product line
.

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 Code.gs file has three functions. When the application first loads, it runs the

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

     returnScratchpadFileCollection()

located in the utilities.gs 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

     returnScratchpadFileCollection()

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

     createHtmlOutputFromFile()

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

     submitParams()

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 Code.gs
passing these parameters to the Code.gs returnFormParams() function. In Code.gs

4. The returnFormParams() function
in the Code.gs 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 Code.gs 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 Code.gs file google.script.run 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
utilities.gs to open the scratchpad spreadsheet, returning its ID value. Next, funcSaveSheet() calls formatScratchpadSpreadsheet() in utilities.gs 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 Code.gs file google.script.run 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.

Front-end Google Cloud products with a Google Apps Script application - Part 2

_________________________

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 here 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.
____________________

Before starting on the application front-end, first build the associated BigQuery project as described here. Although parts of that process - the dialog boxes, steps, etc. - have changed since the original article publication, the core ideas remain. One new step involves Drive API configuration. At the Developer's Console, enable the BigQuery and Drive APIs. The process involves the same steps for both APIs; here, we'll focus on the Drive API. First search for the Drive API


1. Search for the Drive API for the project
and in this

2. Enable the Drive API
panel, click  Enable . Wait a few moments, and the API Manager will enable the Drive API

3. The Drive API is enabled
for the project. Do the same for the BigQuery API. Next, we'll need the Project ID. In the Cloud Console



4. The project ID value
look for the project ID value, circled and hidden here in Figure 4. In Google Drive


5. Build a new Google Apps Script application
build a new Google Apps Script application. At this point, the dropdown might not show the Google Apps Script pick. If this happens, click Connect more apps and in the dialog that opens, look for and add "Apps Script", and proceed.

When the Google Apps Script editor opens, name the project at the upper left. Drill down to

     File - > Project properties

6. Pick Project properties in the dropdown
to open this


7. Script properties: add the project Id
box, adding the Project ID value from figure four above.

Finally, in the script editor, drill down to

     Resources -> Advanced Google Services

to open the Advanced Google Services

8. Advanced Google Services
picker. Turn the BigQuery and Drive API's  on .

When the application first runs, its configuration will a few more steps. We'll look at them now.

When a user first deploys the application, this dialog


9. Application first run: authorization required
will open. Click "Continue" to open an authorization dialog

10. The authorization dialog
that shows an  Allow  at the bottom. Click that  Allow  to proceed.

Next, the application will probably want additional authorizations to use the Drive and BigQuery APIs. It might seem that the earlier steps would cover this, but they might not have. Google Apps Script can show the messages asking for these authorizations in two ways, and we'll cover both of them now.

When the application runs at this point, it could show an error message like


11. Message: enable the Drive API
this. For the Drive API, the second sentence of this error message has a URL pointing to this


12. Enable the Drive API
page. Enable the API and run the application again. If the web application form looks okay but nothing happens when you click  SUBMIT , switch back to the script editor. Drill down to

     View -> Execution transcript

and scroll down, looking for


13. URL to configure the BigQuery API
a failure message with a URL. Open that URL in a new tab, proceed as instructed, and run the application. Note that for this "extra" API authorization, either of these techniques should work.

The same Google account should "own" both the Drive and the Cloud resources used by the application. In other words, a single Google account should both own the application in and through Google Drive. That same Google account should also reference, or point to, a Cloud resource or resources used by that application. The OAuth2 machinery drives this security behavior.
_______________

Part two here described the application configuration. Part three will explain the application engineering.