9

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?

Eugene Lisitsky
  • 12,113
  • 5
  • 38
  • 59
David Apltauer
  • 963
  • 2
  • 11
  • 23
  • 4
    Possible duplicate of [Programatically build a hyperlink to a named range in Sheets](https://stackoverflow.com/questions/42536797/programatically-build-a-hyperlink-to-a-named-range-in-sheets) – Rubén Nov 27 '17 at 17:47

4 Answers4

12

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.

Brian
  • 4,274
  • 2
  • 27
  • 55
  • I'd got this ERROR! `("#gid=1975816060&range=A1:A10", "Click to jump to Sheet 2")` – Trimax Jul 06 '18 at 09:39
  • 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) https://docs.google.com/spreadsheets/d//edit#rangeid=1881154819 – Rathan Jul 23 '20 at 09:26
  • @RathanKumar You could try using the `NamedRange` class in apps script: https://developers.google.com/apps-script/reference/document/named-range – Brian Aug 07 '20 at 19:14
0

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;   
}
Community
  • 1
  • 1
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
0

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)
  
}
Nick
  • 1
0

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.

Pasha
  • 76
  • 2