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.

Answer 2

Custom functions

Use in a formula.

Simple range:

=HYPERLINK(getLinkByRange("Sheet1","A1"), "Link to A1")

Named range:

=HYPERLINK(getLinkByNamedRange("NamedRange"), "Link to named range")

The code, insert into the script editor (Tools > Script Editor):

function getLinkByRange(sheetName, rangeA1, fileId)
{
  // file + sheet
  var file = getDafaultFile_(fileId);  
  var sheet = file.getSheetByName(sheetName);
  
  return getCombinedLink_(rangeA1, sheet.getSheetId(), fileId, file)    
}

function getLinkByNamedRange(name, fileId)
{
  // file + range + sheet
  var file = getDafaultFile_(fileId);    
  var range = file.getRangeByName(name);  
  var sheet = range.getSheet();
  
  return getCombinedLink_(range.getA1Notation(), sheet.getSheetId(), fileId, file)
      
}


function getDafaultFile_(fileId)
{
  // get file
  var file;
  if (fileId) { file = SpreadsheetApp.openById(fileId); }
  else file = SpreadsheetApp.getActive();      
  return file;      
}

function getCombinedLink_(rangeA1, sheetId, fileId, file)
{
  var externalPart = '';
  if (fileId) { externalPart = file.getUrl(); }    
  return externalPart + '#gid=' + sheetId + 'range=' + rangeA1;   
}

Answer 3

Here is another example. Hopefully, it is clean and self-explanatory

function hyperlinkRange(shDest,rgDest,shSrc,rgSrc,linkText) {
  
  // get the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  
  // get the destination sheet id
  var idDest = shDest.getSheetId()  
  
  // link the range
  var formula = '=hyperlink("#gid='+idDest+'&range='+rgDest+'","'+linkText+'")'
  shSrc.getRange(rgSrc).setValue(formula)
  
}

Answer 4

In case you want to create a link to another sheet which will open the sheet in the same browser tab here is what you want to do: 1. Get the id of the sheet. Check the link in your browser and you will see #gid=x where x is the sheet id 2. Then you want to set the formula (hyperlink) to the cell and make it show as a hyperlink SpreadsheetApp.getActiveSheet().getRange("A1").setFormula('=HYPERLINK("#gid=X","test")').setShowHyperlink(true); If you don't use setShowHyperlink(true) it will be shown as a regular text.