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

user_2205451 0
Viewed 11041

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?

google-apps-script google-sheets hyperlink
4 Answers

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.

user_22784290

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;   
}
user_53724000

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)
  
}

user_135273600

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.

user_131762990
Related