Practical use case of DeveloperMetadata in GAS

Question

I recently found published new Classes and methods of Spreadsheet related to DeveloperMetadata, and I am seeking code examples showing practical use of such data structures.

I have tried to grasp the idea of metadata, but so far it is too confusing for me.

Let's suppose this scenario - I'm developing some automation for spreadsheets. There is table with orders (date, customer name, sold item name, price, etc). In bounded script I address price column like this sheet.getRange("H:H") (hardcoded). But when an user of the sheet change position of a column "H" somewhere else the script will stop working properly - unless I change in code "H" to new column letter.

I am seeking some real GAS code (not Advanced Sheet services) which will show how easily handle this situation.

I'm imagining something like this (pseudo-code):

sheet.getRange("H:H").setMatadata("columnName","price"); //First set column identification

var priceColumnRange = sheet.getMetadaDataByKey("price").getRange(); //Then retrieve column range by its identification

I found a way how to set metadata to a range, but I didn't find a way how to easily retrieve (search/find) this data. It looks really difficult from documentation, and I still don't know how to use metadata for such an easy task.

Answer 1

• You want to search and retrieve DeveloperMetadata from rows and columns using Class DeveloperMetadata.
  • You want the sample scripts for above situation.

If my understanding is correct, how about this sample script? I tried to think of about this situation because this is also useful for my situation. I think that there are several sample scripts for your situation. So please think of this as one of them.

Sample script 1:

In this sample script, all DeveloperMetadata is retrieved from the sheet which has the sheet name of "Sheet1".

var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().find();
var res = v.map(function(e) {
  var loc = e.getLocation();
  var obj = loc.getLocationType() === SpreadsheetApp.DeveloperMetadataLocationType.COLUMN ? {range: loc.getColumn().getA1Notation()} :
    loc.getLocationType() === SpreadsheetApp.DeveloperMetadataLocationType.ROW ? {range: loc.getRow().getA1Notation()} : {};
  obj[e.getKey()] = e.getValue();
  return obj;
});
Logger.log(res)

• As a sample situation, after DeveloperMetadata is set to the column "A", when it is moved to the column "B", this script retrieves the key and values of column "B".
• For example, if you want to search using key and value, you can also use the following scripts.
  • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withKey("key1").find();
  • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withValue("value").find();
  • var v = s.getRange(1, 1, s.getMaxRows(), s.getMaxColumns()).createDeveloperMetadataFinder().onIntersectingLocations().withKey("key1").withValue("value1").find();
• This is a sample script. So please modify this for your situation.

Result:

When 2 DeveloperMetadata of {key1: "value1"} and {key2: "value2"} are set to row 1 and column A, the following result is obtained.

[
  {
    "range": "1:1",
    "type": "ROW",
    "key1": "value1"
  },
  {
    "range": "A:A",
    "type": "COLUMN",
    "key2": "value2"
  }
]

Sample script 2:

As another way, you can also search DeveloperMetadata using Sheets API as follows. When you use Sheets API, please enable Sheets API at Advanced Google Services and API console. You can see about how to enable Sheets API at here.

var resource = {"dataFilters": [{"developerMetadataLookup": {"metadataKey": "key1"}}]}; // Search by key
// var resource = {"dataFilters": [{"developerMetadataLookup": {"metadataValue": "value1"}}]}; // Search by value
// var resource = {"dataFilters": [{"developerMetadataLookup": {"metadataKey": "key1", "metadataValue": "value1"}}]}; // Search by key and value
var res = Sheets.Spreadsheets.DeveloperMetadata.search(resource, spreadsheetId);

References:

• Class DeveloperMetadata
• spreadsheets.developerMetadata.search

If this was not what you want, I'm sorry.

Edit:

For your 2nd question.

1. Add a metadata to "H:H" as the key of "columnName" and the value of "price".
2. Envn if the column "H" was moved, you want to retrieve the new range using the value of metadata.

If my understanding is correct, how about this?

Add metadata

In order to add a metadata to the column "H", you can use the following script. Because there is no method of setMatadata(), please use addDeveloperMetadata().

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange("H:H").addDeveloperMetadata("columnName", "price");

Retrieve ranges using value

This sample script retrieves ranges using a value of metadata. In this script, envn if the column "H" was moved, the new range using the value of metadata can be retrieved.

var sheet = SpreadsheetApp.getActiveSheet();
var value = "price";
var v = sheet.getRange(1, 1, 1, sheet.getMaxColumns())
  .createDeveloperMetadataFinder()
  .onIntersectingLocations()
  .withValue(value)
  .find();
var ranges = v.map(function(e) {return e.getLocation().getColumn()}); // "ranges" is one dimensional array.

Answer 2

Here is a super simplified example:

const initialize = () => {
  spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadSheet.addDeveloperMetadata("initializeDone", "true", SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT)
  
  const metaData = spreadSheet.getDeveloperMetadata();
  Logger.log('Metadata key: ' + metaData[0].getKey()); // "initializedDone"
  Logger.log('Metadata value: ' + metaData[0].getValue()); // "true"
}

Notice the [0] reference: the metaData object is kind of like an array containing many elements. But you still need to use the DeveloperMetadataFinder to search based on key or value:

  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const finder = spreadSheet.createDeveloperMetadataFinder();
  metaData = finder.withKey('initializeDone').find();
  Logger.log(metaData[0].getValue()); // "true"