How to generate range ID hyperlink, #rangeID=XXXXXXXXXX, in Google Sheets using Scripts?

Question

So recently, Google added the feature where you can hyperlink to a specific cell, which is great. To do so, you use the "Insert link" feature on a specific cell, and then from the dropdown menu, you can click "Select a range of cells to link" where you then choose the cell or range in which you'd like to link to. In doing so, Google generates a very handy (and dynamic) ten-digit "Range ID".

It looks something like this: =HYPERLINK("#rangeid=1234567890","link")

What I can't figure out, though, is how to create this using a script.

It seems strange to me that this function wouldn't exist, since it's possible to do manually.

I can use the following code to generate a sheet ID, which comes in very handy when creating hyperlinks to jump between pages:

var ss = SpreadsheetApp.getActive(); 
var sheet1 = ss.getSheetByName(name); 
var gid = sheet.getSheetId();
sheet2.getRange("A1").setFormula('=hyperlink("#gid='+gid+'","'name'")');

And I can use the following code to create a link to a specific cell, but it's not dynamic and breaks once rows/columns are inserted into the sheet:

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var range = ss.getRangeByName("A10");
var sheetID = range.getSheet().getSheetId();
var rangeCode = range.getA1Notation();
sheet2.getRange("J10").setFormula('=hyperlink("https://docs.google.com/spreadsheets/d/'+ss.getId()+'/edit#gid='+sheetID+'&range='+rangeCode+'","link")');

There has to be a way to do something like this following code, but I can't find a way, currently:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getRangeByName("A10");
var rangeID = range.**getRangeID()**;
sheet2.getRange("J10").setFormula('=hyperlink("#rangeID='+rangeID+'","link")');

I know that getRangeID() doesn't currently exist in the available options for Google Apps Script; however, is there something that does this function? It would be incredibly helpful if this command existed.

Any help out there? Is there another way to accomplish this that I'm missing?

Thanks for any help!

Ancient stuff, I know. But worth linking to a workaround answer: stackoverflow.com/a/46304000/6827197 Doesn't create a rangeID but works nonetheless. — a-burge
Thanks @a-burge. I'm not sure if you saw this in my original post, but the second string of script that I listed showed this same workaround. The issue I have with this is that once you insert a line into your spreadsheet, it breaks these hyperlinks. I'm looking for a more dynamic, set-it-and-forget-it method. — Gergazoid
I have created a feature request for this. issuetracker.google.com/issues/129841094 — Paul Law
Does this answer your question? Programmatically build a hyperlink to a named range in Sheets — Kos

lastlink lastlink · Accepted Answer · 2019-11-12T22:33:08

You can use a relative url. The answer in a linked document can be found here https://webapps.stackexchange.com/questions/44473/link-to-a-cell-in-a-google-sheets-via-url.

Example:

=hyperlink("#gid=0&range=A2",Bookmarks!A2)

I think #gid=0 represents the first sheet. Then I can specify the range in the sheet. I was unable to get named sheets working. Besides the first sheet/tab the others all have a #gid which you can find in the url of that sheet.

Ex:

=hyperlink("#gid=1756281106&range=A2",Bookmarks!A2)

How to generate range ID hyperlink, #rangeID=XXXXXXXXXX, in Google Sheets using Scripts?

2 Answers

Example:

Ex: