Implementing cache invalidation in a Google Apps Script web app

There's an old joke in software development that says that there are only two hard problems: cache invalidation and naming.

Recently, when building an app on Google Apps Script, I walked straight into it.

The setup

I built a web app for a local kayak club that members can use to sign up to weekly events.

The app runs on Google Apps Script with a Google Sheet as the database, which consists of several sheets.

Only one of them is the actual sign-up list. The other sheets have config data:

• the boat fleet (a list of all available and broken kayaks),
• the season dates (summer vs. winter schedule),
• special events (outside of the regular schedule),
• canceled events.

Every time a member opens the app, the server needs all of this data to work out what's going on this week.

Otherwise, the weekly schedule wouldn't show the accurate events.

The problem: cache and cache invalidation

I'm not a developer (some people would call me a vibe coder). So when I built the first version of the web app, I noticed that it was very accurate and... very slow.

Reading several sheets through Apps Script's built-in SpreadsheetApp service on every single page load wasn't an example of great implementation. Every getDataRange().getValues() is a round-trip to Google's backend. So with a few sheets holding data, these round-trips quickly added up.

So I decided to cache them using the Apps Script built-in CacheService and storing each sheet as JSON for six hours (the maximum time-to-live allowed):

function cacheWrapRead(key, fetcher) {
  var cache = CacheService.getScriptCache();
  var stored = cache.get(CACHE_PREFIX + key);
  if (stored) {
    try {
      return JSON.parse(stored);
    } catch (e) {
      // fall through and re-fetch
    }
  }
  var fresh = fetcher();
  try {
    cache.put(CACHE_PREFIX + key, JSON.stringify(fresh), CACHE_TTL_SECONDS);
  } catch (e) {
    // Cache write can fail if the payload is too large — ignore, read uncached
  }
  return fresh;
}

Page loads got noticeably faster.

Caching seemed like the right answer to the page load problem. But this simple web app has real users, and this meant: things didn't always go as planned.

Sometimes a committee member needed to change a headcount cap, cancel an event, mark a boat as broken... In these cases, caching produced stale copies of data.

That's where I learned first-hand about the importance of cache invalidation. I might never have run into this question if I'd just vibe coded an app for personal use.

The fix

Apps Script has a simple trigger called onEdit.

It fires whenever someone edits a cell in Google Sheets, which can have different tabs.

Each tab maps to one cache key:

var SHEET_TO_CACHE_KEY = {
  'Boats': 'boats',
  'Schedule': 'schedule',
  'Config': 'config',
  'Special Events': 'specialEvents',
  'Cancelled Dates': 'cancelledDates',
};

So when a committee member edits a cell in a tab, the trigger drops the cache entry for that specific tab:

function onEdit(e) {
  if (!e || !e.range) return;
  try {
    var sheetName = e.range.getSheet().getName();
    var cacheKey = SHEET_TO_CACHE_KEY[sheetName];
    if (cacheKey) {
      CacheService.getScriptCache().remove(CACHE_PREFIX + cacheKey);
    }
    // (edits to a cap column also trigger a re-shuffle of sign-ups — omitted here)
  } catch (err) {
    // Never let a trigger failure block the edit from completing
  }
}

Every other unedited sheet with config data still loads a cached version.

The invalidation tactic

Here's the detail that makes this cache invalidation smooth: simple triggers don't fire for writes made by scripts.

That matters because the app writes to the database constantly. Every sign-up and cancellation appends or updates a row. If onEdit fired on those, it would be clearing caches hundreds of times a week, slowing down the app.