Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function

Get Sheet Names and Spreadsheet Names: Google Apps Script

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 thing in Google Sheets: 

  1. Get the current sheet name.  That’s the same sheet name as the cell you are working in.
  2. Get all the sheet names. A full list of all the sheet names. 
  3. Get the name of the Spreadsheet file. 
Get Sheet Names and Spreadsheet Names Only - Google Apps Script

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 <Tools>>Script Editor> 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. 

<File><Save> in the Project and then return to your spreadsheet and give it a try. 

The Code

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:

Which would generate:

2018-2019 Top Stocks-Tech

Another options might be a referencing tool to quickly create formulas in other sheets. Take a look a this example: 

Get SheetName Example Google Apps Script

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.

~Yagi

Leave a Reply