Tips on building a reliable, secure & scalable architecture using Google Apps Script

Photo by Àlex Rodriguez on Unsplash

Most conversations around solutions or automations created using Apps Script eventually get to a point where being able to address concerns around its reliability, security and scalability becomes a challenge — more so if compared to having similar applications being built on other platforms (say: AWS, GCP, Firebase etc.).

Few of the most common points of friction include -

• Managing concurrent executions
• Being able to securely store, access & manage API keys, tokens etc.
• Scale smartly without hitting those quotas and limitations

I wouldn’t recommend using G Suite’s Apps Script if one were building the next Spotify or Netflix (obviously 😂) but depending on the kind of applications being used by small or medium sized businesses, we can still very effectively make use of this platform by employing some of the much lesser-known services quite easily.
While none of this is to say that Apps Script would be able to accommodate every single use-case, think of this discourse as ways to rather improve on even the existing applications.

In this post, we’ll see how to navigate our way through said constructs by addressing parts of these problems and also explore ways in which we can bake some solutions right from the get-go!

Reliability

For the first 6 months of me using Apps Script — which was mainly as a webhook to capture and store data in a Spreadsheet — I experienced issues with missing & sometimes even inaccurate entries, making the whole data set unreliable. Took me some time to understand that this was happening because of concurrent hits being made on the endpoint (the link we get when deploying the script as a web app). It was a lot later in time that I got introduced to the ‘Lock Service’ that Apps Script offers which is designed to handle just that.

This service allows scripts to prevent concurrent access to sections of code. This can be useful when you have multiple users or processes modifying a shared resource and want to prevent collisions.

Lucky for me, I don’t even have to create an example of my own to illustrate its usage as the existing documentation of the ‘Lock’ class already has one that talks about exactly this scenario.

Here’s a snapshot from the documentation for reference -

Lock Class in Google Apps Script

There are different implementation methods available too where you can choose to either get a boolean value in return or have the function throw an exception, depending on the kind of architecture you intend to build.

Introducing ‘LockService’ in your existing development environment should definitely give you a boost in the area of reliability.

Security

Most open-source examples of apps script that connect to 3rd party services have at least one (if not more) variables in the codebase that would be declared as placeholders for your API key, token or other secrets. Now, imagine if you plan to share this implementation either with your colleagues or clients, there’s a good chance that you’ll have to expose your hard-coded credentials with them as well; in almost all my conversations with other technology experts and personal experiences, this has been deemed as a major security* risk.

*For the purposes of this article, I’ll be restricting my coverage around security to this (specific) scenario.

Let’s take the feedback-sentiment-analysis solution as an example where line 2 has a placeholder for you to set your API key -

Example solution: feedback-sentiment-analysis

Given the implementation to create a custom menu in Google Sheets (line 17), you could safely assume this to be a container-bound script (also, no installable trigger found).

One way to solve for these scenarios is to -

• Introduce a “prompt” dialog where the user could input API credentials at the time of executing the application (perhaps for the first time)

Prompt dialogs in Google Apps Script

• then store that information using ‘Properties Service’ either as part of document, user or script properties (depending on the use-case)

This way, all such important credentials and secrets are not visibly shown for everyone to consume and should someone programmatically try to retrieve information from set properties, you could always trace the log to identify the user.

It’s similar to the approach used while building the OAuth2 library for Google Apps Script.

Scalability

One of the important factors that don’t allow Apps Script to scale indefinitely is its quotas and limitations. In this article, I’ll lay out ways to get smart about two of the dimensions that might matter the most -

1. Script runtime — applicable against every execution
   (not to be confused with ‘Custom function runtime’)
2. URL Fetch calls — applicable against a user’s / account’s daily threshold

Script runtime

Typically, you get to be in need of having a higher runtime when iterating through a large set of spreadsheet data but more times that not, end up with the error — ‘Exceeded maximum execution time’.

“Exceeded maximum execution time” error in Google Apps Script

A better approach would be where instead of running the main function through the loop directly, you could also keep track of how much time the execution has exhausted after every cycle and programmatically terminate the script by spawning a time-based trigger, just before the script meets its runtime threshold, where the main function is smart enough to recognize what its last checkpoint was and start from there when invoked the next time, as opposed to starting over, from the top — this can easily be achieved either by a marker from within the spreadsheet or perhaps by storing that information within the script properties.

A more detailed walkthrough of this implementation can be found in the original article that I’d authored a while ago here.

URL Fetch calls

There could be several instances where you may end up consuming a huge amount of URL Fetch calls viz. -

• Though widely unpopular, you may have to poll an API to know the updated status against any number of its resources
• Connecting with data studio via its connector services could also prove a heavy utilization of URL Fetch calls considering larger date ranges
• Some 3rd party API services may have extensive pagination given the data set you’re querying, or have multiple hops to capture the necessary information

Let’s take the data studio connector use-case as an example! Here, in order to conserve URL Fetch calls, you could make use of Apps Script’s ‘Cache Services’ where the data requested by one user at a given point in time can be stored in the script cache and consumed by another user requesting the same set (or a subset) of information at around the same time.

It would then be up to the code logic to first check for the data requested in cache and if within the bounds of the date range against which it may’ve been stored, return that data instead of making another URL Fetch call.

A different, albeit good example can be found within the ‘Cache’ class documentation that talks about fetching an RSS feed that has a terribly slow response -

Cache Class in Google Apps Script

While this addresses a slightly different pain point, you could use the same approach to conserve your URL Fetch Calls too.

Some of the services that this article talks about aren’t the ones that get highlighted much or even thought after in most implementations and while they in no way depict perfect solutions, I hope that it helps to know of their existence.

Should you happen to know of better ways to achieve improved efficiency against some of these constructs, do please feel free to let know 😊 either via Twitter (DMs are open), LinkedIn or in the comments section here.