A Guide to Generating a Table of Contents in Google Sheets

Have you ever found yourself struggling to navigate through a Google Sheet with numerous tabs? Creating a table of contents manually for each sheet can be a tedious task. But fear not, in this guide we will learn how to dynamically generate a new table of contents using a script. So, let’s get started!

Getting the Sheet Names

First, we need to use the script editor. If you’re not familiar with it, check out our guide on checkbox automation before continuing. Otherwise, simply open the editor by going to “Tools” and selecting “Script Editor”.

Replace the dummy function with a new function, let’s call it “generateTableOfContents()”. For most scripts you create for a Google Sheet, you’ll need to declare the spreadsheet you’re working with. Since the script editor is based on Google Apps Script, we can use all the built-in libraries. Here’s what your code should look like:

function generateTableOfContents() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
}

To get the sheets in the spreadsheet, we’ll use a function called “getSheets()”. Once we have that, we’ll need to iterate through each sheet to get the sheet name. We also want to store these names somewhere, so let’s create an empty array called “sheet_names”.

function generateTableOfContents() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var sheet_names = [];
  for (i=0; i<sheets.length; i++){
    sheet_names.push([sheets[i].getName()]);
  }
}

In this code, we’re using a for loop to iterate through the sheet objects we obtained from the “getSheets()” function. You don’t have to use a for loop, it’s just my preferred method when writing new code from scratch. Feel free to use your preferred loop method here, as there won’t be much difference between the different types.

Now that you have collected the names of your sheets, it’s time to create the hyperlinks you’ll need for your table of contents. To check what your sheet names are, place a “console.log(sheet_names)” right after the closing brace of the “for” loop, “}” and run it.

Creating the Hyperlinks

To create the links that will take your user to each specific sheet/tab, we’ll generate a “=HYPERLINK()” formula and populate it with the URL and the text/label to display. Each Google sheet URL starts with “docs.google.com/spreadsheets/d/”, followed by the spreadsheet ID, and then “/edit”. If you have multiple tabs, you’ll notice there’s also a “gid” attached at the end of the URL, which is your sheet ID. Just like we obtained the sheet names earlier, we’ll do the same to get the sheet IDs.

The first thing we need to do is get the ID of your spreadsheet. The simplest method is to copy the URL and extract it from there. Since we’re writing the code to do it, let’s make it more easily reusable for your other spreadsheets. To do this, we’ll use the “getId()” function, available at the spreadsheet level. Create a new variable called “url” and assign it the typical URL of a Google sheet.

var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/edit'

Place this variable right after where we used “getSheets()” and above where we looped through the sheets. We’ll use this “url” variable in our loop so that we can add the hyperlink to the array that held our sheet names.

Once you’ve set up your sheet URL, go to your array and add a new variable called “sheet_url”. We’ll use the URL and concatenate it with the parameter “#gid” (which helps identify a specific sheet), and extract the sheet ID using the “getSheetId()” function, available at the spreadsheet level. Your code should look like this afterwards:

function generateTableOfContents() {
  ...
  var sheets = spreadsheet.getSheets();
  var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/edit'
  var sheet_names = []
  for (i=0; i<sheets.length; i++){
    var sheet_url = url + '#gid=' + sheets[i].getSheetId();
    sheet_names.push([sheets[i].getName()]);
  }
}

Now that you’ve set up the sheet URL, it’s time to associate it with the “=HYPERLINK()” formula. This formula takes into account a URL and a label. When doing it in code, make sure to use the appropriate quotes. Google Sheets formulas require double quotes, so you’ll need to use single quotes for all the string values in your actual code. Your “hyperlink” variable should look like this:

var hyperlink = '=HYPERLINK("' + sheet_url + '", "Go to tab")'

While we’re at it, let’s clean up our code a bit by declaring a variable for “sheet_name” and using it in our “Array.push()”. Your loop should look like this afterwards:

...
for (i=0; i<sheets.length; i++){
  var sheet_name = sheets[i].getName();
  var sheet_url = url + '#gid=' + sheets[i].getSheetId();
  var hyperlink = '=HYPERLINK("' + sheet_url + '", "Go to tab")'
  sheet_names.push([sheet_name, hyperlink]);
}
...

This will make the “sheet_names” array into a nested array where each position contains an array inside. The data set will essentially look like this once it has been executed:

[
  ["sheet name 1", "hyperlink 1"],
  ["sheet name 2", "hyperlink 2"],
  ["sheet name 3", "hyperlink 3"]
]

