Get Validation Errors - Strange Results with Multiple Sheets

[Jarred]

Hello,

I'm trying to fetch the total number of cells with validation errors; this works well with one sheet, but unfortunately, appears to cause issues when I have multiple sheets. I've followed https://docs.telerik.com/kendo-ui/controls/data-management/spreadsheet/how-to/get-flagged-cells for starters... but have since wrapped it to iterate all sheets.

 

First concern: that method would consider undefined as invalid, which is often the case on initial load. Demo: https://dojo.telerik.com/OZOcasag (page load error count should be 0, but is instead 1)

 

Second Concern: If I update the method to consider not undefined, but === false... then I get into a system where the count is wrong/too-lower. Demo: https://dojo.telerik.com/iDoBiXAf/ . Please note that even starting with valid cells still shows this behavior when I make the cells invalid and bounce between cells. It's as if the cells are connected even though they're on different sheets.

 

Any advice on what I'm doing wrong or workarounds would be appreciated

[Jarred]

Update: The second concern's demo link should be: https://dojo.telerik.com/iDoBiXAf/3

[Neli]

Hi Jarred,

As far as I see my colleague Ivan has already answered the questions in the support thread you have opened on the same topic. For convenience, I will paste the main part of his reply below as it could be helpful to the other users in the Forum. 

 

In the second scenario cell.validation.value returns undefined for the cell on Sheet2, which is why it doesn't fulfill the condition cell.validation.value === false. The condition should be !cell.validation.value. In this case to avoid scenario 1, where the cells are valid but cell.validation.value returns undefined for the cell on Sheet2, the Sheet should be made active beforehand. Here's an updated dojo example: https://dojo.telerik.com/oDEYUKaK

  function getFlaggedCells(e) {
      e.sender.unbind("render");
     var spreadsheet = e.sender;
      var totalErrors = 0;

      spreadsheet.sheets().forEach(function(sheet, index){
        if(spreadsheet.activeSheet() !== sheet) {        
        	spreadsheet.activeSheet(sheet)
        }
        
        //log the cell value
        
        console.log(sheet.range('A1:A1').value())
        sheet.range('A1:A1').forEachCell(function (row, column, cell) {
          //log validation value
          console.log(cell.validation.value)
          if(cell.validation && !cell.validation.value) {
            //log error details
            console.log('sheet ', sheet.name(), ", row ", row, ", col ", column, ', raw value: ', cell.validation.value);
            totalErrors += 1;
          }
        });

      });
      
      $('#total-errors').text(totalErrors);
    }

Regards,
Neli
Progress Telerik