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

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 things 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:


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 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. 

App Script Save Button
Click to Expand!

(Note: the gif below is an outdated visual but the steps above are correct)

The Code

You can learn more about how to build a Custom Function by following my tutorial:

Hire a Google Workspace Developer for your Business Needs


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

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

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

Get SheetName Example Google Apps Script


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.

Create and Publish a Google Workspace Add-on with Apps Script Course

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.

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


28 thoughts on “Google Apps Script – How to Get the Sheet Name and Spreadsheet Name and add to a Cell on Google Sheets with a Custom Function (Updated Feb 2022)”

  1. 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.

    1. 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.


  2. You’re welcome. Best of luck on your project.

  3. 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?

  4. How would I import the SPREADSHEET NAME from another document?

  5. 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?

  6. Should this script update as sheets are created, deleted, renamed, etc? I’m not finding that to be the case. Any advice?

    1. 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.



      1. Is it somehow possible? ? Would love that feature since we create new sheets pretty often. Otherwise great work.

  7. 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! ?

    1. 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.

    2. 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

  8. Hi, thanks for your code.

    Can I use this code to discovery the file name of others files? With de “importrange” formula?


    1. 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


  9. 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)?

  10. 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){
    return sheetList;

  11. Is it possible to write a script to name tabs with multiple dynamic variables?

    ie: tab name = “vehicleId | techId | startDate to endDate”

  12. 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


    //Refresh spreadsheet and wait .5 seconds.


    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.

  13. 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).

    1. 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?


  14. 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?

Leave a Reply