Custom Async Function called too many times

Posted by: gabriel.inzirillo on 13 November 2020, 3:06 am EST

  • Posted 13 November 2020, 3:06 am EST

    Hi,

    I have reproduced an issue in a minimalistic situation to show what we are experiencing in a more large scope.

    We have a custom async function, like in this example (https://www.grapecity.com/spreadjs/demos/features/calculation/async-function/purejs).

    In one cell we can chain the call to this custom function like this :
    =ASUM(A1,B1)+ASUM(A1,B1)+ASUM(A1,B1)+ASUM(A1,B1)+ASUM(A1,B1)

    In this scenario, we expect the ASUM function to be called 5 times.

    However, we can see that this function is called 10 times on the initial load and if we change the value in A1, we see more than 20 calls. This become exponential more we add ASUM calls in one cell.

    This is problematic because in real life we can have 15+ calls in one cell and this can cause the custom function to be called 100'000+. Our custom function is more complexe than in the example and fetch data in a database.

    Here in attachment, a simple example on how to reproduce this issue. A debugger statement is here to see how many times we execute the custom async function.

    Minimal_spreadJS.zip.

    Best Regards.
  • Replied 16 November 2020, 10:44 pm EST

    Hi Gabriel,

    We are able to observe the issue at our end hence we have escalated this issue to our devs for further investigation. We will update you regarding this as soon as we get any information. The internal ID for this issue will be SJS-6518.

    Regards
    Avinash
  • Replied 17 November 2020, 3:53 am EST

    Hi,

    Thank you for the rapidity.

    Looking forward to see what the devs can do.

    Best Regards.
  • Replied 17 November 2020, 3:02 pm EST

    Hi Gabriel,

    The dev team informed us that the Spread V14 supports the LET function which can be used for chaining the formulas. Please refer to the following code snippet and the attached sample for demonstration.

    sheet.setFormula(
    1,
    1,
    "LET(asum_a1b1, ASUM(A1,B1), asum_a1b1+asum_a1b1+asum_a1b1+asum_a1b1+asum_a1b1)"
    );


    sample: https://codesandbox.io/s/boring-gauss-j1j1u?file=/src/index.js:1123-1244

    LET Demo: https://www.grapecity.com/spreadjs/demos/features/calculation/let-function/purejs

    Regards
    Avinash
  • Replied 17 November 2020, 9:22 pm EST

    Hi,

    Thank you for the quick answer.

    Sadly this solution cannot be used in our case.

    That is sometime the problem with too much reduced case.

    In the example I show you, I used only 1 async function with always the same data. But in the real life we use the same async function but with data coming from different cells.

    I have updated your example and also uploaded an example that shows that even using the LET function, we still have too much call (15 calls in the case if we use only 5 times the async function)

    Here is what the formula could look like :

    LET(asum1,ASUM(A1,B1),asum2,ASUM(c1,d1),asum3,ASUM(e1,f1),asum4,ASUM(g1,h1),asum5,ASUM(i1,j1),asum1+asum2+asum3+asum4+asum5)

    I have try to push the calls to 10 times and I reach 62 calls to the async function. In fact I have observed something : The first time, it seems to call the async function correctly (almost), then we have a really tiny pause and after we get a lot more calls.

    For 5 calls I got this : 6 calls and then 10 calls
    For 10 calls I got this : 10 calls and then 52 calls

    It doesn't seems to be regular and maybe those numbers can depend on the machine or the browser we use.
    I don't know if this information could help.

    https://codesandbox.io/s/angry-wiles-l10kq?fontsize=14&hidenavigation=1&theme=dark

    Minimal_spreadJS 2.zip
  • Replied 18 November 2020, 5:59 pm EST

    Hi Gabriel,

    Thanks for the explanation. We have shared the information with Devs. We will update once we have any information regarding the same.

    Regards
    Avinash
  • Replied 23 November 2020, 3:59 am EST

    Hi Avinash,

    Thanks for the answer.

    Do you think you can solve this issue?

    How could be the delay before we can get a working solution?
  • Replied 23 November 2020, 3:29 pm EST

    Hi Gabriel,

    We are sorry but currently, We don't have any ETA. The Devs are working on this case. They will surely provide you a working solution soon. Further, We have asked Devs about the updates on this issue. We will update you once we have any information.

    Regards
    Avinash
  • Replied 23 November 2020, 8:37 pm EST

    Hi,

    Thank you for the update. I will wait to get more informations :)

    Best regards,
    Gabriel
Need extra support?

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

Learn More

Forum Channels