CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
jfenal
jfenal

Reputation: 11

In Google Sheets, is there a function returning the status of data validation rules set on a cell?

To top-up on data validation, I'd like to trigger an error message when a data validation rule defined on cell is not validated, to remind the user to change their input.

Is there a function, taking a cell reference as input, and returning the failed status of all data validation rules applied to that cell?

I couldn't find such a function in the function list, nor any SpreadsheetApp API Range method in Google Apps Script to allow peeking the status of data validations rules defined on a cell.

Upvotes: 1

Views: 76

Answers (1)

TheWizEd
TheWizEd

Reputation: 8606

I don't think there is a method of DataValidation that says whether or not it passes or fails but the following custom formula example does. You would need to build a list of else ifs to test for other criteria. Mine only test for NUMBER_GREATER_THAN.

I have the follwing sheet with a Data Validation set in cell A1. And a custom formula that gets the cell reference in the formula and tests that value against the validation criteria.

enter image description here

function testDataValidation(param) {
  try {
    let cell = SpreadsheetApp.getActiveRange();
    let formula = cell.getFormula();
    let range = formula.match(/\(.+\)/)[0];
    range = range.substring(1,range.length-1);
    cell = cell.getSheet().getRange(range);
    let valid = cell.getDataValidation();

    if( valid ) {
      if( valid.getCriteriaType() === SpreadsheetApp.DataValidationCriteria.NUMBER_GREATER_THAN ) {
        let test = valid.getCriteriaValues();
        return cell.getValue() > test[0] ? "yes" : "no";
      }
      else {
        return "Unknown type";
      }
    }
    else {
      return "No data valiation";
    }
  }
  catch(err) {
    console.log(err);
  }
}

References

Upvotes: 0

Related Questions