Programmatically build a hyperlink to a named range in Sheets

Question

I have a spreadsheet with lots of named ranges, and I'd like to have a sort of table of contents which provides hyperlinks to jump to them.

In the UI I can create a hyperlink to a named range, which ends up with the format:

https://docs.google.com/spreadsheets/d/xxxxx/edit#rangeid=yyyyy

Where xxxx is a long spreadsheet id, and yyyy is a series of digits.

Since I have an awful lot of these, I'd like to use Google Apps Script to generate all of these links programatically. I can find the named range objects using Spreadsheet.getRangeByName, but I can't find a way to get a rangeid from this.

See the NamedRange class documentation where you will find getId() — Karl_S
– Karl_S, Commented Mar 1, 2017 at 16:27
Sadly, that document is for Google Docs, not Google Sheets. I've hit it a few times this afternoon! — Peter Russell
– Peter Russell, Commented Mar 1, 2017 at 16:31
I found named ranges in the external Google Sheets REST API - and tried it in the API explorer but sadly the namedRangeIds returned don't seem to work in this context. — Peter Russell
– Peter Russell, Commented Mar 1, 2017 at 17:04
I ran a couple scripts looking to see if something was not documented and could not find anything. I show sections without using named ranges, but the actual range, so get the R1 notation of the range and generate the link as: docs.google.com/spreadsheets/d/xxxx/… where zzzz = the Sheet ID. Obtain it with var key = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getSheetId(); — Karl_S
– Karl_S, Commented Mar 1, 2017 at 17:10

Community · Accepted Answer · 2017-05-23 12:00:24Z

2

It doesn't appear that this is possible, but as a workaround, Karl_S suggested using a range link which does work:

function createNamedRangeUrl(name) {
  var root = SpreadsheetApp.getActiveSpreadsheet(); 
  var range = root.getRangeByName(name); 
  var sheetId = range.getSheet().getSheetId(); 
  var rangeCode = range.getA1Notation(); 
  return ("https://docs.google.com/spreadsheets/d/" + 
  root.getId() + "/edit#gid=" + sheetId + "&range=" + rangeCode); 
}

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered Mar 2, 2017 at 17:08

Peter Russell

2432 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vstepaniuk Over a year ago

If you change something on a spreadsheet (add/remove rows) this link (with direct address) will break unfortunately

Peter Russell Over a year ago

Yes, it's unfortunate. In my situation it didn't matter, because I was regenerating all the links on each run.

vstepaniuk Over a year ago

If you need not to navigate, but to submit a value to a given cell you can use this answer stackoverflow.com/a/77453492/3525368

user555121 · Accepted Answer · 2020-04-09 12:18:53Z

2

You can get id of named range using Advanced Google service: Google Sheets API. Turn it on at Resources - Advanced Google services...

Then use spreadsheets.get to get your Google Sheet data including named ranges info:

var spreadsheetId = '...';

var ssData = Sheets.Spreadsheets.get(spreadsheetId);
var namedRanges = ssData.namedRanges;

Logger.log(namedRanges);

Result:

Note: namedRangeId returned by API is obfuscated (?) and you cannot use it directly to create link programmatically. For some reason it different from what seen in UI:

answered Apr 9, 2020 at 12:18

user555121

1 Comment

Peter Russell Over a year ago

I presume these are the same ids returned by the REST API. As we both noticed, these sadly don't seem to work in links.

bpgriner · Accepted Answer · 2018-04-05 22:46:48Z

-3

var fullSpreadsheetLink = "full spreadsheet link goes here"
var spreadsheetTabName = "tab name goes here"

var spreadsheet = SpreadsheetApp.openByUrl(fullSpreadsheetLink);
var sheet = spreadsheet.getSheetByName(spreadsheetTabName);

var myCell = sheet.getRange("A1");
var linkCell = sheet.getRange("A2")

var dataSourceUrl = myCell.getDataSourceUrl();
var urlArray = dataSourceUrl.split("&");

linkCell.setValue("=HYPERLINK(\"#"+urlArray[3]+"&"+urlArray[4]+"\",\"link to A1\")");

answered Apr 5, 2018 at 22:46

bpgriner

3916 silver badges14 bronze badges

2 Comments

tehhowch Over a year ago

Code only answers with no commentary are not useful. Explain your code, your process, your solution, especially since this is a late answer.

lcrespilho Over a year ago

Range.getDataSourceUrl() returns a full link with &range=<a1notation> at the end. It's not a named range id.

Collectives™ on Stack Overflow

Programmatically build a hyperlink to a named range in Sheets

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related