Using Google Cache to persist data across function calls

When working with spreadsheet data, I always use sheet caching – a class in the mcpher library. This gives great performance improvements and simplification for processing Sheet Data and other attributes.
You can use it just like this.

But the  cache doesn’t persist across discrete function calls. Lets say you want to read in parameters from some external workbook, and with various disconnected functions, do something with those parameters. You could of course put the parameters in scriptDB, or script Properties etc, but let’s say these are in a sheet. The sheetCaching code will work, but it will need to go off and read the parameter sheet since it would not be able to persist between disconnected function calls.

Put the data in Google cache service.
So why not use Google Cache service to stick your sheet data into, and try and get it from there first if you can. Here’s a real simple function to do that. It’s already implemented in the
mcpher library if you want to use it from there.

And here’s how to call it. It will get the data for the given workbook and worksheet from cache if it can find it. If not it will get it from the workbook and store it in cache in case you need it again shortly.

Using the Apps Script Timer I find that a small sheet takes about 300ms to get the data from a workbook. When I run it subsequent times it takes about 3. Try this on the above and see for yourself (you’ll need the mcpher library.)

This little trick is ideal for data that is being used as a reference in various sheets, where events drive multiple data lookups. I published it in response to a question on our forum the other day , but thought you might find it useful.

About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


19 − four =