Sheets API - Developer Metadata - Desktop Liberation

With Sheets API developer metadata, you can associate keys and data with a spreadsheet, sheet, row or column. Previously, if you wanted to ‘remember’ some data attribute, let’s say the column to find particular data on in a given sheet, there was always the possibility that the column heading would change, or that extra columns would be inserted meaning that neither of these were reliable ways to re-address data in a future session. This idea of being able to both abstract data from its position, and also associate hidden commentary with it gives a new dimension to the developer.

The official documentation has just been released and can be found here.

This post has a brief summary of how to use the Sheets API to manipulate this data, along with an example use case. The SlidesMerge add-on creates new slide decks from a template presentation and some spreadsheet data. As a convenience, it stores preferences information specific to a template/spreadsheet combination so they are available the next time you run it.

For more info on developer metadata see Sheets Meta Data Advanced service library utilities, Keeping track of columns for on Edit triggers using sheets metadata and Sheets Developer Meta Data – Apps Script advanced service

Why not use just Properties Service?

Some data could indeed be stored in the document properties of the Spreadsheet, but some of the options refer to sheet and column choices which might change in name or position between runs. Using the Sheets Developer Metadata also allows richer data to be stored against precise locations – for example retaining the active cell position position between Add-on usages.

Writing metadata

Metadata is written as a key value pair and at multiple levels (LOCATIONS) within a spreadsheet. A unique ID is generated when metadata is created and it can be retrieved either by that key (multiple metadata can have the same key) or by the ID.

SPREADSHEET level metadata

Let’s look an example – the SlidesMerge Add-on. This is an add-on has some settings that we’d like to remember should we run it again with a given combination of Slides template and Sheet.

Ideally, with a given spreadsheet selected, it would be useful to automatically select the last results folder and template that was used with that spreadsheet as a default. By setting some SPREADSHEET level metadata with those IDS, the “last used” values can be retrieved the next time the add-on is started

Similarly, the selected options can be stored, again in the SPREADSHEET level metadata.

SHEETS level metadata

But here there are some values that are actually the names of specific sheets in the workbook, namely “Actors” and “globals”. If either of these names were to be changed between runs, then the stored spreadsheet metadata would no longer be accurate. By using a SHEETS level metadata, you can store information about a LOCATION – in this case a sheet – and even if the name or position of that sheet changes, the metadata will still point to the original sheet.

COLUMN and ROW level metadata

One of the settings, the multi-deck suffix, is specified by a column heading on a specific sheet. If that column heading changes then the SPREADSHEET metadata would again become inaccurate. By creating metadata specifying a LOCATION that defines this COLUMN, you can find the same data again later, even if the column is moved or renamed.

CELL level metadata

The settings have no cell level dependencies, but you may want to, for convenience, recall the active cell in the sheet so that bringing up the add-on positions the cursor at the cell that was active the last time it was used for a given spreadsheet. There is no CELL level metadata, but using metadata for a ROW and for a COLUMN, together with a matching strategy can define the intersection of that ROW and COLUMN. Since metadata LOCATIONS are maintained to take account of changes, even if rows and columns are deleted, moved or inserted this intersection will still refer to the same CELL.

VISIBILITY

This refers to what should be able to see the metadata. DOCUMENT visibility means that any script that can read the document can also read the metadata, and PROJECT means that only the cloud project that wrote the metadata can see it.

KEYS and VALUES

Being able to find this metadata, later on, allows you to maintain consistent settings across sessions in the face of changing spreadsheet layout. You can specify a KEY to find metadata by and assign a value of your choosing. Sometimes knowing the LOCATION of data is enough, but adding additional data can also be useful. Note that there can be multiple metadata against the same key. Here are some examples of requests generated in the examples discussed earlier.

SPREADSHEET level metadata request

The key includes the id of the slides template to allow the same spreadsheet to contain slidesmerge options for multiple templates.

SHEET level metadata request

This is referencing the sheet to use to get data from (the “Actors” sheet in the example), so the key is templateId-data. There’s no need for any data stored against the key at this time, but this has an added timestamp which might be useful at some point.

RANGE level metadata request

This refers to the column “Description” in the settings, which you’ll be able to retrieve with the templateId-multiSuffix key. The LOCATION is defined using a DimensionRange object which is common in the Sheets API.

CELL level metadata request

There are no cell level developer metadata, but this can be done using two requests – one for a row and another for a column. Using the intersection of these to define a cell location for the ActiveCell, you can automatically position there at the beginning of the Add-on.

Retrieving metadata

If you know the id of some metadata you can retrieve it directly, but you are more likely to be retrieving it by key. The search method is used to return all metadata that matches a given DataFilter object. In the SlidesMerge example, I can retrieve the metadata that matches the keys they’ve been written with.

Retrieving SPREADSHEET level data

In the example, the SPREADSHEET level data contains information about the options selected the last time this spreadsheet was used by SlidesMerge. The key contains the presentation templateID, so that the same spreadsheet can be used against multiple templates. Here’s the request to retrieve the metadata, which can be an array of DataFilters.

And the response

Retrieving SHEET level data

At the SHEET level, information about the sheets used to supply the data and global values are stored, keyed by the templateID plus “data” or “global”. Although their names are in the SPREADSHEET level metadata, it’s possible that their name has changed. In this case, the LOCATION is omitted from the DataFilter, since the key is sufficient. Here’s the request to get the sheet that is being used for the “data”.

And the response shows the sheetID for the sheet (that has perhaps been renamed), that was being used as the data input. The next step would be to find that sheet, and update the options with the new name if necessary.

Retrieving COLUMN level metadata

At the COLUMN level, information about the column to use for the multiSuffix option is kept. It’s possible that the column name or position has changed. The metadata saved for the column will have kept track of that and can be retrieved like this

Which will return the data in the column referenced by the given metadataId. My use case is simply to get the first element, which is is the column heading, and change the selected options if the name has changed, but this technique can be used to keep track of data using an abstraction, rather than a specific row or column numbers, or data values, all of which could change.