Log in
x
or
x
x
Register
x

or

How to get list of sheets names in Google sheets?

This article, I will introduce some methods to get the name of current sheet name or a list of sheet names in Google sheets.

Get the current sheet name in Goolge sheets with script

Get a list of sheet names in Google sheets with script


List all sheet names and navigate between them by using hyperlinks or buttons in Excel workbook:

With Kutools for Excel's Create List of Sheet Names utility, you can quickly list all worksheet names in a new sheet with hyperlinks or macro buttons, then you can navigate between them to your need.

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Get the current sheet name in Goolge sheets with script

To get the current sheet name in Google sheets, the following simple script can help you without entering the name manually, please do as this:

1. Click Tools > Script editor, see screenshot:

doc get sheet name 1

2. In the opened project window, copy and paste the below script code into the blank Code window, see screenshot:

function sheetName() {
  return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
}

doc get sheet name 2

3. Then save the code window, and go back to the sheet that you want to get its name, then enter this formula: =sheetname() in a cell, and press Enter key, the sheet name will be displayed at once, see screenshot:

doc get sheet name 3


Get a list of sheet names in Google sheets with script

If you want to get all sheet names in the Google sheets, here is another script can do you a favor, please do as this:

1. Click Tools > Script editor to go the project window, and then copy and paste the below script code into the blank Code window, see screenshot:

function sheetnames() { 
  var out = new Array()
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) out.push( [ sheets[i].getName() ] )
  return out  
}

doc get sheet name 4

2. Then save the code window, and go back to the sheet that you want to get all sheet names, then enter this formula: =sheetnames() in a cell, and press Enter key, all sheet names in the Google sheets will be listed as following screenshot shown:

doc get sheet name 5


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Imran Joseph · 6 months ago
    For everyone wondering how to get the sheet index to update automatically, you need to be able to call the function and then Re-enter the formula into where you want the index to be kept.
    In my case I have a dedicated Index Sheet, and after most of my functions I call 'sheetnames()' and then just Re-enter the formula in the specific cell.
  • To post as a guest, your comment is unpublished.
    guillermo · 8 months ago
    me pueden ayudar para crear una macro en GAS que abra una nueva hoja (sheet), luego la renombre y luego se puede escribir dentro de esta hoja nueva?
  • To post as a guest, your comment is unpublished.
    Simon · 1 years ago
    Hi, thanks for the post, it works great. I am now wondering how I can make it update the sheet automatically? Thanks
    • To post as a guest, your comment is unpublished.
      JP · 7 months ago
      Me too... I read in forum that is not possible to update automatically... as the real name of the sheet (sheet id: which is a number) does not change when you change the name of the tab. Would love to know is someone came up with a way around this. Great post btw.
    • To post as a guest, your comment is unpublished.
      slp · 9 months ago
      I have the same question