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

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

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.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get you from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

 

 

~Yagi

22 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”

  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.

      ~Yagi

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

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

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

      Cheers,

      Yagi

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

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

  6. Hi, thanks for your code.

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

    Tks

    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

      Cheers,
      Yagi

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

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

Leave a Reply