I was going to write a script to create a Table of Contents tab in my Tiller workbook because I’m getting a lot of tabs – some hidden and some visible. I was surprised to find a script named “CreateListOfSheets” already in the Apps Script Editor. I assume it comes automatically with Tiller Money because I don’t remember adding it.
The script creates a custom function ‘Sheetnames’ that creates a list of sheet names in the workbook. To try this, create a new tab and enter this formula in any cell, =Sheetnames(0) in any cell of the new tab.
It creates a list of all sheets, including hidden and the sheet names are not hyperlinked to their respective tabs.
With the help of ChatGPT, I modified the script to add hyperlinks to each sheet name and it adds a parameter to the “SheetNames” function that will display only sheets that are Not Hidden.
The code for the Original and Modified scripts are included below.
This is a partial view of the results of the Modified script in a tab “TOC” (Table of Contents) in my Tiller workbook. The tab name can be anything. I used TOC because it’s short. The formula that creates the list of tab names is in cell B4 of the TOC tab but it could be in any cell in a new sheet. Arrange the new tab to the far left of the tab list shown at the bottom of your workbook.
The table below shows the various parameter options and the results.
I installed the modified script below the Original Script and commented out all lines in the Original script using // in front of each line.
This is the ORIGINAL SCRIPT
/**
- This function will return a list of sheet names from the current workbook.
- @param {number} option Argument must be -1, 0, or 1.
- @return A list of sheet names from the current workbook.
-
@customfunction
*/
The code below is the original code that returns a list of sheet names but not links and it also includes sheets that are hidden.
function SHEETNAMES(option = 0) {
var sheetNames = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var currSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
if (option === 0) {
for (var i = 0; i < sheets.length; i++) sheetNames.push([sheets[i].getName()]);
} else if (option === -1) {
for (var i = 0; i < sheets.length; i++) if (currSheet != sheets[i].getName()) { sheetNames.push([sheets[i].getName()]) };
} else if (option === 1) {
sheetNames.push(currSheet);
} else {
throw “Option argument must be -1, 0, or 1”;
}
return sheetNames
This is the MODIFIED SCRIPT
function SHEETNAMES(option = 0, includeHidden = false) {
var sheetNames = new Array();
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
var sheetUrlPrefix = “https://docs.google.com/spreadsheets/d/” + spreadsheetId + “/edit#gid=”;
if (option === 0) {
for (var i = 0; i < sheets.length; i++) {
if (includeHidden || !sheets[i].isSheetHidden()) {
sheetNames.push([sheets[i].getName(), sheetUrlPrefix + sheets[i].getSheetId()]);
}
}
} else if (option === -1) {
var currSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
for (var i = 0; i < sheets.length; i++) {
if (currSheet != sheets[i].getName() && (includeHidden || !sheets[i].isSheetHidden())) {
sheetNames.push([sheets[i].getName(), sheetUrlPrefix + sheets[i].getSheetId()]);
}
}
} else if (option === 1) {
var currSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (includeHidden || !currSheet.isSheetHidden()) {
sheetNames.push([currSheet.getName(), sheetUrlPrefix + currSheet.getSheetId()]);
}
} else {
throw “Option argument must be -1, 0, or 1”;
}
return sheetNames;
}
)