CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sregiters
Sregiters

Reputation: 11

how to create dynamic dropdown list in the Google apps script

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.

Upvotes: 1

Views: 5467

Answers (2)

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17792

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

Upvotes: 3

CrandellWS
CrandellWS

Reputation: 2804

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

      }
    }
  }
}

Upvotes: 1

Related Questions