Show Specific Tabs based on drop-down selection in Google Sheets

Question

I am a script newbie and was hoping to get some help with the following Google Sheets exercise.

I currently have 2x tabs in this Google Sheet document: 'Class List' and 'Template'. I am currently running a script that allows names to be added to the 'Class List' and then a tab is generated for each of these names based on the 'Template' page layout.

I was wondering if it is possible to hide all of the tabs and create a'Welcome/Home Page' tab with a drop down list of all the names. Once a selection is made from the drop-down, it will show the user that sheet with the same name (while keeping all of the others hidden).

The aim of this script would be to show only one selected sheet at a time (and the drop-down list would exclude the 2x original 'Class List' and 'Template' tabs. The List of Names and their corresponding tabs would be constantly changing and updating.

Is this possible? Thank you

NaziA NaziA · Accepted Answer · 2021-02-01T16:23:57

This is a sample working code and should be readable to you.

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var classListSheet = spreadsheet.getSheetByName("Class List");

function createMainSheet(){
  // Create mainsheet if it isn't existing
  if(!spreadsheet.getSheetByName("Welcome/Home Page")){
    spreadsheet.insertSheet("Welcome/Home Page"); 
  }
  
  var mainSheet = spreadsheet.getSheetByName("Welcome/Home Page");

  // Hide all other sheets AFTER creating main as you need at least 1 shown sheet
  var sheets = spreadsheet.getSheets();
  sheets.forEach(function (sheet) {
    if(sheet.getName() != "Welcome/Home Page") {
      sheet.hideSheet();
    }
  });

  // Range where names are
  var dynamicList = classListSheet.getRange("A:A");
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInRange(dynamicList).build();
  // Create dropdown in mainSheet on A1
  // Drop down updates when you add name on dynamicList range (column A in Class List)
  mainSheet.getRange("A1").setDataValidation(rangeRule);
}

function onEdit(e) {
  // If editing mainSheet
  if(spreadsheet.getActiveSheet().getSheetName() == "Welcome/Home Page"){
    var mainSheet = spreadsheet.getSheetByName("Welcome/Home Page");
    // Get value of dropdown
    var name = mainSheet.getRange("A1").getValue();
    // Hide old chosen sheet if existing 
    // Drop down default value when created is blank
    // It will fail the first time you change if this isn't added
    if(spreadsheet.getSheetByName(e.oldValue)) {
      spreadsheet.getSheetByName(e.oldValue).hideSheet();
    }
    // Show new chosen sheet
    spreadsheet.getSheetByName(name).showSheet();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName(name));
  }
}

function addName(){
  // Creating dummy function for adding names and tabs on Class List sheet
  var name = "Name" + (classListSheet.getLastRow() + 1);
  // Append "Name<rowNumber>" on column A in Class List sheet
  classListSheet.appendRow([name]);

  // Insert newly added sheet then hide
  spreadsheet.insertSheet(name);
  spreadsheet.getSheetByName(name).hideSheet();
}

You might need to modify some details above like the range where the names are located and the location of the dropdown, but that should be relatively easy.

Class List:

Main Sheet:

Show Specific Tabs based on drop-down selection in Google Sheets

1 Answers