Chart of averages

I agree that arrayformulas are best avoided where possible.
But it sounds like you may not be constructing them correctly. The whole idea of an arrayformula is that you don’t have to “define it” for any specific number of rows. When constructed correctly the formula will be automatically applied as new rows are added.

Can you post an example of one of your arrayformulas? (we might be able to help you to construct it better)

User Specific columns can still be used for users that aren’t signed in, you just need to have them in tables other than your User Profiles table. They will still work in exactly the same way, the only difference is that because they won’t be associated with a signed-in user, the values won’t persist across multiple sessions/devices.


Yes, sorry for bothering, I see now that it works. Thanks!

I’ll make a few assumptions: your arrayformula is in row 1 or 2 of the C column, the formula is using basic data from columns A and B, you are running the arrayformula down the entire C column, and the formula will add itself to every new row (C2:C would be one of the arguments).

Now let’s say you have headers or column titles in row 1, 2 rows of basic data in rows 2 and 3, and what seems to be no data at all from rows 4 to 1000 (for example). It’s all “empty” from row 4 onwards.

Actually, because of the arrayformula, the rows 4 to 1000 are not empty. They might appear empty, but they are not, because each cell in column C is populated with a formula from the arrayformula, which happens to be returning no result.

Glide cannot therefore use the rows 4 to 1000, because these rows are not available. Glide adds any additional data from row 1001 onwards.

To fix this, delete all the rows where you don’t have any basic data. So in my example above, you would delete rows 4 to 1000.

To delete a row (and not just the data in the row): select a row, right click, “Delete row”


Perfecting an imperfection, works perfect! Thanks, but who could imagine that you should use “C2:C” and then delete these rows aimed to obtain formula propagation based on arrayformula from Googlesheet when Glide adds new row! I need your sincere confession: how did you found that workaround? :slight_smile:

I don’t see this as a Google Sheets workaround or hack.

C2:C1000 in an arrayformula will set the formula for the array of cells C2 to C1000. C2:C will set the formula for the array of cells C2 to the rest of column C.

When we open an empty Google spreadsheet, we are given a canvas of columns (dozens) and rows (thousands) to work with. This is quite practical in most situations and that way most people don’t need to bother with adding columns or rows, but from the perspective of using a Google sheet as a source of data (database) for Glide, having such a big canvas is not ideal.

The first thing I do when formatting a Google sheet for Glide if I’m starting from scratch: I will delete all columns beyond F and all rows beyond 11. I’ll end up with a miniscule table and anything outside of it will be grey. I used to be even more extreme and leave the cells A1 and A2 only with a column header in A1.

Long story short: arrayformula populates your cells with a formula, so though the output is null and the cell might appear empty, the cell is not empty. In fact, if we try to manually type in data in a cell below an arrayformula, I believe we get an error message.


That’s all very good advice :+1:

I do the same, but I use a small snippet of Apps Script that processes all sheets in one go :wink: