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

how to Get Sheet Name in Google Sheets 33

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.

See also  How to Make Table of Contents in Google Sheets (3 Methods)

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.

See also  How to Sort in Google Sheets (Complete Guide)

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

how to Get Sheet Name in Google Sheets 1

Step 2

Go to Extensions > Apps Script

how to Get Sheet Name in Google Sheets 2

Step 3

Remove any function if already written

how to Get Sheet Name in Google Sheets 3

Step 4

Now start writing the code, create a function block

how to Get Sheet Name in Google Sheets 4

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.

how to Get Sheet Name in Google Sheets 5

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();

how to Get Sheet Name in Google Sheets 6

Step 7

Close the function block by closing the } curly bracket

how to Get Sheet Name in Google Sheets 7

Step 8

Click on the Save button to save the script

how to Get Sheet Name in Google Sheets 8

Step 9

Click on Run

how to Get Sheet Name in Google Sheets 9

Step 10

It will ask to allow access to your review permissions

how to Get Sheet Name in Google Sheets 10

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

how to Get Sheet Name in Google Sheets 11

Step 12

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

how to Get Sheet Name in Google Sheets 12

Step 13

Click on “Go to Untitled project (unsafe)”

how to Get Sheet Name in Google Sheets 13

Step 14

Click on Allow

how to Get Sheet Name in Google Sheets 14

Step 15

The function will start execution

how to Get Sheet Name in Google Sheets 15

Step 16

Execution completed

how to Get Sheet Name in Google Sheets 16

Step 17

Now copy the function name

how to Get Sheet Name in Google Sheets 17

Step 18

Go to your sheets file (in the previous tab)

how to Get Sheet Name in Google Sheets 18

Step 19

Call the function using the below syntax

how to Get Sheet Name in Google Sheets 19

=functionname()

Step 20

Press Enter key

It is loading, may take some time

how to Get Sheet Name in Google Sheets 20

Step 21

Here is your sheet name

how to Get Sheet Name in Google Sheets 21

Step 22

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

how to Get Sheet Name in Google Sheets 22

Tip: If a sheet name is changed, it will not update the previously called functions result even after refreshing the sheet, but when you call the function after the sheet is renamed, then you will get the new name.

how to Get Sheet Name in Google Sheets 23

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.

See also  How to Name a Column in Google Sheets [2 Methods]

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

how to Get Sheet Name in Google Sheets 24

Step 2

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

how to Get Sheet Name in Google Sheets 25

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

how to Get Sheet Name in Google Sheets 26

Step 4

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

how to Get Sheet Name in Google Sheets 27

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

how to Get Sheet Name in Google Sheets 28

Step 6

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

how to Get Sheet Name in Google Sheets 29

Step 7

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

how to Get Sheet Name in Google Sheets 30

Step 8

Click on run

how to Get Sheet Name in Google Sheets 31

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.

how to Get Sheet Name in Google Sheets 32

Step 10

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

how to Get Sheet Name in Google Sheets 33

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

how to Get Sheet Name in Google Sheets 34

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

  • Google apps script works online and will not be executed if there is no internet connection.
  • Google apps script is just like VBA for Microsoft excel.
  • Google Apps Script is powered by scripting programming and can be programmed using JavaScript code
  • There are thousands of things you can do using the google apps script, getting the sheet name is just a simple thing performed by the app’s script code.
  • The case used is motley the camel case for variable and function name but it’s not mandatory. The Google apps script is case-sensitive.

See also  Turn On Dark Mode in Google Sheets [Desktop and Mobile]

Frequently Asked Questions

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

We will be happy to hear your thoughts

Leave a reply

OfficeDemy.com
Logo
Enable registration in settings - general