Google App Scripting - GAS : Script to get an automatic dropdown with a list of all the sheets in the workbook

Here's a script you can use to automatically populate a dropdown in a Google Spreadsheet containing a list of all the sheets in the workbook.

Sources :

  • How To Get List Of Sheets Names In Google Sheets? - ExtendedOffice.com
  • Multi-Row Dependent Dropdown List in Google Sheets -  BpWebs.com

Enjoy !

//source : https://www.extendoffice.com/documents/excel/5222-google-sheets-get-list-of-sheets.html

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 

}

//source : https://www.bpwebs.com/multi-row-dependent-dropdown-list-in-google-sheets/
//CREATE DROPDOWN LIST
function Dropdown() {
  /* SET FOLLOWING VARIABLES */
  
  var data = sheetnames();
  
  var dropSS           = "Template";      //Name of the sheet which dropdown list to be created
  var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange("B2"); //Range where the dropdown is created
  var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(data).build();
  primaryDropRange.setDataValidation(validationRule);
}