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)?
39
migrated from superuser.com Jun 6 '11 at 15:58
This question came from our site for computer enthusiasts and power users.
-
Please see my answer to this question on stackoverflow : stackoverflow.com/a/53409439/279326 – Nicolas Janel Nov 21 '18 at 9:58
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().
-
9How to access this script in the "new" Google Sheets (which now uses an add-on store)? – Benjamin Mar 29 '14 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. – 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.
-
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
-
4While 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
-
@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
-
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
5
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. " – Aurovrata Jun 9 at 12:13
-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.
-
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
