ARRAYFORMULA

a.k.a. ARRAYFORMULA

You’ve probably written a formula in the first row of your sheet and then copied it all the way down the column. However, this can cause issues:

  1. When new rows get added – the formula doesn't get copied down.

  2. Glide can see this formula and can count it as an existing – even if you don't see data in your sheet. This results in new records that are added in your Glide app appearing at the bottom of your sheet

Well =ARRAYFORMULA can help with that.

The =ARRAYFORMULA can be used for lots of things but one of the simplest is copying.

Keep a live duplicate of a column

Let's say I have a column in my sheet that I want to duplicate and keep updated. I can use =ARRAYFORMULA() to do this. In the image below we write =ARRAYFORMULA(A2:A).

The A2:A is what we call an open ended reference - meaning that it starts at A2 and goes all the way to the end of A.

Instead of using =A2 in column 4 and then copying the formula down, we have wrapped the =A2 formula in an =ARRAYFORMULA which allows us to apply our formula to an array of rows.

Concatenating text

You can also use the ARRAYFORMULA to concatenate (join) text into greater structures.

For example, here is a sheet with friends’ birthdays. We want to create a Message column that uses the Next Age and Days Away columns to compose a message about their upcoming birthday. We're using the formula ="Turns " & D2 & " in " & E2 & " days” to calculate the message to display in cell F2

By wrapping this formula with ARRAYFORMULA and changing cell references D2 and E2 to open-ended ranges D2:D and E2:E, we can calculate the message for all rows.

="Turns " & D2 & " in " & E2 & " days”

becomes

=ARRAYFORMULA("Turns " & D2:D & " in " & E2:E & " days”)

You can see that the formula is applied for every row, so now we have a message for all of our friends! One small gotcha is that the full column is computed, even for rows without birthdays:

We can avoid generating messages for empty rows with an IF formula that checks whether our input columns have data. Specifically, we’ll require Next Age (column D) to be non-empty to calculate a message.

=ARRAYFORMULA("Turns " & D2:D & " in " & E2:E & " days”)

becomes

=ARRAYFORMULA(IF(LEN(D2:D) = 0, "", "Turns " & D2:D & " in " & E2:E & " days”))

Now we can use the Message column as the list item detail in a Birthdays Glide app to see how old our friends are, and how close their next birthday is at a glance:

Glide also has a Template Column that can achieve this in the data editor.

Automatically copy down functions

You can also encase your functions with the =ARRAYFORMULA to copy the formula down for new rows.

Below we have a list of scores and their grade. We have a formula that outputs a "Pass" if the score is greater or equal to 10 and a "Fail" if it's less → =if(A2>=10,"Pass","Fail").

Instead of copying this formula down, we can encase it in the =ARRAYFORMULA and use open-ended references to automatically copy it down to all rows.

However, when we do this we'll get "Fail" for all empty rows. If we add an =IF() statement in our function we can make our function not populate rows that are empty.

=ARRAYFORMULA(if(D2:D>=10,"Pass",if(D2:D="","","Fail")))

Now the formula will only populate when new rows are added and the column with the score in it is filled.