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.