How does one specify a row when trying to programatically create DeveloperMetadata for just a row

Question

I'm using this script to create some DeveloperMetadata for some rows. But I have to admit I don't know how to specify a complete row or column using the Sheet.getRange(row,cols,numrows,numcols) method and I keep getting the error show below.

function setupMetaData() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  for(var r=2;r<sh.getLastRow();r++) {
    let rg=sh.getRange(r,1,1,sh.getMaxColumns());//tried sh.getLastColumn()
    let lbl=Utilities.formatString('omer%s',r);//creating a label for later use
    rg.addDeveloperMetadata(lbl,SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT);//get error here
  }
}

This is the error

Exception: Adding developer metadata to arbitrary ranges is not currently supported. Developer metadata may only be added to the top-level spreadsheet, an individual sheet, or an entire row or column.

I also tried to create DeveloperMetadata for the Active Row and the Active Column using these scripts and just selecting an entire row or column and that doesn't seem to get me anywhere either.

function createMetadataForActiveRow() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getActiveRange();
  let lbl=Utilities.formatString('omer%s',rg.getRow());
  rg.addDeveloperMetadata(lbl,SpreadsheetApp.DeveloperMetadataVisibility.PROJECT);
}

function createMetadataForActiveCol() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getActiveRange();
  let lbl=Utilities.formatString('omec%s',rg.getColumn());
  rg.addDeveloperMetadata(lbl,SpreadsheetApp.DeveloperMetadataVisibility.PROJECT);
}

The above functions seem to run okay I not getting any errors in view logs or from the console.log

I been trying to use the following function to get the sheet DeveloperMetadata but dmd is always returned with zero length,

function getSheetMetaData() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const dmd=sh.getDeveloperMetadata();
  var html='Developer Metadata';
  dmd.forEach((d,i)=>{html+=Utilities.formatString('<br />Item: %s key: %s row: %s column: %s',i+1,d.getKey(),d.getLocation().getRow(),d.getLocation().getColumn())});//always comes back with zero length
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'Metadata');
}

So this may be working because I'm not actually creating any metadata but I'm not understanding why. I must be missing something important.

In the youtube video 'Totally UnScripted' about DeveloperMetadata the speaker had access to a library and with that library he was able to create the DMD object himself by hand. It would be nice to know if that library is available.

I just realized that library is written by Bruce McPherson so probably that library is on his website.

With Tanaike's help I'm finding the developer metadata now and the two functions that create rows and columns using the active range are working okay.

function getMetadataUsingFinder() {
  const ss=SpreadsheetApp.getActive()
  const sh=ss.getSheetByName("Sheet1");
  const f=sh.createDeveloperMetadataFinder().find();
  let html='';
  f.forEach(d=>{html+=Utilities.formatString('<br  />key: %s row: %s',d.getKey(), d.getLocation().getLocationType())});
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html), 'MetaData');
}

Here's the final script for setting up my working example for learning about how to use Developer Metadata:

function setupMetaData() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const lr=sh.getLastRow();
  for(let r=2;r<=lr;r++) {
    let rg=sh.getRange(`${r}:${r}`);
    let lbl=Utilities.formatString('omer%s',r);
    rg.addDeveloperMetadata(lbl,SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT);
  }
  const lc=sh.getLastColumn();
  const colA=["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];
  for(let c=1;c<lc;c++) {
    let rg=sh.getRange(`${colA[c]}:${colA[c]}`);
    let lbl=Utilities.formatString('omec%s',c);
    rg.addDeveloperMetadata(lbl,SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT);
  }
  getMetadataUsingFinder();
}

Answer 1

In your 1st script, as a method, how about the following modification using A1Notation?

From:

let rg=sh.getRange(r,1,1,sh.getMaxColumns());

To:

let rg=sh.getRange(`${r}:${r}`);

• For example, the A1Notation of 2:2 is used, the row 2 is retrieved.

Note:

• In this case, when you want to retrieve the developer metadata, you can also use the following sample script. This is from this thread.

    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)
  

Reference:

• getRange(a1Notation)