This takes care of the hyperlinks. You now have the sheet names and links for each sheet you need for the table of contents. The next step is to insert them into your TOC sheet/tab.

Updating Your TOC Sheet

To insert data into a spreadsheet, we first need to determine where to place the data. If you don’t already have a TOC sheet, create one. We’ll place our newly generated sheet names and links in cell A1 of this sheet/tab.

Let’s go back to the script editor, we’ll point the code to this sheet using the “getSheetByName()” function.

var destination_sheet = spreadsheet.getSheetByName("TOC")

Next, we’ll need to point the code to cell A1, which we can do with the “getRange()” function. In this function, we’ll pass 4 parameters – the start of the row, the start of the column, the number of rows, and the number of columns. Since we want to start at A1, we’ll set the first two parameters to 1. The number of rows will depend on how much data we have in our “sheet_names” array, and the number of columns will be the number of values we have in the nested array (which should be 2 – one for the sheet name and another for the hyperlink).

var destination_range = destination_sheet.getRange(1, 1, sheet_names.length, sheet_names[0].length)

Notice that for the number of columns, we’re saying “sheet_names[0].length” – this essentially means we’re just looking at one of the positions of the “sheet_names” array, the first position in this case (since arrays start at 0), and counting the number of values in that part. The previous visualization is a great representation of what this looks like and how it works.

To insert what we’ve generated for “sheet_names”, we just need to call “setValues()” on “destination_range”. Like this:

destination_range.setValues(sheet_names);

You’ll want to place all this new code at the end of our “generateTableOfContents()” function, after the loop. Once you’ve put it together, it should look like this:

function generateTableOfContents() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = spreadsheet.getSheets();
  var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/edit'
  var sheet_names = []
  for (i=0; i<sheets.length; i++){
    var sheet_name = sheets[i].getName();
    var sheet_url = url + '#gid=' + sheets[i].getSheetId();
    var hyperlink = '=HYPERLINK("' + sheet_url + '", "Go to tab")'
    sheet_names.push([sheet_name, hyperlink]);
  }
  var destination_sheet = spreadsheet.getSheetByName("TOC")
  var destination_range = destination_sheet.getRange(1, 1, sheet_names.length, sheet_names[0].length)
  destination_range.setValues(sheet_names);
}

Generating the TOC with the Spreadsheet

Running it as a Macro

To use our function as a macro, we’ll need to import it into the toolbar. To do this, click on the “Tools” option in your toolbar, open the “Macros” option, then select “Import” from the list of options.

When the modal window opens, you’ll see your function as an option. Click the “Add function” button to import it. You’ll see the button change to a checkmark when it’s finished.

Close the modal window, then open your macro list again — you’ll see the function we just added as an option. Clicking on it will immediately run the code. You’ll know it’s running when a small alert banner pops up, saying “Running script” and it will update to say “Script completed” once it’s finished.

Assigning Images

A more interactive way to run the code is by inserting an image and assigning the function to it. This method is more user-friendly as you place the button on the sheet and allow people to click on it instead of going through the menu bar.

To insert an image, click on the “Insert” option in the toolbar, hover over the “Image” option, then select “Image over cells”. Make sure to select “Image over cells”. If you select “Image in cell”, you won’t be able to assign a script to it.

If you have an image in mind, select it. Otherwise, let’s just search for a refresh icon in a Google search. The image insertion modal window will offer you this option if you click on the “Google Search” tab.

Once you’ve inserted the image, click on it to select it. Find the 3 dots in the top right of the image. When you click on those dots, a menu should appear giving you the option to “Assign script”.

In the modal window that opens, simply enter the name of the function you want to run when someone clicks on that image. In our case, it will be “generateTableOfContents”.

Now that everything is set up, the script will run every time someone clicks on the image. To show the 3 dots again or to resize the image, right-click on the image and you’ll see the options.

After some size adjustments, positioning tweaks, and adding some instructional text, you can make it really intuitive for anyone. I placed my button next to the list and added instructions to its right. Here’s what mine looks like:

Now you have an easy way to generate and update a table of contents for any spreadsheet you want. All you need is your “generateTableOfContents()” code and a TOC sheet/tab set up in the spreadsheet you want to add it to. In our next guide on the table of contents, we’ll dive into fully automating the experience so that no manual clicks are required to keep it up to date.

Find more technical guides and tips at Crawlan.com.