This is a migrated thread and some comments may be shown as answers.

Spreadsheet - Grab value from cell showing #CIRCULAR!

5 Answers 257 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Vincent
Top achievements
Rank 1
Vincent asked on 29 Oct 2020, 04:45 AM

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!

5 Answers, 1 is accepted

Sort by
0
Veselin Tsvetanov
Telerik team
answered on 30 Oct 2020, 09:29 AM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Vincent
Top achievements
Rank 1
answered on 10 Nov 2020, 06:06 AM

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!

0
Veselin Tsvetanov
Telerik team
answered on 11 Nov 2020, 03:07 PM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

0
Vincent
Top achievements
Rank 1
answered on 12 Nov 2020, 03:21 PM

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!

0
Vincent
Top achievements
Rank 1
answered on 12 Nov 2020, 07:00 PM

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

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

this created another instance which overwrote it.

Tags
General Discussions
Asked by
Vincent
Top achievements
Rank 1
Answers by
Veselin Tsvetanov
Telerik team
Vincent
Top achievements
Rank 1
Share this question
or