Using a Custom Function in Data Validation
I am trying to use a custom function developed in Google Script to validate a value in a spreadsheet.
However, I get the answer: There is a problem "Please enter a value that satisfies the formula: = validateContent ()"
The function itself was not called at all.
Am I pushing Google Spreadsheet validation too hard here with a custom function?
I was expecting my function to return true or false, is this how it is supposed to work?
function validateContent() {
var val = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue();
if (val == value) return true;
return false;
}
source to share
First, it is useful to follow the pattern suggested by google for validating the current cell input:
=ISODD(C8)
In your case:
=validateContent(C8)
The validation generator is smart enough to translate a cell reference to all other cells correctly! I. If this check applies to C8: C100, the check of cell C42 will be read =ISODD(C42)
.
However, I found that custom functions don't seem to work in validation! See next example:
In this screenshot, cell G2 uses a custom validation ( =ssvDataVerify(G2)
) function that evaluates to TRUE, but displays as invalid (red corner)! As proof, the data value of cell I2 is =ssvDataVerify(G2)
. Now check if I2 = I2 which now appears as correctly checked!
I concluded that there are currently no custom functions implemented to work with validation.
source to share
I also believe that UDFs don't work for data validation.
I created a function to validate a string value in a RegExp list and didn't work:
function vaidate(){
var range = SpreadsheetApp.getActive().getRange('A1');
var validation = SpreadsheetApp.newDataValidation().requireFormulaSatisfied('=checkValid(A1)').build();
range.setDataValidation(validation);
}
function checkValid(text){
var regexs = [/\|{2,}/g,/\.{2,}/g,];
var valid = true;
for(var i=0;i<regexs.length;i++){
if(testString(text,regexs[i])){
valid = false;
break;
}
}
return valid;
}
function testString(str, regex){
try{
var localRegex = regex;
return localRegex.test(str);
}catch(e) {
return false;
}
}
source to share