# Google Sheets and ARRAYFORMULA
**URL:** https://community.glideapps.com/t/google-sheets-and-arrayformula/51609
**Category:** Ask for Help
**Created:** [October 12, 2022, 11:16pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609 "2022-10-12T23:16:27Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@escott](https://community.glideapps.com/u/escott)
#### Post date: [October 12, 2022, 11:16pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/1 "2022-10-12T23:16:27Z")
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.
Thanks.
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [October 12, 2022, 11:27pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/2 "2022-10-12T23:27:17Z")
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”))`
---
### Author:  [@nathanaelb](https://community.glideapps.com/u/nathanaelb)
#### Post date: [October 12, 2022, 11:28pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/3 "2022-10-12T23:28:10Z")
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.
---
### Author:  [@escott](https://community.glideapps.com/u/escott)
#### Post date: [October 12, 2022, 11:35pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/4 "2022-10-12T23:35:15Z")
I laughed out loud at the “Header” thing because I was trying to figure out a trick like that earlier. Nice one!
In the second example, wouldn’t you end up with a blank row in your data? For some apps not a problem, I image. But still.
Thanks!
---
### Author:  [@escott](https://community.glideapps.com/u/escott)
#### Post date: [October 12, 2022, 11:39pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/5 "2022-10-12T23:39:00Z")
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. 😈
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [October 12, 2022, 11:42pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/6 "2022-10-12T23:42:50Z")
Glide ignores blank rows.
---
### Author:  [@nathanaelb](https://community.glideapps.com/u/nathanaelb)
#### Post date: [October 12, 2022, 11:47pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/7 "2022-10-12T23:47:57Z")
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?
---
### Author:  [@escott](https://community.glideapps.com/u/escott)
#### Post date: [October 12, 2022, 11:48pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/8 "2022-10-12T23:48:10Z")
Good to know! I was just wondering that.
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [October 12, 2022, 11:53pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/9 "2022-10-12T23:53:25Z")
Hehe, I did a quick search in the forum to find examples. No particular reason other than those are the two examples I found.
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [October 13, 2022, 12:45am UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/10 "2022-10-13T00:45:11Z")
> [@nathanaelb](#):
>
> 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.
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.
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [October 13, 2022, 1:11am UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/11 "2022-10-13T01:11:21Z")
> [@escott](#):
>
> 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.)
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.
---
### Author:  [@escott](https://community.glideapps.com/u/escott)
#### Post date: [October 13, 2022, 1:27am UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/12 "2022-10-13T01:27:55Z")
The doc needs to catch up with current practice. ARRAYFORMULA is still there, with no warning about this issue or, IIRC, any other.
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [October 13, 2022, 1:32am UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/13 "2022-10-13T01:32:04Z")
yeah, for sure.
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.
---
### Author:  [@escott](https://community.glideapps.com/u/escott)
#### Post date: [October 13, 2022, 1:39am UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/14 "2022-10-13T01:39:28Z")
If Glide is looking for a doc guy, I know a great one. And no, it’s not me. 😁
---
### Author:  [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)
#### Post date: [October 13, 2022, 1:42am UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/15 "2022-10-13T01:42:21Z")
I’ll pass that on 🙂
---
### Author:  [@JackVaughan](https://community.glideapps.com/u/JackVaughan)
#### Post date: [October 13, 2022, 9:15am UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/16 "2022-10-13T09:15:14Z")
Great @escott put them in touch!
[jack.vaughan@heyglide.com](mailto:jack.vaughan@heyglide.com)
---
### Author:  [@escott](https://community.glideapps.com/u/escott)
#### Post date: [October 14, 2022, 9:40pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/17 "2022-10-14T21:40:35Z")
Will do!
---
### Author:  [@Uzo](https://community.glideapps.com/u/Uzo)
#### Post date: [October 14, 2022, 10:12pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/18 "2022-10-14T22:12:49Z")
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)`
---
### Author:  [@burningmikey](https://community.glideapps.com/u/burningmikey)
#### Post date: [July 12, 2023, 2:36pm UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/20 "2023-07-12T14:36:47Z")
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 😉
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [July 13, 2023, 12:30am UTC](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609/21 "2023-07-13T00:30:25Z")
Once you get the hang of it, I think it’s more robust, but for Glide I hardly use Sheet formulas nowadays.
[Next page](https://community.glideapps.com/t/google-sheets-and-arrayformula/51609.md?page=2)