how to create dynamic dropdown list in the Google

问题:

I would like to dynamically change the value of the candidate list in the cell validation feature of a Google Spreadsheet using the Google Apps Script.

I failed to create dynamic dropdown list in a Google Spreadsheet using the Google Formulas Feature. In Excel, it is possible in Excel Spreadsheet using the INDIRECT function set in the cell where the dropdown list feature works, referring the cell the value of which is set the list range name dynamically.

回答1:

Unfortunately it's not possible to manage cell validation using Apps Script. There's an enhancement request opened regarding this. You should star it to keep track of updates and kind of vote for it.

Also, Excel INDIRECT feature does not work for Google Spreadsheets.

回答2:

Times have changed see this blog post

My spreadsheet example that could use some improvements

The blog post a simple way to do it. My code starter is

function loadMenu() {
  var ga = SpreadsheetApp.getActive();

  var firstMenu = 'B3:B4';
  var firstValues = 'E3:F3';
  var staticValue1 = 'E3';
  var staticValue2 = 'F3';

  var secondMenu = 'C';
  var secondMenuRow = 3;
  var secondMenu1 = 'E4:E';
  var secondMenu2 = 'F4:F';

  dynamicDrop(ga, firstMenu, firstValues, staticValue1, staticValue2, secondMenu, secondMenuRow, secondMenu1, secondMenu2);

}


function dynamicDrop(ga, fm, fv, sv1, sv2, sm, smr, sm1, sm2) {

  //first drop choices
  var cells = ga.getRange(fm);
  var range = ga.getRange(fv);
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
  cells.setDataValidation(rule);

  // set as static value
  var staticVar1 = ga.getRange(sv1).getValue();

  // set as static value
  var staticVar2 = ga.getRange(sv2).getValue();

  // set to chosen value of fm
  var chosenVar1 = ga.getRange(fm).getValues();

  for (var row in chosenVar1) {

    for (var col in chosenVar1[row]) {

      if (chosenVar1[row][col] == staticVar1) {

        num = parseInt(row) + smr;
        var cells = ga.getRange(sm+num);
        var range = ga.getRange(sm1);
        var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
        cells.setDataValidation(rule);

      } else if (chosenVar1[row][col] == staticVar2) {

        num = parseInt(row) + smr;
        var cells = ga.getRange(sm+num);
        //range is Static values
        var range = ga.getRange(sm2);
        var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
        cells.setDataValidation(rule);

      }
    }
  }
}