是否有自定义函数或脚本返回 Google Sheets 中特定工作表的 gid? [英] Is there a custom function or script that returns gid of a specific sheet in Google Sheets?

问题描述

我一直在互联网上的浩瀚知识海洋中钻研，以获取我需要的东西，但无济于事.

I have been poring through the vast sea of knowledge on the Internet to get what I need but to no avail.

背景

在一个 Google Sheets 文件中，我有一个主表和以人的姓氏命名的其他各种表.

In a single Google Sheets file, I have one master sheet and various other sheets named after the last names of people.

在主表中，我创建了一个单元格，我可以在其中键入任何人的姓名.我想查找多个人的工作表，因此我的搜索是动态的.无论如何，让我们称这个单元为搜索单元.

In the master sheet, I created a cell in which I can type the name of any person. I would like to look up sheets for multiple individuals, so my search is dynamic. Anyway, let's call this cell the SEARCH CELL.

在 SEARCH CELL 旁边，我有另一个单元格，我想在其中添加一个超链接到另一个工作表，该工作表带有在 SEARCH CELL 中输入的姓氏.让我们将此带有超链接的单元格称为超链接单元格.因此，当我单击此单元格中的超链接时，我想跳转到与在 SEARCH CELL 中输入的内容相匹配的个人姓氏的工作表.

Right next to the SEARCH CELL, I have another cell in which I would like to add a hyperlink to another sheet which bears the last name entered in the SEARCH CELL. Let's call this cell with the hyperlink the HYPERLINK CELL. So, when I click on the hyperlink in this cell, I would like to jump to the sheet with the last name of the individual that matches what had been entered in the SEARCH CELL.

我需要的帮助是这个 HYPERLINK CELL.

The help I need is with this HYPERLINK CELL.

=hyperlink("#gid=               ")

如何提取每张表的gid并输入上面代码中的空白处?

How do I extract the gid of each sheet and enter in the blank space in the code above?

我被告知没有 Google Sheets 功能，因此需要自定义函数或脚本.任何人都可以提供一个在脚本编辑器中输入时有效的(即运行没有错误)?

I am told that there is no Google Sheets function for this, so a custom function or script is needed. Can anyone please provide one that works (i.e. runs without errors) when it is entered in the Script Editor?

推荐答案

脚本:

function SHEETLIST() {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1 ) ;
  out[0] = [ "NAME" , "#GID" ];
  for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = 
  [sheets[i-1].getName() , sheets[i-1].getSheetId() ];
  return out
}
catch( err ) {
  return "#ERROR!" }}

公式:

=SHEETLIST()

将工作表名称转换为活动超链接:

turn sheet names into active hyperlinks:

=ARRAYFORMULA(HYPERLINK("#gid="&
 QUERY(INDEX(SHEETLIST();;2); "offset 1"); 
 QUERY(INDEX(SHEETLIST();;1); "offset 1")))

如果你想把它绑定到手动输入，你可以使用 VLOOKUP 像

and if you want to bind it to manual input you can use VLOOKUP like

=ARRAYFORMULA(IFNA(VLOOKUP(A1, HYPERLINK("#gid="&
 QUERY(INDEX(SHEETLIST();;2); "offset 1"); 
 QUERY(INDEX(SHEETLIST();;1); "offset 1")); 1; 0)))

其中 A1 是您的搜索单元格"

where A1 is your "search cell"

这篇关于是否有自定义函数或脚本返回 Google Sheets 中特定工作表的 gid?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！