Spreadsheet - Grab value from cell showing #CIRCULAR!

[Vincent]

Hello,

 

How can I grab the #CIRCULAR! text/string from the cell?

This occurs when there is a formula error on the formula column.

 

I am trying to create a validation method that will validate the spreadsheet on the UI has no #CIRCULAR! errors before allowing the user to Submit their changes to the backend, if #CIRCULAR! is present in the spreadsheet, I will show a popup letting the user know #CIRCULAR! exist in a specific row.

 

Thank you!

[Veselin Tsvetanov]

Hello Vincent,

You will need to first get a reference to all the cell Ranges you need to validate. Then, you could check for the circular reference in each cell Range with the following expression:

if(range.value() && range.value().code === "CIRCULAR") {
   // There is a circular error in the `range` object
}

Regards,
Veselin Tsvetanov
Progress Telerik

[Vincent]

Hi Veselin, thank you for responding and apologies for not reploying back soon.

I am looking into the range link you provided and I am trying to understand how to use it.

I have a few questions about it:

1. sheet.range(200, 4) does this mean row 200 column 4? and is this a valid use of .range() ?
2. How do I grab a specific cell value? Or I must grab the range, like E1:E200 then iterate through this?
   If so.. could you provide an example of it?

The reason I ask is because, when I do var sheet = spreadsheet.activeSheet();

sheet provides me with a lot of data that starts with underscore, example _range, _element, etc.

 

Thank you!

[Veselin Tsvetanov]

Hello Vincent,

Here are the answers to your questions:

1. Yes, that is a valid use of the range() method, that would return the range object for the cell on row 201, column E. Note that when using row/column indexes, those are zero-based. The row that is marked in the Spreadsheet with number 200 is actually on index 199;

2. You could retrieve the value for a given cell by using the value() method of its range object (retrieved as above). So if you need to check all the values for the E1:E200 range, you could create one loop starting from 0 (row 1 is on index 0) to 199 (included). Because all required cells are in the same column (E, that is at index 4), you will retrieve the range like the following:

var range = sheet.range(i, 4);
var value = range.value();

As per all the fields and methods that are prefixed with a lower dash, by convention those are private and should not be used.

Regards,
Veselin Tsvetanov
Progress Telerik

[Vincent]

I see, that make sense.

 

I tried on the dojo and it is working as you say, but for some reason in my code .value or .values return null.

 

Do you know if kendo-UI version “^2019.1.220” is compatible with the methods we’re using here?

 

Thanks!

[Vincent]

Never mind, I got it resolved, it was due to 

$(“#spreadsheet”).kendoSpreadsheet();

this created another instance which overwrote it.