Is there a way to check in a Google Sheets spreadsheet, if there is at least one cell violating a Data Validation rule? If such functionality is not built-into the app, how can I write a Google Apps Script to implement a "Check for Data Validation errors" menu item that checks for any error?

  • Set data validations properly before you start polluting data, there is no easy way to do this. It would be easier to literally create a "discrepancy report" with filter and or query peculiar to your particular case.
    – CodeCamper
    Dec 13, 2019 at 20:02
  • Thank you for your answer! The problem is that, even if I enter data only after setting Data Validation, it is possible that validation is violated subsequently. For example, if a table A has a column whose value must be one of the columns of a table B, and then I delete a row of table B referenced by table A, in table A there will be a cell that violates the rule. Dec 16, 2019 at 8:34
  • It sounds like data validation is not the problem you have, but organizing your spreadsheet in a way where you don't accidentally delete rows. You should protect your spreadsheet by right clicking and protect, only warning will work with you as owner but it will give you a message box when you try to edit something you really should not be editing (or at least make you aware you are doing something serious). In your example though I suggest creating a query looking for those specific violations for your specific situation, a catch all will be more work than it is worth in your situation.
    – CodeCamper
    Dec 16, 2019 at 15:28


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.