Sharing cache between Google Workspace projects

A recent question on the Google Apps Script group community forum asked if it was possible to share user managed configuration data (in a spreadsheet) with multiple (presumably) container bound scripts in various other projects, while avoiding the overhead associated with continually reading some shared configuration spreadsheet. So, of course I created a new cache library (bmCacheSharer) to make all that possible.

Although the initial request was spreadsheet focused you can use it for any data shared between scripts – for example API results or any other derived data that is expensive or slow to access.

This library provides a number of useful caching features, so even if you don’t need to share caching between scripts, you can still benefit from the features by using it with your own script’s CacheService.

Here’s how it works.

Hello world

In the example below, we set up a function (a refresher) to run if the value for ‘myentry’ is not in cache. Any script which shares the same library and accesses ‘myentry’ will first try to find it in cache and populate cache for any future access attempts by this or other scripts.

    sharer.setRefresher('myentry', ()=> (
      return 'hello world'
    ))

    console.log (sharer.getValue ('myentry'))

How it works

Obviously caching will help solve the overhead issue, and Apps Script has a caching service, but it is associated with a specific script. In other words, data cached by script A is not visible to script B. However, script C deployed as a library has it’s own cache store (and propert store) which it can access on behalf of any other scripts that reference it. Therefore, if script A and B both reference script C, they can use C’s cacheservice to share data.

Cache service limitations

The cache service has a number of limits that can get in the way of a generalized solution to this problem, however this library has some workarounds to deal with them.

Cache service size and type

The Apps Script CacheService has a 100k maximum item size, and only supports string keys and payloads However, I have incorporated a previously released library Apps script caching with compression and enhanced size limitations which gets over this by:

• converting payloads of any type string and converting them – which means you can write a an array of objects for example, and get them restored when you get them back.
• digesting keys of any type to a string, meaning you can use an object as a key.
• spreading a compressed payload over more multiple physical cache items, and recombining them on access.

Time limit on cache retention

The mazimum time a CacheService item can exist for is 6 hours after which it expires. The library expects a refresher function which it automatically executes if it can’t find the requested data in cache.

Protecting data in a shared environment

Of course if multiple people (both in your group and elsewhere) are sharing a library, everyone’s data is in the same cache store and it would be possible to accidentally (or on purpose) end up accessing someone else’s data. You can minimize or eliminate this risk as follows

• Each entry has a name, an optionally a version and a payload, which isolates cache entries from each other.
• The library supports the concept of a ‘cache community’. This is a unique key (generated by the library), which you can use when you create a refresher. This guarantees that cache items are only findable by those scripts that specify the same cache community key. In addition it prevents accidental key collision by multiple uses of the same entry name.
• By default, the library uses the ScriptCache service – meaning all users in the same community share entries. To restrict to a specific user you can choose to use the libraries UserCache instead.
• You can restrict the the library scope to your own organization only, by taking a copy of and deploying your own private version of the library.
• It can also use a cache service you pass to it rather than use it’s own cache service. This allows you to get all the functionality in a single script if you are not interested in the sharing aspect (by passing your script’s own CacheService) or still get sharing by passing the CacheService of some other library to use.

More of some these techniques later in the post.

Library Exports

I have a standard way of organizing library usage with an Exports object. You can choose to do this or not, but since the examples that follow assume it exists, you can just paste in the code below after including the library bmCacheSharer (17IbCJbPqwEhAUCq-ePZGpUILT_w7tnVBGmmzU1p394nM_lzqX0NPYa2_) – or substitute your own copy if you’ve decided to clone it.

var Exports = {

  get LibExports() {
    return bmCacheSharer.Exports
  },

  /**
   * AppStore object proxy
   * @return {AppStore}
   */
  get Sharer() {
    return this.LibExports.AppStore
  },

  // used to trap access to unknown properties
  guard(target) {
    return new Proxy(target, this.validateProperties)
  },
  /**
    * for validating attempts to access non existent properties
    */
  get validateProperties() {
    return {
      get(target, prop, receiver) {
        // typeof and console use the inspect prop
        if (
          typeof prop !== 'symbol' &&
          prop !== 'inspect' &&
          !Reflect.has(target, prop)
        ) throw `guard detected attempt to get non-existent property ${prop}`

        return Reflect.get(target, prop, receiver)
      },

      set(target, prop, value, receiver) {
        if (!Reflect.has(target, prop)) throw `guard attempt to set non-existent property ${prop}`
        return Reflect.set(target, prop, value, receiver)
      }
    }
  }
}

Community key

Although it’s not absolutely mandatory, I highly recommed if you are using the publicly shared library, or intend to use the library for more than one purpose, that your first step is to create a community key. You would use this in every script that needs to share the same data. A typical community key will look like this

bm-group-a:e81a2c98-66df-4f56-8add-3a6331566a48

You can generate one as below. You’ll use this in every script that wants to share the same data. If you prefer you can use any string but using the function below will guarantee you’ll get a unique one for each community.

console.log(Exports.LibExports.getCommunityKey('bm-group-a'))

You should treat your community key as you would any password or secret data in Apps Script – by storing it in a secure store such as your scripts’ property stores. For simplicity, I’ve just defined it explicitly in these demo code snippets.

Creating a refresher

A refresher references a function that will seed cache with values and is indentified by a name and optionally a version (and a payload – see later). Typically a refresher automatically trigger each time any co-operating script tries to access a cache entry and doesn’t find it. Any script that is allowed to perform such an update would have this same function defined.

