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

Extract value from another sheet's calculated cell

4 Answers 391 Views
Spreadsheet
This is a migrated thread and some comments may be shown as answers.
Gabriel
Top achievements
Rank 1
Gabriel asked on 23 Jan 2018, 03:17 PM

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?

4 Answers, 1 is accepted

Sort by
0
Veselin Tsvetanov
Telerik team
answered on 24 Jan 2018, 03:31 PM
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:
change: function(e) {
  var spread = e.sender;
  var sheet1 = spread.sheets()[0];
  var activeSheet = spread.activeSheet();
  var range = sheet1.range('A1');
   
  // Sheet1 is not the active one
  if (activeSheet.name !== 'Sheet1') {
    spread.activeSheet(sheet1);
    spread.activeSheet(activeSheet);
  }
   
  console.log(range.value());
}

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

Regards,
Veselin Tsvetanov
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
Gabriel
Top achievements
Rank 1
answered on 24 Jan 2018, 07:44 PM

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).

0
Gabriel
Top achievements
Rank 1
answered on 24 Jan 2018, 08:01 PM
**The spreadsheet's change event
0
Veselin Tsvetanov
Telerik team
answered on 26 Jan 2018, 11:24 AM
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:
removeSheet: function(e) {
  var spreadsheet = e.sender;
   
  if (shouldPrevent) {
    e.preventDefault();
    shouldPrevent = false;
    spreadsheet.removeSheet(e.sheet);
    console.log('sheet removed');
    spreadsheet.activeSheet(spreadsheet.sheets()[0]);
    // execute the update logic here
  } else {
    shouldPrevent = true;
  }
},

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

Regards,
Veselin Tsvetanov
Progress Telerik
Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Tags
Spreadsheet
Asked by
Gabriel
Top achievements
Rank 1
Answers by
Veselin Tsvetanov
Telerik team
Gabriel
Top achievements
Rank 1
Share this question
or