How to Get Sheet Name using Script in Google Sheets [2 Methods]

Hi. In this article, we are going to learn how to get sheet name in google sheets. Getting sheet names in google sheets does not make any sense in a normal situation. But in a situation where you have a lot of sheets, and somehow you can not access the sheet names may be protected by the owner. In this case, you can use Google App script to write a piece of code that is a function and you can get the sheet name using the app script code. Now, don’t worry if you are afraid of programming or coding.

This is not like a coding tutorial, it’s going to be extremely easy and I will keep it as simple as it can be to make you understand, another thing, google sheets is a highly interactive application, similarly, google sheets coding is not that difficult as you think it can be. It’s very similar to JavaScript, and if you know only basic JavaScript, you can work with it it’s easy and fun. You can do almost anything using the google apps script.

Use Case of Getting Sheet Name in Google Sheets

When you work on a collaborative sheet, you may not have full access to view the sheets name, and there can be some methods or custom functions where you need to pass the sheet name, and to keep it dynamic, you need to pass the reference of the cell and not hard code the name. If you hard code a sheet name inside a formula or function, and in the future, someone has changed the sheet name, then the function will be destroyed.

So, to avoid these we specify the sheet name programmatically and let google sheets pick the sheet name in real-time. There are so many methods and get functions that can be coded and then used in google sheets, but for the beginning, I think getting the sheet name is an ideal start to start your Google apps script learning journey. You will love it this is by google developers, and extremely easy to learn with a wide community. So back to the topic, I have told you why you need to learn how to get sheet names in google sheets.

How to Get Sheet Name in Google Sheets

So, in total, we have two scenarios, one in which we will write a code on the google app script to get the name of the active sheet, and the other scenario can be when we need to have all the sheet’s names either active or not. So, learning both of these methods, and understanding the google app script code for these methods is going to be the outcome of this specific tutorial.

How to Get Sheet Name in Google Sheets – Get Active Sheet Name

In this first section, we will learn how to get sheet name in google sheets using app script code to get the name of an active sheet in a cell. So let’s move forward, and let’s learn some basics of Google app script.

For this section, I don’t have any data in the google sheets file, I have a simple black file in which I have some sheets having different names, we will design the code and will test it on different sheets to validate if we are getting the active sheet name rightly.

Step 1

Sample sheets I have kept in a single workbook

Step 2

Go to Extensions > Apps Script

Step 3

Remove any function if already written

Step 4

Now start writing the code, create a function block

Tip: Give your function a meaningful name (It’s a good programming practice)

Step 5

This function will not perform any action, it will only going to return the sheet name, so we need to write here the return keyword.

Step 6

Now, we need to tell sheets, that what we want to be returned on the execution of this function, or when the function is called. Here, we will define that we want to get the active sheet name in return:

The syntax will be below

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

Step 7

Close the function block by closing the } curly bracket

Step 8

Click on the Save button to save the script

Step 9

Click on Run

Step 10

It will ask to allow access to your review permissions

Authorization required

This project requires your permission to access your data.

Read the message and click on review permissions

Step 11

It will ask to choose a google account, choose an account

Step 12

Read the message and caution of data uses, and then click on advanced if you want to proceed unsafely.

Step 13

Click on “Go to Untitled project (unsafe)”

Step 14

Click on Allow

Step 15

The function will start execution

Step 16

Execution completed

Step 17

Now copy the function name

Step 18

Go to your sheets file (in the previous tab)

Step 19

Call the function using the below syntax

=functionname()

Step 20

Press Enter key

It is loading, may take some time

Step 21

Here is your sheet name

Step 22

It will return the active sheet, so let’s try on other sheets

Reference Code

function GetActiveSheetName() {

return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();

}

Congratulations on your first app script code other than hello world.

Now let’s see the other method to get all sheet’s names using a similar logic but different code.

How to Get Sheet Name in Google Sheets – Get All Sheet Names

In this section, we will learn how to get sheet in google sheets of all the sheets in a workbook like an array. We will fetch it using an apps script code, so let’s start coding

Step 1

Go to Extensions > Apps Script

Step 2

First, create a function block and give your function a meaningful name

Step 3

Here, we have to create an array using a new constructor, so that all the sheet’s name can be stored inside an array

Step 4

We store this array in a variable to call it in future

Step 5

Now, we need to use a loop to get the sheet names from index zero to length-1, so we are using a for loop, we will pass the values of all the sheets name in the array using index numbers and will close the loop when the length is index-1

Step 6

Now after the loop, we will return the variable having the array

Step 7

The code is completed, now click on the save button to save the script

Step 8

Click on run

Step 9

Now back to sheets, and like the previous section, call the function using the function name starting with a = equal sign and ending with empty square brackets.

Step 10

You can see that all the values are here just like an array

Step 11

Make sure you dont have anything written in the array where your array is going to be spread, otherwise the array values will not be able to spread and you will get an error

This is how you can use any of these methods to call active or all sheets of a workbook.

I hope you find this article helpful, and now you have some knowledge about the google app script, dont worry if you dont understand the code fully, in the beginning, you should keep practicing to understand things better.

Reference Code

function GetAllTheSheetNames() {

var result = new Array()

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

for (var i=0 ; i<sheets.length ; i++) result.push( [ sheets[i].getName() ] )

return result

}

Important Notes

Frequently Asked Questions

Can I Use the Same Methods to Name a Column and Get Sheet Name in Google Sheets?

Yes, you can use the same methods to name a column and get the sheet name in Google Sheets. By using the Google Sheets API or the scripting interface, you can retrieve the names of the columns and the active sheet. This way, you can easily access and manipulate the google sheets column names for your desired actions.

Can I Use Named Functions to Get the Sheet Name in Google Sheets?

Yes, you can use advanced formulas in google sheets to get the sheet name using named functions. By utilizing a specific formula, you can retrieve the name of the current sheet without any modifications. This feature allows for efficient data organization and analysis within Google Sheets.

What is the Google apps script?

Google apps script is a platform for script programming like JavaScript. Google apps script is a google product developed by google developers. Google apps script helps us to enhance the overall functionalities of google sheets when working with any kind of data, it’s a lightweight application built-in in google sheets. It follows simple coding norms and can be easily learned by people having some basic understanding of general purpose scripting languages like JavaScript. The google developers platform is freely available for everyone to learn basic and essentials about Google app script online.

You can learn more here on the official Google Apps Script platform offered by Google.

Conclusion

Wrapping up how to get sheet name in google sheets. Firstly, it was not a programming or coding tutorial the code used in this tutorial is the most fundamental piece of code in any programming or scripting language. By this article, we meant to introduce beginners, and non-programming people to google apps script. Google apps script is getting popularity every day and can be the future of software developers and software engineers. Google apps script can be a good career path if you start learning it as a hobby and work on dummy data on google sheets along with Google apps script coding. We learned how to get active sheet names in google sheets using the google apps script, and how to get all sheet names.

I hope you find this article helpful and that you have learned something new about Google apps script. Keep learning with Office Demy.