I know ARRAYFORMULA is out of favor at the moment, and that’s great. Because it seems to me there’s a fundamental problem with it: namely, the ARRAYFORMULA itself exists in a row of your data. If that row gets deleted, your ARRAYFORMULA is gone. (I’ve tried it.)
Have I missed something? Because, if not, every ARRAYFORMULA is a bug waiting to happen. (Yes, you can work around this in various ugly ways, that IMHO are also bugs waiting to happen.)
This seems like a big thing not to have come up before, and I find it unlikely I’d be the guy who’d notice it. So let me know how I’m wrong here.
There are two ways to approach this that many of us have done in the past.
One is to place the arrayformula in your first row, but append the header name to the first item. ={"Header";ARRAYFORMULA(IF(ISBLANK(A2:A),"",Formula here))}
The second method is to create two rows. The first will be the header, and the second row will contain the arrayformula. Just make sure the the arrayformula is set up so it doesn’t return a value in that second blank row. I do this with an IF statement to return blank, if the row is empty. =arrayformula(IF(LEN(A2:A) = 0, “”, “formula here”))
I’m not sure it’s a question of right or wrong. ARRAYFORMULA comes up often and can be a source of trouble, but perhaps at times they cannot be avoided.
Let’s keep a few things in mind:
Some Glide users have an preexisting Google Sheets spreadsheet, it is cleanly formatted for Glide, and they intend to build a Glide project on top of the sheet. The sheet’s existence precedes that of the app and some formulas might need to stay there, including an arrayformula, maybe because that sheet is used by other team members and not specifically for the Glide project.
New used don’t necessarily understand how arrayformula is interpreted by Glide: that cells populated by arrayformula may appear blank in GS but in fact are not blank at all. Data added to sheet therefore is added at the bottom and that confuses a lot of new Glide developers.
Generally speaking, formulae are best avoided in Google Sheets if the computation can be done by a computed column in a Glide Table. This applies to arrayformula, but not only.
Yeah, I agree with that. But it’s clear from the online discussions that people still use it. I’ve been staying away from it myself, although once in a while I’m certainly tempted.
Jeff, I haven’t used arrayformula in ages. Any reason why you use isblank ARRAYFORMULA(IF(ISBLANK(A2:A) in one case and len=0 ARRAYFORMULA(IF(LEN(A2:A)=0 in the other?
To be clearer about this, it’s a Google Sheets API problem. They see rows with arrayformula as “not empty”, hence skipping those rows when you try to add data. Glide adds data through Google Sheets API, hence the situation above arises.
Absolutely agree with that, and have been bitten by it in the past.
I have older projects that still have the odd arrayformula here or there.
But for new projects, I just don’t use them - or any sheet formulas for that matter.
That whole section on Spreadsheet Tips is really outdated. There is a big effort going on at the moment to update the Glide Docs, but I don’t believe this section has been touched yet.
don’t be afraid of arrayformulas, it works great every time… just don’t write empty values in cells using "", Glide does not see them as empty, simply skip action with no value ie: if(A:A="",,B:B)
I just saw this post.
One more efficient way to do this (but more complex), is using QUERY.
=QUERY(‘Sheet1’!A:D, “select Col2, Col4 where Col1 is not null”, 1)
It’s slightly more complex but chatGPT does it easily for us
