How to add a hyperlinked index sheet to your large Google Sheet workbooks

Admit it, we’ve all been there. Getting frustrated trying to find a specific sheet inside a huge workbook, and not being able to see it amongst all the other sheets.

Well, here’s a quick Apps Script to create a hyperlinked index page at the start of your workbook.

Quick caveat first: There’s one drawback – the hyperlinks open the sheets in new tabs, which unfortunately I can’t do anything about. However, it should still be useful for anyone working with workbooks with 10+ sheets.

Here it is in action:

Create hyperlinked index sheet

What if we already have a sheet by the name ‘Index’?

Well, you’ll be prompted to enter a different index name or to cancel the operation:

Create hyperlinked index sheet

Steps to create your own hyperlinked index sheet in Google Sheets

Step 1: Open up the workbook you want to add the index sheet to and open the apps script editor (Tools > Script editor...).

New to Apps Script? Start here.

Step 2: Clear out all of the myFunction boilerplate code and paste in the following:

Code is also here on GitHub.

Step 3: Change the “https://docs.google.com/spreadsheets/d/…YOUR SPREADSHEET ID HERE…./edit#gid=” to your spreadsheet url, up to the #gid= part.

Step 4: Hit Save and give your script project a new name: “Index”

Step 5: Select the onOpen function in the toolbar (1) and hit the run button (2):

Run onOpen function

Step 6: When prompted, grant the necessary permissions to apps script (one-time procedure):

Review permissions
Click Review permissions
Grant permissions
Click Allow

Step 7: Navigate back to your sheet and use the newly created Index Menu to run the program and create your hyperlinked index:

Create hyperlinked index sheet

Note: In some European countries (and possibly elsewhere) you use the semi-colon “;” in your formulas where we use the comma “,”. In this case, you’ll get an error with the hyperlink formulas in this example but it’s a super quick and easy fix. Simply change the “,” to a “;” on line 96, so it reads:

+ '";"'

Create a hyperlinked index list in a sidebar

Thanks to reader Clarence for this brilliant suggestion.

Instead of a new sheet at the front of your workbook, we can create an index list in a sidebar so that we can see from any tab of our Google Sheet. The code is a little more complex because we have the HTML sidebar which we need to pass the sheet names and IDs to.

In your worksheet script editor, you’ll want to have the following two files, one a GS file, the other an HTML file:

code.gs
sidebar.html

The code in the code.gs file is:

See this file on GitHub here.

Then the code in the sidebar.html file is:

See this file on GitHub here.

Here’s the Google Sheet if you want to make your own copy.

And this is the index sidebar script in action:

Index in sidebar apps script

Update February 2017: Google have just launched the ability to link to specific cells within your worksheet, so they open in the same browser tab! I’ll post an update soon to incorporate that functionality here.

21 thoughts on “How to add a hyperlinked index sheet to your large Google Sheet workbooks”

    1. Hi indigene,

      I’ve updated the code to include a function to refresh the existing index. You can re-copy the code above, re-run the onOpen function and then you should be able to update.

      Cheers,
      Ben

      1. I used it in combination with result I have in the different sheets. For example I have a sum always in C1 in every sheet. Now I can use the index and then use the indirect formula to get the Sum from every single sheet. Works awesome for large workbooks

        1. Very cool! So I guess you must be adding formula like this:

          =indirect(sheetNameVariable&"!C1") which evaluates to something like this: =indirect("Sheet1!C1")

          into the array that gets printed on the index page.

          Thanks for sharing your extension, there’s probably quite a lot you could do along these lines actually!

          Cheers,
          Ben

  1. Hi Ben, it is awesome! Thank you! but in my case when “,” is used in the hyperlink function (index sheet) between url and sheet name I get an error (guess its smh about google docs in different countries?), when I change it to “;” everything is ok.. How can I change the script to get it right without correcting every time?

  2. got it, i changed one thing and it’s working 😀

    // create array of sheet names and sheet gids
    sheets.forEach(function(sheet){
    indexSheetNames.push([sheet.getSheetName()]);
    indexSheetIds.push([‘=hyperlink(“https://docs.google.com/spreadsheets/d/1OXfDXlz8UB5NOKIYtkUM5FDYISUKRWWCFWG8KSUfQEI/edit#gid=’
    + sheet.getSheetId()
    + ‘”;”‘
    + sheet.getSheetName()
    + ‘”)’]);
    });

    1. Good job! Yes, you’re absolutely right, in some countries (mainly continental Europe I think) you use the “;” where we would use the “,”. Going to make a note in the article to reflect that. You’ve found the correct place to make that change!

      Thanks!
      Ben

  3. TypeError: Cannot call method “clearContents” of undefined. (line 76, file “Code”)Dismiss

    When I run this script. I receive this message.

    1. Hmm, that’s because apps script doesn’t have a sheet object selected to clear, so the “sheet” variable must be empty. I’d check you have all the code lines in place. Feel free to share here or a link to your sheet…

        1. Question is there a way to place Index in a Created Right Aligned Sidebar ? So no matter what tab your currently on you can still see the Index ?

          Thanks

          1. Hey Clarence,

            This is a great idea! I’m working on it now and will post a solution in the next day or two.

            Cheers,
            Ben

  4. I successfully created a hyperlinked index sheet with your help, thank you for that.
    Now I just want to see the visible sheets in my index sheet, so exclude hidden sheets. Is that possible?

    1. Hey Max,

      Yes, this should definitely be possible using the isSheetHidden() method.

      I’d suggest combining it with a .filter() to remove the hidden sheets from your array before printing to the sheet.

      Hope that helps. If I find time I’ll try to update the code here to show this.

      Cheers,
      Ben

  5. Up to now, hyperlinked sheet names of only visible sheets were printed out correctly in column B through the following code:

    function sheetNamesIds(sheets) {

    var indexSheetNames = [];
    var indexSheetIds = [];

    sheets.forEach(function(sheet){
    indexSheetNames.push([sheet.getSheetName()]);
    if (sheet.isSheetHidden()){}

    else {
    indexSheetIds.push([‘=hyperlink’
    + sheet.getSheetId()
    + ‘”;”‘
    + sheet.getSheetName()
    + ‘”)’]);
    }
    }
    );

    return [indexSheetNames, indexSheetIds];

    }

    Problem: In column A all sheet names, also of invisible sheets, are printed out. This column should only contains visible sheet names, too.

Leave a Reply

Your email address will not be published. Required fields are marked *