How do you reject input if criteria of REGEX expression is not a match to a cells input using Google apps script

Question

So i have a simple data validation working using Google apps script. The only problem is the user can still input whatever they like (they simply just get that red invalid input marker in the top right corner of the cell to inform them the input is incorrect). How would I be able to make it so that the input is actually rejected from the cell if invalid?

Any help would be greatly appreciated! thank you!

Answer 1

You need to use .setAllowInvalid(false)

function onOpen(e) {
  var range = SpreadsheetApp.getActive().getRange('B2:B');
  var rule = SpreadsheetApp.newDataValidation()
    .requireFormulaSatisfied('=REGEXMATCH(B2,"([#0-9a-fA-F]){7}|green|red|blue|purple|yellow|orange|brown|grey|gray|pink|black|white|lime|cyan")')
    .setAllowInvalid(false) // Set to false to reject input
    .build();
  range.setDataValidation(rule);
}