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

问题描述

我一直在浏览Internet上浩瀚的知识​​海，以获取所需的东西，但无济于事.

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

上下文

在一个Google表格文件中，我有一个母版工作表以及其他各种以人的姓氏命名的工作表.

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

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

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.

在搜索单元格旁边，我有另一个单元格，我想在其中添加一个超链接到另一个带有在搜索单元格中输入的姓氏的工作表.我们将此具有超链接的单元格称为HYPERLINK 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表格功能，因此需要自定义功能或脚本.在脚本编辑器中输入该代码后，任何人都可以提供可以运行的代码(即无错误运行)吗?

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表格中特定工作表的gid?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！