Get data from API in Custom function

[callmehero]

Description

I am using runfunction in FunctionPlugin for showing API result. but it not working as expected due to return executed before the api response.

SO I used to promise inside the fn param but it showing this error

ERROR:
Argument of type ‘(result: any) => Promise’ is not assignable to parameter of type ‘(…arg: any) => InterpreterValue’.
Type ‘Promise’ is not assignable to type ‘InterpreterValue’.ts(2345)

Steps to reproduce

return this.runFunction(

ast.args,

state,

this.metadata("VLOOKUPSHEETFN"),

(param) => {

let item = this.paramFn();

console.log("skjdbvds", item);

return new Promise((resolve, reject) => {
return item = Api call result
});

return Hello , ${item}!;

},

);

Demo

Your environment

angular 13

• HyperFormula version: 12.0.1 (handsontable version)
• Browser Name and version: chrome latest
• Operating System: Windows

Links

[sequba]

Hi, @callmehero. Thanks for your feedback. HyperFormula does not support asynchronous custom functions at its current development stage. We discussed this idea a while ago, but at the moment, we prioritize bug and interoperability fixes, so this feature is not on our immediate roadmap.

But, as @warpech wrote in #9 (comment):

right now, the easiest way to achieve synchronicity is in the user code outside HyperFormula. I mean a two-step strategy, in which a function like SHARE_PRICE("AMZN") synchronously resolves to a value from cache (or gives an error if the cache is empty) while asynchronously fetching the current value. When fetching is done, update the cache and reevaluate getCellValue/getSheetValues.

In your case, I think you can try this approach:

• Fetch data from API every X minutes and store it in the Javascript object (cache).
• Custom function VLOOKUPSHEETFN reads data from the cache object and returns it.
• Refresh HyperFormula every time a cache value change is detected. 2 things might be helpful for that:
  • making VLOOKUPSHEETFN function volatile
  • calling rebuildAndRecalculate() method

Does it make sense for your use case?

[sequba]

Closing. @callmehero, if you have any further questions related to this issue, reopen it, please.

[abhii-bit]

I have a question related to this:

I want

Hi, @callmehero. Thanks for your feedback. HyperFormula does not support asynchronous custom functions at its current development stage. We discussed this idea a while ago, but at the moment, we prioritize bug and interoperability fixes, so this feature is not on our immediate roadmap.

But, as @warpech wrote in #9 (comment):

right now, the easiest way to achieve synchronicity is in the user code outside HyperFormula. I mean a two-step strategy, in which a function like SHARE_PRICE("AMZN") synchronously resolves to a value from cache (or gives an error if the cache is empty) while asynchronously fetching the current value. When fetching is done, update the cache and reevaluate getCellValue/getSheetValues.

In your case, I think you can try this approach:

• Fetch data from API every X minutes and store it in the Javascript object (cache).

• Custom function VLOOKUPSHEETFN reads data from the cache object and returns it.

• Refresh HyperFormula every time a cache value change is detected. 2 things might be helpful for that:

  • making VLOOKUPSHEETFN function volatile
  • calling rebuildAndRecalculate() method

Does it make sense for your use case?

Thanks sequba - would there be a way to reference local data in a custom formula in React? I was trying to follow the documentation - wasn't sure how to reference variables in the formula that were not the input variables.

Grateful for your assistance.

[AMBudnik]

Hi @abhii-bit! @sequba answered the same subject here #808 (reply in thread)
If you'd have any further questions, please let us know.

[sequba]

@abhii-bit @callmehero We have just released HyperFormula 2.7.0 which adds a context configuration parameter that can be used to reference local data in a custom formula.

[sandzone]

The current solution only deals with async cells that do not depend on other async cells. If this were to be integrated with Handsontable, is there a hook that gets invoked after rebuildAndRecalculate() is done?

[AMBudnik]

Hi @sandzone

We keep this board only for HyperFormula issues.

If the issue you are facing relates to the integration between HyperFormula and Handsontable, please contact us at support@handsontable.com with implementation details. We'll do our best to help.