How to tell if a cell value has passed validation

问题:

I am familiar with the Google Apps script DataValidation object. To get and set validation criteria. But how to tell programatically if a cell value is actually valid. So I can see the little red validation fail message in the spreadsheet but can the fact the cell is currently failing validation be picked up thru code?

I have tried to see if there is a cell property that tells you this but there is not. Also I looked for some sort of DataValidation "validate" method - i.e. test a value against validation rules, but nothing there either

Any ideas? Is this possible??

回答1:

Specific answer to your question, there is no method within Google Apps Script that will return the validity of a Range such as .isValid(). As you state, you could reverse engineer a programatic one using Range.getDataValidations() and then parsing the results of that in order to validate again the values of a Range.getValues() call.

It's a good suggestion. I've added a feature request to the issue tracker -> Add a Star to vote it up.

回答2:

I've created a workaround for this issue that works in a very ugly -technically said- and slightly undetermined way.

About the workaround: It works based on the experience that the web browser implementation of catch() function allows to access thrown errors from the Google's JS code parts. In case an invalid input into a cell is rejected by a validation rule then the system will display an error message that is catchable by the user written GAS. In order to make it work first the reject value has to be set on the specified cell then its vale has to be re-entered (modified) then -right after this- calling the getDataValidation() built in function allows the user to catch the necessary error. Only single cells can be tested with this method as setCellValues() ignores any data validation restriction (as of today).

Disadvantages:

• The validity won't be necessarily re-checked for this function: it calls a cell validation function right after the value is inserted into the cell. Therefore the result of this function might be faulty.
• The code messes up the history as cells will be changed - in case they are valid.

I've tested it successfully on both Firefox and Chromium.

function getCellValidity(cell) {
  var origValidRule = cell.getDataValidation();
  if (origValidRule == null || ! (cell.getNumRows() == cell.getNumColumns() == 1)) {
    return null;
  }
  var cell_value = cell.getValue();
  if (cell_value === '') return true; // empty cell is always valid
  var is_valid = true;
  var cell_formula = cell.getFormula();
  // Storing and checking if cell validation is set to allow invalid input with a warning or reject it
  var reject_invalid = ! origValidRule.getAllowInvalid();
  // If invalid value is allowed (just warning), then changing validation to reject it
  // IMPORTANT: this will not throw an error!
  if (! reject_invalid) {
    var rejectValidRule = origValidRule.copy().setAllowInvalid(false).build();
    cell.setDataValidation(rejectValidRule);
  }
  // Re-entering value or formula into the cell itself
  var cell_formula = cell.getFormula();
  if (cell_formula !== '') {
    cell.setFormula(cell_formula);
  } else {
    cell.setValue(cell_value);
  }
  try {
    var tempValidRule = cell.getDataValidation();
  } catch(e) {
    // Exception: The data that you entered in cell XY violates the data validation rules set on this cell.
    // where XY is the A1 style address of the cell
    is_valid = false;
  }
  // Restoring original rule
  if (rejectValidRule != null) {
    cell.setDataValidation(origValidRule.copy().setAllowInvalid(true).build());
  }
  return is_valid;
}

I still recommend starring the above Google bug report opened by Jonathon.