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.

var cache = mcpher.sheetCache("some cache or other");
   cache.forEach ( function (v,r,c)  { ... do something with each value ....} );
cache.close();

 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.

 /** return spreadsheet data from google cache, or get it and write it there
 /* @param {String} wb The id of the workbook containing the data
  * @param {String} wn The name of the sheet required
  * @param {Number=} optSeconds how long to aim to keep it around for
  * @return {Array} the data
 */

function getPersistData ( wb, wn ,  optSeconds) {
  var googCache = CacheService.getPrivateCache();
  var sheetData = googCache.get( shortKeyHash ( wb + "!" + wn ))  ;
  if (sheetData)
    // we know it
    return JSON.parse (sheetData);
  else {
    // we know nothing, open the workbook & cache
    sw = SpreadsheetApp.openById(wb).getSheetByName(wn).getDataRange().getValues();
    googCache.put(shortKeyHash ( wb + "!" + wn ) , JSON.stringify(sw), fixOptional ( optSeconds, 60));
    return sw;
  }

}

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.

function testGoog () {
   // this is all you need to get data from the sheet or cache if it's available
  
var data =  mcpher.getPersistData  
("0At2ExLh4POiZdFAzUElyTGNQLW90aEFKeHhJZDR1WWc", "freegeoip");
 ... do something with data[][]....

}

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.)

function testGoogTime () {
 // Measure it
   mcpher.useTimer('a').start('first');
   testGoog ();
   mcpher.useTimer('a').stop();
   Logger.log(mcpher.useTimer().report());
}

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.

(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘https://apis.google.com/js/plusone.js’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();

Author: bm082975

Leave a Reply

Your email address will not be published. Required fields are marked *