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.
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
);
};
};