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


function in 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


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