How to get list of sheets names in Google sheets?
When working with complex Google Sheets documents containing multiple worksheets, it can be useful to generate a dynamic list of all sheet names. Whether you're organizing data, creating a table of contents, or building automated workflows, having quick access to your sheet names improves efficiency. In this guide, we'll explore the most effective ways to generate and maintain an up-to-date list of all sheets in your Google sheets.
Get the current sheet name in Google Sheets with script
Get a list of sheet names in Google Sheets with script
Get the current sheet name in Google Sheets with script
If you want to display the name of the currently active sheet dynamically and avoid manual entry, a simple custom script can help. This is especially useful in dashboards, reports, or templates where the sheet name is used as a reference or in headers for easy identification.
1. Click Tools > Script editor to open the Apps Script editor. This action opens a new tab where you can write Google Apps Script code:
2. In the newly opened project window, copy and paste the following script code into the otherwise blank Code editor. This code creates a custom function to return the current sheet name:
function sheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}
3. After pasting the code, click the Save icon. Then return to your Google Sheets workbook and, on the sheet where you want the name displayed, enter the formula: =sheetname() in your chosen cell. Press Enter, and the current sheet name will be shown immediately.
Applicable Scenarios and Tips: This solution is well-suited for report automation, documentation, or when generating templates where the tab name needs to be referenced within the sheet content. Note that the displayed sheet name is updated when the sheet is recalculated.
Troubleshooting: If you encounter a #NAME? error or nothing appears, ensure the script was saved correctly, and the function name matches the one used in your formula. If you copy the spreadsheet or script, you may need to reauthorize permissions.
Get a list of sheet names in Google Sheets with script
For those who need to retrieve the names of all sheets within the same Google Sheets file—for example, to build an index, summary dashboard, or table of contents—you can create a custom function through Apps Script. This helps avoid the manual process of updating sheet references as your file evolves.
1. Click Tools > Script editor once again to open the project window. In the blank Code editor, copy and paste the provided script code below:
function sheetnames() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out
}
2. Save the script, then go back to your spreadsheet. On the sheet (and in the starting cell) where you want the list of all sheet names to appear, enter this formula: =sheetnames() and press Enter. Instantly, each sheet name will populate its own cell vertically in the column.
Use Cases and Cautions: This method is ideal for dashboards, table of contents, quick navigation links within large or shared workbooks, or directories where you need to keep an updated sheet list automatically. Be aware that any script-based solution requires authorization the first time and might need refreshing or updating if you make changes to script or copy your file. Custom functions like =sheetnames() do not auto-refresh in real-time; if a sheet is added or removed, you may need to re-enter the formula or reload your sheet to update the output.
Error Reminders: If you receive permission prompts, follow them to authorize the script. If no names are listed, double-check that the function name in the Apps Script file and in the cell formula match sheetnames exactly.
List all sheet names and navigate between them by using hyperlinks or buttons in Excel workbook: With Kutools for Excel's Create List of Sheet Names utility, you can quickly list all worksheet names in a new sheet with hyperlinks or macro buttons, then you can navigate between them to your need. This tool is especially convenient when working with large, complex Excel files where manual tracking or navigation of sheet tabs becomes cumbersome. Features include automatic hyperlink creation, easy management of renamed sheets, and a user interface designed for effortless access.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now |
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in