Google Apps Script: How To Persist Data In Spreadsheet Between Different Function Calls?

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

Solution 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();

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

functiongetValue() {
  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

Solution 2:

Persistent data can be stored using the Class ScriptProperties:

http://code.google.com/googleapps/appsscript/class_scriptproperties.html

All values are stored as a string and will have to be converted back with the likes or parsInt or parseFloat when they are retrieved.

JSON objects can also be stored in this manner.

Solution 3:

My experience has been that every query to retrieve or store values takes a long time. At the very least, I would cache the information in your javascript code as much as possible when it is safe. My scripts always execute all at once, so I don't need to keep global variables as I simply pass the retrieved data arrays around, manipulate them, and finally store them back in one fell swoop. If I needed persistence across script invocations and I didn't care about dropping intermediate values on close of the webpage, then I'd use globals. Clearly you have to think about what happens if your script is stopped in the middle and you haven't yet stored the values back to Google.