问题描述
限时送ChatGPT账号..对于包含多个工作表的 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屋!
更多推荐
[db:关键词]
发布评论