New Bug Encountered: Regenerating structure of table does not adjust spreadsheet formula

The “Spreadsheet formula” you see toward the bottom of the attached image is probably rather difficult to interpret if you haven’t had to work with formulas like this before. These formulas are automatically generated by AppSheet based on a corresponding formula in your spreadsheet. And, they have an important role: When you add a new record, the "sum(R2C[17]:RC[17]) produces a normal formula in your spreadsheet. In this case, because this column is “A” in the spreadsheet, the following formula is produced:

=sum(R$2:R2)

The key point is that the “17” means “17 to the right of this column” which turns out to be “R.”

Now, the problem is that, if I insert a column in my spreadsheet somewhere between A and R, the formula will no longer correspond to the same columns, even after the “regeneration” process. It’s difficult to fix these formulas manually and I doubt that most AppSheet users know how to do this.

So, I’d like to request that the regeneration process be adjusted to automatically recalculate the spreadsheet formulas correctly, if possible.

In the meantime, users should know that inserting columns can mess up your spreadsheet formulas. It’s also good to know that, if you have to, you can manually, change the formula to make it word properly again. If, for example, I had inserted a column between A and R, I would want to change the 17s to 18s. By the way, negative numbers indicate the number of columns to the left whereas positive numbers indicate the number to the right.

AppSheet hasn’t provided good documentation about this, as far as I know. It would be nice if the documentation could be improved as well. There is the following page but it doesn’t describe what I have written here:

1 Like

It occurred to me after I posted this that the problem (failure of the Spreadsheet formulas to be regerated correctly) may have been due to the fact that my sheet was empty (not formulas inside) at the time I inserted a column and then regenerated. That may be something to be careful about. Still it would be cool if the platform could adjust for this kind of user error. Repair can be difficult.