[Bug]: moveColumns and setColumnOrder update relative cell references differently

[JohannCooper]

Description

Behavior

HyperFormula.moveColumns updates formulas with relative cell references to use the new column addresses.

Demo: https://codesandbox.io/p/sandbox/hyperformula-movecolumns-relative-references-7zlvyz

HyperFormula.setColumnOrder does not update formulas with relative cell references.

Demo: https://codesandbox.io/p/sandbox/hyperformula-setcolumnorder-relative-references-3n7y35

Expectation

I would expect HyperFormula.setColumnOrder to update formulas with relative cell references to match the behavior of HyperFormula.moveColumns.

My understanding is that HyperFormula.setColumnOrder should behave identically to multiple, equivalent calls to HyperFormula.moveColumns.

For example:

const engine = HyperFormula.buildFromSheets({
  Sheet1: [["A", "B", "C", "D"]],
});

engine.setColumnOrder(0, [2, 3, 0, 1]);

// [[ "C", "D", "A", "B" ]]
console.log(engine.getSheetSerialized(0));

should always behave equivalently to

const engine = HyperFormula.buildFromSheets({
  Sheet1: [["A", "B", "C", "D"]],
});

engine.moveColumns(0, 0, 1, 4);
engine.moveColumns(0, 0, 1, 4);

// [[ "C", "D", "A", "B" ]]
console.log(engine.getSheetSerialized(0));

If this is not the intended behavior, could you please provide a recommendation for how to move multiple columns simultaneously such that formulas with relative cell references are updated.

Video or screenshots

No response

Demo

See description (multiple demos provided)

HyperFormula version

3.0.0

Your framework

No response

Your environment

Ubuntu 24.04.2 LTS

[AMBudnik]

Thank you for sharing the issue report, @JohannCooper. I'm sorry for the details. I missed the notification for the issue.

I have to agree that based on the description of the moveColumns() method in the documentation (https://hyperformula.handsontable.com/api/classes/hyperformula.html#movecolumns) I would also expect to see the barqux being reflected as the value of engine.getCellValue({ sheet: 0, col: 0, row: 0 }) after switching those two columns.

I will mark this report as a bug.

[JohannCooper]

Thanks for following up, @AMBudnik.

I agree that this is a bug but I think moveColumns() is functioning correctly and I would expect the value foobaz to remain after moving the columns. I think setColumnOrder() is the function that is behaving incorrectly.

This is the behavior in Google Sheets, for example.

simplescreenrecorder-2025-03-27_08.31.53.mp4