39

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

migrated from superuser.com Jun 6 '11 at 15:58

This question came from our site for computer enthusiasts and power users.

11

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
  • 9
    How to access this script in the "new" Google Sheets (which now uses an add-on store)? – Benjamin Mar 29 '14 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 '15 at 18:24
49

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
  • Could you expand upon your answer for those of us that are not familiar with the script tool? – codingbadger♦ Apr 1 '15 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 '15 at 12:43
  • 5
    While this function works fine, it doesn't update when sheet name changes, so it's not actually useful. – jesjimher May 4 '16 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. – aribeiro Jul 2 '18 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 '18 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
  • 1
    This is just a slight change to @user93341 answer above. – JamesThomasMoon1979 Feb 20 '15 at 22:34
  • This gives the following error: "You do not have permission to call SpreadsheetApp.openById. " – Aurovrata Jun 9 at 12:13
  • 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 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
  • 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 '16 at 14:54

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