以编程方式构建超链接到表格中的命名范围 [英] Programmatically build a hyperlink to a named range in Sheets

问题描述

我有一个包含大量命名范围的电子表格，并且我想要一个可以跳转到其中的超链接的内容列表。

在用户界面我可以创建一个超链接到一个命名的范围，最终的格式为：

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

其中xxxx是一个长电子表格ID，yyyy是一系列数字。 / p>

由于我有很多这样的内容，因此我希望使用Google Apps脚本以编程方式生成所有这些链接。我可以使用 Spreadsheet.getRangeByName 查找命名的范围对象，但我无法找到一个方法来获取rangeid。

解决方案

看起来这不是可能的，但作为一种解决方法， Karl_S 建议使用可以工作的范围链接：

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

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.

解决方案

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

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