IssueTracker

I'd also love to see this as a formula, in order to apply conditional formatting based on the whether the content of a cell passes the data validation rule or not. Something like ISVALID(value).

I'm completely baffled: what's the point of "Data Validation" if there's no way of checking whether the data satisfies the condition?  Could we all be missing something?

@marc.art: The validation still validates within the UI itself; it's just that the results of the validation aren't available in code.

This would be extremely helpful!  Im looking to optimize my team's workflow and it is counterintuitive and wasteful on resources to reverse engineer the validation just to check if it is valid when that information is already shown in the UI!  Please add .isValid()!!

This is a blocking feature for implementing a compliance changelog of only valid entries.

Would definitely be useful.

Can't see why this isn't already available. I hope it is added soon!

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

My problem is that the validation is already done by the SpreadsheetApp, but only visually accessible by the user. It feels like that "you can see but cannot touch" :) It's against the whole idea of GAS.
I'd really love to have this result accessible (the feature implemented).

About the workaround:
It works based on the experience that the web browser implementation of catch() function allows to access thrown errors by the script editor 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 us 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: checking cell validity right after the value is inserted into it. 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;
}

Finally let me express my gratitude for all the great works of the developers! With their shared tools/system I can solve many logistical problems of my and others' lives... Cheers!
On this issue: Hurry up guys!

I'm desperate for this method to be added!  Please please please do it soonest possible!  Thank you!

Yes please implement this. I suppose the data could be checked against the data validation rules before setting the values, but that would really slow the write process down. Not to mention how much of a pain it would be to code all the validity checks to accommodate all the different possible data validation rules.

I've been starting to use Google Sheets to serve some of our company's data storage in place of data typically stored in a SQL database, but the hard data validation rules are an essential part to maintaining data integrity in the way that's possible with a SQL database.

Please add this. It will bring down so much bad code that I have put in place due to lack of this function.

Please add this in asimple manner: isValid() true or false

I would say the methis should be intiutive to read.

Range.dataValidate() :: [][]bool

Since we are doing DataValidations ...

I need it like a thirsty desert traveler needs a few drops of water.

I need it like a few drops of water need hydrogen and oxygen

Must I beg?

Seems that begging is necessary...

Also require this, so i can track any validations fails centrally, without logging into each spreadsheet and sheet to check which is time consuming.

OK Google... We know you can do it !

Nearly 5 years later and still no change..
I don't think this will happen

+1

It would be very cool

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1 would be incredibly helpful to just have an ISVALID() formula that returns TRUE has a legit data validation value and FALSE if somebody entered a value into the cell that doesn't match a data validation value. Simple addition to Google Sheets that would make tons of people happy.

+1 Hoping...

This would be extremely helpful!
Strange that it is not yet implemented, or able to get error validation.
+1

+1

Please add this

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1

Is somebody working on this one?

I'm an experienced app developer and just starting w/ Appscript to customize my client's google sheets. I realized that this simple but critical function-- check if a cell value satisfies a data validation, or if satisfies the conditional formatting condition-- is missing!! Really surprised and disappointed. Tried to find a work-around but apparently I couldn't use some very useful built-in functions such as ISEMAIL within appscript. Lack of these features are quite discouraging to diving into Google Workspace Dev seriously. HOPEFULLY this feature will be RELEASE SOON!

+1 for sure!

@co...@geeksconnect.com
" HOPEFULLY this feature will be RELEASE SOON!"

Been asked since 2016 with no significant progress.
I think there´s more chance of google redoing their whole platform services (which they do frequently) at this point that getting some of these basic stuff.

Would not hold my code waiting for it.

Best worst solution might be to write your validation as a conditional to a temporary (or permanent) cell and read the value of the result I guess...

Haven´t used this in a while just because it´s not practical.

+1

+1 & holding my breath.

+1

+!

+1

+1 as well!!

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1

+1


On Fri, Aug 5, 2022 at 2:37 AM <buganizer-system@google.com> wrote:

+1

+1

+1

+1. Add isValid() method

+1
Above suggested a good hack but "try{} catch(e){}" code section takes a long time ( >120 sec). If it processes 3 cells, then we get "Exceeded maximum execution time".
Need a solution from Google.

+1

+1
It's unfortunate that after the better part of a decade since the original ask, it's pretty clear Google doesn't seem to have the time of day (or care?) to implement this pretty useful feature. Would enjoy having it nonetheless, so I'll continue the bump.

I checked out the Developer References for Apps Script, there is a DataValidation section that I thought would resolve my use-case, but unfortunately after some experimentation it failed to do the trick. I am using a Data Validation that rejects inputs that are not given in the dropdown menu, however I am using a query-based two-layer data validation dropdown system (in other words, my first drop down menu alters a query range that influences what options my second dropdown menu offers). When I change the selection of the first dropdown to something with new options in the second dropdown menu, the selected option remains even though it is invalid in the second dropdown menu. getAllowInvalid() returns "false" in that it will reject any input that fails validation, however when I use getCriteriaValues() to determine a true/false difference between the first menu (which should be valid and TRUE) and the second menu (which is invalid and should be FALSE) they both return TRUE.

Ergo, the currently implemented system is flawed and does not satisfy the simple requirements that my needs nor the needs of many on this 8-year old tracker. I will be looking into an onEdit script to blank out the second dropdown menu as a bandage fix, but it is exactly that: a bandage.

Google doesn't care. Share this on twitter... it wont matter. This project is dead. Do much potential lost. The API provided doesn't work as documented. Its broken and will never work. Stop using Google services.

+1

+1

why google doesn't care?

+1 cmon it has been almost 10 years

+1

+1...million

+1

+100

+1

+100

+++ would be useful. Want to make sure validation requirements are met before running

+1

+1