7

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?

0

2 Answers 2

10

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

1
  • any idea how often it refreshes? I changed the name of the sheets, was wondering how long it takes for it to update
    – jason
    Apr 19 at 15:16
13

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

3
  • I tried using this script but it throwing below errors: [3, 16] Cannot find name 'SpreadsheetApp'. [3, 7] Office Scripts cannot infer the data type of this variable. Please declare a type for the variable. [2, 7] Office Scripts cannot infer the data type of this variable. Please declare a type for the variable. [1, 10] No function of name 'main' found. Ensure the function exists and is not nested in anything.
    – Pawan
    Feb 8, 2022 at 22:03
  • I´m having tha same problem as described by @Pawan: "Cannot find name 'SpreadsheetApp'." "Office Scripts cannot infer the data type of this variable. Please declare a type for the variable." Someone can help us? Feb 24, 2023 at 21:05
  • 1
    @Pawan: It looks that you are trying to use code for Google Apps Script in Office Scripts, unfortunatelly that isn't possible. Feb 24, 2023 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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