Async and real-time formulas support

[dberardo-com]

does the spreadsheet support async formulas (promises) and real-time ones (websockets) ?

in the first case, all cells referencing the async formula will be reprocessed when the promise resolves, in the second case, all references will reprocess when a new value "reaches" the formula and generates a different output (cache-invalidation)

[LucasLefevre]

Hello @dberardo-com

Async formulas (formulas returning a promise) aren't supported directly.

However, it's not difficult to implement an async behavior on top of your custom function.
The idea is to store the promise result and trigger a new evaluation once your promise resolves by dispatching the command.

promise.then((result) => {
      // store the result somewhere
      // and dispatch a new evaluation to use the result
      this.dispatch("EVALUATE_CELLS");
});

You can find more explanation in the documentation: https://github.com/odoo/o-spreadsheet/blob/16.0/doc/add_function.md#asynchronous-function

Let me know it anything is unclear.

You can implement the same strategy with real-time websocket when new data comes.

[rrahir]

Without any update for three months, I will close this issue.

Regards

[dberardo-com]

looks great, i was able to test the async loop with the documentation you provided. here a couple of issues:

• the class Currency rate class given in the example is not 100% consistent with the type definition in const { UIPlugin } = o_spreadsheet; as it misses some attribute implementations.
• i cannot resolve this import const { uiPluginRegistry } = o_spreadsheet.registries; i rather get this one object instead statefulUIPluginRegistry. i hope that's also ok

here a couple of questions:

• i assume that every plugin is a singleton in the application, meaning that if 2 different formulas use the same plugin, they will be using the same instance of that plugin and that the cache is also shared between the 2 formulas. if this is not desired, then in order to prevent key conflicts in the cache, then one should pass to the plugin getter a different cache key per formulas, example:
  formula1 --> this.mySharedPlugin.fancyGetter(...stuff, cacheKey: "formula1")
formula2 --> this.mySharedPlugin.fancyGetter(...stuff, cacheKey: "formula2")
  this way the cache will be correctly updated for both formulas separately.
  is this intuition right ?
• VERY IMPORTANTLY: is it possible to define a custom Error handler in the spreadsheet so that the cell could show #LOADING instead of #ERROR while the promise resolve?

cheers

[LucasLefevre]

Hello @dberardo-com

• the class Currency rate class given in the example is not 100% consistent with the type definition in const { UIPlugin } = o_spreadsheet; as it misses some attribute implementations.
• i cannot resolve this import const { uiPluginRegistry } = o_spreadsheet.registries; i rather get this one object instead statefulUIPluginRegistry. i hope that's also ok

The doc isn't up-to-date. Thanks for pointing that. I'll fix it. You can use any "UI plugin" it doesn't really matter for such things.

• i assume that every plugin is a singleton in the application, meaning that if 2 different formulas use the same plugin, they will be using the same instance of that plugin and that the cache is also shared between the 2 formulas. if this is not desired, then in order to prevent key conflicts in the cache, then one should pass to the plugin getter a different cache key per formulas, example:
  formula1 --> this.mySharedPlugin.fancyGetter(...stuff, cacheKey: "formula1")
  formula2 --> this.mySharedPlugin.fancyGetter(...stuff, cacheKey: "formula2")
  this way the cache will be correctly updated for both formulas separately.
  is this intuition right ?

You assumed correctly, every plugin is instantiated once. You have to manage different caches or cache keys. The strategy is up to you. We usually use the functions arguments to build the key. And we used different caches for different functions (or even different plugins).

• VERY IMPORTANTLY: is it possible to define a custom Error handler in the spreadsheet so that the cell could show #LOADING instead of #ERROR while the promise resolve?

Yes you can! You can use EvaluationError for custom tags and messages. You can also have errors that do not display the little red triangle with CellErrorLevel.silent

import { helpers } from "@odoo/o-spreadsheet";

const { EvaluationError, CellErrorLevel } = helpers;

throw new EvaluationError("#LOADING", "The data is loading", CellErrorLevel.silent);

// or create an other error
class LoadingDataError extends EvaluationError {
    constructor() {
        super("#LOADING", "The data is loading",CellErrorLevel.silent);
    }
}

// then
throw new LoadingDataError();

[dberardo-com]

thanks a lot, i think documentation would benefit greatly from this latest piece of information