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' ) );