How To Add A Hyperlinked Index Sheet In Google Sheets

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.

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:

function onOpen() {
  
  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Index Menu')
      .addItem('Create Index', 'createIndex')
      .addItem('Update Index', 'updateIndex')
      .addToUi();
}


// function to create the index
function createIndex() {
  
  // Get all the different sheet IDs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  
  var namesArray = sheetNamesIds(sheets);
  
  var indexSheetNames = namesArray[0];
  var indexSheetIds = namesArray[1];
  
  // check if sheet called sheet called already exists
  // if no index sheet exists, create one
  if (ss.getSheetByName('index') == null) {
    
    var indexSheet = ss.insertSheet('Index',0);
    
  }
  // if sheet called index does exist, prompt user for a different name or option to cancel
  else {
    
    var indexNewName = Browser.inputBox('The name Index is already being used, please choose a different name:', 'Please choose another name', Browser.Buttons.OK_CANCEL);
    
    if (indexNewName != 'cancel') {
      var indexSheet = ss.insertSheet(indexNewName,0);
    }
    else {
      Browser.msgBox('No index sheet created');
    }
    
  }
  
  // add sheet title, sheet names and hyperlink formulas
  if (indexSheet) {
    
    printIndex(indexSheet,indexSheetNames,indexSheetIds);

  }
    
}

// function to update the index, assumes index is the first sheet in the workbook
function updateIndex() {
  
  // Get all the different sheet IDs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var indexSheet = sheets[0];
  
  var namesArray = sheetNamesIds(sheets);
  
  var indexSheetNames = namesArray[0];
  var indexSheetIds = namesArray[1];
  
  printIndex(indexSheet,indexSheetNames,indexSheetIds);
}

// function to print out the index
function printIndex(sheet,names,formulas) {
  
  sheet.clearContents();
  
  sheet.getRange(1,1).setValue('Workbook Index').setFontWeight('bold');
  sheet.getRange(3,1,names.length,1).setValues(names);
  sheet.getRange(3,2,formulas.length,1).setFormulas(formulas);
  
}

// function to create array of sheet names and sheet ids
function sheetNamesIds(sheets) {
  
  var indexSheetNames = [];
  var indexSheetIds = [];
  
  // create array of sheet names and sheet gids
  sheets.forEach(function(sheet){
    indexSheetNames.push([sheet.getSheetName()]);
    indexSheetIds.push(['=hyperlink("#gid=' 
                        + sheet.getSheetId() 
                        + '","' 
                        + sheet.getSheetName() 
                        + '")']);
  });
  
  return [indexSheetNames, indexSheetIds];
  
}

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:

code.gs
sidebar.html

The code in the code.gs file is:

/**
 * menu
 */
function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Sidebar Menu')
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
}

/**
 * show sidebar
 */
function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('sidebar.html')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Index Sidebar');
  
  SpreadsheetApp.getUi().showSidebar(ui);
}

/**
 * get the sheet names
 */
function getSheetNames() {
  
  // Get all the different sheet IDs
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  
  return sheetNamesIds(sheets);
}

// function to create array of sheet names and sheet ids
function sheetNamesIds(sheets) {
  
  var indexOfSheets = [];
  
  // create array of sheet names and sheet gids
  sheets.forEach(function(sheet){
    indexOfSheets.push([sheet.getSheetName(),sheet.getSheetId()]);
  });
  
  //Logger.log(indexOfSheets);
  return indexOfSheets;
  
}

See this file on GitHub here.

Then the code in the sidebar.html file is:

<!DOCTYPE html>
<h2>Index of all sheets in this workbook:</h2>
<input type="button" value="Close" onclick="google.script.host.close()" />

<ol>
<?!= 
  getSheetNames().map(function(d) {
    return "<li><a href='https://docs.google.com/spreadsheets/d/ ...YOUR SPREADSHEET ID HERE... /edit#gid=" + d[1] + "' target='_blank' rel="noopener noreferrer">" + d[0] + "</a></li>";
  }).join('');
?>
</ol>

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.

