Sheets Developer Meta Data – Apps Script advanced service, shows how to used the Sheets Advanced service to work with Sheets DeveloperMetada. This post goes a little further and adds a library to simplify access to the metadata resource which can be a little verbose, and failure prone when there are undefined object properties along the way. You don’t need this library of course, since you can work with the advanced service directly, but it might come in handy in any case. It doesn’t have many methods for now – just the most common ones, but I’ll be adding to it as we go along.

Library cSAM is at

Mcbr-1wYuya4Vchqa8Ovl0MYSxYwKM8ZjiGFqO5NUo_KzGi-GPMT-HfDEJ6i3k

, and the code is available on GitHub or here.

You will of course need to activate the Sheets advanced service first.

Searching

The usual way to search for metadata is by Key. You can do that like this example.

That returns something that looks like this

 

Which can be quite a mouthful. .tidyMatched will clean that up a bit to this.
To get a more manageable

Getting values by Key

You can use the result of a search to get Values from a given metadata item.

Which will return something like this

Applying a convenience tidy up

returns data in the same format as range.getValues()

Getting an intersection range.

Sheets metadata can be referenced by ROWS or COLUMNS location definition, but quite often you’ll want the range that is the intersection of that. SAM has a handy method to take two metadata keys and return a function that can generate a range that describes where those intersect. You can then use the normal Sheets getValues function to return values or backgrounds or whatever property is required.
Here’s an example of using it.
Note that getIntersection returns a closure function which is expecting the spreadsheet object as an argument. This is a more secure way of having libraries do work for you as you never have to pass a handle to your spreadsheet directly to the library. Instead it passes a function to you which you execute in your app. Here’s the code – the returned function can be executed to obtain a range that describes the intersection of a row and column metadata object – which sheetsMetadata magic will have automatically adjust even if there have been  rows and columns added or deleted.
For more like this see Google Apps Scripts Snippets
Why not join our community, follow the blog or follow me on Twitter