Sheets Meta Data Advanced service library utilities

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.

Page Content hide

1 Searching

2 Getting values by Key

3 Getting an intersection range.

4 Share with your network

Searching

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

 var meta = cSAM.SAM.searchByKey (SSID , "municipalityColumn");

That returns something that looks like this

{
  "matchedDeveloperMetadata": [{
  "dataFilters": [{
  "developerMetadataLookup": {
  "metadataKey": "municipalityColumn"
  }
  }],
  "developerMetadata": {
  "metadataKey": "municipalityColumn",
  "visibility": "DOCUMENT",
  "metadataValue": "{\"writtenBy\":\"bruce@mcpher.com\",\"createdAt\":1508156976227}",
  "metadataId": 1274397116,
  "location": {
  "dimensionRange": {
  "startIndex": 6,
  "endIndex": 7,
  "sheetId": 55669457,
  "dimension": "COLUMNS"
  },
  "locationType": "COLUMN"
  }
  }
  }]
 }

Which can be quite a mouthful. .tidyMatched will clean that up a bit to this.

var tidy = cSAM.SAM.tidyMatched(meta));

To get a more manageable

[{
 "id": 1274397116,
 "key": "municipalityColumn",
 "visibility": "DOCUMENT",
 "value": {
 "writtenBy": "bruce@mcpher.com",
 "createdAt": 1508156976227
 },
 "location": {
 "dimensionRange": {
 "startIndex": 6,
 "endIndex": 7,
 "sheetId": 55669457,
 "dimension": "COLUMNS"
 },
 "locationType": "COLUMN"
 }
}]

Getting values by Key

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

var sheetValues = cSAM.SAM.getByDataFilters (SSID, "municipalityColumn");

Which will return something like this

{"valueRanges":[{"valueRange":{"majorDimension":"ROWS","values":[["municipality"],["Port Moresby"],["Reykjavik"],["Prishtina"],["Edmonton"],["Halifax"],["Ottawa"],["Quebe....

Applying a convenience tidy up

var values = cSAM.SAM.tidyValues(sheetValues);

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

[["municipality"],["Port Moresby"],["Reykjavik"],["Prishtina"],["Edmonton"],["Halifax"],["Ottawa"],["Quebe...

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.

var ss = SpreadsheetApp.openById(SSID);
  var range = cSAM.SAM.getIntersection ( ss.getId()  , "originalFirstAirport", "municipalityColumn")(ss);
  var values;
  if (range) {
    values = range.getValues();
  }

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.

/**
    * get intersecting range
    * of two items by key
    * @param {string} ssId  we'll need the ss for this
    * @param {string} rowKey
    * @param {string} colKey
    * @return {function} a function that creates a range given an ss .. function (ss) { return ..Range}
    */
    ns.getIntersection = function (ssId, rowKey , colKey) {
      
      // do the searches and use the latest keys
      var rowMeta = ns.searchByKey (ssId , rowKey);
      var colMeta = ns.searchByKey (ssId , colKey);
      
      // tidy that up
      var rowTidy = rowMeta && ns.tidyMatched (rowMeta);
      var colTidy = colMeta && ns.tidyMatched (colMeta);
      
      // now we show have dimension ranges, just use the latest
      if (!rowTidy || !colTidy || !rowTidy.length || !colTidy.length) return function (ss) {
        return null;
      };
      
      
      var dimRow = rowTidy[rowTidy.length-1].location && 
        rowTidy[rowTidy.length-1].location.dimensionRange;
      var dimCol = colTidy[colTidy.length-1].location && 
        colTidy[rowTidy.length-1].location.dimensionRange;
      
      // do a few checks
      if (!dimRow || !dimCol || dimCol.sheetId !== dimRow.sheetId) {
            throw 'row and column sheetIds dont match';
      }
      
      if (dimRow.dimension !== "ROWS") {
        throw 'row key ' + rowKey + ' must refer to rows metadata';
      }
      
      if (dimCol.dimension !== "COLUMNS") {
        throw 'column key ' + colKey + ' must refer to columns metadata';
      }
      
      // now construct a function that can make a range
      return function (ss) {
        
        
        // first find the sheet
        var sheet = ss.getSheets().filter (function (d) {
          return d.getSheetId() === dimRow.sheetId;
        })[0];
        if (!sheet) throw 'sheet ' + dimRow.sheetId + ' was not found';
        
        // now work out the range
        return sheet.getRange (
          dimRow.startIndex +1 , 
          dimCol.startIndex +1 , 
          dimRow.endIndex - dimRow.startIndex , 
          dimCol.endIndex - dimCol.startIndex
        );
        
      };
      
      
    };

For more like this see Google Apps Scripts Snippets