如何使用脚本在Google表格中生成范围ID超链接#rangeID = XXXXXXXXXX? [英] How to generate range ID hyperlink, #rangeID=XXXXXXXXXX, in Google Sheets using Scripts?

问题描述

最近，Google添加了该功能，您可以在其中链接到特定的单元格，这很棒.为此，您可以在特定单元格上使用插入链接"功能，然后从下拉菜单中单击选择要链接的单元格范围"，然后在其中选择所需的单元格或范围链接到.为此，Google会生成一个非常方便(且动态)的十位数的范围ID".

So recently, Google added the feature where you can hyperlink to a specific cell, which is great. To do so, you use the "Insert link" feature on a specific cell, and then from the dropdown menu, you can click "Select a range of cells to link" where you then choose the cell or range in which you'd like to link to. In doing so, Google generates a very handy (and dynamic) ten-digit "Range ID".

它看起来像这样:=HYPERLINK("#rangeid=1234567890","link")

不过，我不知道要如何使用脚本来创建它.

What I can't figure out, though, is how to create this using a script.

对于我来说，这个功能不存在似乎很奇怪，因为它可以手动完成.

It seems strange to me that this function wouldn't exist, since it's possible to do manually.

我可以使用以下代码来生成工作表ID，该ID在创建在页面之间跳转的超链接时非常方便:

I can use the following code to generate a sheet ID, which comes in very handy when creating hyperlinks to jump between pages:

var ss = SpreadsheetApp.getActive(); 
var sheet1 = ss.getSheetByName(name); 
var gid = sheet.getSheetId();
sheet2.getRange("A1").setFormula('=hyperlink("#gid='+gid+'","'name'")');

我可以使用以下代码创建指向特定单元格的链接，但是它不是动态的，一旦将行/列插入到工作表中就会中断:

And I can use the following code to create a link to a specific cell, but it's not dynamic and breaks once rows/columns are inserted into the sheet:

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var range = ss.getRangeByName("A10");
var sheetID = range.getSheet().getSheetId();
var rangeCode = range.getA1Notation();
sheet2.getRange("J10").setFormula('=hyperlink("https://docs.google.com/spreadsheets/d/'+ss.getId()+'/edit#gid='+sheetID+'&range='+rangeCode+'","link")');

必须有一种方法可以执行以下代码，但目前我找不到方法:

There has to be a way to do something like this following code, but I can't find a way, currently:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = ss.getRangeByName("A10");
var rangeID = range.**getRangeID()**;
sheet2.getRange("J10").setFormula('=hyperlink("#rangeID='+rangeID+'","link")');

我知道getRangeID()当前在Google Apps脚本的可用选项中不存在；但是，有什么功能吗?如果存在此命令，将非常有用.

I know that getRangeID() doesn't currently exist in the available options for Google Apps Script; however, is there something that does this function? It would be incredibly helpful if this command existed.

有什么帮助吗?还有其他方法可以实现我所缺少的吗?

Any help out there? Is there another way to accomplish this that I'm missing?

感谢您的帮助！

推荐答案

您可以使用相对网址.链接文档中的答案可以在以下位置找到 https://webapps.stackexchange.com/questions/44473/link-to-a-cell-in-a-google-sheets-via-url .

You can use a relative url. The answer in a linked document can be found here https://webapps.stackexchange.com/questions/44473/link-to-a-cell-in-a-google-sheets-via-url.

=hyperlink("#gid=0&range=A2",Bookmarks!A2)

我认为#gid = 0代表第一张纸.然后，我可以在工作表中指定范围.我无法使命名工作表正常工作.除了第一个工作表/标签外，其他所有标签都有一个#gid，您可以在该工作表的网址中找到它.

I think #gid=0 represents the first sheet. Then I can specify the range in the sheet. I was unable to get named sheets working. Besides the first sheet/tab the others all have a #gid which you can find in the url of that sheet.

=hyperlink("#gid=1756281106&range=A2",Bookmarks!A2)

这篇关于如何使用脚本在Google表格中生成范围ID超链接#rangeID = XXXXXXXXXX?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！