How to list or index all sheets/tabs in Google Sheets with formulas (no scripts)

2
votes

I have a Google Sheets documents with several tabs that every so often get their positions rearranged or where new tabs get added. Is there a way to create an index of tabs with formulas
(no scripts) on a column? Due to work constraints (safety and connection policies and guidelines for the multiple users), scripts can't be implemented.

I'm adding the links for scripting solution for anyone that might be looking for this and can use them:

Is there a Google Sheets formula to put the name of the sheet into a cell?

How do I dynamically reference multiple sheets on Google SpreadSheets?

1
google-apps-scriptindexinggoogle-sheetsgoogle-sheets-formula

1 Answers

2
votes

without a script, it is not possible.

___________________________________________________________

a scripted solution would be:

function SHEETLIST() {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1 ) ;
  out[0] = [ "NAME" , "#GID" ];
  for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = 
  [sheets[i-1].getName() , sheets[i-1].getSheetId() ];
  return out
}
catch( err ) {
  return "#ERROR!" 
}
}

and then formula in some cell:

=SHEETLIST()

for sheet order it would be:

function SHEET(input) {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() ;
  if( (input>0) && (input <= sheets.length)) return sheets[(input-1)].getName() ;
  else return "invalid sheet #" ;
}
catch( err ) {
  return "#ERROR!" 
}
}

and formula returning 2nd sheet from left:

=SHEET(2)