Improve Script performance by caching Spreadsheet values

Question

I am trying to develop a webapp using Google Apps Script to be embedded into a Google Site which simply displays the contents of a Google Sheet and filters it using some simple parameters. For the time being, at least. I may add more features later.

I got a functional app, but found that filtering could often take a while as the client sometimes had to wait up to 5 seconds for a response from the server. I decided that this was most likely due to the fact that I was loading the spreadsheet by ID using the SpreadsheetApp class every time it was called.

I decided to cache the spreadsheet values in my doGet function using the CacheService and retrieve the data from the cache each time instead.

However, for some reason this has meant that what was a 2-dimensional array is now treated as a 1-dimensional array. And, so, when displaying the data in an HTML table, I end up with a single column, with each cell being occupied by a single character.

This is how I have implemented the caching; as far as I can tell from the API reference I am not doing anything wrong:

function doGet() {
  CacheService.getScriptCache().put('data', SpreadsheetApp
                                  .openById('####')
                                  .getActiveSheet()
                                  .getDataRange()
                                  .getValues());

  return HtmlService
      .createTemplateFromFile('index')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getData() {
  return CacheService.getScriptCache().get('data');
}

This is my first time developing a proper application using GAS (I have used it in Sheets before). Is there something very obvious I am missing? I didn't see any type restrictions on the CacheService reference page...

Answer 1

CacheService stores Strings, so objects such as your two-dimensional array will be coerced to Strings, which may not meet your needs.

Use the JSON utility to take control of the results.

myCache.put( 'tag', JSON.stringify( myObj ) );

...

var cachedObj = JSON.parse( myCache.get( 'tag' ) );

Answer 2

Cache expires. The put method, without an expirationInSeconds parameter expires in 10 minutes. If you need your data to stay alive for more than 10 minutes, you need to specify an expirationInSeconds, and the maximum is 6 hours. So, if you specifically do NOT need the data to expire, Cache might not be the best use.

You can use Cache for something like controlling how long a user can be logged in.

You could also try using a global variable, which some people would tell you to never use. To declare a global variable, define the variable outside of any function.