3 Ways to List All Sheet Names in Google Sheets

Are you looking for a way to list all the sheet names in your Google Sheets? Don’t worry, we’ve got you covered. In this article, we will explore three different methods to make this task a breeze.

The All Tabs Button Method

Did you know that there is a handy little button located at the bottom left corner of your Google Sheets workbook? It’s called the “All Tabs” button, and it can save you a lot of time and effort.

Simply click on the “All Tabs” button in the toolbar of your Google Sheets, and a vertical dialog box will appear, displaying a list of all the sheet names in your workbook. Not only does this allow you to see all the sheets at once, but it also lets you navigate to any sheet with just a click.

Unfortunately, you cannot copy these sheet names directly into the grid. But don’t worry, we have a couple more methods up our sleeve.

Using Apps Script

Apps Script is a powerful tool that can automate almost anything in Google Sheets, including listing all the sheet names in a workbook with just a click. Here’s how you can do it:

1. Select the “Extensions” menu.
2. Click on “Apps Script” to open the code editor.

3. Copy and paste the provided script into the code editor of your Google Sheet.
4. Click on the “Save” button.

This script creates a menu named “Tabs” with an option called “List All Sheets” that triggers the listSheets function.

The next time you refresh or open your Google Sheet, you’ll see the additional “Tabs” menu in the toolbar. Now, follow these steps to list all the sheet names:

1. Select any cell and enter your header, for example, “Sheet Names.”
2. Select the cell below, let’s say cell “B5.”
3. Choose the “Tabs” option in the menu added by your script.
4. Click on the “List All Sheets” option to trigger the listSheets() function.

You will see the list of sheet names automatically added to the range “B5:B9”. Keep in mind that this gives you a static list, so you’ll need to rerun the script if you modify the sheet names or add new sheets to your workbook.

Using Apps Script Custom Function

But wait, there’s more! You can even create a custom function using Google Sheets Apps Script that will return a list of sheet names.

Here’s how you can do it:

1. Select the “Extensions” menu.
2. Click on “Apps Script” to open the code editor.

3. Copy and paste the provided script into the code editor of your Google Sheet.
4. Click on the “Save” button.

The SHEETNAMES() function requires an argument called “option.” It accepts three option values: 0, 1, and -1. If you don’t include a value for the “option” argument, the default value is 0.

Now, follow these steps to use the custom function SHEETNAMES() to list all the sheet names in your Google Sheets:

1. Select any cell and enter your header, for example, “Sheet Names.”
2. Select the cell below, let’s say cell “C5.”
3. Type =SHEETNAMES() and press Enter.

Note that you haven’t passed any argument value to your SHEETNAMES() function. The script sets the default value of 0 for the “option” argument and returns a list of all the sheet names in Google Sheets.

You will see the list of sheet names returned in the range “C5:C9”.

If you only want to return the current sheet in Google Sheets, type =SHEETNAMES(1) and press Enter. When you pass the value 1 to the “option” argument, the script will only return the name of the current sheet.

Alternatively, if you want to list all the sheet names except the current sheet in your workbook, type =SHEETNAMES(-1) and press Enter. Note that you pass the value -1 to the “option” argument to exclude the current sheet name from the results.

You will see the list of sheet names, excluding the current sheet name (“Index”), in the range “E5:E9”.

Using a custom function allows you to list the sheets dynamically, which will update automatically when you modify the sheet names or add new sheets to your workbook.

Now you know three different methods to list all the sheet names in Google Sheets. Have you tried any of these tricks? Do you know any other methods to get all the sheet names? Let us know in the comments below!

For more Google Sheets tips and tricks, visit Crawlan.com.