Exploring Apps Script CacheService Limits: Empirical Findings and FIFO Eviction Gotchas

Caching is a critical strategy for optimizing performance in Google Apps Script properties, especially when dealing with slow APIs or heavy computations. The built-in CacheService provides a simple key-value store, but its documentation leaves several “edge cases” and failure scenarios vague. While the documentation states a maximum value size of 100KB, it doesn’t explicitly detail what happens when you hit the Apps Script CacheService key length limit or throw odd types at it. In this post, we’ll write a script to empirically test these limits … and explore how the service behaves under stress.

Google Workspace alum Justin Poehnelt (whose open-source work many in the community will recognise from the Google Workspace CLI (gws)) has shared a great investigation in Apps Script CacheService Limits. As noted by Justin, while the official CacheService documentation states some of the limits like key value size and the 1,000 cacheable item limit, it leaves out some important details. One finding I think every Apps Script developer should know about is the mechanics of cache eviction when you go over 1,000 items. When you reach this limit, rather than one-in, one-out, CacheService will drop approximately 100 of the oldest items.

For developers who use other caching tools like Redis or Memcached, which primarily rely on Least Recently Used (LRU) where frequently accessed keys stay alive, another key finding is that CacheService uses First-In, First-Out (FIFO). This means CacheService throws away your oldest data even if you are regularly using it in your script. I’m sure most developers are defensively using CacheService to cache values if not found. To keep “hot” keys from aging out of the queue, Justin shares a function in his post to manually remove and re-insert them.

What I really like about Justin’s post is the empirical approach, writing a small test harness to verify how the Apps Script runtime behaves in practice rather than taking standard cache behaviour for granted. As more developers use the Gemini side panel in the Apps Script IDE or AI coding assistants to vibe-code Workspace automations, having Justin’s posts and ones like it hopefully nudge these tools in the right direction regarding this nuanced behaviour. Understanding these runtime quirks upfront can save a lot of head-scratching when debugging intermittent cache misses in production.

Follow the source link for the full walkthrough and test outputs.

Source: Apps Script CacheService Limits — Justin Poehnelt

Navigating the Gatekeepers: A guide to Google OAuth verification for Google Workspace Add-on developers

How to pass Google’s OAuth verification process for your Workspace add-on—branding requirements, privacy policy, terms of service, scopes justification, and the demo video.

Over the years on Pulse, we have featured a number of guides and resources aimed at helping developers navigate the complexities of Google’s authorisation requirements. However, given it has been a while since we last touched on this topic, and considering how frequently the process can be a stumbling block for the community, I thought it was a subject worth revisiting.

If you have ever attempted to publish a Google Workspace add-on, you likely know the specific mix of excitement and trepidation that comes with the submission process. While building the functionality is one hurdle, ensuring your application meets Google’s rigorous security and branding standards is quite another. One area that frequently trips up even seasoned developers is OAuth verification.

In a recent post on the ShipAddons blog, Manuel Napoli shares a step-by-step guide to getting through Google’s OAuth verification. Having navigated this process multiple times to launch his own tools, Manuel provides a comprehensive look at what Google reviewers actually expect, offering a practical checklist to help you get verified on your first attempt.

The guide breaks down the requirements into four primary areas: branding, privacy policy, terms of service, and scope justification. Rather than rehashing the technical minutiae here, Manuel’s post serves as a comprehensive “pre-flight” checklist for anyone ready to hit the submit button.

It is also interesting to see this content coming from ShipAddons. For those unfamiliar, ShipAddons is a production-ready service designed for building premium Google Workspace add-ons with React and Supabase user management integrations. It aims to solve many of the recurring “pain points” we often discuss in the community, specifically handling the “plumbing” of a commercial application.

For developers looking to transition from a single-purpose script to a monetised solution, having these systems pre-configured can significantly reduce the time spent on infrastructure.

Source: Google OAuth Verification: A Step-by-Step Guide for Add-on Developers

Optimizing Google Apps Script: Minimizing latency with Google Cloud data regions

Ping results to Google Cloud regions and short code snippet demonstrating how to measure latency from Google Apps Script.

As Google Apps Script is a software as a service, writing efficient code is crucial, especially for complex solutions. It’s also important to consider marginal gains, such as finding small ways to improve your script’s responsiveness.

