Retrieve validation errors programatically

[David M]

I was wondering if there is any way to retrieve all the problematic cells that the validation scripts are flagging?  Sometimes users will have hundreds if not thousands of rows, and many columns, and I'd like to be able to give them a list of problematic cell coordinates so that they don't have to hunt through the UI.  Is this possible?  

[Dimiter Topalov]

Hello David,

You can handle the render event of the Kendo UI Spreadsheet, and perform the following custom logic to access and log the flagged cells:

render: function(e){

var spreadsheet = e.sender;

var sheet = spreadsheet.activeSheet();

var cellsStates = sheet.range('A3:E8').getState();

for(var c in cellsStates){

if(cellsStates[c].validation && !cellsStates[c].validation.value){ 

console.log(c)
          }

}

}

Here is the API reference for some of the used methods:

Get the active sheet:
http://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet#methods-activeSheet

Get the state of all cells in a given range:
http://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/sheet#methods-range

The Range.getState() method returns an object, with keys - the coordinates of all of the cells in the given range, and cells' objects as values. The cells objects in turn have a validation property which has a boolean valid property that is true if the cell contains a valid value, and false otherwise.

I hope this helps.

Regards,
Dimiter Topalov
Telerik

[David M]

I was e-mailed a response, but didn't see that the thread was updated.  Dimiter Topalov replied, and his solution worked great!  One thing I'd like to add is instead of applying the function on the "render" event i added it to the "change" event.  The render event fires on almost every single click and scroll.  Applying Dimiter's code below on "change" causes my error list to refresh at the right times.  Thanks again!

 

Hello David,

You can handle the render event of the Kendo UI Spreadsheet, and perform the following custom logic to access and log the flagged cells:
render: function(e){
var spreadsheet = e.sender;
var sheet = spreadsheet.activeSheet();
var cellsStates = sheet.range('A3:E8').getState();
for(var c in cellsStates){
if(cellsStates[c].validation && !cellsStates[c].validation.value){ 
console.log(c)
          }
}
}

Here is the API reference for some of the used methods:

Get the active sheet:
http://docs.telerik.com/kendo-ui/api/javascript/ui/spreadsheet#methods-activeSheet

Get the state of all cells in a given range:
http://docs.telerik.com/kendo-ui/api/javascript/spreadsheet/sheet#methods-range

The Range.getState() method returns an object, with keys - the coordinates of all of the cells in the given range, and cells' objects as values. The cells objects in turn have a validation property which has a booleanvalid property that is true if the cell contains a valid value, and false otherwise.

I hope this helps.

Regards,
Dimiter Topalov
Telerik
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!

[David M]

As of v2016.2.504 the method for doing this process above needs to be tweaked because you no longer are returned an object with a property name of each cell, you are now returned a data property that contains multidimensional arrays to represent the rows and cells of the data object.  

for (var r in cellsStates.data) {
for(var c in cellsStates.data[r]){
if (cellsStates.data[r][c].validation && !cellsStates.data[r][c].validation.value) {
console.log(r+","+c);
}
}
}

[Dimiter Topalov]

Hi David,

Thank you for pointing this out. I have updated the how-to article from our documentation accordingly, and the new version will be live shortly a the following address:

http://docs.telerik.com/kendo-ui/controls/data-management/spreadsheet/how-to/get-flagged-cells

Regards,
Dimiter Topalov
Telerik