Formulas becoming disconnected with what cell they're associated with

Posted by: kwalker on 12 September 2017, 2:00 am EST

    • Post Options:
    • Link

    Posted 12 September 2017, 2:00 am EST

    Hi, I tried to put in a support ticket for this, but it didn’t seem to work. I’ve found a rather big bug I believe. When adding a row and assigning formulas to that row in a loop, the formulas get disconnected with what cell they were assigned to, at least that’s what seems to be happening.

    
    for (let i = 0; i < 75; ++i) {
            const rowNumber = ~(i - 76);
            sheet.addRows(0, 1);
            
            sheet.getCell(0, 0).value(randNumber());
            sheet.getCell(0, 1).value(randNumber());
            sheet.getCell(0, 2).formula(`INDIRECT("A"&ROW())-INDIRECT("B"&ROW())`);
            sheet.getCell(0, 2).formula(`A${rowNumber}-B${rowNumber}`);
            sheet.getCell(0, 3).formula(`INDIRECT("A"&ROW())-INDIRECT("B"&ROW())`);
            sheet.getCell(0, 4).formula('ROW()');
        }
    
    

    Below is a full example (your file attachment told me “this file type is not able to be uploaded”). This bug happens whether I suspend event, paint and calc service or don’t.

    
    <html>
    <head>
    <script src="gc.spread.sheets.all.10.2.0.min.js"></script>
    </head>
    <body>
    <div id="ss"></div>
    <script>
        var randNumber = function () { return Math.floor(Math.random() * 100); };
        var div = document.getElementById('ss');
        var spread = new GC.Spread.Sheets.Workbook(div, { sheetCount: 1 });
    
        spread.suspendPaint();
        spread.suspendCalcService();
        spread.suspendEvent();
    
        const sheet = spread.getSheet(0);
        for (let i = 0; i < 75; ++i) {
            const rowNumber = ~(i - 76);
            sheet.addRows(0, 1);
            
            sheet.getCell(0, 0).value(randNumber());
            sheet.getCell(0, 1).value(randNumber());
            sheet.getCell(0, 2).formula(`A${rowNumber}-B${rowNumber}`);
            sheet.getCell(0, 3).formula(`INDIRECT("A"&ROW())-INDIRECT("B"&ROW())`);
            sheet.getCell(0, 4).formula('ROW()');
            sheet.getCell(0, 5).value(rowNumber);
        }
    
        spread.resumeEvent();
        spread.resumeCalcService(true);
        spread.resumePaint();
    </script>
    </body>
    </html>
    
    
  • Posted 12 October 2017, 2:28 am EST

    Hello,

    Sorry for missing out on this forum thread. I tried to use this code in my sample application but it throws an error everytime in Spread.Sheet JS code. Please refer to the attached image.

    Could you please provide me your sample application where you got the whole working and seeing the rows detached from the formulas? I would like to further debug this issue before escalating it to the developers.

    Thanks,

    Deepak Sharma

  • Posted 6 November 2017, 7:34 pm EST

    Hello,

    I was able to replicate this issue at my end. I saw that the formulas for Column2 cells are not updated correctly. However this is an issue with the approach that you are following to set formulas for cells in loop and not with SpreadJS calc engine. You can use the code below to get the formulas working correctly:

    
    const sheet = spread.getSheet(0);
                for (let i = 0; i < 75; ++i) {
                    const rowNumber = ~(i - 76);
                    sheet.addRows(0, 1);
                    sheet.getCell(0, 0).value(randNumber());
                    sheet.getCell(0, 1).value(randNumber());
                    sheet.getCell(0, 2).formula(`A1-B1`);
                    sheet.getCell(0, 3).formula(`INDIRECT("A"&ROW())-INDIRECT("B"&ROW())`);
                    sheet.getCell(0, 4).formula('ROW()');
                    sheet.getCell(0, 5).value(rowNumber);
                }
    
    

    I would also like to know why you are directly setting the formula after adding the rows for Rows 1-76 instead of using the approach above?

    Thanks,

    Deepak Sharma

Need extra support?

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

Learn More

Forum Channels