Data validation using appscript

Question

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

kaitlynmm569 · Accepted Answer · 2022-07-12 17:15:57Z

1

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

answered Jul 12, 2022 at 17:15

kaitlynmm569

1,7251 gold badge9 silver badges19 bronze badges

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

2 Comments

Sid Over a year ago

I did, C3 = Office and D3 = Retail.

kaitlynmm569 Over a year ago

@Sid Look at my code and make sure you notice the code I added where you define cell2 and cell3. You need to use the .getValue() method on these cells in order to compare their values to a string in your if() statement.

Collectives™ on Stack Overflow

Data validation using appscript

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related