43

Is there any way to display a sheet name as the result of a function in a Google Spreadsheets (exactly like this question, except in Google Sheets)?

1

5 Answers 5

12

Someone has written a script to do this. It is available in Tools > Script gallery.... Search for "sheet name" and install the script, then use it with =getCurrentSheetName().

2
  • 9
    How to access this script in the "new" Google Sheets (which now uses an add-on store)? Commented Mar 29, 2014 at 6:13
  • 4
    This no longer works. Use the answer from @user93341. I went through the entire process to publish his function to the the Add-On Store and in the last step they wanted $5 to verify my account and I would be limited to publishing 20 items. Le sigh. Commented Aug 19, 2015 at 18:24
55

Go to ToolsScript Editor and add this code:

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

In a cell where you want the sheet name add:

=sheetName()

There was an issue with accessing sheet names via API functions shown below—where it only returned the first sheet name. This was fixed in March 2015.

5
  • Could you expand upon your answer for those of us that are not familiar with the script tool? Commented Apr 1, 2015 at 7:09
  • 1
    @Barry 1) Select Tools->Script Editor 2) In the resulting text file, add this function at the end 3) Click save 4) Now you can use this function in your sheet.
    – gamliela
    Commented May 13, 2015 at 12:43
  • 6
    While this function works fine, it doesn't update when sheet name changes, so it's not actually useful.
    – jesjimher
    Commented May 4, 2016 at 7:47
  • 1
    @jesjimher according to this Google Docs Help Forum, if one goes to the script editor and re-saves the script, going back to the sheet one sees that the tab names were properly updated. Commented Jul 2, 2018 at 14:17
  • 1
    I know, but it's confusing and totally opposite to how sheet formulas work. I expect a formula to update whenever its referenced data changes. Now this formula doesn't do that unless I open and save the source script. I could just enter sheet name by hand every time, saving the hassle.
    – jesjimher
    Commented Jul 5, 2018 at 6:25
6

Here is a Custom Function code snippet to get a spreadsheet name by it's Identifier.

function GetSpreadsheetNameById(id) {
  var ss = SpreadsheetApp.openById(id);
  return ss.getName();
}

In a cell, pass a sheet Identifier (look in the spreadsheet URL for the identfier).

=GetSpreadsheetNameById('49nvkwOE-203nXDLGGJtg0htFKK020dsd939AAPZU')
3
  • 1
    This is just a slight change to @user93341 answer above. Commented Feb 20, 2015 at 22:34
  • This gives the following error: "You do not have permission to call SpreadsheetApp.openById. "
    – Aurovrata
    Commented Jun 9, 2019 at 12:13
  • 1
    This returns the document's name instead of the sheet's name, replace return ss.getName(); by return ss.getActiveSheet().getName(); if you want the sheet's name (I actually needed the document's name, so thanks)
    – Emilien
    Commented Aug 31, 2019 at 9:37
0

You can do this with a formula as long as you're not trying to get the name of the worksheet that the formula is in:

=REGEXREPLACE(CELL("address", 'Sheet Name'!A1), "'?([^']+)'?!.*", "$1")

Replace Sheet Name with the name of the sheet and you'll have it.

If you do need the name of the worksheet you're in, you can put that formula in another worksheet and refer to the cell it's in.

I got this method from this answer to a similar question on StackOverflow and have been using it for a while now.

-1

Try this:

function getNameOfThisSheet() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

Paste it in Tools > Script editor and call the function from a cell like this:

=getNameOfThisSheet(NOW())

The NOW(), former GoogleClock(), parameter helps avoid issues with memoization.

1
  • GOOGLECLOCK() was replaced by NOW() by the other hand non-deterministic functions can't be used as arguments of custom functions. Commented Aug 30, 2016 at 14:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.