Take list of sheets and create dropdown onSelectionChange-CodePudding

was asked in 2018 Turning a list of sheet names into a data validation I want to create a dataValadation dropdown by getting a list of sheets onSelectionChange(e). I can get a list for the sheets I want but it won't make a dropdown. I am trying to make it so when you make a new sheet and go back to the "Main" the "A1" cell will create a new dropdown with the added sheet.

function onSelectionChange(e) {
  const src = e.source.getActiveSheet();
  if (src.getName() == 'Main'){ 
    const excluded = src.getSheets().filter(sheet =>!["DontAdd"].includes(sheet.getName()));
    var names = excluded.map(function(s) {
            return [s.getName()];
        })
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(names).build();
    src.getRange("A1").setDataValidation(rule);
  }
}

Tried putting it into a list of names but still no go.

CodePudding user response：

I did not tested this solution, but something like this should put dropdown from the list:

function onSelectionChange(e) {
  const src = e.source.getActiveSheet();
  if (src.getName() == 'Main'){ 
    const excluded = src.getSheets().filter(sheet =>!["DontAdd"].includes(sheet.getName()));
    var names = excluded.map(function(s) {
            return [s.getName()];
        });
    let range = src.getRange("Z1:Z"   names.length);
    range.setValues(names);
    var rule = SpreadsheetApp.newDataValidation().requireValueInList(range, true).build();
    src.getRange("A1").setDataValidation(rule);
  }
}

You can check it as regular function (without onSelectionChange trigger first) to be sure...

CodePudding user response：

need to use SpreadsheetApp.getActiveSpreadsheet() instead of src

function onSelectionChange(e) {
      const src = e.source.getActiveSheet();
      if (src.getName() == 'Main'){ 
        const excluded = SpreadsheetApp.getActiveSpreadsheet().getSheets().filter(sheet =>!["DontAdd"].includes(sheet.getName()));
        var names = excluded.map(function(s) {
                return [s.getName()];
            });
        var rule = SpreadsheetApp.newDataValidation().requireValueInList(names, true).build();
        src.getRange("A1").setDataValidation(rule);
      }
    }