Recently, developer metadata was added to the capabilities of the Sheets V4 API, as described here Sheets API – Developer Metadata (which used the API directly). If you don’t know about Metadata, take a look at that post first for background. Now it’s available as part of the Apps Script Sheets advanced service. The requests and responses structure are quite complex, so here’s a detailed run through of some things you can do and how to do them. You’ll need to activate the Sheets advanced service in Apps Script an in the cloud console first.

Since posting this, there’s a utility library available to make all this a little simpler – see Sheets Meta Data Advanced service library utilities

Writing metadata

All the examples here will be using this sheet, and writing metadata to the containing spreadsheet.

All write operations to the API are done in batch. This means that you can do many things in one request, with the logic being built into the request body. The request following writes 4 pieces of metadata
  • Spreadsheet level metadata
  • Sheet level meta data
  • ROWS metadata identifying a particular row
  • COLUMNS metadata identify a particular column

The first step is to create an array of requests that creates all this metadata in one go. The requests are pretty verbose and together, look like this in abstract form

Filled out with some actual data, it looks like this.  Note that each item has a location to say where it applies to, a key to retrieve it by, and a value to store interesting data against. multiple metadata items can be stored against the same key, so it may be better to use updateDeveloperData requests if you want them to be unique, but this example will create multiple items if run more than once.

Finally, a request to the advanced service

The response

Is also verbose, and looks like this

It’s often easier to tidy that up before examining it – here’s a pattern to do that

Then the response looks like this

Getting by ID

You’ll notice that each item of metadata has had an ID generated. This ID can be used to retrieve the item.
which gives a response like this

Searching

Normally you won’t know the id, and you’ll be searching by key. Here’s a function to search by key
This again returns a verbose response, which I won’t reproduce here, but here’s a pattern to use the function, and tidy up the response.
which gives this tidied up version

Using the metadata

It’s obvious that spreadsheet and sheet level metadata can be used to do things like tracking updates and keeping abstract information. Even if a sheet is moved or renamed, its sheet metadata will still be attached to the original sheets. But metadata can also be used to retrieve (or update) values in the sheet without bothering with ranges. Here’s a function that can be used to get metaData for a given key and to retrieve the data. You can adapt it by adding an appropriate level of error checking for no data and so on. The .batchGetByDataFilter method is able to take the dataFilters returned by the metadata search to get the data associated with the metadata’s location.
Again the response is a little verbose so it could do with some tidying up as below, and of course needs a few checks for null data data. Here’s an example retrieving the entire data from the sheet.
And the data looks just like you would get back from getValues() with the Spreadsheet service.
Exactly the same technique can be used to retrieve row or column data
gives
and
gives

The magic

Let’s say that things in the sheet get changed from the time the metadata is written and the time you want to retrieve data. For example
becomes
Here, the municipality column has been renamed and moved, and the first row has been moved down – yet the row and column locations returned by using the metadata has automatically been adapted to take account of all changes!
row data
column data

Cell data

There is no cell level metadata, but you can use the intersection of a row and a column to retrieve data. This location adapts with row and column changes so you can keep track of a cell even though new rows and columns are added. More on that here

Deleting metadata

Searching could return multiple responses if you have more than one item with the same key. Deleting is done in batch, so the most efficient way is to create a request that contains all the delete requests, then make one call to the Sheets service. One way would be to search for every matching item, get its id, and delete them all as part of a long delete request. However, since the deleteDeveloperData request takes a DataFilter argument, we can use the key to delete multiple items in one go, rather than one at a time with the id.
Here’s how to delete all the metadata created in this example.
Here’s the final delete request array
For more like this see Google Apps Scripts Snippets
Why not join our community, follow the blog or follow me on Twitter.