14

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!

Kos
  • 4,890
  • 9
  • 38
  • 42
Gergazoid
  • 161
  • 2
  • 4
  • Ancient stuff, I know. But worth linking to a workaround answer: https://stackoverflow.com/a/46304000/6827197 Doesn't create a rangeID but works nonetheless. – a-burge Jun 07 '18 at 15:54
  • 1
    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 Jun 11 '18 at 13:51
  • 2
    I have created a feature request for this. https://issuetracker.google.com/issues/129841094 – Paul Law Apr 17 '19 at 00:18
  • Does this answer your question? [Programmatically build a hyperlink to a named range in Sheets](https://stackoverflow.com/questions/42536797/programmatically-build-a-hyperlink-to-a-named-range-in-sheets) – Kos Apr 09 '20 at 12:01

2 Answers2

2

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)

lastlink
  • 1,505
  • 2
  • 19
  • 29
0

This is a perfect use case for named ranges, and it doesn't require any change to your script logic.

var range = ss.getRangeByName("NamedRange1");
Diego
  • 9,261
  • 2
  • 19
  • 33
  • Thanks for the quick response, Diego. – Gergazoid Apr 13 '18 at 18:08
  • Unfortunately, I run into the same problem with named ranges. I can write a script to create a named range, but I still can't find a way to create a hyperlink to that named range. Am I missing something? – Gergazoid Apr 13 '18 at 18:43
  • @Gergazoid I assume that's because you're generating the link and _later_ changing the position of the named range? If so, can you regenerate the link when there are edits like that? – Diego Apr 14 '18 at 16:57
  • Thanks @Diego. I'm just curious, how exactly would you advise on regenerating the link when edits are made (e.g., inserting rows/columns)? – Gergazoid Jun 11 '18 at 13:53
  • @Gergazoid Sorry for the delay... I would suggest using an [onEdit()](https://developers.google.com/apps-script/guides/triggers/#onedit), although you could also try [onChange()](https://developers.google.com/apps-script/guides/triggers/installable). – Diego Jun 15 '18 at 06:18