Getting the sheet name in Google Sheets can be a handy trick, especially when you need to create a table of contents or reference different sheets within your workbook.
While Google Sheets doesn’t have a built-in formula to get the sheet name, you can use the power of Google Apps Script to create a custom formula that does the job.
Imagine you’re working on a large spreadsheet with multiple sheets, and you want to quickly grab the name of the current sheet you’re on. Or perhaps you need to list out all the sheet names in a separate column for easy reference.
Without a dedicated function to get the sheet name in Google Sheets, these tasks can become cumbersome. Fortunately, Google Apps Script provides a solution.
By creating a custom formula, you can easily fetch the name of the current sheet or retrieve a list of all sheet names within your Google Sheets document.
This tutorial will guide you through the process step-by-step, making it simple to get the sheet name in Google Sheets using a custom formula.
Access Sample Sheet
Its always a brilliant idea to follow along with our tutorial step by step, so you get the complete experience. Hit the link below to copy the sample sheet we will use to show you how to get the sheet name in Google Sheets.
Get the Current Sheet Name in Google Sheets
Now that you have a basic understanding of what it means to get the sheet name in Google Sheets, it’s time to dive into the practical aspect of this guide.
In this section, we’ll walk you through the step-by-step process of getting a sheet name in Google Sheets. Whether you’re a beginner or have some experience working with Google Sheets, you’ll be able to grasp this concept easily.
For our tutorial, we’ll be using the following sample worksheet, which features several sheets, each named accordingly:
This hands-on approach will ensure that you can follow along and get a firm grasp of how to retrieve the sheet name in Google Sheets, regardless of your skill level.
By the end of this section, you’ll have the confidence to independently implement this technique in your own Google Sheets projects, saving you time and effort when you need to reference or work with multiple sheets within a single workbook.
So, let’s get started.
Step 1: Launch Apps Script
To get the sheet name in Google Sheets, the first step is to launch the Google Apps Script editor, which is a straightforward process.
Begin by navigating to the “Extension” menu in the Google Sheets interface. This menu is located in the top menu bar and provides access to various tools and features.
Within the “Extensions” menu, you’ll find an option labeled “Apps Script.” This option will open the Google Apps Script editor, a powerful tool that allows you to write and execute custom scripts within your Google Sheets environment.
Once you select the “Apps Script” option, a new window or tab will open, revealing the Apps Script editor interface. This editor is where you’ll write and manage your custom scripts, including the one we’ll be using to fetch the sheet name.
Here is what it looks like:
Step 2: Paste the Script Code
Now that you’ve successfully launched the Apps Script editor, it’s time to move on to the exciting part: writing the custom function that will retrieve the sheet name.
Begin by clearing the code window. You’ll notice that the editor already contains some default code when you first open it. To make way for your custom function, delete this default code by selecting it and pressing the “Delete” or “Backspace” key on your keyboard.
With the code window now empty, you can proceed to paste the following code:
function GetSheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}
Step 3: Save the Code
After pasting the custom function code into the Apps Script editor, it’s important to save your work to ensure that the changes are applied and the function becomes available for use within your Google Sheets document.
To save the code, click on the floppy disk icon located in the top-left corner of the Apps Script editor window.
Alternatively, you can use the keyboard shortcut “Ctrl + S” (Windows) or “Command + S” (Mac) to save the code.
If prompted, give your project a name. This name is for organizational purposes within the Apps Script editor and won’t affect the functionality of your custom function.
Verify that the code has been saved successfully by looking for the “Save” confirmation message at the top of the editor window.
Step 4: Verify the Current Sheet Name
After saving the code as demonstrated in Step 3, it’s time to test whether the custom function is working correctly. Verifying the functionality is a crucial step to ensure you can successfully retrieve the current sheet name.
To test the GetSheetName() function, follow these simple steps:
- Navigate back to your Google Sheets document by switching to the appropriate tab or window.
- Locate an empty cell where you want to display the current sheet name. It’s often helpful to choose a cell that’s easily visible and accessible.
- In the formula bar located above the spreadsheet grid, type the following:
=GetSheetName()
- Press the “Enter” key on your keyboard to execute the formula.
If you’ve followed the steps correctly, the selected cell should now display the name of the current sheet you’re working on.
Here is what we got using our own sample spreadsheet:
Looking at the screenshot above, you can see that our “=GetSheetName()” function works nicely and has helped us to get the sheet name we are currently working on.
Helpful tip: If you encounter any issues or the cell doesn’t display the expected sheet name, double-check your code in the Apps Script editor and ensure you’ve saved the changes correctly.
Get All Sheet Names in Google Sheets
In the previous section, we covered how to retrieve the name of the current active sheet using a custom function created with Google Apps Script.
While this approach is useful in many scenarios, there may be times when you need to go a step further and obtain a list of all sheet names within your Google Sheets workbook.
Whether you’re creating a table of contents, generating a summary report, or simply need to reference multiple sheets, having access to a comprehensive list of sheet names can be incredibly valuable.
Fortunately, just like we did for the active sheet name, we can leverage the power of Google Apps Script to create a custom formula that fetches the names of all sheets in your spreadsheet.
This method involves creating a slightly more complex script that iterates through all the sheets in your workbook and populates a range of cells with the corresponding sheet names.
By doing so, you’ll have a convenient reference list that can be easily updated or manipulated as needed.
Let’s show you the step by step process of retrieving multiple sheet names in Google Sheets.
Step 1: Launch the Apps Script Editor
Similar to the previous example, where we retrieved the name of the current active sheet, we’ll need to access the Google Apps Script editor to create the custom function for getting all sheet names.
The Apps Script editor is where you’ll write and manage the custom code that will power this functionality.
To launch the Apps Script editor, open the Google Sheets document where you want to implement the “Get All Sheet Names” functionality.
From the top menu bar, locate and click on the “Extensions” menu. Within the “Extensions” menu, you’ll find an option labeled “Apps Script.” select this option.
A new window or tab will open, revealing the Google Apps Script editor interface.
It should look something like this:
Step 2: Paste the Custom Function Code
With the Apps Script editor launched, it’s time to introduce the custom function code that will retrieve the names of all sheets within your Google Sheets workbook.
Before pasting the new code, it’s essential to clear the editor window of any existing code. When you initially open the Apps Script editor, it may contain some default sample code.
To ensure you have a clean slate, select the existing code in the editor window by clicking and dragging your cursor over it or by pressing “Ctrl+A” (Windows) or “Command+A” (Mac) to select all.
Once the code is highlighted, press the “Delete” or “Backspace” key on your keyboard to remove it entirely.
With the editor window now empty, you can proceed to paste the following code:
function getSheetNames() {
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var sheetNames = [];
for (var i = 0; i < sheets.length; i++) {
sheetNames.push(sheets[i].getName());
}
return sheetNames;
}
Step 3: Save the Custom Function
After pasting the custom function code into the Apps Script editor, it’s essential to save your work. Saving the code ensures that the changes are applied and the function becomes available for use within your Google Sheets document.
To save the code, click on the floppy disk icon located in the top-left corner of the Apps Script editor window.
Alternatively, you can use the keyboard shortcut “Ctrl + S” (Windows) or “Command + S” (Mac) to save the code.
Step 4: Verify All Sheet Names
After saving the custom getSheetNames() function in the previous step, it’s time to test whether the function is working correctly by retrieving a list of all sheet names in your Google Sheets workbook.
To verify the functionality, follow these steps:
- Navigate back to your Google Sheets document by switching to the appropriate tab or window.
- Select a range of cells where you want to display the list of sheet names. It’s often helpful to choose a range that starts from the first row or column, ensuring that the list is easily visible and accessible.
- Click on the first cell in the selected range to make it the active cell.
- In the formula bar, located above the spreadsheet grid, type the following:
=getSheetNames()
- Finally, press the “Enter” key on your keyboard to execute the formula.
If you’ve followed the steps correctly, the selected range of cells should now be populated with the names of all the sheets in your workbook. Each cell will contain the name of a single sheet, providing you with a comprehensive list.
Here is what ours looks like:
Final Thoughts
Learning how to get the sheet name in Google Sheets can be a game-changer for anyone who frequently works with multiple sheets within a single workbook.
By leveraging the power of Google Apps Script, you can create custom functions that seamlessly retrieve the name of the current active sheet or fetch a comprehensive list of all sheet names in your document.
Throughout this tutorial, we’ve covered two distinct approaches to get the sheet name in Google Sheets.
The first method demonstrated how to create a simple custom function that returns the name of the sheet you’re currently working on. This approach can be particularly useful when you need to reference the active sheet within formulas or data analysis processes.
The second method took things a step further by introducing a custom function that populates a range of cells with the names of all sheets in your workbook. This functionality can be invaluable when creating table of contents, generating reports, or any scenario where you need to reference multiple sheets simultaneously.
By following the step-by-step instructions provided, even beginners should feel confident in their ability to get the sheet name in Google Sheets using these custom functions. The process may seem daunting at first, but with a little practice, it becomes second nature.