Sheets Developer Meta Data - Apps Script advanced service

Apps Script (advanced level) posted on 13th Oct 2017

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
    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"
  }
  }
  }
 }]



For more like this, see Google Apps Scripts snippets. Why not join our community , follow the blog, twitter, G+ .You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments