I have a script that filters from a database and creates dependant drop down lists based on initial inputs. I'm wondering is there anyway to set a default value to appear in these drop downs? Ideally so there are automatically set to the first value in the drop-downs?

I think I should be able to modify the applyValidationToCell function but I'm not sure how?

Any help would be appreciated

var mainWSname = "Sheet1"
var optonsWSname = "Sheet2"
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWSname);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optonsWSname);
var options = wsOptions.getRange(2, 15, wsOptions.getLastRow()-1, 3).getValues();
var firstLevel = 3
var secondLevel = 4
var thirdLevel = 5


function onEdit(e){
 
  var activeCell = e.range;
  var r = activeCell.getRow()
  var c = activeCell.getColumn()
  var val = activeCell.getValue();
  var wsName = activeCell.getSheet().getName()
  
  if(wsName === mainWSname && c === firstLevel && r > 76){
    
    applyFirstLevelValidation(val, r)
    
  }  else if(wsName === mainWSname && c === secondLevel && r >76){
  
    
  applySecondLevelValidation(val, r)
  
  }
  
} /// end onEdit

function applyFirstLevelValidation(val, r){

if(val === ""){
    ws.getRange(r,secondLevel).clearContent();
    ws.getRange(r,secondLevel).clearDataValidations();
    ws.getRange(r,thirdLevel).clearContent();
    ws.getRange(r,thirdLevel).clearDataValidations();
    } else {
      
    ws.getRange(r,secondLevel).clearContent();
    ws.getRange(r,secondLevel).clearDataValidations();
    ws.getRange(r,thirdLevel).clearContent();
    ws.getRange(r,thirdLevel).clearDataValidations()
     var filteredOptions = options.filter(function(o){return o[0] === val})
     var listToApply = filteredOptions.map(function(o) {return o[1] })
     var cell =  ws.getRange(r,secondLevel)
    
     applyValidationToCell(listToApply, cell)

    }

} //end applyFirstLevelValidation

function applySecondLevelValidation(val, r){

if(val === ""){
  
    ws.getRange(r,thirdLevel).clearContent();
    ws.getRange(r,thirdLevel).clearDataValidations();
      
    } else {
      
     ws.getRange(r,thirdLevel).clearContent()
     var firstLevelColValue = ws.getRange(r, firstLevel).getValue();
     var filteredOptions = options.filter(function(o){return o[0] === firstLevelColValue && o[1] === val })
     var listToApply = filteredOptions.map(function(o) {return o[2] })
     var cell =  ws.getRange(r,thirdLevel)
    
   applyValidationToCell(listToApply, cell)
    }

} //end


function applyValidationToCell(list, cell){
  
  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build();
  
  cell.setDataValidation(rule)
  
}

CodePudding user response：

You can create data validations as lists and then put them in any range you wish:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  let r = SpreadsheetApp.newDataValidation().requireValueInList(['1','2','3','4','5']).build();
  sh.getRange(1,1,5,1).setDataValidation(r);
}

DataValidation RequireValueInList

CodePudding user response：

In your script, how about the following modification? In this modification, the function applyValidationToCell is modified.

From:

cell.setDataValidation(rule)

To:

cell.setDataValidation(rule).setValue(list[0]);

By the way, when the above modification is reflected in your script, 2nd data validation shows the initial value. But, in this case, the 3rd data validation is not shown. Because the 2nd data validation is not edited. So, when you want to put 2nd and 3rd data validations with the initial value when the 1st value is put to the column "C", how about the following modification? In this case, please modify your functions of applyValidationToCell and applyFirstLevelValidation as follows.

applyValidationToCell:

function applyValidationToCell(list, cell) {
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(list).setAllowInvalid(false).build();
  cell.setDataValidation(rule).setValue(list[0]); // Modified
}

applyFirstLevelValidation:

function applyFirstLevelValidation(val, r) {
  if (val === "") {
    ws.getRange(r, secondLevel).clearContent();
    ws.getRange(r, secondLevel).clearDataValidations();
    ws.getRange(r, thirdLevel).clearContent();
    ws.getRange(r, thirdLevel).clearDataValidations();
  } else {
    ws.getRange(r, secondLevel).clearContent();
    ws.getRange(r, secondLevel).clearDataValidations();
    ws.getRange(r, thirdLevel).clearContent();
    ws.getRange(r, thirdLevel).clearDataValidations()
    var filteredOptions = options.filter(function (o) { return o[0] === val })
    var listToApply = filteredOptions.map(function (o) { return o[1] })
    var cell = ws.getRange(r, secondLevel)
    applyValidationToCell(listToApply, cell)

    applySecondLevelValidation(listToApply[0], r); // Added
  }
}

Note:

• If the above modifications were not the result you expect, can you provide the sample Spreadsheet for replicating the issue? By this, I would like to confirm it.