Google Apps Script: 2 Caching Methods You Need to Use!

If you’ve ever had to do any serious data crunching in Google spreadsheets, then you’ve probably had to implement some Apps Script code to lend a hand. And if you’ve ever had to do that, then you’ve probably run into the dreaded execution time limits that Apps Script imposes.

The specific time limits imposed of course depend on how the code is running - i.e: as a custom function, as a trigger, as a web app, etc. But regardless of the context, the time limits can be a real pain.

My Recent Experience

Case in point, some Apps Script code I’ve been working on recently that needed to pull data from an external API.

The data needed was relatively small in size, and while the API itself wasn’t slow, API calls are expensive from a time perspective, and the code was running as a custom function in a Google Sheet.

The result? The code was timing out before it could finish.

Now in a normal JavaScript runtime environment, there’s no way the code would have taken as long as it was taking to run. But I guess due to the shared nature of the Apps Script environment, the code was taking way longer than it should have… more than 30 seconds!!!

So I spent time looking at ways to optimize the code, and certainly was able to make some decent improvements, but those improvements still weren’t enough to get the code to run within the time limits, which in this case was the 30 seconds max imposed for custom functions.

My Mistaken Assumption

Now for some reason, I originally thought that Google Sheets had automatic caching built in for some types of code execution. I know for a fact that it does some caching of custom functions, but I guess it wasn’t working as I expected.

I had already implemented a form of long-term caching in the code from the start, but I finally realized I needed to utilize some short-term caching as well. By implementing both caching strategies, I’d be able to eliminate a lot of the API calls, but also some of the sheet reads that were also slowing down the code.

The Two Apps Script Caching Strategies

Alright, so without further ado, let’s dive into the two caching strategies I used to improve the efficiency of the code…

Long-Term Caching

I’ll start with the long-term caching strategy, as it’s the one I had already implemented in the code from the start.

For the long-term caching, I used the PropertiesService class to store the data in the script properties. This way, the data would persist across executions of the script, and I wouldn’t have to make the API calls every time the script ran.

Note: You can view the script properties by going to the Apps Script editor, and clicking on the gear icon in the left sidebar, and then scrolling down to the “Script Properties” section.

More details about the PropertiesService class can be found on the official developers.google.com apps-script properties-service page.

Now obviously this is the type of caching you’d want to use for data that will not be changing frequently, or ever for that matter. For example, say you wanted to know the international currency code for a country (like CAD for Canada, USD for the US, GBP for Great Britain, etc), you could cache that data long-term, as it’s not going to change.

So I created two functions, one to get the data from the properties, and one to set the data in the properties. Here’s what they look like:

const getStoredData = (key) => {
  try {
    const scriptProperties = PropertiesService.getScriptProperties();

    if (key) {
      return scriptProperties.getProperty(key);
    } else {
      return scriptProperties.getProperties();
    }
  } catch (e) {
    console.log(`error getting stored data: ${e.message}`);
    return null;
  }
};

const storeData = (key, value) => {
  const scriptProperties = PropertiesService.getScriptProperties();

  scriptProperties.setProperty(key, value);
};

Super simple.

The getStoredData function takes an optional key parameter, which if provided, will return the value stored in the properties for that key. If no key is provided, it will return all the properties.

Note that in the getStoredData() function, I’ve also added a try...catch block to catch any errors that might occur when trying to get the stored data.

This probably isn’t necessary, as the PropertiesService should simply return null if the key doesn’t exist. But I wanted to be safe and catch any other errors that might occur, as the Google docs don’t specify what errors, if any, might be thrown.

The storeData function takes a key and a value, and stores the value in the properties under the key.

Now the important thing to note is that the data stored in the properties is stored as a string. So if you’re storing an object, you’ll need to JSON.stringify it before storing it, and JSON.parse it when you retrieve it.

I’ve purposely left the JSON conversion out of the functions above, because we can’t assume that all data will be objects. But if you’re storing objects, you’ll need to add that in to your code where necessary.

