Using ARRAYFORMULA()'s in Row 1 of your Spreadsheet

Project Showcase
1

I can’t take credit for this tip but I just found out about this trick/tip by reading a post here. So the “Normal” way of using an ARRAYFORMULA function is to use it in row 2 of a given column. You just have to be careful not to accidentally deleted it, or have a user delete it.

So the next idea was to put it in row 2 but not put any other data in the row and then hide or minimize that row. Basically your data for the non ARRAYFORMULA columns would start on row 3.

Then I saw the post I’ll link to below. Credit goes to @sardamit and @B_Y who had their own way of handling it. In both methods, both the column title and the ARRAYFORMULA are in row 1. Making it much harder to accidentally delete the accidentally. Just take care if you decide to change the column title however :slight_smile:
Here is a demo spreadsheet that shows how to format it, and put it to use with a simple formula. Make a copy and test it out.
Here are the formulas that are in row 1 of the above spreadsheet

={"Add Method 1";ARRAYFORMULA(IF(ISBLANK(A2:A),"",A2:A + B2:B + C2:C))}

=ARRAYFORMULA(IF(ROW(A:A)=1,"Add Method 2",IF(ISBLANK(A:A),"",A:A+B:B+C:C)))

Both work great, I guess it’s just a matter of which you like best visually and is easier for you to understand what is going on.

Here is the topic I gleamed this tidbit from: If user deletes 1st row, he loses array formula

Edit: One other tip is to delete all of the empty rows below any data you may have in the sheet that has those arrayformulas. Many a person, including yours truly, has had a “problem” that a new row if data that you can see in the app, but has somehow disappeared in the sheet. It hasn’t disappeared it’s just at the very bottom row, maybe row 1001, with 900 empty rows before it.

18 Likes
2

Thank you, George. :slight_smile:

3

Yeah that’s it I make it work ! in fact at first I was indeed doing an arrayformula from D2 to D and the new data was going on row 37000… So the solution was to delete all the lines under the already existing data as you said so when I was creating new profile, it was adding it at the end of the sheet, so just under my other rows with Data ! But even when cutting the sheet to a 10 row sheet, you still have to use the formula from D2 to D otherwise it will add the new values in row 13 ( what I was doing). Thank you all for your answers.

4

Also, for anyone new to using arrayformula() The ISBLANK() stops the arrayformula from populating blank rows, which as George notes, will populate all 1000 rows in your sheet, blowing out the Glide free plan allowance of 500 rows. The solution, again as George notes, is to delete all those blank rows.

However, it should be noted, the IF(ISBLANK(A:A)… check is still necessary even if you delete all the empty rows at the bottom of your sheet.

The reason being, when Glide deletes a record, it only clears the row in the sheet, leaving a blank row, which gets populated by arrayformulas. This then can show as a bogus (empty) record in your app.

It should also be noted, that for simple concatenations of columns, use Glide’s own Template Column in the Data Editor.

5 Likes
6

Hi,

I have seen this behavior when any user delete an item (row) from a Form. I would hope that Glide deletes the whole row

7

Take care guys, if you use zapier, and moreover multisteo zaps with the last step writing in your sheet, you may encounter issue if you delete rows, it will be a mess.

1 Like
8

glad a found this a while ago. and it’s good Glide understand this as it is. a perfect way to work with ArrayFormula :smiley:

Now the phrase “if(A1:A=”","",…" has become my best friend.

2 Likes