asynchronous custom formulas

[christianaranda]

Hello,

Is there any way to have the custom formula function resolve the function by returning a Promise or via a callback? Using this code as an example:

var COLORIZE = function(v) {
    var d = document.createElement('div');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

var data = [
    [ 'red', '=COLORIZE(A1)' ],
    [ 'green', '=COLORIZE(A2)' ],
    [ 'blue', '=COLORIZE(A3)' ],
];

We need the COLORIZE function to be asynchronous and return a Promise or use a callback, but unfortunately I don't see any evidence in the Jspreadsheet code that shows asynchronous support.

Is anyone doing this?

[hodeware]

Hi,

This probably what you are looking for. https://jsfiddle.net/spreadsheet/g8mo9cpr/

In this example, you change the value of A1, and the other cell which contains the formula CUSTOM will take two seconds to change its value.

This only works on the PRO distribution.

[christianaranda]

Thanks for that, and it is on the right track, but I can't reference the value in another cell because it returns a div and not the value. If in your example I use the formula "=B2", it results in [object HTMLDivElement] instead of the value.

If I can get the value back, I might be in business.

[hodeware]

You have special methods you can use:

TABLE() you get the instance of the worksheet so you can use all methods. VALUE(x, y) you get the value from other cells.

If you describe exactly what do you want I might change the example to help you.

[christianaranda]

Thanks @hodeware. My objective is to be able to populate a cell (call it B1) using the results of an asynchronous REST API call based on user input, and then be able to use the value of B1 in another cell's formula.

Let B1 = the result of the query: how many database users are there? Result: 7

Let B2 = the maximum number of database users: B2=10

Let B3 = B2-B1 Result: 3

With the example you provided, I can populate the value in B1 from the result of the asynchronous REST API call, but I don't see that I can calculate the value in B3 correctly because the formula for B1 returns the div and not the value.

Thanks again.

[hodeware]

I have done something here for you. https://jsfiddle.net/spreadsheet/g8mo9cpr/