Array Formula not extending to rest of column

Karthick_Rajave · 06-05-2019 04:37 AM

I’m not sure why, but my array formula is not working in google sheet and even when it’s working, it doesn’t extend the formula down to through the rest of the column’s cells. Where have I gone wrong?

=ARRAYFORMULA(LOOKUP(2,1/($J:$J=$J87),$S:$S))

Aleksi

It won’t work if you have the row limit 87.

Karthick_Rajave

trying to bring the last value of column A. Ref below sheet. My array formula is working but it doesn’t extend the formula down to through the rest of the column’s cells.

Aleksi

Aaa… okay. It won’t work with the arrayformula. You would need to create a same functinality without arrayformula OR you could probably do that calculation directly with an app formula.

Controller_Plan

Hi! I also have ArrayFormulas and they were ALL working, until recently they stopped getting added to the new rows…

This and having an open limit like $A$2:$A, which used to work, I now have to add something like $A$2:$A10000 as to avoid having the limit reached too early (but this is still troublesome in terms of availability)

Also, When i modify the formula and regenerate, it doesn’t work, so i have to delete the spreadsheet formula form appsheet, save, and then regenerate…

LeventK

@Controller_Planeador
Please refer to this page:

Phil

When you do a “regenerate”, AppSheet checks the spreadsheet formulas in each cell of your worksheet. If every cell in a column contains the same spreadsheet formula, it stores that spreadsheet formula in R1C1 format in the “Spreadsheet Formula” property for the field. You can view and update the “Spreadsheet Formula” property in the Editor.

If a column contains different spreadsheet formulas, AppSheet displays a warning when you do the “regenerate”. It lists the first five variations of the spreadsheet formula that it finds in the column and tells you which rows contain each variation.

When you add a new record and a “Spreadsheet Formula” value is present, AppSheet inserts that spreadsheet formula in the newly added row.

LeventK

@Controller_Planeador
I may also advise constructing your ARRAYFORMULA in the Header of your gSheet (row#1) like this which won’t be treated as a spreadsheet formula:

=ARRAYFORMULA(IF(ROW()=1,"YourColumnName",IF(LEN(A2:A),*IF_TRUE:Your_Spreadsheet_Formula_Here*,"")))

Controller_Plan

Hi @LeventK, thanks for the info!

I have been using Appsheet for almost three years, and some of these Sheets Formula issues are new; I know because a lot of my Apps had them, and they have been crashing over the last couple of months, so I have to go, re-think the formulas and make new ones.

I think these kind of “upgrades” that render working features useless should be notified to users, so we can take appropiate action with enough time, and not when our users catch the issues

Thanks!

David S

Phil

I wrote the code to handle sheet formulas and aside from improving the error messages I am not aware of changes that should affect how sheet formulas are handled.

If you have a specific problem I am happy to investigate it.

Controller_Plan

Hi @Phil,

Basically they were working before (getting copied to new rows), and all of the sudden, they stopped being written to new rows.

The other thing that happened was that i could use “undefined” arrays in my formulas, like A1:A to get the whole column, that stopped working as well, I know have to write something like A1:A10000, even if there are only 100 rows that makes the Sheet slower, but it’s the only workaround I found that works.

If there’s anything i can do to help you look into it, please let me know!

Thanks!

Phil

Hi Controller_Planeador,

If there is a worksheet formula value in R1C1 format in the “Spreadsheet formula” property of the AppSheet table, we convert that formula from R1C1 format to A1 format, and place that formula into new rows that you add through the AppSheet app. At least, that is how it should work.

If you have either an app that is failing or a simple test case that fails, I am happy to investigate.

I ask for an example that reproduces the problem because it makes debugging the problem so much more efficient. I have found that reproducing problems is often much harder than it seems. Sometimes the problem is obvious, but more often there is some subtle combination of circumstances that provokes the problem. When you can provide a failing example, I can help you much better.

Since you have already worked around the problem, I am not sure if you want to invest more in this issue, but I am happy to help.

Controller_Plan

Hi @Phil!

Thanks! All our Apps are live right now, so I’ll have to wait until the weekend to reproduce the issue, I think I should be able to do it, and this will definitely help as it simplifies how formulas are formulated on Sheets

Thanks!

Phil

Great!

If it is easier to create a “test” app, feel free to do that. You might prefer that option because it safeguards your production app yet allows me to debug at a little lower risk to you.

On the other hand, I can debug your prod app is that is easier for you.

Controller_Plan

Hi @Phil

Here’s an example:

This is the formula:

When I add that table to Appsheet i get this:

It’s fixed if i change the formula like this and then regenerate the table’s columns:

This is a nuance, because it either makes the sheet calculate more than needed, or limits my formulas, and at some point my data might go over the “forced defined” limit and will miss data from the lookup.

Please note that this formula was working flawlessly before with Appsheet

Thanks!