Add Table of Contents with hyperlinks to all sheets in workbook. Option to exclude Hidden sheets

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.

Sample Sheetnames List

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 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) {
} else {
throw “Option argument must be -1, 0, or 1”;
return sheetNames


function SHEETNAMES(option = 0, includeHidden = false) {
var sheetNames = new Array();
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
var sheetUrlPrefix = “” + 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;

1 Like

Hey, Thanks for this! It worked great for me. One issue I had with the script was that any double quotes needed to be turned into single quotes. (syntax error for double quotes).
Other than that, I love it, especially that I can exclude hidden tabs!

Glad it worked for you but I can’t explain the syntax error.

I just compared the code I’m actually using to the code in the post and their identical – including the use of double quotes in two places.

Which lines did you have to change? I’d like to change the post if you found errors.


I’m very interested in trying this but do not know how to implement from your post. Can you provide a step-by-step on how to use your script? If it’s a copy & paste, where and how am I doing this. It looks great and would love to try.

I have been having ChatGPT help me with writing scripts and formulas too, it works amazingly well most of the time.
I can see where it would be nice to have a separate tab that served as a table of contents that you could format and sort as you wished.

But I did want to point out the built-in list of all google sheet tabs in case someone didn’t want to work with scripts or formulas. It is a bit unnoticeable. In the lower left-hand corner of the tabs bar at the bottom of any sheet just right of the plus sign are four parallel lines that you can click on to display a list of all tabs in the sheet.

The hidden tabs are low-lighted so you can tell them from the non-hidden tabs. Click on the name you want, and it takes you right to that tab.


That’s a good point, @fehegner

The script I posted is helpful when you have a lot of sheets / tabs. In my case, my Tiller sheet has about 25 tabs.

It’s not worth the time or effort if all of the tabs in the workbook are visible along the bottom of the workbook.


The code that I originally posted is a script that creates a custom function called “SHEETNAMES” that works like the functions built into Google Sheets (e.g. SUM, PMT, VLOOKUP, etc.) To use it, you would enter one of the formulas I listed in the original post into a blank cell. (e.g. =SHEETNAMES(0,true)

Try this and let me know if it works for you.

First, create a new tab in your Tiller workbook. Name it TOC for now. You can change it later to anything you want.

To create a new Apps Script, go to Extensions > Apps Script.

Extensions Drop Down

This will open a new tab with a window that will look something like this.

Yours probably won’t look exactly like this because Tiller already has some scripts there. Don’t do anything to them.

Create a new script by clicking on the big + sign to the right of FILES.

Replace the default text with the Modified Script code in the original post.

Save or click on the floppy disk icon next to the word, “Run”

Test that it works:

  • Return to the TOC tab in your Tiller workbook.

  • Enter this into a blank cell with plenty of blank cells below: =SHEETNAMES(0,true)

  • Say a short prayer.

With any luck, a list of all the tabs in your workbook will appear. The list should have two columns. One will show the title of the tab. The other will show the internal link to each tab. I make this column very narrow just for appearances. Clicking on the link will take you to the corresponding tab.

Check out the other versions of the formula using the table of examples I gave in the original post.

Good luck.

It was line 5 and 26 (counting blank lines too)
Once a changed the double quote to single, it ran.
I did do a copy and paste from your post. Maybe the quotes didn’t paste properly?

1 Like

It’s a mystery. I just looked at my script and the double quotes are there and it works.

FYI, with the help of ChatGPT, and inspired by Ben Collins, I approached this a little differently.

The new approach creates a menu “Sidebar Menu” that has two dropdown choices: “Show Sidebar” and “Refresh Sidebar”. Choosing “Show Sidebar” opens a sidebar (similar to Tiller Money) that displays a list of hyperlinks to all tabs in the workbook. The sidebar stays open unless you close it.

I think this is better than the first approach because you don’t have to go back to the TOC tab to navigate. Going back to the TOC tab kind of defeats the purpose if you have a lot of tabs.

Let me know if you’re interested.

Also, check out Ben Collins’s article on this. How To Add A Hyperlinked Index Sheet In Google Sheets -

Scroll down until you see a section heading, “Create a hyperlinked index list in a sidebar.”

His code didn’t work exactly as I wanted but I used it as a start and with ChatGPT. He’s a great resource if you don’t already know about him.

I’ll check it out. And yes, Ben Collins has taught me a lot about how sheets works.
I would be interested in a sidebar TOC, since it is awkward to have to go back to it!

Also Tiller doesn’t use any script in this area. They use their add-on. By default this should be blank.


Can you please clarify? Is the script I described going to cause a problem with Tiller?

Thank you.

I’m getting
SyntaxError: Invalid or unexpected
token line: 26 file:
for this line:
var sheetUrlPrefix = “ ” + spreadsheetId + “/edit#gid=”;

Is it possible it is included with the smart quotes (as displayed in the community) rather than dumb quotes?

Or is there really a carriage return after the first static string?


@jemmoa7 reported the same problem. See below. He replaced the double quotes with single quotes and got it to work. I’ve looked at the script that I use and it does have double quotes so I can’t explain the problem.

I’ll update the code in the original post to replace the double quotes with single quotes. Hopefully that will resolve the problem.

FROM @jemmoa7

Hey, Thanks for this! It worked great for me. One issue I had with the script was that any double quotes needed to be turned into single quotes. (syntax error for double quotes).
Other than that, I love it, especially that I can exclude hidden tabs!

Single quotes fixed it
var sheetUrlPrefix = ‘ 2’ + spreadsheetId + ‘/edit#gid=’;

Here is an inelegant solution which works:
The index tab looks like this:

All other tabs must have a dedicated cell in which to put a link to the Index tab.
To make this work,
IN LINE 5 of your modified script, make sure here are no spaces or charcters between the link and it’s closing quote:
var sheetUrlPrefix = ‘’ + spreadsheetId + ‘/edit#gid=’ ;
in LINE 10, add +‘&range=A1’ before the closing ] and )
sheetNames.push([sheets[i].getName(), sheetUrlPrefix + sheets[i].getSheetId() +‘&range=A1’ ]);

In a spreadsheet, on the INDEX tab

  1. populate a column to create hyperlinks of =SHEETNAMES() column B result
  2. create a link to cell A1 (right click \ view more cell actions \ get link to this cell)
    Then you can hide Column B

On all other tabs, paste the link to Index!A1 in each tab’s cell A1

Less than desirable features of this solution are

  1. needing a dedicated row and cell in each tab for the Index link
  2. Need to run the AppsScript whenever a new tab needs indexing.

Ultimately, I think a sidebar with clickable links is the way to go, but I have to learn a whole lot more coding. !! :slight_smile: )

