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: Hit Save and give your script project a new name: “Index”

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

Run onOpen function

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

Review permissions
Click Review permissions
Grant permissions
Click Allow

Step 6: 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. Unfortunately, the sidebar version cannot opens the link (i.e. the tab you want to navigate to) in a new browser tab.

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

The code in the 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: Google added functionality to link to specific cells within your worksheet, so that might be worth checking out.

49 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.


      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!


          1. Thank you very much, Ben. It has been extremely helpful to create the index.
            I used the =indirect formula to get the sum from all worksheet tabs. The issue is that everytime I update the index, all reference formulas are gone. The sheet name and links are in Column A of the index. Wondering if there is a way to just update that column and not the whole index worksheet to retain other content?

  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
    + 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!


  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 ?


  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.


  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 = [];

    if (sheet.isSheetHidden()){}

    else {
    + 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.

    1. Hey Jim,

      Letting you know that this has been fixed now, and the updated script will open Sheet tabs in the same browser tab.


  6. This is awesome and exactly the solution I needed, except that it opens a tab for each link clicked… is there an update to account for this?

    1. Not with this method. Actually, this method could be improved by using the direct linking to cells (or ranges) available in Sheets now. See “Link to a range of cells in a spreadsheet” in this Google help menu:

      At some point I’ll update this article so the script builds a link to the cells instead of opening in new tabs!

      1. I see his comment but when I am looking at the code, I’m not sure where it fits? He provides a line to replace, but I don’t see that line in the code. Or am I adding that line as a new line in the function sheetNameIds(sheets){…?

        1. It’s not a new line, so you just need to change line 94 from this:

          indexSheetIds.push(['=hyperlink(" SPREADSHEET ID HERE..../edit#gid='

          to this:


          I’ve updated the code in the article to show this now. Note, it only works for the version where you create an Index Sheet, not the sidebar method.

  7. Thanks a lot, Ben. Exactly what I need.
    I only changed one line, to create the link with the sheet Id:


    (instead of: indexSheetIds.push([‘=hyperlink(“….)

    This way the sheet is open in the same spreadsheet (no new tabs)

    Thanks again!

  8. I would like to use this with an index and match formula. That if the item is not list it is added to the bottom and if it is it puts another link in 3rd column. The data would be coming from another workbook.

  9. Thanks so much for sharing. It almost seems to be working for me. I’m getting a “formula parse error” in the hyperlink portion of the index. Any ideas on the cause?

    1. Hey Lori,

      I wonder if you’ve missed a quotation mark, or mixed up single and double quotations, in the hyperlink part, lines 94 – 98. It’s pretty finnicky…

      Another possibility, are you based in Europe where you use semi-colons in your formulas where I would use a comma? If so, you’ll need to modify the formula to account for that.

      Hope that helps.


  10. This makes things so much easier to navigate.
    Is there possiblity to:

    1. Freeze the Index menu to the left side?
    2. Way to remove underline from text?
    3. With one click you would go to the tab when text / clicked? Now you have to click twice, the text and the hyperlink popup.
    4. Way to hide the tab bar below?

    Thanks Ben.

  11. Is the sidebar option supposed to be able to open the desired tab from the index? Or is it just showing what tabs you have? Mine shows the links but the links say “Sorry, the file you have requested does not exist.”

    1. Ryan, I’m trying to implement this as well. I noticed you need to copy/paste the URL of your spreadsheet in place of the link placeholder “ …YOUR SPREADSHEET ID HERE…” in the HTML code.

  12. Hi,

    This is exactly what I have been looking for! Can you update your script a bit so that incorporates including a cell of your choice(This cell choice is constant for all sheets that I need indexed.)

  13. What did I do wrong?

    I successfully added both code for the HTML index. I updated the spreadsheet URL reference. When I select the new menu option, I get the HTML sidebar with list of sheets. When I click any link, a new tab opens to the first sheet. I was expecting the sheet I selected to open in the same tab.

    I copy/pasted the code listed above. The only change was the spreadhsheet URL in the HTML code.

    1. I solved my problem with the link. My URL included the current sheet gid. After deleting that portion of the URL, the links work.

      However, my links still open a new tab, instead of loading in the current tab. Comments above suggest the selected sheet will load without opening a new tab. Any help explaining how to load in a current tab is much appreciated. I do not recognize where in the code that is specified.

  14. Thank you so much! This make my day! I been working in this for a few time, and is solve now, thanks to you!
    Thank you again!

  15. Thank you so much – this is awesome, I have looked for so long for a solution to this. How can I edit the script to make the index alphabetical by default?

Leave a Reply

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