Posted 22 August 2020, 4:24 am EST
We have issues with spreadsheet performance in executing custom functions.
a) Is there a way for all the asynchronous Javascript function to be accumulated/batched into a series of server side call rather than trigerring independently?
b) When we are using custom function on multiple tabs, the spreadsheet freezes for a time? What are the optimization tricks that we can use to avoid the spreadsheet freezing?
Sample code for reference below.
DCubeReadPLAN.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction("DCubeReadPLAN", 1, 7);
DCubeReadPLAN.prototype.evaluate = function (context, arg1, arg2, arg3, arg4, arg5, arg6, arg7) {
arg1 = arg1 || 0;
arg2 = arg2 || '';
arg3 = arg3 || '';
arg4 = arg4 || '';
arg5 = arg5 || '';
arg6 = arg6 || '';
arg7 = arg7 || '';
if (validateMasterTemplateCubeRead(arg4)) {
loadPanel.hide();
return false;
}
if (iCalcCounter === 0) progressBarStatus.option("visible", true);
iCalcCounter += 1;
if (iCalcCounter > 10000) divider = 1000;
var arrKey = generateArrKey("PLAN", arg1, arg2, arg3, arg4, arg5, arg6, arg7);
var validateFormula = validateCubeReadFormula(arg1, arg2, arg3, arg4, arg5, arg6, arg7);
if (validateFormula === '') {
getFromDictionary("PLAN", arg1, arg2, arg3, arg4, arg5, arg6, arg7)
// Calling resolve in the Promise will get us here, to the first then(…)
.then(function (result) {
if (result !== null) {
context.setAsyncResult(result.measureValue);
} else {
context.setAsyncResult(0);
}
iCalcCounterDone += 1;
if (iCalcCounterDone % divider === 0) {
var progressVal = Math.round((iCalcCounterDone / iCalcCounter) * 100);
progressBarStatus.option("value", progressVal);
updateStatusBar(progressVal);
//console.log("progressVal", progressVal);
if (progressVal <= 60) {
if (isSuspend === 0 && recalc === 0) {
isSuspend = 1;
spread.suspendPaint();
spread.suspendEvent();
//spread.suspendCalcService(true);
}
} else {
loadPanel.hide();
if (isSuspend === 1 && recalc === 0) {
isSuspend = 0;
//spread.resumeCalcService(false);
spread.resumeEvent();
spread.resumePaint();
}
}
} else {
refreshProgressBar();
}
})
.catch(function (error) {
iCalcCounterDone += 1;
console.log('error', error);
refreshProgressBar();
});
}
else {
iCalcCounterDone += 1;
errCount += 1;
if (errCount > 500) {
return false;
}
else {
refreshProgressBar();
setTimeout(function () {
context.setAsyncResult("#ERR: " + validateFormula);
}, 200);
}
}
};
DCubeReadPLAN.prototype.evaluateMode = function () {
return 0;
};