1 Like

I used ChatGPT to build me a script that would open the sidebar, list my unhidden sheets and provided a radio button for each one. You click on a radio button and it goes to that sheet. WOW!
Here’s the code if anybody is interested: (check the quotes, they may be wonky!)

function showSheetsInSidebar() {
  var html = "<style>" +
             "table, th, td { border: 1px solid black; padding: 5px; }" +
             "</style>" +
             "<table><tr><th>Select</th><th>Sheet Name</th></tr>";
  var sheets = SpreadsheetApp.getActive().getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (!sheets[i].isSheetHidden()) {
      html += "<tr><td><input type='radio' name='sheet' value='" + sheets[i].getName() + "' onclick=''></td><td>" + sheets[i].getName() + "</td></tr>";
  html += "</table>";
  var ui = HtmlService.createHtmlOutput(html)
      .setTitle("TABLE OF CONTENTS")

function goToSheet(sheetName) {
  var sheets = SpreadsheetApp.getActive().getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() == sheetName) {


jemmoa7 |

  • |

Can’t wait to give this a try. It’s a great improvement.

FYI … I signed up for the ChatGPT Plus account today. Not sure if I’ll keep it but it’s giving me more than $20 of entertainment right now.

As promised, there’s no waiting and it’s fast.

It was a very short question. I just kept it simple and to the point. What’s more, you can ask it to modify the script, like in, "can you show only sheets not hidden? I love how it tells you how this works.
I would definitely pay $20 per month for ChatGPT. It is absolutely amazing. I think this technology is going to be a real game changer for everything.