Calculate the functions value in background without loading the spreadsheet

Calculate the functions value in background without loading the spreadsheet

Posted by: dave.welch on 21 October 2020, 8:13 pm EST

• 

  dave.welch

  • Post Options:
  • Link
    Copy

  Posted 21 October 2020, 8:13 pm EST

  Hi,
  
  We have a custom function built which gets the data from DB. That function is dependent on table columns in DB (and other cells in spreadsheet).
  
  Now when the data is changed in Table Column backend, is there a way to recalculate the value in my cell which has this column based function?
  
  I know that when I reload the spreadsheet, the function re-calculates the function using the latest value of underlying table-column from DB.

  I just wanted to know if this calculation can be called from backend.

  Thanks

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 22 October 2020, 11:49 pm EST

  Hi Dave,

  SJS does not provide any method for recalculating the formula in a single cell. But you may use the sheet.recalcAll method which recalculates all the formulas present in a sheet. Please refer to the following code snippet and let us know if you face any issues.

  activeSheet.setValue(0,0,1);
  activeSheet.setValue(0,1,2);
  activeSheet.setValue(0,2,10);
  activeSheet.getCell(1,1).formula("=SUM(A1:C1)");
  activeSheet.recalcAll();
  

  API References

  recalcAll: https://www.grapecity.com/spreadjs/docs/v13/online/SpreadJS~GC.Spread.Sheets.Worksheet~recalcAll.html

  Regards
  
  Avinash

• 

  dave.welch

  • Post Options:
  • Link
    Copy

  Posted 23 October 2020, 3:08 am EST

  Hi Avinash,
  
  I guess I didnt make myself clear.

  We have a custom function MYFUNC(val1, val2, val3). The val1, val2, val3 are from DB and the function is evaluated using new GC.Spread.CalcEngine.Functions.AsyncFunction
  
  When the page loads the formula is calculated based on the latest values for val1, val2… from DB. All works fine.

  Now suppose, the val1, val2 are changed from some other page, I will have to reload the GC page to get my cells value (which has MYFUNC(val1, val2, val3)) recalculated using the new changed values and then save the function value back to DB.

  Is there any way to call the calculate engine backend without opening the GC page. i.e when the val1, val2 changes, recalculate my function and save value without opening the GC page.

  Thanks,
  
  Dave.

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 26 October 2020, 1:45 am EST

  Hi Dave,

  We are working on this case. We shall provide you an update soon.

  Regards
  
  Avinash

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 26 October 2020, 9:01 pm EST

  Hi Dave,

  Could you please confirm that you need to avoid the page refresh or you just do not want to open the page. If you want to avoid referesh when DB value change from any where you may need to call recalcAll whenever the value gets changed. Please refer to the following code snippet and attahced sample that demonstrates the same.

  asyncSum.prototype.evaluateAsync = function (context) {
  // use setTimeout to simulate server side evaluation
  var args = arguments;
  console.log("called async");
  setTimeout(function () {
  var result = 0;
  for (var i = 1; i < args.length; i++) {
  result += args[i];
  }
  result *= dbValue;
  context.setAsyncResult(result);
  }, 2000);
  };
  

  sample: https://codesandbox.io/s/practical-gagarin-gel7h?file=/src/index.js:499-884

  Regards
  
  Avinash

• 

  dave.welch

  • Post Options:
  • Link
    Copy

  Posted 11 November 2020, 2:04 am EST

  Hi Avinash,

  I do not want the page to open at all, i just want the values in the background to calculate. Is this possible?
  
  Thanks,
  
  Dave

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 11 November 2020, 9:53 pm EST

  Hi Dave,

  For this, you may override the evaluation mode and interval method which refreshes the formula according to the defined interval. Please refer to the following code snippet and the attached sample that demonstrates the same.

  
   asyncSum.prototype.interval = function () {
      return 100; //in ms
    };
    asyncSum.prototype.evaluateMode = function () {
      return 2; // 2 for refresh on interval
    };
  
  

  sample: https://codesandbox.io/s/recursing-raman-oqtc7?file=/src/index.js

  Regards
  
  Avinash