If you’re using Google Cloud services like Vertex AI, Cloud Functions, or others, you’ll also want to consider the latency between your Apps Script code and the Google Cloud data region. Justin Poehnelt recently revisited the work of Ivan Kutil, providing a way to measure Google Cloud region latency from your Apps Script project.

Interestingly, when I ran Justin’s sample code, I found that the region with the lowest median latency (us-east1) was approximately 11 times faster than the region with the highest (asia-south2). You can make a copy of this sheet to view my results and run the test for yourself.

Source: Google Cloud Region Latency in Google Apps Script | Justin Poehnelt

Google Chat Apps: Publication experiences on the Google Workspace Marketplace

Image credit: Anton Shevchuk

It has finally happened! Bot Bender 2.0 is now available for installation from the Google Workspace Marketplace. In this post, I will walk you through the steps needed to get to the marketplace.

We’ve shared a couple of journeys to Google Workspace Marketplace publication but this is the first I’ve seen for publishing a Google Chat App. For people who have gone through the process it’s probably a familiar story of back and forth with the Marketplace review team.

In this post Anton Shevchuk shares how his Chat App received multiple rejects due to issues with the location of the support/privacy pages. The moral of the story is to be persistent, and don’t hesitate to push back if you believe the reviewers are mistaken. The post from Anton also outlines the other steps involved, including connecting the Google Workspace Marketplace SDK and configuring app visibility settings.

Source: Google Chat Bot. Publication on Google Workspace Marketplace

Allowing access for IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, and IMPORTRANGE on behalf of the user in Google Sheets

Image credit: Kanshi Tanaike

In February 2024, Google made a change in Google Sheets to enhance data security. Now, users must provide explicit consent before Sheets can connect to external data sources. This change aims to prevent unintentional data leaks and improve overall data loss prevention within Google Sheets.

Impact on Users:

When manually adding or editing certain IMPORT functions (IMPORTHTML, IMPORTXML, IMPORTFEED, IMPORTDATA) and the IMAGE function in a Google Sheet, a warning banner will appear blocking sending a receiving data until the user gives consent. To enable this functionality, users need to click “Allow access.”

Impact on Developers:

As highlighted in the first source post by Justin Poehnelt, this change caused issues for developers who programmatically create Google Sheets and automate tasks, such as generating PDF documents  as the IMPORT and IMAGE functions were blocked.

Solutions for Developers:

Fortunately, Google introduced an update to the Google Sheets API to address this challenge. Developers can now enable external data access programmatically by setting the importFunctionsExternalUrlAccessAllowed property in the Google Sheet metadata. This allows specific IMPORT and IMAGE functions to work without manual user consent.

For the IMPORTRANGE function, a similar API property is currently unavailable. However, an unofficial workaround exists that involves calling an undocumented endpoint using Google Apps Script as detailed in the second source post by Kanshi Tanaike.

Future Expectations:

As Google continues to prioritize data security, there will likely be further enhancements to data loss prevention controls within Google Workspace. Hopefully, future updates will also include official API controls for IMPORTRANGE and other functions to facilitate legitimate data access for developers while maintaining data security.

Additional Resources:

Avoiding Google Apps Script onFormSubmit simultaneous executions when unlinking/linking Google Sheet responses

Caution when using onFormSubmit triggers to avoid mass simultaneous executions when un/linking a Response Sheet.

The following post is a caution around onFormSubmit triggers after I recently discovered a way that somebody had inadvertently managed to execute my code simultaneously over 253 times. Now unless a Google Form is submitted by 253 individuals all at the same time … then this is baffling.

When should an onFormSubmit trigger run?
At the point when a Google Form has been completed and the ‘Submit’ button pressed.

How else can the trigger be activated?
Well … it turns out when you unlink and then link back a Google Response Sheet containing the onFormSubmit trigger, it will activate it for every single Form response collected up to that point in time …

Source: The Gift of Script: onFormSubmit Trigger simultaneous executions

Guide to completing Casa Tier 2 Security Assessment for Google Apps Script (and how to scan your Google Apps Script project for CASA)

