Flexsheet Functions running on whole sheet multiple times (Bug)

Posted by: curiosichi on 7 November 2018, 5:35 pm EST

  • Posted 7 November 2018, 5:35 pm EST

    https://angular-ml186y.stackblitz.io

    When exiting a cell with a custom function, every function on the sheet refreshes itself. This was not the expected behavior, and I created an example that demonstrates this.

    With two custom functions, Tacos and Burritos, entering one of the cells and exiting triggers all functions on the page to run, multiple times EQUAL to the amount of functions on the sheet.

    I.E. 3 cells, one with "=Tacos()", one with "=Tacos()", and another with "=Burritos()", the functions are run 3 times each, and outputting to the console 6 times total.

    Please provide a fix for this issue, Thanks
  • Replied 8 November 2018, 1:52 am EST

    Hi,

    Thank for sharing your observations. This behavior is intended ,when a cell exits edit mode the Grid needs to refresh all cells and re-render them as cells may have dependencies on other cells. In this case it leads to the custom function to be added again. However we have optimized this behavior so that it doesn't affect application performance. If it is then please let me know we will look into it,
  • Replied 8 November 2018, 2:15 pm EST

    This example was created just to show the issue at hand, but this does severely affect our application.

    We have custom functions that pull data from separate sheets, which might be pointing to a cell that in itself is the same custom function that points at other custom functions. We have examples of this that goes up to 4-5 cell/functions deep.

    The getCellValue() function equates the target cell's function, so if the targeted cell has a function, it triggers off many times. For one cell on sheet 1, we might see 12 functions all have to evaluate themselves.

    For initialization, we see a delay of 20+ seconds before the flexsheet opens, and this is on a powerful development machine. On our clients' more conservative PCs, I would expect this to be even slower.

    All that said, flexsheet runs these functions multiple times each, same as the "intended behavior" of the simplistic example I provided above. There must be a way to limit multiple refreshes.

    While I am very much concerned with these functions running twice during normal use (post initialization), the flaw is exponentially more severe during the initial load as the sheet runs the functions several times over (for use in triggering events on cell changes based on SUMs/Functions):

    https://www.grapecity.com/en/forums/wijmo/how-to-detect-cell-change-



    // register all formulas in sheet init
    registerAllFormulas(flexSheet){
    flexSheet.beginUpdate();
    const selectedIndex = flexSheet.selectedSheetIndex;

    for(let i = 0; i < flexSheet.sheets.length; i++){
    this.registerSheetFormulas(flexSheet, flexSheet.sheets[i]);
    }

    flexSheet.selectedSheetIndex = selectedIndex;
    flexSheet.endUpdate();
    }

    // register formulas in single sheet
    registerSheetFormulas(flexSheet, sheet){
    flexSheet.selectedSheetIndex = flexSheet.sheets.indexOf(sheet);

    for(let i = 0; i < flexSheet.rows.length; i++){
    for(let j = 0; j < flexSheet.columns.length; j++){
    const cellData = flexSheet.getCellData(i,j,false);


    if(cellData && cellData.toString().startsWith('=')){

    flexSheet._calcEngine.evaluate(cellData);

    this.addRemoveExpDependencies(cellData, flexSheet, sheet.name.toLowerCase(), i, j);
    }
    }
    }
    }

    // add/remove all dependencies a cell requires based on expresion in cell
    addRemoveExpDependencies(exp:string, flexSheet, sheetName, row, col, shouldRemove?){
    let expr;

    try{
    expr = flexSheet._calcEngine._checkCache(exp);
    }catch(err){
    return;
    }

    if(typeof expr === 'string'){
    return;
    }

    const dependentCell = sheetName+'__!'+row+'__!'+col;

    this.addRemoveExp(expr, flexSheet.selectedSheet.name.toLowerCase(), dependentCell.toLowerCase(), shouldRemove);
    }

    // add/remove all dependencies a cell requires based on expresion in cell
    addRemoveExp(exp, sheetName, dependentCell, shouldRemove?){
    if(exp.cells) {
    this.addRemoveCells(exp.cells, exp.sheetRef || sheetName, dependentCell, shouldRemove);
    return;
    } else if (exp._params) {
    this.addRemoveParams(exp._params, sheetName, dependentCell, shouldRemove);
    return;
    }

    if (exp._leftExpr) {
    this.addRemoveExp(exp._leftExpr, sheetName, dependentCell, shouldRemove);
    }

    if (exp._rightExpr) {
    this.addRemoveExp(exp._rightExpr, sheetName, dependentCell, shouldRemove);
    }
    }

    // add/remove dependencies on a function param
    addRemoveParams(params, sheetName, dependentCell, shouldRemove?){
    for(let i = 0; i < params.length; i++){
    if (params[i].cells && sheetName) {
    this.addRemoveCells(params[i].cells, params[i].sheetRef || sheetName, dependentCell, shouldRemove);
    }
    }
    }

    addRemoveCells(cells , sheetName, dependentCell, shouldRemove?){

    for(let i = cells.topRow; i <= cells.bottomRow; i++){

    for(let j = cells.leftCol; j <= cells.rightCol; j++){

    const dependencyCell = sheetName+'__!'+i+'__!'+j;

    if (!this.cellsDependencyMonitor[dependencyCell]) {
    this.cellsDependencyMonitor[dependencyCell] = [];
    }

    const tempIndex = this.cellsDependencyMonitor[dependencyCell].indexOf(dependentCell);

    if (!shouldRemove && tempIndex < 0) {
    this.cellsDependencyMonitor[dependencyCell].push(dependentCell);
    } else if (shouldRemove && tempIndex >= 0){
    this.cellsDependencyMonitor[dependencyCell].splice(tempIndex, 1);
    }

    }
    }
    }


  • Replied 9 November 2018, 4:00 am EST

    Hi,

    Thank you for elaborating the issue. This issue has been escalated to the concerned team so that they can investigate the issue. The internal tracking ID for this case is 352914. We will update you once we have more information.
  • Replied 6 February 2019, 8:05 pm EST

    Any update on this request?



    Here is an image breaking down our load time for a flexsheet book with ~20 pages of grids up to 30 columns by 200 rows (maybe a total of 75,000 cells, ~20,000 calculating cells with functions). The 4 seconds to run 'All' functions is a bit misleading, its just running a select group for data validation. The 25 seconds is the more concerning part, and that is running the code pasted above in my previous response.

    We can't load just one page or evaluate functions on just one page at a time because many of these sheets' functions reference data from other sheets, so it's all or nothing. Once this load is complete, using the flexsheet is fairly instant. The initial load is bad though, as you can see, and it locks the UI thread so the browser is basically dead.

    Cutting the 25 seconds with the ability to detect grid changes would be really nice... any chance that could be a feature request?

    https://www.grapecity.com/en/forums/wijmo/how-to-detect-cell-change-
  • Replied 7 February 2019, 1:11 am EST

    Hi,

    I have requested the concerned team to look into ion priority and also shared additional information that you have provided. Will get back to you on this very soon.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels