1

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.

2 Answers 2

3

You can now use newDataValidation() to build a data validation rule.

Example:

// Set the data-validation rule for cell A1 to require a value from B1:B10.
var cell = SpreadsheetApp.getActive().getRange('A1');
var range = SpreadsheetApp.getActive().getRange('B1:B10');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range)
    .build();
cell.setDataValidation(rule);

Source: https://gsuite-developers.googleblog.com/2013/08/answering-another-top-request-data.html

Sign up to request clarification or add additional context in comments.

Comments

1

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);

      }
    }
  }
}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.