[Bug]: Wrong result when referencing a different sheet in `OFFSET` function

[bacek97]

Description

Passing a range as the first argument to an OFFSET formula results in an error For example: ={OFFSET(A1:B1, 0, 0)} ={OFFSET(myRangeOne, 0, 0)}

// expected result in all cases: cellA
const HyperFormula = require('hyperformula');

const options = {
  licenseKey: 'gpl-v3',
};

// Success
let hf = HyperFormula.HyperFormula.buildFromArray(
  [['cellA', 'cellB', '=OFFSET(A1, 0, 0)']],
  options
);
console.log(
  'Result1:',
  hf.getCellValue(hf.simpleCellAddressFromString('C1', 0))
);

// Success
hf = HyperFormula.HyperFormula.buildFromArray(
  [['cellA', 'cellB', '={OFFSET(A1, 0, 0):OFFSET(A1, 0, 0)}']],
  options
);
console.log(
  'Result2:',
  hf.getCellValue(hf.simpleCellAddressFromString('C1', 0))
);

// BUG: First argument to OFFSET is not a reference
hf = HyperFormula.HyperFormula.buildFromArray(
  [['cellA', 'cellB', '={OFFSET(A1:B1, 0, 0)}']],
  options
);
console.log(
  'Result3:',
  hf.getCellValue(hf.simpleCellAddressFromString('C1', 0))
);

// Success
hf = HyperFormula.HyperFormula.buildFromArray(
  [['cellA', 'cellB', `={'Sheet1'!$A$1:$B$1}`]],
  options
);
console.log(
  'Result4:',
  hf.getCellValue(hf.simpleCellAddressFromString('C1', 0))
);

// BUG: Cell range not allowed.
hf.addNamedExpression('myRangeOne', `='Sheet1'!$A$1:$B$1`);
hf.setCellContents(hf.simpleCellAddressFromString('C1', 0), [
  [`={myRangeOne}`],
]);
console.log(
  'Result5:',
  hf.getCellValue(hf.simpleCellAddressFromString('C1', 0))
);

// BUG: Cell range not allowed.
// hf.addNamedExpression("myRangeTwo", `={'Sheet1'!$A$1:$B$1}`)
// hf.setCellContents(hf.simpleCellAddressFromString('C1', 0), [[`=myRangeTwo`]] )
// console.log(
//   'Result6:',
//   hf.getCellValue(hf.simpleCellAddressFromString('C1', 0))
// );

// BUG: First argument to OFFSET is not a reference.
hf.setCellContents(hf.simpleCellAddressFromString('C1', 0), [
  [`={OFFSET(myRangeOne, 0, 0)}`],
]);
console.log(
  'Result7:',
  hf.getCellValue(hf.simpleCellAddressFromString('C1', 0))
);

Added: OFSSET formula cannot return range of another sheet

const HyperFormula = require('hyperformula');

let hf = HyperFormula.HyperFormula.buildEmpty( { licenseKey: 'gpl-v3' })
hf.addSheet();
hf.addSheet();
hf.setSheetContent(1, [['expectResult']]);
hf.setSheetContent(0, [['cellA', 'cellB', `={OFFSET('Sheet2'!A1, 0, 0)}`]]);

// BUG: Returned value from wrong sheet (cellA)
console.log(
  'Result1:',
  hf.getCellValue(hf.simpleCellAddressFromString('C1', 0))
);

Video or screenshots

No response

Demo

https://stackblitz.com/edit/stackblitz-starters-xv2m7kwi?file=index.js

HyperFormula version

2.7.1

Your framework

No response

Your environment

node v18.20.3, v23.1.0

[sequba]

Hi @bacek97, thank you for reporting this issue.

The implementation of the OFFSET function in HyperFormula has some limitations. The first argument must be a reference to a single cell. Ranges are not allowed. I will add this limitation to list of runtime differences between HyperFormula and Excel.

If you want to use OFFSET to work with ranges, you can take advantage of the 4th and 5th parameters (height and width):

=OFFSET(A1, 0, 0, 2, 2)

[bacek97]

Please add information about the impossibility of specifying a cell from another sheet.

[sequba]

The first argument must be a reference to a single cell. Ranges are not allowed.

This will be done in https://github.com/handsontable/hyperformula/pull/1488

Please add information about the impossibility of specifying a cell from another sheet.

I consider it a bug and I'll add it to our backlog as it's worth fixing. We'll use this issue (#1477) to track it. Thanks for reporting it, @bacek97.

[sequba]

Here's a unit test that demonstrates the issue:

  it('function OFFSET can reference a different sheet', () => {
    const engine = HyperFormula.buildFromSheets({
      Sheet1: [['sheet1']],
      Sheet2: [['sheet2', '=OFFSET(Sheet1!A1, 0, 0)']],
    })

    // Error: expect(received).toEqual(expected)
    // Expected: "sheet1"
    // Received: "sheet2"
    expect(engine.getCellValue(adr('B1', engine.getSheetId('Sheet2')))).toEqual('sheet1')
  })

[sequba]

Hi @bacek97

I am happy to share the good news. We just launched HyperFormula v3.1.0, which fixes this issue.

Here, you can read more about the changes in this version.