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)?

Improve this question
1

4 Answers 4

Reset to default
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().

Improve this answer
2
  • 9
    How to access this script in the "new" Google Sheets (which now uses an add-on store)?
    – neydroydrec
    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.
    – Bruno Bronosky
    Aug 19, 2015 at 18:24
55

Go to Tools → Script 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.

Improve this answer
5
  • Could you expand upon your answer for those of us that are not familiar with the script tool?
    – codingbadger
    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
    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
    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.
    – António Ribeiro
    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
    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')
Improve this answer
3
  • 1
    This is just a slight change to @user93341 answer above.
    – JamesThomasMoon
    Feb 20, 2015 at 22:34
  • This gives the following error: "You do not have permission to call SpreadsheetApp.openById. "
    – Aurovrata
    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
    Aug 31, 2019 at 9:37
-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.

Improve this answer
1
  • GOOGLECLOCK() was replaced by NOW() by the other hand non-deterministic functions can't be used as arguments of custom functions.
    – Rubén ♦
    Aug 30, 2016 at 14:54

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