以编程方式构建指向 Sheets 中命名范围的超链接 [英] 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

其中 xxxx 是一个长电子表格 ID，而 yyyy 是一系列数字.

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

由于我有很多这样的链接，我想使用 Google Apps Script 以编程方式生成所有这些链接.我可以使用 Spreadsheet.getRangeByName 找到命名范围对象，但我找不到从中获取 rangeid 的方法.

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.

推荐答案

这似乎不可能，但作为一种解决方法，Karl_S 建议使用有效的范围链接:

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); 
}

这篇关于以编程方式构建指向 Sheets 中命名范围的超链接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！