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" } } }] }
var tidy = cSAM.SAM.tidyMatched(meta));
[{ "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.
var ss = SpreadsheetApp.openById(SSID); var range = cSAM.SAM.getIntersection ( ss.getId() , "originalFirstAirport", "municipalityColumn")(ss); var values; if (range) { values = range.getValues(); }
/** * 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 ); }; };