Sometimes, you may want a quick way to get the name of the current sheet in a cell (or a list of all the sheets in the Google Sheets documents in cells).
One common scenario where this may be needed could be when you want to create a table of contents and want to quickly get the sheet names in one place as a list.
Unfortunately, there is no inbuilt formula or feature in Google Sheets that will do this.
But then, to our good fortune, there is Google Apps Script where you can create a custom formula and do a lot of stuff you otherwise can not (including fetching sheet name).
In this tutorial, I will show you how to use a custom formula to get the name of the current sheets in a cell or get the names of all the sheets in a column.
Table of Contents
Get the Current Sheet Name in a Cell
To get the current Sheet name, we first need to create a custom app script that will create a formula that will allow us to do this.
Below is the script that you can use for this
function GetSheetName() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}
Here are the steps on how to use this:
- Copy the above Google Apps script code
- Click the Tools option in the menu
- In the options that show up, click on Script editor. This will open the back end Apps Script editor in Google Sheets
- In the Code.gs window, delete the default text and paste the above script code
- Save the project by clicking on the Save icon in the toolbar
- Close the App script window
With the above steps, we have added the code in that specific Google Sheets document.
Now you can use the function GetSheetName within the worksheet, and it will give you the current sheet name.
=GetSheetName()
The above formula does not take any input argument and would return the name of the Active sheet.
While using the formula, you may see the text ‘Loading’ written in the cell. Just wait for a few seconds and the result should appear.
Unlike regular formulas, any formula made using a custom apps script would not show you help while using the formula in the worksheet.
For example, while you are entering the text =GetSheetName, it will not show you a prompt to autocomplete the function.
So you need to know the exact name of the function and the number of arguments and the nature of arguments in case the function takes any arguments.
Get All Sheet Names in Google Sheets
Just like we created a custom app script formula to get the active sheet name, we can also create a formula that goes through all the sheets in the Google sheets workbook and lists the names of all the sheets.
Below is the apps script code that you can use for this
function GetAllSheetNames() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out
}
You can use the same steps mentioned in the previous step to copy this Google app script code and put it in the back end.
Once you have the code in the Google app script and you have saved it, come back to the workbook an in any cell enter the formula
=GetAllSheetNames()
The above code uses a For loop to circle through all the worksheets in the Google sheets document.
It then starts listing these sheet names starting from the cell where you entered the formula and going one cell down with each sheet name.
For this formula to work, you need to have all the empty cells that would be filled by the formula result.
For example, if you have 10 worksheets in the document, using this formula is going to occupy 10 cells starting from the cell where you entered the formula.
In case there is any content in any of the nine cells below the cell where you entered the formula, this formula is going to show you a reference error.
So this is how you can use a simple Google app script code to quickly get the active sheet name (or all sheet names) in Google Sheets.
I hope you found this tutorial useful.
Other Google Sheets tutorials you may also like:
