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:

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:


Index of all sheets in this workbook:

    " + d[0] + ""; }).join(''); ?>

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.

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

  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.

    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?

  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.

  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.

Leave a Reply

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