[GH #2051] Move cells with formulas after initialization

Tags: #<Tag:0x00007efc65035508> #<Tag:0x00007efc650353c8>
#1

Hi there!
I’m working on a project when adding/removing a column is a risk (calculation mismatch in different places in an app). I need to add a column so the easiest way is to add it at the end of a spreadsheet and move it to the beginning.
The problem here is I use formulas and when I try to use manualColumnMove plugin to reorder columns I get an error #VALUE (I guess Hyperformula doesn’t get rebuild and recalculated).
Here’s an example with my attempt.
How can I move columns after a spreadsheet initialization so the formulas stay correct?

#2

Hi @alojzy231

In this case using the manualColumnMove plugin and setting it the way you did happens before the Formulas plugin gets the access to the rearranged data which results in the error you get.

Instead, you can use the moveColumn method of the manualColumnMove plugin directly so the data is registered and the Formulas plugin can rearrange the formula underneath. Here’s an example: https://jsfiddle.net/handsoncode/0ecwq2kx/

1 Like
#3

Hi!
I’ve checked your solution and it works in the case I’ve added as and example but my real use case is as follows:
I’ve got two spreadsheets: a source one and one that only has references to the other spreadsheet (='Sheet1'!A1 formulas).
When I try moving a column after the Formulas plugin get rearranged by code the cells stay as before and only columns’ headers are moved but if I manually move columns by dragging them everything works fine.
Here’s code example
Could you provide some a solution/steps that I may take so it works correctly so when I try to rearrange columns in a spreadsheet where there are only references to other spreadsheet?

#4

Hi @alojzy231

Thank you for the update. That’s really strange. I tried many different scenarios, and the column header is always mismatched no matter the settings. However, my final attempt revealed a solution, which is what I previously suggested as not the best way to do this in such scenario.

Please, check this example, where I used the solution you used in your initial demo, it works fine: https://stackblitz.com/edit/react-pd1kzb-ggrwx8?file=src%2FExampleComponent.jsx

#5

I’ve tested the solution with our setup (52 columns, some of them hidden + custom settings) and everything looked fine but I’ve found out that on one column we use the combination of referencing a cell in the same spreadsheet and referencing a cell in another spreadsheet (=F1-'Sheet1'!P1).
Sorry for confusion!
In this case using manualColumnsMove setting looks promising but then I get an issue where columns:

In this case using the manualColumnMove plugin and setting it the way you did happens before the Formulas plugin gets the access to the rearranged data which results in the error you get.

as you mentioned (so references to another spreadsheet work correctly but they take into calculations the position of the same spreadsheet after the columns are moved).
Here’s an example.
Is there a way to combine 2 solutions that you provided: moving columns when having calculations within the same spreadsheet and moving columns when referencing cells in other spreadsheet?

#6

Hi @alojzy231

Thank you for more details. I’ll check your example and will see if there’s any other approach we can try here. If not I will report it internally. For now, it looks more and more like a bug in our manualColumnsMove plugin.

1 Like
#7

Hi @alojzy231

I have one additional question, as I checked your demo and I noticed that you put a comment saying that the equation should be equal to 3, but it seems to be correct that it’s 4, as the formula is referencing to A1 and B2 cells. The columns moved are correct, as well as their content, so the calculation seems to be ok. Can you please confirm it?

#8

Hi @adrian.szymanski!
The columns titles may be misleading so I’ve edited the example.
When I try to move columns with manualColumnMove={[2]} prop I get a result:

image
image.png700×132 3.37 KB

(the formula in sum of #1 and #2 persists: =A1 + B1)
but when I try to move columns by dragging I get a result:
image
(the formula in sum of #1 and #2 is updated to: =B1 + C1).
What I want achieve by code (so by using manualColumnMove prop) is the same as a user would move columns by dragging (so the formulas are updated accordingly)

#9

Hi @alojzy231

Thank you for clarification. I reported this case internally as a bug, as it should give the same result, regarding we are basically performing the same action, but in two different ways.

This will probably need a specification and deeper investigation by our development team, but I will update you as soon as we have estimation and we include it in our roadmap.

1 Like
#10

Hi Adrian!
Thanks for the ongoing support so far. However, it’s been a while, and I’m still unclear about when or if this bug with the manualColumnMove plugin will be fixed.

As a paid user with a license, I need to know if there is any progress or timeline on this. Can you please confirm:

  1. Is there a fix possible for this issue? If so, what’s the expected timeframe for its release?
  2. If a fix isn’t possible in the near term, what alternative approaches or workarounds should I consider to avoid this problem?

I’m trying to make informed decisions about how to proceed, so any clear and definitive updates would be greatly appreciated. Looking forward to a detailed response soon!

#11

Hi @alojzy231

As you know from our investigation and different approaches checked, we don’t have a workaround for this problem so it has to be fixed at the core.

This issue is added in our internal backlog and awaits estimation and fixing. I will update you as soon as I have more accurate information.

1 Like