Creating Datavalidation with custom Formula

1
votes

I want to create DataValidation for a cell in google sheets with google app scripts, but i cannot find the syntax to create a custum formula for the validation.

My idea is to create a code to validate a time format HH:MM. For this matter i already have a a working Regexp (function CheckRegexp)

The only documentation i´ve found so far to this issue is this one: https://developers.google.com/apps-script/reference/spreadsheet/data-validation-criteria

function test() {
        var sheet = SpreadsheetApp.getActiveSheet();
        var cell = sheet.getRange("E4");
        var criteria = SpreadsheetApp.DataValidationCriteria.CUSTOM_FORMULA
        //Custom formula CheckRegexp
        var dv = SpreadsheetApp.newDataValidation().withCriteria(criteria, args).build();
        cell.setDataValidation(dv);
}

function CheckRegexp(input) {
  return /^([0-9]|0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]$/.test(input);
}

I want the result to be a Data Validation for my Regexp in a desire range.

3
javascriptgoogle-apps-scriptgoogle-sheets
At first, about your idea.. to check format by data validation. It sounds wrong, because we should validate data by means of data validation. And we should check (make) format by format settings, including date-time formats. If you still want to input strings and check their by regex, we will explain how to do that. – Александр Ермолин
If i state a format in a column for example HH:MM it will not be validated if a user puts "asd". So the only way i imagine is with a DataValidation or a OnEdit trigger. If you can share another way it would be nice! – CrisGuN

3 Answers

0
votes

It's not possible to set a custom formula(script-based) as a data validation criteria. Custom formula in this context(DataValidationCriteria.CUSTOM_FORMULA) refers to custom crafted formula by nesting inbuilt formulas.

Possible solution(s):

  • Use a edit trigger onEdit(e) to check each edit, whether it satifies a condition and clear the cell e.range.clear(), if not OR

  • Use inbuilt formula like this:

    =REGEXMATCH(TO_TEXT(E4),"^([0-9]|0[0-9]|1[0-9]|2[0-3]):[0-5][0-9]$")

To Read:

0
votes

Just another way for data validation is below:

=AND(ISDATE(E4), TEXT(E4, "hh:mm")=TO_TEXT(E4))

REGEXMATCH function is also suitable, but it may appear to be less readable for somebody. By the way, both cases do not reject incorrect numerical input (I dont know why). They only mark cells as "Invalid: This cell's contents violate its validation rule".

0
votes

It's too late, but might help someone still looking for it. Here's how you can set Custom Formula validation through script:

var range = SpreadsheetApp.getActive().getRange("A2");
var criteria = SpreadsheetApp.DataValidationCriteria.CUSTOM_FORMULA;
var args = ["=ISNUMBER(A2)"];
var val = SpreadsheetApp.newDataValidation().withCriteria(criteria,args);
val.setAllowInvalid(false);
val.setHelpText("Please enter valid number.");
range.setDataValidation(val);