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屋!