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?

Answer 1

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.