If you want to publish your Google Apps Script project on the Google Workspace Marketplace, or if you already have an add-on or app on this marketplace and are using authorization scopes which are now restricted, you will have to go through a TIER 2 CASA security assessment.

Previously in Pulse we have shared ReDriveApp: A new Google Apps Script library to replace DriveApp and restricted scopes. If your Workspace Add-on still requires restricted scopes then you are going to have to think about the next steps and options. One option is going through the enhanced verification process, which requires a Cloud Application Security Assessment (CASA).

If you would like an overview of the process from a developer’s perspective you are in luck as Kelig Lefeuvre (Product Engineer at Scriptit & Folgo) has shared a guide specifically with Apps Script developers in mind. The article includes a number of useful tips and information about the review process which you won’t find in the official documentation.

As part of the CASA process Kelig recommends using the option to  you can submit bypass the Fortify scan and submit your own results. those results Kelig has also provided a second article with a step-by-step guide on ‘how to scan your Google Apps Script project for CASA’.

Source: Guide to Completing Casa Tier 2 Security Assessment for Google Apps Script & How to scan your Google Apps Script project for CASA

🔒Preventing Cross-Site Request Forgery (CSRF) in Google Apps Script Dialogs and Sidebars 

 

If you are looking to publish a Google Workspace Editor Add-on, you may need to consider upping your security with an anti-CSRF token.

Scott Donald has shared some really useful information about protecting your Google Workspace and Apps Script data from malicious websites using anti-CSRF tokens. Google is currently in the process of reviewing Google Workspace Add-ons which means for many restricted scopes in Google Drive developers need to complete CASA Tier 2 security assessments, which require anti-CSRF token.

For more context CSRF attacks are a type of cyberattack that can allow hackers to trick your browser into sending unwanted requests from your account, such as making purchases or changing your settings. This can put your data and privacy at risk.

Anti-CSRF tokens are a simple but effective way to prevent these attacks. They work by generating a unique ID for each user session, which is then included in all requests sent to Google Workspace and Apps Script. This way, the server can verify that the request is legitimate before processing it.

Implementing anti-CSRF protection is relatively easy and this tutorial from Scott explains have you can implement it in your Google Workspace Editor Add-on.

Source: Preventing Cross-Site Request Forgery (CSRF) in Google Apps Script Dialogs and Sidebars – Yagisanatode

Memoization in Apps Script with Cache Service

A generic Apps Script memoization function can be written to cache any function.

We’ve featured a couple of Apps Script optimisation techniques in the past. This example from Justin Poehnelt uses a technique found in many coding syntaxes of ‘memoization‘:

In computing, memoization or memoisation is an optimization technique used primarily to speed up computer programs by storing the results of expensive function calls to pure functions and returning the cached result when the same inputs occur again

For more background on when and why you might use ‘memorization’ you can read about Memorization [sic] in JavaScript. In the case of Google Apps Script developers have opportunities to integrate the built-in Cache Service and Properties Service to memorize function results in the context of the script, document or the user.

In the example provided in the source post by Justin it defaults to CacheService.getScriptCache() to store the memoized results but it would be easy to change this to CacheService.getUserCache() or CacheService.getDocumentCache(). With a little modification you could also include Properties Service.

To help you see how memoization works here’s a gist for both pure JavaScript and Apps Script techniques which you can copy and run in the Apps Script Editor. The results hopefully speak for themselves:

First run (cold start) and second run (warm start) with better performance for cached results first call

Source: Memoization in Apps Script | Justin Poehnelt

How to Write to a JDBC Database with Google Apps Script: My Adventure with a Pesky Character Limit

I recently faced a frustrating issue when writing data to a CloudSQL database with the JDBC class in Apps Script. I kept getting the following error:

Exception: Argument too large: SQL

I also observed that it only happened when my SQL query reached a certain length. I considered breaking it down into multiple queries, but I was still puzzled 🤔 because I was only sending a few dozen kilobytes of data.

Now, the thing is, the official documentation could be more helpful; even though the solution is there, it needs to be better explained. So, I turned to StackOverflow. There was a discussion on this exact topic, but to my surprise, I was still waiting for an answer. Until, well, I wrote it 😉

Source: How to Write to a JDBC Database with Google Apps Script: My Adventure with a Pesky Character Limit