Sometimes when working in Google Sheets, you may want a quick way to get the sheet name of the current sheet into a cell, or you may want to get a list of all the sheets in a workbook into your spreadsheet.
Currently, there is not a formula that can do this automatically, but there is a workaround solution for this.
We can create our own custom formula using Google Apps Script that will get the name of all the sheets used in your workbook.
In this tutorial, I will show you how to get the sheet name in Google Sheets by creating our own custom formula.
Table of Contents
How to Get the Current Sheet Name
To get the current sheet name with a formula, we will have to create a custom formula with a Google Script that will do this.
If you’ve never used the Google script editor before, this seems really complex, but it is actually really simple.
Our custom formula for getting the sheet name will be:
function GetCurrentSheet() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}
To enter this code into the script editor:
1. In the top menu select Tools>Script editor
2. Delete any code that is already there and replace it with this custom code
3. Click the save icon to save this code. You should now be able to exit this script editor and go back to your sheet
You should now be able to enter the formula into your spreadsheet to call the custom function that will get the sheet name:
=GetCurrentSheet()
Here is how this would look in your spreadsheet:
How to Get All Sheet Names
We can also create a formula using the Script editor that will get all the sheet or tab names that are used in your workbook instead of just the current sheet like in the previous method.
The steps will be the same. Open the Script editor by selecting Tools>Script editor and paste this formula into the area for your code:
function GetAllSheets() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out
}
Once you have entered this into the script editor and saved you can now enter this formula into a cell:
=GetAllSheets()
You should now be seeing every sheet or tab name that is used in your spreadsheet. Here is how this looks:
Closing Thoughts
Creating your own custom formulas is really not that complicated. If you ever want to quickly grab the current sheet or all the sheet names used in Google Sheets, creating your own formula with the Script editor is the best way to do that.
It just takes a minute to set up your script and then you can use this formula to easily pull all the sheet names into your cells.
Hopefully you found this tutorial helpful!
More Google Sheets Tutorials:
How to Compare Two Columns
How to Refresh Formulas