CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptnamed-ranges
Peter Russell
Peter Russell

Reputation: 243

Programmatically build a hyperlink to a named range in Sheets

I have a spreadsheet with lots of named ranges, and I'd like to have a sort of table of contents which provides hyperlinks to jump to them.

In the UI I can create a hyperlink to a named range, which ends up with the format:

https://docs.google.com/spreadsheets/d/xxxxx/edit#rangeid=yyyyy

Where xxxx is a long spreadsheet id, and yyyy is a series of digits.

Since I have an awful lot of these, I'd like to use Google Apps Script to generate all of these links programatically. I can find the named range objects using Spreadsheet.getRangeByName, but I can't find a way to get a rangeid from this.

Upvotes: 4

Views: 3414

Answers (3)

user555121
user555121

Reputation:

You can get id of named range using Advanced Google service: Google Sheets API. Turn it on at Resources - Advanced Google services...

Then use spreadsheets.get to get your Google Sheet data including named ranges info:

var spreadsheetId = '...';

var ssData = Sheets.Spreadsheets.get(spreadsheetId);
var namedRanges = ssData.namedRanges;

Logger.log(namedRanges);

Result:

enter image description here

Note: namedRangeId returned by API is obfuscated (?) and you cannot use it directly to create link programmatically. For some reason it different from what seen in UI:

enter image description here

Upvotes: 2

bpgriner
bpgriner

Reputation: 391

var fullSpreadsheetLink = "full spreadsheet link goes here"
var spreadsheetTabName = "tab name goes here"

var spreadsheet = SpreadsheetApp.openByUrl(fullSpreadsheetLink);
var sheet = spreadsheet.getSheetByName(spreadsheetTabName);

var myCell = sheet.getRange("A1");
var linkCell = sheet.getRange("A2")

var dataSourceUrl = myCell.getDataSourceUrl();
var urlArray = dataSourceUrl.split("&");

linkCell.setValue("=HYPERLINK(\"#"+urlArray[3]+"&"+urlArray[4]+"\",\"link to A1\")");

Upvotes: -3

Peter Russell
Peter Russell

Reputation: 243

It doesn't appear that this is possible, but as a workaround, Karl_S suggested using a range link which does work:

function createNamedRangeUrl(name) {
  var root = SpreadsheetApp.getActiveSpreadsheet(); 
  var range = root.getRangeByName(name); 
  var sheetId = range.getSheet().getSheetId(); 
  var rangeCode = range.getA1Notation(); 
  return ("https://docs.google.com/spreadsheets/d/" + 
  root.getId() + "/edit#gid=" + sheetId + "&range=" + rangeCode); 
}

Upvotes: 2

Related Questions