How to Get the Sheet Name in Google Sheets

Sometimes when working in Google Sheets, you may need a quick way to get the name of the current sheet in a cell, or you may want to get a list of all the sheets in a workbook onto your spreadsheet. While there isn’t currently a built-in formula to do this automatically, there is a workaround.

You can create your own custom formula using Google Apps Script that will retrieve the names of all the sheets used in your workbook. In this tutorial, I’ll show you how to get the sheet name in Google Sheets by creating our own custom formula.

How to Get the Current Sheet Name

To get the name of the current sheet using a formula, we’ll need to create a custom formula using a Google script that will do it for us. If you’ve never used the Google script editor before, it may seem daunting, but it’s actually quite straightforward.

Our custom formula to get the sheet name will be as follows:

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. Remove any existing code and replace it with this custom code.
3. Click the save icon to save this code. You should now be able to exit the script editor and return to your sheet.

You should now be able to enter the formula into your spreadsheet to call the custom function that will retrieve the sheet name:

=GetCurrentSheet()

Here’s what it would look like in your spreadsheet:

How to Get All Sheet Names

We can also create a formula using the script editor that will retrieve all the sheet names or tabs used in your workbook instead of just the current sheet as in the previous method. The steps are the same. Open the script editor by selecting Tools > Script editor and paste this formula into your code area:

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’ve entered this into the script editor and saved it, you can now enter this formula into a cell:

=GetAllSheets()

You should now see all the sheet names or tabs used in your spreadsheet. Here’s what it looks like:

Final Thoughts

Creating your own custom formulas is not as complicated as it may seem. If you want to quickly get 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 it. It only takes a few minutes to set up your script, and then you can use this formula to easily extract all the sheet names into your cells.

I hope you found this tutorial helpful! For more tutorials on Google Sheets, visit Crawlan.com.