Coordinates of an external named reference

Question

In a spreadsheet, I've got a column named range: Sheet1!I:I, named NamedRanged1.

In another sheet, I'd like to get the index of that named range, i.e., 9 (I is 9th column).

I tried with ADDRESS and IMPORTRANGE:

=ADDRESS(IMPORTRANGE("1TBLF4H6r7EpCBtbTaa9Yan986DnyC_7UhDIWAG5gQX8","Sheet1!A1:Z100"))

but without success.

Is there a function which can retrieve coordinates from the name of a named reference?

Answer 1

The current version of Google Sheets doesn't have a built-in function to get the address of a named range from another spreadsheet but you could build a script by using Google Apps Script / google-apps-script.

The following script will add the address of a named range from an external spreadsheet to the active cell

function demo() {
  var url = 'https://docs.google.com/spreadsheets/d/1TBLF4H6r7EpCBtbTaa9Yan986DnyC_7UhDIWAG5gQX8/edit#gid=0';
  var name = 'NamedRange1';
  NAMEDRANGEADDRESS(url,name);
}

function NAMEDRANGEADDRESS(url,name) {
    var ss = SpreadsheetApp.openByUrl(url);
    var rng = ss.getRangeByName(name);
    var address = rng.getA1Notation();
    SpreadsheetApp.getActiveRange().setValue(address);
}

For detailed instructions about how to create your first script checkout https://developers.google.com/apps-script/overview.