Google Apps Script APIs performance

By Luis Peralta

During the last few months, as I was implementing a few scripts on Google’s Apps Script, I needed to answer the question of what is the right place to store data.

Factors to consider when storing data in typical Apps Script programs would be volume, persistence needs, access requirements and speed.

A few APIs are available that provide these features, namely:

PropertiesService
CacheService

These are key-value stores that serialize stored values to string (careful, one can be bitten by this). Both of them allow to store user or script data, depending if we want the data to be accessible for every user or not, and are supposed to be used to store small amounts of data (each store can store only up to 500KB). Well, that is good enough for the simple scripts that one builds on the platform on a majority of cases. See the official documentation.

What caught my attention, as I was evaluating how to store my script’s data, was how slow some of those APIs were. See the following table for some of the measures I got for the different methods, based on 100 consecutive sample runs. The times listed are in milliseconds.

Method Min Avg Max StDev
PropertiesService.getScriptProperties().setProperty 43 62 231 37
PropertiesService.getScriptProperties().getProperty 20 30 84 9
PropertiesService.getUserProperties().setProperty 43 64 204 37
PropertiesService.getUserProperties().getProperty 21 34 84 11
CacheService.getScriptCache().put 73 93 281 18
CacheService.getScriptCache().get 3 3 7 1
CacheService.getUserCache().put 73 92 108 9
CacheService.getUserCache().get 3 4 8 0

Some interesting facts by looking at the data:

  1. Sloooowwwww. Setting a k-v is expected to be cheap. Well, wrong!
  2. CacheService put is ~25x slower than the get
  3. PropertiesService set is 2x slower than the get
  4. Standard deviations for PropertiesService methods are considerable
  5. Storing a k-v in the CacheService is ~30% slower than in the PropertyService
  6. Reading a k-v from the CacheService is 10x faster than in the PropertyService
  7. If setting CacheService k-v’s would not be that expensive and the TTL so low (600 seconds, non guaranteed), it would be a great temporary data store

Times can vary heavily depending on the time of the day (I think this is the biggest driver). Easy to see 2x on the above times without any reason.

The services allow getting or setting multiple keys at once, introducing the challenge of knowing every key in advance, which makes relying on these APIs less costly in terms of time. Good luck organising your code to achieve that.

My overall recommendation before using these APIs is: do not over rely on them, as the performance of your script / app is going to be negatively impacted, and think of the access patterns that you need, as a certain combination of the available solutions might work for you. For the particular thing I was building, I ended up creating an external CRUD REST API to store the data I needed to persist.

Feel free to perform the benchmark test yourself by accessing the AppScript Benchmark project or copying this gist to your own project.