G Sheets 目录脚本 [英] G Sheets Table of Contents Script

问题描述

对于包含多个工作表的 Google 工作表工作簿，我创建了一个目录工作表，其中列出了工作簿中的所有工作表，以提高用户的易用性.我一直在寻找可以加速该过程的附加组件、宏或脚本.没有骰子.关于如何自动创建列出所有其他工作表名称(每个单元格一个工作表名称)的新工作表的过程，然后自动将单元格链接到该工作表的任何想法?

For Google sheet workbooks that have several sheets, I create a Table of Contents sheet that lists all of the sheets in the workbook to increase ease of use for users. I have looked for an add-on, macro, or script that can speed up the process. No dice. Any ideas for how to automate the process of creating a new sheet that lists the names of all of the other sheets (One sheet name per cell) and then automatically links the cell to that sheet?

推荐答案

脚本:

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"

这篇关于G Sheets 目录脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！