Forums Home / Spread / SpreadJS
Posted by: ismail00b on 28 January 2018, 12:17 am EST
ismail00b
Posted 28 January 2018, 12:17 am EST
I want to import an excel file with a customAsyncFunc used in ArrayFormula.
Steps I followed:
1-Define a customAsyncFunc to fetch data from the server.
2-in context.setResult I used the data to fill the array of the formula.
Result: Cells always override by the first value. (Error)
deepak.sharma
Posted 29 January 2018, 5:37 am EST
Hello,
Could you please provide me the sample application or the code snippet to test this issue further? Are you importing the Excel file and then applying the Async function?
Thanks,
Deepak Sharma
ismail00b
Posted 29 January 2018, 8:25 pm EST
It’s a little complicated to provide a sample, I will try to explain another time, else if we can set up a remote session to share with u my screen to see the application.
so what I have is an excel sheet containing a custom functions that are not built in in excel. So if I use spreadJs to import the excel sheet, the cells containing formula based on those custom functions will not be reconigzed, As a solution I created customFunction with spreadjs and that’s cool. But the issue is those functions are async and return Arrays, so they are producing a strange behavior, which is:
suppose that my CustomAsyncFunc is AB(A1) will produce an output [4,8,16,32,64]
that will be displayd as array output on C1:C5 so the result should be for each cell as follow [C1:4,C2:8,C3:16,C4:32,C5:64] but unexpectedly the result is [C1:4,C2:4,C3:4,C4:4,C5:4] (only 4 displayed, in the background you can see that the arrayformula is calculated for each cell from C1 to C5, each time it started to put values from index 0 wich is 4)
deepak.sharma
Posted 30 January 2018, 10:24 am EST
Hello,
If possible, please provide me the code snippet you are using to create Async function. And also let me know if the function works fine without opening the Excel file?
Thanks,
Deepak Sharma
ismail00b
Posted 30 January 2018, 8:52 pm EST
Hello,
I tried to create a sample to describe the issue, you can find a spreadsheet associated with the sample, containing a arbitrary customFunc, I created it as follow in my code:
var ab = function () { }
ab.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("AB", 1, 1);
//Set default value to "Loading..."
ab.prototype.defaultValue = function () { return "Loading..."; };
//Override the evaluateAsync function
ab.prototype.evaluateAsync = function (context, arg1) {
//Use a timeout to simulate the server side evaluate or use an ajax post
setTimeout(function () {
var data = fakeData(arg1, 1)
var result = data.results.map(function (val, key) { return [key == 0 ? val.TimeStamp : JSON.parse(val.TimeStamp), val.Value] });
//context.setAsyncResult(result);
console.log(result);
context.setAsyncResult(new MyArray(result));
}, 2000);
}
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction("AB", new ab());
this is the fakeData helper func to generate some data :
function fakeData(val, n){
var data = {results : []}
for(i = 1; i <= n; i++) data.results.push({TimeStamp: i*val.length, Value: 2*i*val.length});
return data;
}
I have catched three abnormal behaviors:
1-We can not refer to a sheet with spaces in name.
2-In the Array formula when only one value is returned, the value is populated over all the cells in the array formula (which should not)
3-this is A big issue you have to look after or explain how to do it otherwise. The custom async function is running for each cell in the formula that’s affect the performance when we have a bigger Array, (remark: the data is already fetched for the first time why we ar doing it again and again …)
https://www.dropbox.com/s/ei7qo26viscvzo7/AsyncFuncErrors.zip
Best regards,
Ismail Bougnouch
deepak.sharma
Posted 1 February 2018, 10:18 am EST
Hello Ismail,
I am able to replicate this issue at my end. Hence I have asked the dev team to look into this.
I will let you know as soon as I get an update on this.
The tracking id for this issue is 253943
Thanks,
Deepak Sharma
deepak.sharma
Posted 2 February 2018, 7:55 am EST
Hello,
As per the development team,
When reference a sheet whose name contains space, single quotes is required. As in this file, the referenced cell should be ‘Inputs in’!$A$2. You can try with Excel: make one cell in inputs sheet refer 1st sheet’s, you will get what the sheet name is used in formula.
The below code in the demo won’t get more expected data (limited by the 2nd parameter 1 that used as the loop count).
var data = fakeData(arg1, 1)
Replace it with a bigger number, for example
var data = fakeData(arg1, 20);
then you can get an array with 20 items.
As in the fakeData function, in order to avoid exception caused by null value, check is required; as one workaround fix the val argument before use it:
val = val || ‘’;
Thanks,
Deepak Sharma
ismail00b
Posted 2 February 2018, 2:05 pm EST
Hello,
Thanks for your answer, as for the 1st one it’s clear. But for the 2nd I know that if I pass more than 1 for argument I will not have the issue (exactly if I pass the exact expected number of values), for the project I’m working on there is a lot of custom functions that returns data depends on parameters, in Excel application the normal behavior of ArrayFormula is to print out #N/A instead of copying the first value, and this should be handled by the library not by customFunctions as it will be so tedious to check for those cases.
For the 3rd point, I think the problem of making those calls for each cell is affecting the client-side rendering performance, an example of an array with 200values, customFunc will run 200times redoing the same work for nothing. (the asyncfunc will run for 200 times each time returning and displaying 200values …, anyway I hope that you find a solution for this design issue).
Thanks,
Ismail Bougnouch
deepak.sharma
Posted 6 February 2018, 11:05 am EST
Hi Ismail,
I will forward the request to the development team. I will let you know once I get an update on this.
Thanks,
Deepak Sharma
deepak.sharma
Posted 29 March 2018, 3:08 am EST
Hi Ismail,
For 2nd issue please understand excel array formula policy:
When referring range is a single cell, excel will copy the first cell to others.
When referring range is multi-cells, excel will exactly match the array result, if not match, will handle as #N/A.
See attached excel.
For 3rd issues, we confirm that it is a known issue which we try to improve in future build(no ETA as of now) For now you to build the result cache by yourself as mentioned earlier.
Thanks,
Deepak SharmaTest.xlsx.zip
