Arrayformula storage issue

Hi Jeff,

I’ve got loads of arrayformulas across very many cells in multiple sheets. However, only one sheet seems to add 500 rows when a form is filled. A typical formula on this sheet looks like this: =ARRAYFORMULA(iferror(if(A2:A="","",sumif(Orders!M2:M,A2:A,Orders!J2:J)),“Error”))

I’m putting the arrayformulae in row 2.

Ok. I just wanted to make sure your formula wasn’t offset by a row. I’ve seen that throw things into a loop and add thousands of new empty rows. I’ve never seen it add 500 rows for each entry of things are set up correctly, but I’ll try to test it out tonight and see if it happens for me.

Thanks Jeff.

1 Like

I couldn’t duplicate your issue, but I’m not working with the same data either. My best guess is that you have a formula that’s doing something weird, like pulling the number of rows from a different sheet. I tired a few different things, but I couldn’t replicate. As mentioned in your other post, if you could share your sheet or allowed your app to be copied, then we could take a closer look. Otherwise, have you tried duplicating your app and sheet and trying it in the duplicate, or have you tried recreating a new sheet tab with the same data to see if something is just weird with that particular sheet? I’m not sure on this one.

Hi Jeff - here’s the app and sheet: https://papaglidecomm.glideapp.io/

The affected sheet is called Order Summary.
I tried it with a duplicate app, and had the same issue, but I haven’t tried creating a new sheet tab with the same data.

500 lines GIF

Hi @Jeff_Hager, I’ve made the above GIF to demonstrate the problem in action.

OK, I think I understand this a little better now. I don’t think I realized that your sheet was built from a unique formula. I think the rows are being created by google when the UNIQUE formula hits the end of the sheet and automatically adds the 500 rows as a buffer. I think what we have here is a non-issue. Since your are only building the sheet from a unique formula instead of a from a form button (this is what I thought was happening), you really don’t have to worry about the extra rows unlike using arrayformulas in a sheet that filled via the Add button or a Form button. In that case, the new rows would be added to the very bottom of the sheet as a new row. The Unique formula that you are using will always keep the data at the top of the sheet. The only thing I could not duplicate is the number of used rows in Glide jumping to 500/500. For me adding a new item to the cart only added 2 rows. I’m guessing you maybe had a formula in your sheet that I couldn’t see in your GIF that wasn’t writing a blank value if that row wasn’t filled from the unique formula. I’m assuming you maybe fixed that. Even with an arrayformula and the extra rows, if you are filling them with blanks when the formula doesn’t apply, then Glide will not recognize them as filled rows.

I think the 500 additional rows in your Order Summary sheet should not be a problem and should not affect anything in your app. It’s just the google sheet expanding for any potential future values.

1 Like

Hi Jeff - thanks for taking the time to look through the sheet and formulae, and for identifying the UNIQUE formula as driving the behaviour rather than the form submission or another element of Glide’s working, thereby dispelling alternative hypotheses.

Nevertheless, I’m not sure I see how it’s a non-issue if the app storage limit is filled as soon as anyone enters an item into the cart. I haven’t changed the formulas so I’m perplexed as to how you’re unable to recreate the same behaviour demonstrated in my GIF. As you’ve noted, the behaviour manifests when the UNIQUE formula hits the end of the sheet; perhaps in your troubleshooting, you were not at the end of the “Order Summary” sheet? If not, is there a specific adjustment to some of the formulas to either a) allow me to have extra rows without Glide counting them, b) allow for the UNIQUE formula to add only 1 row as a buffer instead of 500?

As my issue persists, I remain at a loss as to how to proceed.

Maybe I should rephrase. It’s not a non issue, but we are looking at the wrong thing. Yes, I hit end of the order sheet when adding to the cart and yes it created the 500 new rows, but the new rows were empty. Glide ignores empty rows. All it did was add 2 rows to the quota for the 500 row limit. My only thought is you have a formula or a script or something populating those new rows with data that I don’t have in my copy of your app. I couldn’t verify that in your GIF since you didn’t show all of the columns. Can you verify that the new rows are completely empty?

Hi Jeff,

I really appreciate the time you’ve spent looking into this. I’m not sure how you’re ending up with only 2 rows being added by Glide as I’m still ending up with 500.

I’ve made new GIFs to demonstrate. In the first, I’m setting up the new user, showing that I’ve no empty rows in my Google Sheet or on the corresponding Glide sheet.

Initial Setup

In the second, I’m demonstrating the problem in action, when the new user makes an order, what it does to the corresponding sheet in Glide, and what it looks like in the Google Sheet.

I don’t have scripts - haven’t yet learnt how to do those - and there are no formulas in my copy of the app that are different from those in yours.

Did you make any changes to the sheet or in Glide in order to get Glide to only add / recognise 2 rows as opposed to all 500?

I’ll keep experimenting and see if I’m able to achieve the same result you seem to have achieved.

It seems that switch / checkbox items are being automatically filled in the new rows generated by the creation of the 500 rows, so the new rows are being created automatically with a FALSE value in them. These are the only ‘filled’ elements in the new rows, and I can’t figure out what is causing this to happen.

Clearing the contents of the new rows seems to return the count in Glide back to the correct level (if the rows themselves are not deleted), and subsequent form submissions do not trigger the autofill of the switch columns with FALSE.
However, if I am at the end of the sheet (such as when the rows themselves are deleted), then the creation of a 500 new rows by Google sheets somehow seems to cause the columns with switches to fill FALSE values into these new rows automatically.

This seems to be the root of the problem, finally. However, it implies that the patch would be to have as many empty rows as possible in the Order Summary sheet so that the 500 new rows trigger doesn’t cause an autofill of the columns with switches in them. Doing this, unfortunately, means that it takes an enormous amount of time for the arrayformulae on the sheet to calculate since it iterates through the entire document (the reason for minimizing the number of rows to begin with). It also doesn’t solve the problem of Google Sheet’s autofilling of boolean values in new rows triggered by a new form from Glide being filled.

Sorry I have not been in this discussion, what is the arrayformula for said column?

Hi Thinh,

This is the app with the sheet: https://papaglidecomm.glideapp.io/ - the relevant sheet is called “Order Summary” and the relevant columns are “Order Verified” and “Favourite”. They are one of the few columns in the whole sheet that do not have arrayformulae.

Below is a GIF of the problem now that I’ve realised that the autofilling of boolean values in newly formed rows is the problem.

Strangely enough, it’s made me notice a new problem with the relation column, where a match is being made with a non-matching field (see “Client Relation” column in Glide data).

Please let me know if you’re able to replicate the issue as somehow Ran and Jeff aren’t running into it.

I think you hit on the problem. I’ll bet it’s the checkboxes on your sheet. Those didn’t copy over when I copied your app. Instead those columns were only showing ‘TRUE’ or ‘FALSE’ or empty. That’s why i couldn’t duplicate your issue since a google sheet specific function like the checkboxes do not copy over when glide copies the sheet data to a new sheet in my account.

Go into your Google sheet, highlight the entire column (Order Verified and Favourite), right click and then click on Data Validation.

Now click on Remove Validation:
image

Finally delete all of your extra rows, if you have any, and try adding another order. It will still add the extra rows, but without the checkboxes, so Glide will no longer see those extra rows.

3 Likes

That did it!
Breath of fresh air Jeff! Thank you so much! It’s been such a thorn in my side, I’m extremely pleased with the result and really appreciative of the entire community’s help.

1 Like

Possibly premature celebration - If I delete the rows again, and remove validation, and the checkboxes get filled in-app, then the checkboxes return in Google Sheets with the new 500 rows. Must I just have to accept having the additional empty rows on the sheet that Glide doesn’t count, but which (I hypothesise) slow down the app?

Sorry to drag this on…thanks again.

Hi,
I usually use IF B3:B is empty (ISBLANK) leave COLUMN… empty
It must work

I see what you are saying. I could duplicate it. One thing you could do is get crafty to trick it a little bit. First create a dummy order with an email that has all 'z’s. Then sort your unique formula in your orders summary sheet so the dummy record is always at the bottom and can’t have the checkbox validation set on it. Then when the 500 new rows kick in, it shouldn’t add the checkbox validation because I think it’s based on the last row in the sheet before the 500 rows are added. It’s not ideal, but a thought.

I know you are concerned about speed, but I really don’t think that having the extra rows should affect performance as glide ignores them, so I would also consider just adding 10,000 rows, so you have enough of a buffer and don’t have to worry about the 500 rows issue.

1 Like

Thanks Jeff.

1 Like