Hey,

In the context of our application, we need to extract a specific cell's value anywhere in the spreadsheet.

However, calculated cells from the not activeSheet don't seem to be updated unless we switch the actual sheet.

Scenario :

Les say we have Sheet1 and Sheet2

A1 in Sheet1 is our targeted cell which we re-fetch the value onChange of the spreadsheet component with : this.spreadsheet.sheetByName(this.targetedRangeSheetName).range(this.targetedRangeCell).value();

If A1 is a formula like =SUM(B1:B3), when we change something in B1, B2 or B2, onChange is triggered and we re-fetch our targeted cell's value, which matches the value we would expect.

However, if the formula of Sheet1 A1 is something like : =SUM(Sheet2!B1:B3), if we change someting in Sheet2's B1, B2 or B3, onChange is trigger but 

this.spreadsheet.sheetByName(this.targetedRangeSheetName).range(this.targetedRangeCell).value() 

returns the old value.

 

How can I an updated value of Sheet1!A1 without switching the activeSheet to Sheet1? Is there a way to trigger the calc on Sheet1?

Hi Gabriel,

As the calculated (formula) values in the Spreadsheet are updated only for the currently active Sheet, I would suggest you to activate the required sheet before you retrieve the updated value. By instantly switching back to the initially active sheet, the end user will not notice that change:

Here you will find a simple Dojo, implementing the above suggestion.

Regards,
Veselin Tsvetanov
Progress Telerik

Thanks a lot for the quick response!

Seems a bit hackish but it'll do fine.

In the same vein of idea, when I delete a column, row or sheet, I need to update my targeted value because it could contain a formula targeting the deleted target.

However, removeSheet's, deleteColumn's and deleteRow's callback are executed before the actual removal of the item  (So I cant just switch sheet, update, switch back) and after the removal the spreadsheet's onChange isn't executed.

Any tips on how I  could update my value after these events? (Without using setTimeout).

**The spreadsheet's change event

Hi Gabriel,

I believe, that you will be able to achieve the desired by preventing the default execution of the respective event (removeSheet, deleteColumn or deleteRow) and proceed with the action by using the respective methods available in the Spreadsheet and the Spreadsheet Sheet APIs (removeSheet, deleteColumn and deleteRow). Here is how this scenario could be approached in respect to the removeSheet event:

Here you will find a simple Dojo implementing the above suggestion.

Regards,
Veselin Tsvetanov
Progress Telerik