Calculate the functions value in background without loading the spreadsheet

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

  • 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

  • Replied 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
  • Replied 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.
  • Replied 26 October 2020, 1:45 am EST

    Hi Dave,

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

    Regards
    Avinash
  • Replied 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
  • Replied 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
  • Replied 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
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels