How to get list of sheets names in Google sheets?

Google Sheets is a cloud-based spreadsheet program developed by Google. It allows users to create, edit and collaborate on spreadsheets online, in real-time with other users. Google Sheets is similar to Microsoft Excel but with added features that make it more collaborative and accessible. Users can create and format cells, import data from other sources, and use functions and formulas to perform calculations and analyze data. Sheets can also be shared with others for real-time collaboration and editing, making it a useful tool for teams and organizations. It can be accessed through any web browser and is available as a mobile app for Android and iOS devices. In this article will guide users about the process of obtaining sheet name. This article breaks down the approach into two examples. First example guide user about the process of obtaining the current sheet name by using the script code, and second example briefs the code to generate the list of all the available google sheet on the current sheet.

Example 1: Example to display the name of current google sheet, within the active cell that generate the function call

Step 1

For this example, first open the google sheet. Now, create 5 sheets with different names. for this example, the name provided to sheet, is listed in the below given snapshot. The main purpose of this example is to display the name of current google sheet within the cell, that calls the written method.

Step 2

To execute the code, go to the “extensions” tab, and then click on “Apps Script”. The reference image for the same is given below:

Step 3

The above provided step will open the code area for google sheet. Consider image for proper reference:

Step 4

Copy below given code to editor:

//function header
function name_of_sheet() 
{
  // returning spreadsheet name to the cell
  // that calls the method
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}// end of function header

Write code properly to avoid, runtime errors. Consider image for proper reference:

Step 5

After that come back to the google sheet, and select any cell. for this example, will select the B3 cell, and type “=name_of_sheet()” within the cell, and press “Enter” key. The cell will display the name of current google sheet.

Step 6

The proper output snapshot is given below:

Example 2: To display the list of available google sheet, to the current sheet that generate the function call

Step 1

In this example, the main focus is to display the list of available google sheets, to the current sheet. To do so, again consider the same sheet. Go to the “Extensions” tab and click on “App Script” option. Consider the snapshot provided below to understand steps clearly.

Step 2

The above step will open a code area. Simply type or paste the below given code to code editor:

// define function header
function list_of_sheet_names() 
{ 
  //declaring required variables
  var d_out = new Array()
  // to access the required sheet names
  var name_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  // use for loop to read name for all the available sheets
  for (var i=0 ; i<name_sheet.length ; i++) d_out.push( [ name_sheet[i].getName() ] )
  // return statement for function definition 
  return d_out  
}

Step 3

After properly typing the code, code area will appear as given below:

Step 4

After that come back to the google sheet, and select any cell. for this example, will select the B3 cell, and type “=list_of _sheet_name()” within the cell, and press “Enter” key. The cell will display the name of available sheet within the current google sheet.

Step 5

Final obtained list is displayed below:

Conclusion

After going to the above article, the user will understand the process of generating current sheets as well as the process of generating the list of the sheets. All the provided steps are precise, detailed, and thorough. Both the examples contain proper stepwise explanation for the provided task. Output snapshots are properly provided at the end of both the examples. Try to perform same task by yourself this will increase the knowledge to a great extent.