Ok, so that was the long-term caching strategy. Now let’s move on to the short-term caching strategy…

Short-Term Caching

For the short-term caching, things work a bit differently. Because I might need to cache an API call response, or a spreadsheet read/write, which are very different things, I opted for separate functions for each type of caching.

The syntax of the cache.put() method is as follows:

cache.put(key, value, expirationInSeconds);

Where:

• key is a string that uniquely identifies the data in the cache
• value is the data you want to store in the cache, in string form, just like with the PropertiesService
• expirationInSeconds is the number of seconds (21,600 seconds max) the data should be stored in the cache before it expires.

And the syntax of the cache.get() method is as follows:

cache.get(key);

Where:

• key is the string that uniquely identifies the data in the cache

Note: CacheService has a few different methods for creating different types of caches, such as getScriptCache(), getDocumentCache(), and getUserCache(). I used the getDocumentCache() method, as I wanted the cache to be available across all executions of the script, but only for the current document.

More details about the cache class can be found on the official developers.google.com apps-script cache page.

API Caching

For API caching, I used the following code:

const cachedFetch = (url, expirationInSeconds) => {
  const cache = CacheService.getDocumentCache();
  const cachedData = cache.get(url);

  if (cachedData) {
    return cachedData;
  }

  const res = UrlFetchApp.fetch(url);
  const content = res.getContentText();

  if (content) {
    cache.put(url, content, expirationInSeconds);
  }

  return content;
};

The cachedFetch function takes a url and an expirationInSeconds parameter. It first checks the cache for the data, and if it finds it, it returns the cached data.

If the data isn’t found in the cache, it fetches the data from the URL, and then stores it in the cache with the expiration time provided, using the url as the cache key.

Note: The max expiration time for the cache service is 6 hours (21,600 seconds), however Google doesn’t guarantee that the data will be available for that long.

Spreadsheet Caching

For spreadsheet caching, I used the following code:

const cachedSheetRead = (sheetName, range, expirationInSeconds) => {
  const cache = CacheService.getDocumentCache();
  const cachedData = cache.get(sheetName + range);

  if (cachedData) {
    return JSON.parse(cachedData);
  }

  const data = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getRange(range)
    .getValues();

  cache.put(sheetName + range, JSON.stringify(data), expirationInSeconds);

  return data;
};

The cachedSheetRead function takes a sheetName, a range, and an expirationInSeconds parameter.

First, we check the cache for the data, and if it’s found, we return it and exit the function. Otherwise, we read the data from the sheet, store it in the cache, and return that data.

In the above code, because I know the data I’m caching is an array of arrays (the values from a range in a Google Sheet), I’m using JSON.stringify and JSON.parse to convert the data to and from a string directly in the function. This is in contrast to my long-term caching strategy, where I left the JSON conversion out of the functions.

Clearing the Cache

If you ever need to clear the cache, you can use the remove() method, like so:

cache.remove(key);

Where key is the string that uniquely identifies the data in the cache.

Or alternatively, you can use the removeAll() method to clear multiple items from the cache at once:

cache.removeAll([key1, key2, key3, ...]);

Where key1, key2, key3, etc are the keys of the data you want to remove from the cache.

How Much of a Difference did it Actually Make?

Well, I’m pretty lazy and honestly haven’t bothered to time the spreadsheet caching to see the exact performance improvement. And actually, because the code was timing out before, I can’t even tell you how long it was taking before I implemented the caching.

But because we no longer need to read from the sheet every time, it will certainly add a significant speed improvement. The API caching will also help a lot, especially if the API calls are slow, and if the API calls are metered (which they most likely are).

Conclusion

So there you have it, two caching strategies you can use to vastly improve the efficiency of your Apps Script code.

In retrospect, I should have implemented the short-term caching from the start, as it would have saved me a lot of time and headache. But hey, you live and you learn! And now that I’ve learned, I’ll be sure to implement these caching strategies in all my future Apps Script projects.

I hope you found this post helpful, and that it saves you some time and headache as well. If you have any questions or comments, feel free to leave them below!