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
var requests = [{
createDeveloperMetadata:{
developerMetadata:{
// DeveloperMetaDataLocation with spreadsheet scope
}
}},{
createDeveloperMetadata:{
developerMetadata:{
// DeveloperMetaDataLocation with sheet scope
}
}}, {
createDeveloperMetadata:{
developerMetadata:{
// DeveloperMetaDataLocation with row scope
}
}}, {
// stuff for a column level-----
// CreateDeveloperMetadataRequest
createDeveloperMetadata:{
// DeveloperMetaData
developerMetadata:{
// DeveloperMetaDataLocation with column scope
}
}}
];
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.
var requests = [{
// stuff at spreadsheet level-----
// CreateDeveloperMetadataRequest
createDeveloperMetadata:{
// DeveloperMetaData
developerMetadata:{
// DeveloperMetaDataLocation with spreadsheet scope
metadataKey:"spreadsheetDetails",
metadataValue:JSON.stringify({
writtenBy:Session.getActiveUser().getEmail(),
createdAt:new Date().getTime()
}),
location:{
spreadsheet:true
},
visibility:"DOCUMENT"
}
}},{
// stuff at sheet level-----
// CreateDeveloperMetadataRequest
createDeveloperMetadata:{
// DeveloperMetaData
developerMetadata:{
// DeveloperMetaDataLocation with sheet scope
metadataKey:"sheetDetails",
metadataValue:JSON.stringify({
writtenBy:Session.getActiveUser().getEmail(),
createdAt:new Date().getTime(),
name:sheet.getName()
}),
location:{
sheetId:sheet.getSheetId()
},
visibility:"DOCUMENT"
}
}}, {
// stuff for a row level-----
// CreateDeveloperMetadataRequest
createDeveloperMetadata:{
// DeveloperMetaData
developerMetadata:{
// DeveloperMetaDataLocation with rows scope
metadataKey:"originalFirstAirport",
metadataValue:JSON.stringify({
writtenBy:Session.getActiveUser().getEmail(),
createdAt:new Date().getTime(),
name:sheet.getRange("A2").getValue()
}),
location:{
dimensionRange: {
sheetId:sheet.getSheetId(),
dimension:"ROWS",
startIndex:1, //(row2)
endIndex:2 // actually only 1 row (works like .slice)
}
},
visibility:"DOCUMENT"
}
}}, {
// stuff for a column level-----
// CreateDeveloperMetadataRequest
createDeveloperMetadata:{
// DeveloperMetaData
developerMetadata:{
// DeveloperMetaDataLocation with columns scope
metadataKey:"municipalityColumn",
metadataValue:JSON.stringify({
writtenBy:Session.getActiveUser().getEmail(),
createdAt:new Date().getTime()
}),
location:{
dimensionRange: {
sheetId:sheet.getSheetId(),
dimension:"COLUMNS",
startIndex:6, //(column 7)
endIndex:7 // actually only 1 row (works like .slice)
}
},
visibility:"DOCUMENT"
}
}}
];
Finally, a request to the advanced service
return Sheets.Spreadsheets.batchUpdate({requests:requests}, ss.getId());
The response
Is also verbose, and looks like this
{"replies":[{"createDeveloperMetadata":{"developerMetadata":{"metadataKey":"spreadsheetDetails","visibility":"DOCUMENT","metadataValue":"{\"writtenBy\":\"bruce@mcpher.com\",\"createdAt\":1507900026270}","metadataId":1184936567,"location":{"spreadsheet":true,"locationType":"SPREADSHEET"}}}},{"createDeveloperMetadata":{"developerMetadata":{"metadataKey":"sheetDetails","visibility":"DOCUMENT","metadataValue":"{\"writtenBy\":\"bruce@mcpher.com\",\"createdAt\":1507900026270,\"name\":\"Airports-play\"}","metadataId":458801985,"location":{"locationType":"SHEET","sheetId":145189286}}}},{"createDeveloperMetadata":{"developerMetadata":{"metadataKey":"originalFirstAirport","visibility":"DOCUMENT","metadataValue":"{\"writtenBy\":\"bruce@mcpher.com\",\"createdAt\":1507900026319,\"name\":\"Port Moresby Jacksons International Airport\"}","metadataId":1974435956,"location":{"dimensionRange":{"startIndex":1,"endIndex":2,"sheetId":145189286,"dimension":"ROWS"},"locationType":"ROW"}}}},{"createDeveloperMetadata":{"developerMetadata":{"metadataKey":"municipalityColumn","visibility":"DOCUMENT","metadataValue":"{\"writtenBy\":\"bruce@mcpher.com\",\"createdAt\":1507900026533}","metadataId":1341111322,"location":{"dimensionRange":{"startIndex":6,"endIndex":7,"sheetId":145189286,"dimension":"COLUMNS"},"locationType":"COLUMN"}}}}],"spreadsheetId":"1HRPS7jy2CBr6-8sJJdKDztCECDSAlh27UExezewtakM"}
It’s often easier to tidy that up before examining it – here’s a pattern to do that
var tidy = created.replies.map(function (d) {
return {
id:d.createDeveloperMetadata.developerMetadata.metadataId,
key:d.createDeveloperMetadata.developerMetadata.metadataKey,
value:JSON.parse(d.createDeveloperMetadata.developerMetadata.metadataValue)
};
});
Then the response looks like this
[{
"id": 1184936567,
"key": "spreadsheetDetails",
"value": {
"writtenBy": "bruce@mcpher.com",
"createdAt": 1507900026270
}
}, {
"id": 458801985,
"key": "sheetDetails",
"value": {
"writtenBy": "bruce@mcpher.com",
"createdAt": 1507900026270,
"name": "Airports-play"
}
}, {
"id": 1974435956,
"key": "originalFirstAirport",
"value": {
"writtenBy": "bruce@mcpher.com",
"createdAt": 1507900026319,
"name": "Port Moresby Jacksons International Airport"
}
}, {
"id": 1341111322,
"key": "municipalityColumn",
"value": {
"writtenBy": "bruce@mcpher.com",
"createdAt": 1507900026533
}
}]
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.
var gotById = Sheets.Spreadsheets.DeveloperMetadata.get (ss.getId() , id);
which gives a response like this
{"metadataKey":"spreadsheetDetails","visibility":"DOCUMENT","metadataValue":"{\"writtenBy\":\"bruce@mcpher.com\",\"createdAt\":1507900026270}","metadataId":1184936567,"location":{"spreadsheet":true,"locationType":"SPREADSHEET"}}
Searching
Normally you won’t know the id, and you’ll be searching by key. Here’s a function to search by key
function searchByKey(ss, key) {
return Sheets.Spreadsheets.DeveloperMetadata.search({
dataFilters:[{
developerMetadataLookup: {
metadataKey: key
}}]
}, ss.getId());
}
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.
function search () {
var ss = SpreadsheetApp.openById(SSID);
// get the spreadsheet level data
var sLevel = searchByKey (ss, "spreadsheetDetails");
// really we just want the ID and the values
var tidy = sLevel.matchedDeveloperMetadata.map (function (d) {
return {
id:d.developerMetadata.metadataId,
value:JSON.parse(d.developerMetadata.metadataValue)
};
});
Logger.log (JSON.stringify(tidy));
}
which gives this tidied up version
[{"id":118751552,"value":{"writtenBy":"bruce@mcpher.com","createdAt":1507900648520}}]
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.
function getByDataFilters (ss, key) {
// get the metadata for the key
var meta = searchByKey (ss , key);
var request = {
dataFilters:meta.matchedDeveloperMetadata[0].dataFilters
};
return Sheets.Spreadsheets.Values.batchGetByDataFilter(request , ss.getId());
}
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.
function getData () {
var ss = SpreadsheetApp.openById(SSID);
// get by key, then use the first match to get data
// using the sheet level filter
var sheetValues = getByDataFilters (ss, "sheetDetails");
// tidy up
var tidySheetValues = sheetValues.valueRanges[0].valueRange.values;
Logger.log (tidySheetValues);
}
And the data looks just like you would get back from getValues() with the Spreadsheet service.
[[name, latitude_deg, longitude_deg, elevation_ft, iso_country, iso_region, municipality, iata_code], [Port Moresby Jacksons International Airport, -9.443380356, 147.2200012, 146, PG, PG-NCD, Port Moresby, POM], [Keflavik International Airport, 63.98500061, -22.60560036, 171, IS, IS-2, Reykjavik, KEF] ... etc.
Exactly the same technique can be used to retrieve row or column data
function getRowData () {
var ss = SpreadsheetApp.openById(SSID);
// get by key, then use the first match to get data
// using the sheet level filter
var rowValues = getByDataFilters (ss, "originalFirstAirport");
// tidy up
var tidyRowValues = rowValues.valueRanges[0].valueRange.values;
Logger.log (tidyRowValues);
}
gives
[[Port Moresby Jacksons International Airport, -9.443380356, 147.2200012, 146, PG, PG-NCD, Port Moresby, POM]]
and
function getColumnData () {
var ss = SpreadsheetApp.openById(SSID);
// get by key, then use the first match to get data
// using the sheet level filter
var colValues = getByDataFilters (ss, "municipalityColumn");
// tidy up
var tidyColValues = colValues.valueRanges[0].valueRange.values;
Logger.log (tidyColValues);
}
gives
[[municipality], [Port Moresby], [Reykjavik], [Prishtina], [Edmonton], [Halifax], [Ottawa], [Quebec], [Montreal], [Vancouver], [Winnipeg], [London], [Calgary], [Victoria], [St. John's], [Toronto], [Algiers], [Accra], [Abuja], [Uyo], [Lagos], [Tunis], [Brussels], [Brussels], [Liege], [Berlin], [Dresden], [Frankfurt-am-Ma ...etc..
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
[[Port Moresby Jacksons International Airport, -9.443380356, 147.2200012, 146, PG, PG-NCD, Port Moresby, POM]]
column data
[[town], [Edmonton], [Port Moresby], [Reykjavik], [Prishtina], [Edmonton], [Halifax], [Ottawa], [Quebec], [Montreal], [Vancouver], [Winnipeg], [London], [Calgary], [Victoria], [St. John's], [Toronto], [Algiers], [Accra], [Abuja], [Uyo], [Lagos], [Tunis], [Brussels], [Brussels], [Liege], [Berlin], [Dresden], [Frankfurt-am-Main], [Münster], [Hamburg], [Cologne], [Dusseldorf], [Munich], [Nuremberg etc...
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.
function cleanUp () {
var ss = SpreadsheetApp.openById(SSID);
// get all the things and delete them in one go
var requests = ["spreadsheetDetails","sheetDetails","originalFirstAirport","municipalityColumn"]
.map (function (d) {
return {
deleteDeveloperMetadata: {
dataFilter:{
developerMetadataLookup: {
metadataKey: d
}}
}};
});
Logger.log (JSON.stringify(requests));
if (requests.length) {
var result = Sheets.Spreadsheets.batchUpdate({requests:requests}, ss.getId());
Logger.log (JSON.stringify(result));
}
}
Here’s the final delete request array
[{
"deleteDeveloperMetadata": {
"dataFilter": {
"developerMetadataLookup": {
"metadataKey": "spreadsheetDetails"
}
}
}
}, {
"deleteDeveloperMetadata": {
"dataFilter": {
"developerMetadataLookup": {
"metadataKey": "sheetDetails"
}
}
}
}, {
"deleteDeveloperMetadata": {
"dataFilter": {
"developerMetadataLookup": {
"metadataKey": "originalFirstAirport"
}
}
}
}, {
"deleteDeveloperMetadata": {
"dataFilter": {
"developerMetadataLookup": {
"metadataKey": "municipalityColumn"
}
}
}
}]