Saving Data Between Script Runs via Properties Service

Any variable values you set while running an Apps Script will be stored only for that one execution of the script. The next time you run the script, those values will be gone. So how do you save some variables to re-use them later?

There are a few options, like using range of data within your spreadsheet, or perhaps saving data to a file in Google Drive. Either of these approaches let you save your data in a place from where you can later retrieve it.

A cleaner and perhaps more secure way, though, is to use Google’s Properties Service global object.

The Properties service lets you store data in key-value pairs relevant either to only one particular script,  or to one particular user (there is actually a third way, to one particular document, but only where the script is running as an add-on). It is typically used to store setup data, user preferences or similar.

In this example I’ll use the UserProperties property store to save some data on a per-user basis. First, let’s get the current user properties:

var userProperties = PropertiesService.getUserProperties();

To retrieve an individual property (in this case, a property I’ve called CURRENT_TIMESTAMP in which I’ve previously stored the date and time the script was last opened by me) I can use the getProperty() method. Here I’ll display it in a MsgBox dialg, as described in this previous post:

Browser.msgBox('Last run: ' + userProperties.getProperty('CURRENT_TIMESTAMP'), 
   Browser.Buttons.OK);

Now I want to save a new value to this variable, overwriting the value previously stored. This means getting the current timestamp and setting the value in userProperties:

var rightnow = new Date();
userProperties.setProperty('CURRENT_TIMESTAMP', rightnow);

Here’s the complete code, which is triggered by the onOpen() trigger of the sheet:

Click image to enlarge

Click image to enlarge

Now, whenever the sheet is run, the script will display in a dialog the time and date it was last opened by this user. (Of course, the first time you run it, it will return a null value, as no previous timestamp was stored).

Click image to enlarge

Click image to enlarge

You’re free to store whatever key-value pairs you like. To save data in bulk, pass a map of key-value pairs to the object userProperties using setProperties():

userProperties.setProperties({
   'CURRENT_TIMESTAMP': rightnow,
   'CURRENCY_FORMAT': 'GBP',
   'FAVOURITE_BREAKFAST': 'bacon'
});