I am trying to use a custom function developed in Google Script to validate a value in the spreadsheet.

However I get a response: There is a problem "Enter a value that satisfies the formula: =validateContent()"

The function itself has not been called at all.

Am I pushing Google Spreadsheet validation too far here with custom function?
I was expecting my function to return true or false, is that how it is suppose to work?

function validateContent() {

  var val = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue();

  if (val == value) return true;

  return false;

}

I am trying to use a custom function developed in Google Script to validate a value in the spreadsheet.

However I get a response: There is a problem "Enter a value that satisfies the formula: =validateContent()"

The function itself has not been called at all.

Am I pushing Google Spreadsheet validation too far here with custom function?
I was expecting my function to return true or false, is that how it is suppose to work?

function validateContent() {

  var val = SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue();

  if (val == value) return true;

  return false;

}

• javascript
• google-apps-script
• google-sheets

• Sorry. I thought I have posted a ment already. C8 cell is just an example. It can be any cell that I want to validate. – theworldismyoyster Commented Dec 24, 2014 at 5:16
• There is a function that pare it to a different source and returns true or false depends whether it is valid or not. As mentioned though, the function has not even been called. – theworldismyoyster Commented Dec 24, 2014 at 18:08

4 Answers 4

First, to validate the current cell input it is useful to follow the pattern suggested by google:

=ISODD(C8)

In your case:

=validateContent(C8)

The validation generator is smart enough to translate the cell reference correctly to all other cells! I.e if this validation is applied to C8:C100 the validation of cell C42 will read =ISODD(C42).

Still, I have found that custom functions seem not to work in validation! See the following example:

In this screenshot the cell G2 uses a custom validation function (=ssvDataVerify(G2)), which evaluates to TRUE but is shown as invalid (red corner)! As a proof, the data value of cell I2 is =ssvDataVerify(G2). Now the validation if I2 is =I2, which now is shown as correctly validated!

I conclude that currently custom functions are not implemented to work with validation.

Currently, functions cannot be used in validations, but there is a workaround:

• Use your custom function in some cell, let's say B2: =validateContent(A2).
• Add a validation to cell A2 with the criteria Custom Formula is -> =B2.

I also believe that custom functions don't work for data validation.

I created a function to check a string value , against a list of RegExp and it didn't worked:

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

Here you go :)

The idea is to build the validation rule once the cell is edited

function onEdit(e){
  const isValid = validateValue(e.value);
  const rule = SpreadsheetApp
  .newDataValidation("Yep")
   // You can use any function but i believe you only need to use "EQ" with boolean 
  .requireFormulaSatisfied('=EQ("'+isValid+'","TRUE")')
   // Your help message
  .setHelpText('Please Enter "123" :P')
   // Building the rule 
  .build();
  e.range.setDataValidation(rule);
}

function validateValue(value){
  // do what ever you want ;)
  return value === "123";
}