Clever Geek Handbook

Improving Script Performance by Caching Spreadsheet Values

I am trying to create a webapp using Google Apps Script to be embedded in Google Site that simply displays the content of a Google Sheet and filters it with some simple options. For now, at least. I can add more features later.

I got a functional app, but found that filtering might take a while as the client sometimes had to wait up to 5 seconds for a response from the server. I figured 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 function doGet

using the CacheService and fetch data from the cache every time instead.

However, for some reason, this meant that what was a two-dimensional array is now treated as a one-dimensional array. And thus, when displaying data in an HTML table, I get one column, with each cell occupied by one character.

This is how I implemented caching; as far as I can tell from the API link, 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 development of a suitable application using GAS (I used it earlier in Sheets). Is there something very obvious that I am missing? I didn't see any type constraints on the CacheService help page ...

+3


source to share


2 answers


The CacheService stores strings, so objects like your 2D array will be coerced into strings, which may not suit your needs.

Use JSON utility to manage results.



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

...

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


source


The cache is about to expire. The method put

without the expirationInSeconds parameter expires in 10 minutes. If you need data to stay alive for more than 10 minutes, you need to specify expirationInSeconds , and the maximum is 6 hours. So, if you don't need data that is about to expire, Cache might not be the best way.

You can use Cache to control how long a user can log in.



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

+1


source






More articles:


All Articles