how to create spreadsheet dropdown with values from multiple tabs in Google Sheets

Question

Imagine a spreadsheet Google Spreadsheet.

Given I have multiple tabs, each tab has a name. There could be 60-80 tabs.

I want to create a dropdown on a frontpage where I can see the of each tab in the workbook.

It could look like this:

   ┌────────────────────────────────────────────────────┐
   │                                                    │
   │                                                    │
   │           Selected                                 │
   │           sheet     Value                          │
   │          ┌──────▼  ┌─────────────┐ ┌──────┐        │
   │          │sheet1│  │             │ │ Save │        │
   │          │sheet2│  └─────────────┘ └──────┘        │
   │          │sheet3│                                  │
   │          └──────┘                                  │
   │                                                    │
   │                                                    │
   │                                                    │
   ├───────────┬────────┬───────┬─────────┬───┬───┬──┬──┤
   │ Frontpage │ sheet1 │sheet2 │ sheet3  │ … │ … │… │… │
   └───────────┴────────┴───────┴─────────┴───┴───┴──┴──┘

Clearly I could build a web application and use a real database, but I want to explore what is possible with just a spreadsheet or similar.

How do I create the dropdown based on all tab names in the workbook? Is this currently possible?

Answer 1

dropdown list

To create the dropdown based on all tab names in the workbook, try

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sh = ss.getSheetByName('FrontPage')
  var sheets = []
  ss.getSheets().forEach(function(sheet){if (sheet.getName()!='FrontPage') {sheets.push(sheet.getName())}})
  var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(sheets).build()
  sh.getRange('B2').clearContent().clearDataValidations()
  sh.getRange('B2').setDataValidation(validationRule); 
}

reporting the value to the selected tab

If you want to report the value in range A1 of the selected sheet, try

function onEdit(e){
  var sh = e.source.getActiveSheet();
  var cel = e.source.getActiveRange();
  if (sh.getName()=='FrontPage' && cel.getA1Notation()=='D2'){
    try{
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sh.getRange('B2').getValue()).getRange('A1').setValue(e.value)
    }
    catch(e){}
  }
}