Is there a formula that will return a list of all the sheet names in a spreadsheet?

Question

I have a Google spreadsheet containing 20 sheets.

Is it possible to create a formula that returns a current list of all the sheet names in the spreadsheet?

Answer 1

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.

References

• Google Developers > Google Apps Script > Extending Google Sheets

Answer 2

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

function sheetnames() {
  return SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()
    .map(s => s.getName())
  ;
}

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

References

• Google Developers > Google Apps Script > Extending Google Sheets