4

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?

Improve this question
3
  • 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.
    – Federico DTales
    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

0

Reset to default

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.