I have a Pro app running with Email Whitelist authentication and likely to handle about 500 users. Each user will have the ability to provide feedback on themselves on a regular basis. Their feedback is not seen by other users but can be seen by an admin.
My design captures all of the feedback in a single sheet with a row per feedback item. This has a relationship with the users sheet via email so that a user’s feedback items are linked to them. It also uses the email as row owner to ensure that each user can only see their own feedback items. A separate Admin app provides access to all feedback items.
All good so far, however I am going to hit an issue if the users like this app (which I hope they will) and start providing feedback on a regular basis. If I have 500 users then after 50 items of feedback each I will hit the 25,000 row limit in Glide / Google (not actually sure which).
So, my proposed strategy is to only let the user review their last, say, 25 feedback items and use something like Integromat to “clean up things” on a daily basis by extracting the oldest feedback items and moving them to another “data store” somewhere. It is important that they are retained for audit purposes.
However, this in itself introduces some issues:
It’s a shame that the user won’t be able to see their entire history
The admin will need to check both Google and the other “data store” to see the full picture
Integromat (or Zapier) will need to DELETE not just clear the older rows - not sure they do that?
What could I use as the “data source” to hold the older feedback items?
Whilst I REALLY like Glide I’m wondering if I might be better using a tool that supports a database “back end” rather than relying on Google Sheets. That would be a massive shame since Glide has addressed everything that I’ve needed to date, I love using it, and the community is awesome.
You can have an array column of emails to not have another app, let’s say “email 1” stores the user’s email, “email 2” is automatically populated by the admin email when “email 1” is not empty. Then set the row owner on that array column in the Editor.
I actually did this same thing a while ago for a post on here, you can see the idea here. Duplicate the sheet and show only last 25 feedback items by each user in the app. I don’t think you need to delete it, 25000 rows is not much for a Sheet, the maximum cells they can hold is 5 million, it’s more of a bottleneck in the connection between Glide and Sheets, but as long as you keep it to only last 25 I don’t think there would be a problem.
Regarding the need for an Admin app: I wasn’t aware that Glide supported setting row owner on an array column of email addresses. That sounds awesome. I think you’re saying that this means the row can be accessed by ANY of the email addresses in the email columns that form the array. Very cool. Presumably the “admin” email column needs to be populated with an ARRAYFORMULA in the sheet in order to have it “ripple” down through?
Regarding the sheet row count: Apologies but I’m not quite getting your idea. Are you saying that 25,000 rows is not an issue (leaving admin access aside for the moment) since the app will only ever pull back the user’s feedback rows, which will always be a significantly smaller number?
If that’s not the case then can you explain the “sheet duplication” method briefly so I can try to get my head around it?
You could write a script to shift the old data to another sheet in the same spreadsheet. Glide just counts the data in the tabs your are using on the app itself. That way your old data remains in the same spreadsheet so it becomes simpler for the admin and you never hit the 25000 row limit.
Yes that’s what I mean. Have an arrayformula to autopopulate the admin email in the “Email 2” column.
Regarding the sheet row count, Manan have explained it to you, the duplicated sheet in my idea will be used to display in the app, the original one, which you use the form to write to, will stay in the Sheet and not counted towards your row count or affect your app performance.
