Extract value from another sheet's calculated cell

5 posts, 0 answers
  1. Gabriel
    Gabriel avatar
    7 posts
    Member since:
    Nov 2017

    Posted 23 Jan 2018 Link to this post

    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?

  2. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1201 posts

    Posted 24 Jan 2018 Link to this post

    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.
  3. Gabriel
    Gabriel avatar
    7 posts
    Member since:
    Nov 2017

    Posted 24 Jan 2018 Link to this post

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

  4. Gabriel
    Gabriel avatar
    7 posts
    Member since:
    Nov 2017

    Posted 24 Jan 2018 in reply to Gabriel Link to this post

    **The spreadsheet's change event
  5. Veselin Tsvetanov
    Admin
    Veselin Tsvetanov avatar
    1201 posts

    Posted 26 Jan 2018 Link to this post

    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.
Back to Top