[GH-DEV1501] Handsontables Does Not Work When Use the Cell and Formulas Options Together

Tags: #<Tag:0x00007f0b0fe532a8> #<Tag:0x00007f0b0fe53168> #<Tag:0x00007f0b0fe52fd8>

For some reason, the Handsontables are not working (they don’t show up at all / don’t render) when I use the “cell” and “formulas” options together with multiple worksheets.

The line that is causing the issue is when I use “this.instance.getData();” in the “cells”. It works when I don’t use the “formulas” option, but when I do, it stops working.

Code:

const hyperformulaInstance = HyperFormula.buildEmpty({
    licenseKey: 'internal-use-in-handsontable',
});

hyperformulaInstance.addSheet('Sheet1')
hyperformulaInstance.addSheet('Sheet2')

const data1 = [
    ['Sheet1 A1', 'Sheet1 B1', '=Sheet2!A1'],
    ['Sheet1 A2', 'Sheet1 B2', '=Sheet2!A2'],
];

const data2 = [
    ['Sheet2 A1', 'Sheet2 B1', '=Sheet1!A1'],
    ['Sheet2 A2', 'Sheet2 B2', '=Sheet1!A2'],
];

const container1 = document.querySelector('#example1');
const hot1 = new Handsontable(container1, {
    data: data1,
    height: 'auto',
    cells(row, col) {
        const cellProperties = {};
       
        //This next line is causing the issue.
        const data = this.instance.getData();
        console.log(data[row][col]);

        if (col >= 2) {
            cellProperties.readOnly = false;
        } else {
            cellProperties.readOnly = true;
        }
        return cellProperties;
    },

    formulas: {
        engine: hyperformulaInstance,
        sheetName: 'Sheet1',
    },
    licenseKey: 'non-commercial-and-evaluation',
});

const container2 = document.querySelector('#example2');
const hot2 = new Handsontable(container2, {
    data: data2,
    height: 'auto',
    formulas: {
        engine: hyperformulaInstance,
        sheetName: 'Sheet2',
    },
    licenseKey: 'non-commercial-and-evaluation',
});

Hi again @ptownbro

This idea looks similar to what I shared recently with another user, here https://jsfiddle.net/Lkdy67z4/2/ in the form of a bootstrap-driven Excell look with sheets in tabs.

If you need anything more than that please let me know.

Hm. I’m sorry. I don’t see what you mean. I didn’t see the use of the “cell” option on your fiddle. My issue is when I use the “cell” and “formula” options together, using the “this.instance.getData();” method causes the handsontable to not work/display.

Ah yes, sorry for the confusion.

You’re right. When I simplify the demo it seems that any call to data getter within cells and data with a formula (enabling formulas without formula data in dataset does not cause this error) creates an infinite loop.

And it seems that is issue happens in version v12.4.0. Here’s 12.3.0 where it all works well https://jsfiddle.net/handsoncode/9L52nd3a/3/

I will add this issue to our Github board and update you as soon as we fix it. Till that time I recommend working on v12.3.0.

Thank you that fixed the issue. Two follow-ups if I may.

  1. Will we be alerted that this is fixed in the current version?
  2. I noticed you have been using a reference to “fixer.js” in a number of your examples. What is this and is it needed in our code?

Snippet

<script src="https://handsontable.com/docs/scripts/fixer.js"></script>

Yes, I will update you here in this forum thread upon the fix. When it comes to the fixer.js just like in its comment

// Necessary for jsFiddle environment

so it’s only there as we keep our demos served via JSFiddle