This post is going to show you the different ways to list all sheet names in Google Sheets.

You may need to create a list of all sheet names in Google Sheets.  But this task can be tedious when you have a workbook with dozens of sheets and need to add an index sheet that lists all the sheet names.

Adding an Index sheet for a table of contents that links to each sheet is a good practice to help organize your workbooks. 

It can be difficult to generate a table of contents or index sheet manually that lists all sheet names.

In this article, you will learn all the different ways to list all sheet names in Google Sheets.

• List All Sheet Names with the All Sheets Button
• List All Sheet Names with Apps Scripts
• List All Sheet Names with Apps Scripts Custom Function

Download a copy of the sample workbook using the above link to follow along with the post.

List All Sheet Names with the All Sheets Button

There is a small button that’s found in the lower left of a Google Sheets workbook.

In the sheets toolbar of your Google Sheet workbook, press the All Sheets Button.  You will see a vertical dialog box with the list of all sheet names in your google sheets.

This will show you all the Sheets in the workbook but it also allows you to navigate to any Sheet. Just click on a sheet name and you will be taken to that sheet.

Unfortunately, there is no way to copy these sheet names into the grid.

List All Sheet Names with Apps Scripts

Apps Scripts is a great way to automate just about anything in Google Sheets and it’s possible to use this tool to generate a list of all the sheet names in a workbook with the click of a button.

Check out the full introduction guide to Google Sheets apps scripts for more details on the amazing tool.

Follow these steps to open the apps script code editor window.

1. Select the Extensions menu.
2. Click on the Apps Script option.

function listSheets() {
  var sheetNames = new Array();
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var selectedSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selectedRow = selectedSheet.getActiveCell().getRow();
  var selectedCol = selectedSheet.getActiveCell().getColumn();
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(selectedRow, selectedCol, sheets.length, 1);
  for (var i = 0; i < sheets.length; i++) sheetNames.push([sheets[i].getName()]);
  range.setValues(sheetNames);
}

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Sheets')
      .addItem('List All Sheets', 'listSheets')
      .addToUi();
}

Copy and paste the above script into your google sheet code editor and click the Save button.

The above script creates a menu named Sheets with an item named List All Sheets that allows you to trigger your function listSheets.

You can see the additional menu Sheets in the google sheets menu bar when you refresh or open the Google Sheet next time.

Follow these steps to list all sheet names in Google Sheets.

1. Select any cell and type your header such as Sheet Names.
2. Select the cell below, in this example cell B5.

3. Select the Sheets option in the menu that was added by your script.
4. Press List All Sheets option to trigger the listSheets() function.

You can see the list of sheet names will be automatically added in range B5:B9.

This results in a static list and you will need to rerun the script if you change any sheet names or add new sheets to the workbook.

List All Sheet Names with Apps Scripts Custom Function

There is another way you can leverage Google Sheets apps scripts to automate the generation of a list of sheet names.

You can build a custom function that will return a list of sheet names!

This can then be used like any other built-in function to return the current list of sheet names from your workbook.

Follow these steps to open the apps script code editor window.

1. Select the Extensions menu.
2. Click the Apps Script option.

/**
 * This function will return a list of sheet names from the current workbook.
 * @param {number} option Argument must be -1, 0, or 1.
 * @return A list of sheet names from the current workbook.
 * @customfunction
 */
function SHEETNAMES(option = 0) {

  var sheetNames = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var currSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

  if (option === 0) {
    for (var i = 0; i < sheets.length; i++) sheetNames.push([sheets[i].getName()]);
  } else if (option === -1) {
    for (var i = 0; i < sheets.length; i++) if (currSheet != sheets[i].getName()) { sheetNames.push([sheets[i].getName()]) };
  } else if (option === 1) {
    sheetNames.push(currSheet);
  } else {
    throw "Option argument must be -1, 0, or 1";
  }
  return sheetNames
}

Copy and paste the script into your Google Sheet code editor and press the Save button.

The SHEETNAMES() function requires one argument called option.

The function accepts three option values 0,1 and -1. If the option the parameter argument value is not included, the default value is set to 0.

Follow these steps to use the SHEETNAMES custom function to list all sheet names in Google Sheets.

1. Select any cell and type your header such as Sheet Names.
2. Select the cell below. In this example, cell C5 is selected.
3. Type =SHEETNAMES() and press the Enter key.

Please note that you did not pass any argument value to your function SHEETNAMES(). The script will set the default value 0 to the option parameter and return a list of all sheet names in Google Sheets.

You can see the list of sheet names is returned to range C5:C9.

Type =SHEETNAMES(1) and press the Enter key if you want to return only the current sheet in Google Sheets.

When you assign the value 1 to the argument, the script will return only the current sheet name.

You can see the current sheet name of Index in cell D5.

Type =SHEETNAMES(-1) and press the Enter key if you would like to list all sheet names excluding the current sheet from your workbook.

Note that you pass the value -1 to the option argument to exclude the current sheet name from the results.

You can see the list of sheet names excluding the current sheet name of Index in Range E5:E9.

Using a custom function allows you to list the sheets in a dynamic manner that will update as you change sheet names or add sheets to the workbook.

Conclusions

There are a few ways to get the sheet names from your workbooks.

The All Sheets button is an easy way to see the list of all sheet names in your google sheets, but you won’t be able to get the data into the workbook.

Fortunately, you can use Apps Scripts to generate the list of sheet names for you, and there are two options using this tool.

You can create a script that will add the list of sheets to your workbook or you can create a custom function that lists the sheet names.

You can even exclude the current sheet name from your output which is a great option for creating an index or table of contents sheet.

Do you use any of the tips to list all sheet names in Google Sheets? Do you know any other methods for getting all the sheet names? Let me know in the comments below!