Google Apps Script, Custom Functions
Boy, are these titles getting longer.
But that’s pretty much the gist of it. In this post we will look at creating a Google Apps Script Custom Function that allows you to do Three things in Google Sheets:
- Get the current sheet name. That’s the same sheet name as the cell you are working in.
- Get all the sheet names. A full list of all the sheet names.
- Get the name of the Spreadsheet file.
The above picture is pretty self-explanatory. If you type in:
=SHEETNAME(#)
Where “#” is a number 0, 1 or 2 you will get the results displayed in the picture. Any other number will display an error.
How do I add this amazing Custom Function To my Google Sheet?
First copy the code below. Then go to <Extensions>>App Script> This will open the Google Apps Script Editor.
Next, rename the project to whatever you want. I usually name it the same as my sheet.
Next, delete all the code in the Code.gs file and paste in our code.
Select save in the header bar in the Project and then return to your spreadsheet and give it a try.
(Note: the gif below is an outdated visual but the steps above are correct)
The Code
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
/** * Gets the Sheet Name of a selected Sheet. * * @param {number} option 0 - Current Sheet, 1 All Sheets, 2 Spreadsheet filename * @return The input multiplied by 2. * @customfunction */ function SHEETNAME(option) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet() var thisSheet = sheet.getName(); //Current option Sheet Name if(option === 0){ return thisSheet; //All Sheet Names in Spreadsheet }else if(option === 1){ var sheetList = []; ss.getSheets().forEach(function(val){ sheetList.push(val.getName()) }); return sheetList; //The Spreadsheet File Name }else if(option === 2){ return ss.getName(); //Error }else{ return "#N/A"; }; }; |
You can learn more about how to build a Custom Function by following my tutorial:
Application
SHEETNAME can be used in conjunction with other built-in functions in Google Sheets.
It can be used in part, or as a whole as a naming or titling tool. For example, imagine our Spreadsheet file title is 2018-2019 Top Stocks and our Sheet Name for the current tab is Tech. Perhaps when we duplicate this file each year we just want the title inside our Tech Sheet to automatically update:
|
1 |
=SHEETNAME(1) & "-" & SHEETNAME(0) |
Which would generate:
2018-2019 Top Stocks-Tech
Another option might be a referencing tool to quickly create formulas in other sheets. Take a look at this example:
Conclusion
So, I would be interested to see what you use this custom function for if you found it useful.
Have you done anything interesting with the code? I would love to see how you have advanced the Google Custom Function.
Need help with Google Workspace development?
My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
~Yagi
Thank you so much It work very well
Great to hear!
Hi,
Is it possible to modify the code so that the function returns the name of a spreadsheet (option 2), but given a hyperlink of a spreadsheet?
Thanks in advance.
Hi Jason,
You can get the current spreadsheet’s URL with the getURL method.
You can certainly grab directory information for your folders using the driveApp class, however, you won’t be able to use a custom function to run this due to the user permissions it required to run it.
~Yagi
Thank you so much!
You’re welcome. Best of luck on your project.
Trying to adjust it to give me the name of ANY ONE sheet. I’m assuming this is possible but I can’t find the right method. Anyone?
How would I import the SPREADSHEET NAME from another document?
I use this code to have a template and then make a new sheet.
I have an area on my sheet that is blacked out. When I Make a Copy of my sheet it is supposed to change names
However it does not work??
When i create the new worksheet the name retains the old sheet? Not sure why?
Should this script update as sheets are created, deleted, renamed, etc? I’m not finding that to be the case. Any advice?
Hi Nathan,
No. This is a shortfall of this code. Perhaps the best solution is to have a “Notes” sheet table with the custom function that you can quickly update with any changes and then reference that sheet tab and range in your other tabs.
Cheers,
Yagi
Sorry but what do you mean Yagi
Is it somehow possible? 😀 Would love that feature since we create new sheets pretty often. Otherwise great work.
Great! I did slightly append the script to refresh with a trigger but it worked great for me! ty ty ty!
We change the first tab in the workbook monthly to keep track of orders and archive the old tabs (move them to the end of the workbook). Since the first tab changes, it’s hard to call functions to it. Now i can just run this script on another tab and call the first cell with option one and it preserves the tab order! 🙂
Awesome work Niel!
Hi Niel, how did you get it to refresh? I have added a trigger but I have to change =sheetname(1) to =sheetname(0) and then back to =sheetname(2) in order to get the list to refresh with new sheets. Thanks for any input you can share.
Can you share your code Neil? I’ve added one but for some reason it only works when I hit “save” on the script and nothing happens when I hit the trigger
Hi, thanks for your code.
Can I use this code to discovery the file name of others files? With de “importrange” formula?
Tks
Hi Fabio,
Not this specific function, but you could perhaps create a separate sheet tab in the sheet you are importing and run this function. Alternatively you could explore the DriveApp Class. It is incredibly powerful.
I even have a few tutorials on DriveApp to get you started:
DriveApp Tutorials
Cheers,
Yagi
Hi Yagi,
How to get the Spreadsheet name in column (A) and the Sheet name in column (B) when someone enters the link of that Sheet in column (C)?
Hi,
I’m new to this kind of thing, and I’m trying to change your code in order to get a list os sheets only for tabs with a certain color. So far, I manage to bring out only color names. Do you know how could I do it? Thanks!
//All Sheet Colors in Spreadsheet
}else if(option === 3){
var sheetList = [];
ss.color = “#00ff00”.forEach(function(val){
sheetList.push(val.getTabColor())
});
return sheetList;
Sorry, it works
Is it possible to write a script to name tabs with multiple dynamic variables?
ie: tab name = “vehicleId | techId | startDate to endDate”
What I figured out is to create a check box in another cell and use that cell as the reference for =SHEETNAME(K2) for example. The check box has true = 1 and false = 0.
I then had some help on getting a custom script written in the Apps Scripts (Tools > Script Editor)
function check(){
const range = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName(‘Dashboard’)
.getRange(‘K2’);
range.uncheck();
//Refresh spreadsheet and wait .5 seconds.
SpreadsheetApp.flush();
Utilities.sleep(200);
range.check();
}
Then over in the Triggers section (still in the Script Editor section, look for the clock icon),
I just told it to run the check function, it is time-driven, and selected how often I wanted it to refresh.
I’m getting an extra blank row. I didn’t notice it until I put in a script that pulls the formula to the last row. It then causes some really strange behavior. Any ideas? Is there a way to get that behavior to stop?
I have 5 tabs and when it refreshes, there are 6 though the last one is blank (except now it’s pulling in broken formula code).
Hi Holla,
I am not too sure where the blank row is coming from, the data should update with the same number of sheets that you have in your workbook each time. Do you have any hidden sheet tabs?
~Yagi
Can you do it with getting all sheet name that starts with a certain letter? For example i want to get all sheet names that starts with MM, like MM 1, MM 2, MM 3, then exclude other tabs like sheet 1 sheet s2 sheet 3 names. Finally, make those sheet name list as a data validation?