87 thoughts on “How To Add A Hyperlinked Index Sheet In Google Sheets”

    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. Can we make an index to update whenever we add or remove a sheet or some predefined duration i.e. 1 min?

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

      1. I added the isSheetHidden line in your code to get the Index of sheets without the hidden sheets. I’m a newbie and it took me quite a few hours for something that in the end looks so simple. I am using the SideBar UI and it works great.

        // function to create array of sheet names and sheet ids
        function sheetNamesIds(sheets) {

        var indexOfSheets = [];

        // create array of sheet names and sheet gids
        sheets.forEach(function(sheet){

        if(sheet.isSheetHidden()!= true){
        indexOfSheets.push([sheet.getSheetName(),sheet.getSheetId()])}

        });

        //Logger.log(indexOfSheets);
        return indexOfSheets;

        }

  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.

    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.

      Cheers,
      Ben

      1. I made a copy of the sample spreadsheet, compared it with the code on Github, and compared it with the code pasted above. It still opens in another tab.

        Where is the updated code?

  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: https://support.google.com/docs/answer/45893

      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("https://docs.google.com/spreadsheets/d/...YOUR SPREADSHEET ID HERE..../edit#gid='

          to this:

          indexSheetIds.push(['=hyperlink("#gid='

          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:

    indexSheetIds.push([‘=HYPERLINK(“#gid=’

    (instead of: indexSheetIds.push([‘=hyperlink(“https://docs.google.com/spreadsheets/d/….)

    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.

      Cheers,
      Ben

  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 “https://docs.google.com/spreadsheets/d/ …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?

    1. I’m no expert, but I run this script in a separate file. It alphabetised all my tabs… so a pain in the bum if you don’t want your tabs alphabetised… be warned! My workbook has 250 tabs and it took days to reorganise everything once I ran the script. It’s fine now though and works with the indexing script too.

      function onOpen()
      {
      sortSheetsByName();
      }
      function sortSheetsByName() {
      var aSheets = new Array();
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      for (var s in ss.getSheets())
      {
      aSheets.push(ss.getSheets()[s].getName());
      }
      if(aSheets.length)
      {
      aSheets.sort();
      for (var i = 0; i < aSheets.length; i++)
      {
      var theSheet = ss.getSheetByName(aSheets[i]);
      if(theSheet.getIndex() != i + 1)
      {
      ss.setActiveSheet(theSheet);
      ss.moveActiveSheet(i + 1);
      }
      }
      }
      }

  16. Hi Ben,

    I was working with the script you mentioned above and there are somethings that is bugging my mind. I hope you can give me a solution for this.
    Link for an example:
    https://docs.google.com/spreadsheets/d/1EhtdFTKy1R8q1oLVaxdVP6G9PI55TBINsjiWHEy-m3Y/edit?usp=sharing

    I am giving you comment access so you even provide me info there.

    So I made this master index to get entire sheet names and hyperlink for them and what am I exactly trying to do is that this ledger get updated everyday and I want those balances to be fetched from all sheets in this master index.

    My main concerns:
    1. Whenever I add a new sheet suppose person 5 and person 6, then if I update the index from that new tab created on top. It deletes the balance column, which I want it it exist there anyhow.

    2. I was expecting that if I can anyhow put a condition over there that row 1 which is person 1 matches the sheet name for Person 1 and then give me the balance. since I’ll be adding more person and that can disturb the alphabetical order of the index and will give me wrong balance for that sheet.
    Since my plan is to update the entire index if I add 5 to 6 sheets more so that formula will look for row 1 which is the text version of sheet name and will match the hyperlink of sheet name in a text form and then give me the balance.

    I hope it makes sense.
    I’ll be really thankful if you can help me in this.

    1. Hi there SaurabhCancel , did you get a solution for your question? I’m experiencing the same problem here.

  17. I’m using the Sidebar version and I’m getting an error that the ‘sidebarContent.js file isn’t found. If I remove the javascript from sidebar.html, it seems to work fine except that the sidebar closes down each time I open a new tab from it. Maybe it’s supposed to close the sidebar each time in order to maximize the spreadsheet space. But what is the Javascript supposed to do and why isn’t it found? This is the problem line:

    HtmlService.createHtmlOutputFromFile(‘sidebarContent.js’).getContent();

  18. I also noticed that every time I pick a spreadsheet tab, the tab opens in a new Chrome tab. Is there any way that it could be changed to open in the same Chrome tab? If so, that would be a GREAT option … to have a variable in the script that could be set to tabs =”overlay” or tabs = “new”. Even if there’s no UI for that, then some people could set it one way and some the other way.

  19. +1

    Sidebar Menu, opening new tab when selecting Index item.

    I have tried a few things but my HTML is not great. I see we are calling target = ‘blank’ in the Href link. I tried other variables like parent or removing the target but these don’t work.

    Is there a way of dynamically leading back to the individual #gid=12345…. in the same workbook that you are viewing the sidebar in? that is to say without triggering a new tab in the browser.

    I guess functionality like you would get on a web page where the href could lead you down a page rather than instigate a new window.

    Apologies if my terminology is a bit vague but I hope it makes some sense.

    1. Many thanks for your scripts “createIndex” and “sidebar menu” . Both scripts are integrated to my sheet (each script has its own project).
      Concerning opening links in a new tab there is no problem since I am using Vivaldi browser and this browser has an option to open link in current tab (there are 5 options how to open the link there), this menu appears after pres RMB.
      And this option works- I always stay in current sheet/tab/.

  20. do hyperlinks of the form #gid=… not work in mobile? In the iOS Sheets app it simply does nothing and remains on the page. In other mobile browsers (Edge, Safari) it reloads the spreadsheet in a new tab but does not navigate to a new page. I don’t want to include a hard reference to the file Id because I’d fear this would break upon sharing a copy of the file with someone else. Maybe mobile just hasn’t caught up with hyperlinking within the sheet yet?

  21. I am getting this error message. Could you help please?
    Exception: No HTML file named sidebarContent.js was found

  22. Hi!
    I’m a NON_TECH
    and i want to try this in my work, because i can’t manage more than 30 sheets of 5 project in my work ;-;
    I tried.
    But i can’t paste the code of html
    so i only have to past this code in html:
    Index of all sheets in this workbook:
    ” + d[0] + “”;
    }).join(”);
    ?>

    I stuck at the html step

  23. Hi Ben,

    thank you so much for excelent work with such a helpful thing like autamatic navi page in google “excel” 😉
    Just one other idea for some additional extention what was importend for me was to do a little change in line 76 to limit clearance the data in index sheet just to specyfic range.
    Why it was so important for me?
    Well … I just insert many other function next to that range with sheet names that connected with indirect function allow me to do realy easly many other statystical tables just autamaticaly 🙂

    Well … maybe for some other people will be also helpful to change this line to:
    sheet.getRange(‘A:B’).clearContent();
    (to do more presise clerance just put excact range e.g. :
    sheet.getRange(‘A3:B50’).clearContent();
    Is just a small addustment compared to Ben’s work but maybe will be also helpful for somebody else 🙂

    Regards

  24. When I try to run the onOpen I get this error message: Exception: Cannot call SpreadsheetApp.getUi() from this context.

    I have no clue what it means. Any help?

    1. Manon, I am having the same issue!
      Ben, do you have any ideas for resolving “Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 3, file “Code”)”?

  25. How do I get the names of the sheets I edit in the index sheet to remain same after updating? I have created an index sheet and after updating the whole sheet gets refreshed so it deletes any other entries on that sheet.

  26. Hi Ben, is it possible to combine the two methods. I want to use the sidebar to navigate the Spreadsheet, but don’t want to open new tabs on my browser to do so. As the current sidebar method does.

  27. Thank you so much for sharing! BTW for anyone who is trying to use the sidebar version & confronted this error “Exception: No HTML file named sidebarContent.js was found”, you’re suppose to put your google spreadsheet key from spreadsheet url & put at line 7(YOUR KEY HERE) of your html file.

  28. Hi Ben. I’ve added the Index script and now decided not to use it (going to use the Sidebar). I’ve deleted the script for Index but “Index Menu” is still in my menu. How do I delete this? Thank you.

  29. Hi Ben

    Just to add to all the thanks – you have a superb teaching style and great explanations – especially for noobs like me.

    A question please for maybe a follow up.

    I have sheets all over – and in my learning stuff duplicated tabs and half finished stuff – I amybe the worst but sure there’s aothers with this need.

    So I extraced a list of all the sheets in my Google Drive – this have all the filenames and links to each in two columns.

    Now looking for a way to have a script work through each of those links on that one sheet and populate the row of each filename/url with the TAB names from that sheet.

    Hope my explanation makes sense – is it possible?

    Thanks again for your superb content.

  30. Hi Ben,

    I really enjoy playing with Google sheets after watching your traning.
    Now I would like to add a formula to your Indexpage.

    indexSheetCells.push([‘=Rows(‘+ sheet.getSheetName() + ‘!A:A)*Columns(‘+ sheet.getSheetName() + ‘!1:1)’]);

    The formula is working but I can’t get it on the index page.
    Could some help?

  31. Adding this sidebar menu would be a great help on some of my sheets but I keep getting the error: “SyntaxError: Unexpected Identifier.” Running the code from the Apps Script Editor executes without error. Running the code from the new menu item, “Sidebar Menu” returns the error.

    I tried this on a new Google Sheet with a new Apps Script project containing two files:

    – code.gs (code copied from this page)
    – sidebar.html (code copied from this page)

    I also tried this using the code from Git Hub which seemed to suggest adding another html file, sidebarContent.js.html. Adding this to the Project returned the same error.

    I’m on Mac OS v 13.2 and Chrome v 109.0.5414.119 (latest).

    Any suggestions to help get this working would be greatly appreciated.

    1. Scott,
      I renamed “sidebar.html” as “sidebarContent.js.html” and it runs, displaying the list without an error. The sheetname links are clickable, but they open a whole new browser session of the spreadsheet … not in the expected sheet … with the code from the code.gs file displayed at the bottom.

      ps thanks for your reply to my post in the Tiller community about this same subject.

  32. Hi Ben,

    It’s now been a year since the last update in this thread. I found the thread today and got your Sidebar Menu working with the index list on my Windows 11 Desktop using Chrome.

    However, the index sidebar still opens the hyperlinks in a new tab.
    Did you not find a way to modify the script to produce hyperlinks (or maybe better yet: “anchors”) to all sheets in the spreadsheet, on the same tab?

    I would really like to use this sidebar if that was the case, but until then I prefer to use your index page solution.

    For that one I am still looking for a way to quickly return to the index page if you’re on a sheet far down the line of sheets. Maybe you have a tip for me for that too?

    Oh, and it would also be great if the index page splits up the list of pagelinks in several columns so that all links are visible at once.
    My screen lets me see about 40 rows but my spreadsheet already contains 96 individual sheets, so if I could split up the list after 40 sheets it would create 3 columns (40, 40 and 16).

    Hope you have some ideas and tips for this.

    Kind regards,

    Tijs

Leave a Reply

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