This refresher function looks up a couple of sheets and returns the values it finds in them. The spreadsheet it references is publicly available for testing.

    const sharer = Exports.Sharer
    const communityKey = "bm-group-a:e81a2c98-66df-4f56-8add-3a6331566a48"

    const ssid = '1zArP5mi86dIKP4Robc7MLc1TabBl9VV34nwBOTirMeE'
    const peopleSheet = 'people'
    const companySheet = 'companies'

    // this will be the referesher function
    const getCompanyValues = ({name, version}) => {
      const ss = SpreadsheetApp.openById(ssid)
      const people = ss.getSheetByName(peopleSheet)
      const companies = ss.getSheetByName(companySheet)

      return {
        name,
        version,
        ssid,
        people: {
          sheetName: peopleSheet,
          values: people.getDataRange().getValues(),
          sheetId: people.getSheetId()
        },
        companies: {
          sheetName: companySheet,
          values: companies.getDataRange().getValues(),
          sheetId: people.getSheetId()
        }
      }
    }

    // now assign the function to a refresher
    sharer.setRefresher('sheet-test', getCompanyValues, {
      communityKey
    })

Getting values

Now get the values that were returned by the refresher. If it doesn’t find them in cache, it’ll run the refresher function and populate cache with the result for next time.

const value = sharer.getValue('sheet-test')

Setting expiration times

You can set a default expiration time (in seconds) for your cache entry. If your source data is likely to update quite often, set this at a low value. Here’s a modified refresher definition with an expiry time of 30 mins.

    // now assign the function to a refresher
    sharer.setRefresher('sheet-test', getCompanyValues, {
      communityKey,
      expiry: 60 * 30
    })

Forcing a refresh

It’s possible that you will want to forcibly reset cache – perhaps when the source data is updated. Instead of the getValue() method, use the refresh method. Refresh returns the same result as getValue()

const value = sharer.refresh('sheet-test')

Versions

You can use version in addition to differentiate versions of the same refresher. Cache results are specific to the name/version combination.

    // now assign the function to a refresher
    sharer.setRefresher('sheet-test', functionA, {
      communityKey,
      version: 'a'
    })
    // this could be a development version for example
    sharer.setRefresher('sheet-test', functionB, {
      communityKey,
      expiration: 60,
      version: 'b'
    })

    // they can be accessed like this
    const resultA = sharer.refresh('sheet-test', { version: 'a '})
    const resultB = sharer.refresh('sheet-test', { version: 'b' })

Using library CacheService UserCache

The ScriptCache store of the library is used by default. If you’d rather use the UserCache then you can specify that when creating the refresher.

    // now assign the function to a refresher
    sharer.setRefresher('sheet-test', functionA, {
      communityKey,
      libraryCacheServiceName: "User"
    })

Using an alternate cache service

You can pass your own cache service like this. In this case, the library will write all values to the cache service you provide. Remember that this will limit sharing to the the script whose cache service is being used, but will allow you to use all the features for regular script caching.

    
    sharer.setRefresher('sheet-test', functionA, {
      communityKey,
      cacheService: CacheService.getScriptCache()
    })

setRefresher arguments

Here are all the arguments for the setRefesher

  /**
   * how to refresh config data
   * @param {string} name for your refresher
   * @param {function} func the refesher function
   * @param {object} p options
   * @param {string} [name] name to identify the refresher
   * @param {string} [communityKey] key to partition cache data into groups
   * @param {string} [version] version of refresher function
   * @param {number} [expiry] expiry how long (secs) data is valid for
   * @param {CacheService} [cacheService] use your own cacheservice
   * @param {string} [libraryCacheServiceName='Script'] which library cache to use
   * @returns string - the ID of the refresher
   */
  setRefresher(name, func, {
    communityKey, 
    version, 
    expiry, 
    cacheService, 
    libraryCacheServiceName = "Script" 
  } = {})

Memory cache

The library also automatically maintains a memory cache, which means that if you access the same entry from the same script instance, memory will be checked first, and if it’s not found it’ll look in cache. Cache could take about 200ms, versus memory cache which is instantaneous.

Memory cache respects expiry times so just like regular cache, it will force a refresh on expiry.

Content addressibility and reusability

Up till now we’ve built variables into the refresher. That could be just fine for many use cases, but we can increase reusability of refresh functions by adding a payload to modify its behavior. The payload is used to create content addressible keys to partition results with different payloads.

In this example, we’ll use https://ipinfo.io/ to pick up location info on ip addresses. Without an api key, this api has very strict rate limits, and since the data is pretty static it’s a perfect target for caching and sharing between any scripts that need this info.

    const sharer = Exports.Sharer
    // remember to secure your own community key in your script's property store
    const communityKey = "bm-group-a:e81a2c98-66df-4f56-8add-3a6331566a48"

    // refresh function for ipgeo 
    const ipGeo = ({ name, version, payload }) => {
      // handle no payload if one is mandatory
      if(!payload) return null

      const { ip } = payload
      const url = `https://ipinfo.io/${ip}/geo`
      const response = UrlFetchApp.fetch(url)
      const text = response.getContentText()
      return {
        info: JSON.parse(text),
        name,
        version,
        payload
      }
    }
    // now we can use the same refresher 
    const name = 'ip-geo'
    const {info: info1} = sharer.getValue (name , {payload: {ip: "161.185.160.93"}})
    const {info: info2} = sharer.getValue (name , {payload: {ip: "173.194.212.106"}})
    

The results for each payload will be stored in cache separately using the payload as part of the key.

Tests

You can learn more features by examing and running the test scripts. These use both my Simple but powerful Apps Script Unit Test library and Handly helper for fiddler to exercise this cache sharer.

Links

bmCacheSharer

• github
• library
• key: 17IbCJbPqwEhAUCq-ePZGpUILT_w7tnVBGmmzU1p394nM_lzqX0NPYa2_

testBmCacheSharer

• github
• library