I'm currently trying to create a data validation using appscript. The aim here is to check the value of 2 different cells then put a validation on a 3rd cell.
Say If C3=Office and D3=Retail then a data validation should be applied on cell F3 where the user can only put the numbers between 50-100.
This code works if I only use 1 cell like C3 but doesn't do anything if I also add D3.
Can someone please help me with this?
Thanks!
function onEdit() {
var ss = SpreadsheetApp.getActive();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('final rough');
var cell2 = sheet.getRange("C3");
var cell3 = sheet.getRange("D3");
var cell4 = sheet.getRange("F3");
var rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(50, 100)
.setAllowInvalid(false)
.build();
if(cell2 == "Office" && cell3 == "Retail"){
cell4.setDataValidation(rule);
}
}
CodePudding user response:
Make sure you're checking the actual values of the cells:
function onEdit() {
var ss = SpreadsheetApp.getActive();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('final rough');
var cell2 = sheet.getRange("C3").getValue(); //<< Get the values
var cell3 = sheet.getRange("D3").getValue(); //<< of these cells
var cell4 = sheet.getRange("F3");
var rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(50, 100)
.setAllowInvalid(false)
.build();
if(cell2 == "Office" && cell3 == "Retail"){
cell4.setDataValidation(rule);
}
}