How to create hyperlink to range in Google Sheets App Script?

Question

I'm trying to fill cells with hyperlinks to ranges in Google Sheets app script with the same desired outcome I would get had I done it in GUI. I managed to create hyperlinks to sheet in the form of "gid=..." with the ... being a sheetID. But I struggle to get the rangeID that is used when generating the hyperlink in GUI e.g.

HYPERLINK("#rangeid=1420762593";"'List 4'!F2:F15") 

Is it possible to create hyperlinks to ranges in app script?

Solution

Yes, you can do this in App Script. Here's a very simple implementation where the HYPERLINK function is built and appended to a cell:

function hyperlinkRange() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Sheet1");
  var sheet2 = ss.getSheetByName("Sheet2").getSheetId();

  sheet1.getRange("A1").setValue('=hyperlink("#gid='+sheet2+'&range='+sheet1.getRange('A1:A10').getA1Notation()+'", "Click to jump to Sheet 2")');
}

You can combine this with loops to set a value of links across multiple sheets.

Comments

[Trimax]

I'd got this ERROR! ("#gid=1975816060&range=A1:A10", "Click to jump to Sheet 2")

[Rathan]

Is there a way to generate rangeid programatically instead of range? The issue I'm facing with range is that the cell points to different cell if we add rows in between. Any programatic way to generate Range ID. On GUI you can get range id by adding link to a cell which points to different cell (shortcut CMD+K) docs.google.com/spreadsheets/d/<id>/…

[Brian]

@RathanKumar You could try using the NamedRange class in apps script: developers.google.com/apps-script/reference/document/…