Google-sheets – Programmatically create an internal hyperlink

google sheetsgoogle-apps-scriptlinks

I need to programmatically create a large number of links in a Google Sheets that point to a specific range in another sheet of the same document. I tried to use named ranges for that but for the HYPERLINK formula to work I would need somehow to transform them into rangeids. For example, this should work:

=HYPERLINK("#rangeid=583860962","Sales")

Unfortunately, I cannot find a way to access those ids. I am trying to do something like this:

function namedRangeId(root, name) {
  var range = root.getRangeByName(name);
  // THIS METHOD DOESN'T EXIST
  return range.getId();      
}

function namedRangeLink(root, name) {
  var id = namedRangeId(root, name);
  var link = '=HYPERLINK("#rangeid=' + id + '", \"link\")';
  return link
}


function run(){
  var root = SpreadsheetApp.getActiveSpreadsheet();
  var range = root.getRange("A1:A20");
  var range2 = root.getRange("B1:B20");

  var links = range.getValues().map(function(x) { return [namedRangeLink(root, x)]; });
  range2.setFormulas(links);
}

Is there a way to solve this problem directly or some work around?

Best Answer

try getGridId()

I had the same situation. A function listed what I needed by using getGridID(). I then created the formula to create the hyperlink.

Name    gid       Go To Link        
Notes   2030470863  Notes   =HYPERLINK("#gid=B5",A2))