4

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?

0

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))
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.