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)?
4 Answers
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()
.
-
9How to access this script in the "new" Google Sheets (which now uses an add-on store)? Mar 29, 2014 at 6:13
-
4This 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. Aug 19, 2015 at 18:24
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.
-
Could you expand upon your answer for those of us that are not familiar with the script tool? 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.– gamlielaMay 13, 2015 at 12:43
-
6While this function works fine, it doesn't update when sheet name changes, so it's not actually useful. 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. Jul 2, 2018 at 14:17
-
1I 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. Jul 5, 2018 at 6:25
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')
-
1
-
This gives the following error: "You do not have permission to call SpreadsheetApp.openById. " Jun 9, 2019 at 12:13
-
1This returns the document's name instead of the sheet's name, replace
return ss.getName();
byreturn ss.getActiveSheet().getName();
if you want the sheet's name (I actually needed the document's name, so thanks)– EmilienAug 31, 2019 at 9:37
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.
-
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