Data validation using appscript

July 12, 2022

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

>Solution :

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