Google apps script: how to persist data in spreadsheet between different function calls?

Question

In a Google spreadsheet using the Script Editor, I do function calls, but I am not quite sure if the best way to store persistant data (data that I will continue to use) is to use global variables (using objects, arrays, strings), or there is a better way to store data.

I don't want to use cells which could be another way.

Another question, is it possible to create (pseudo) classes in this environment? Best way?

Answer 1

Both ScriptProperties and ScriptDB are deprecated.

Instead, you should be using the new class PropertiesService which is split into three sections of narrowing scope:

• Document - Gets a property store that all users can access within the current document, if the script is published as an add-on.
• Script - Gets a property store that all users can access, but only within this script.
• User - Gets a property store that only the current user can access, and only within this script.

Here's an example persisting a user property across calls:

var properties = PropertiesService.getScriptProperties();

function saveValue(lastDate) {
  properties.setProperty('lastCalled', lastDate);
}

function getValue() {
  return properties.getProperty('lastCalled');
}

The script execution environment is stateless, so you cannot access local variables from previous runs, but you can store getScriptProperties() in a local variable because it will be re-run for each return trip to the server so it can be called in either method.

---

If you need to store something on a more temporary basis, you can use the CacheService API