Using a custom function in Data Validation

Question

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;

}

Answer 1

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.

Answer 2

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.