I have a Google drive spreadsheet with 20 sheets.

Is it possible to list all the sheet names, in an index sheet?

  • Please add a brief description of your search/research efforts as is suggested on How to Ask.
    – Rubén
    Sep 20 '19 at 14:06
  • Have you tried the solution offered? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. Sep 20 '19 at 17:05

There is a widely used script for this:

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 

Following that, you refer to it by placing =sheetnames() in a cell.

PS: It is a very useful way when you make a range out of it and use it in formulas.


Here's a more concise version which is easy to achieve because the return type of getSheets is Sheet[].

function sheetnames() {
  return SpreadsheetApp
    .map(s => s.getName())

NOTE: This requires to use the Google Apps Script runtime (Chrome V8). It's the default runtime for new projects.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.