Surprisingly (or not), the app is still semi-functional.
The majority of these ~73K rows are activity rows. These are rows that refer to a specific interaction with a client. On the upside, our team is rather meticulous and have excellent logging ethics. On the down side. this isn’t Glide’s finest hour. Although these are rather simple rows, without any complex computations. It is not a good situation.
Any ideas about how to archive, yet allow users to access data upon request? Something like the 2nd layer of a database, which allows high volume storage, but low loading times. In other words, it will allow users access to VERY old data, but they’ll know it’ll take the system some time to retrieve this data.
I’ve been reading Jeff’s detailed post about his idea.
@Jeff_Hager - has this indeed allowed you to lower the row count and, more importantly, speed up the app?
How will you approach the notion of still allowing users access to your “ARCHIVE-” rows?
I was thinking about IMPORTRANGE, but I’m afraid it’ll mess up the app somewhere downstream, or even upstream. It’s also a bit challenging as this IMPORTRANGE will need to be user-specific but Google Sheets cannot see user-specific columns. Or can it?
Maybe this is something we need to do with the Glide API, but that’s hardly “low code”.
Any other ideas?
I know BQ is already in the works, but that for Enterprise plan that are expensive, to put it mildly.
We currently have a private beta for Enterprise customers testing support for 100,000-row projects in Glide Tables. We hope to have this generally available for Enterprise customers by the end of the year/early next year. Once we fully support larger datasets, we will likely begin to enforce row limits on our other plans. If you’d like to talk more about this, feel free to PM me, and we can schedule a meeting.
Thanks for your input.
So does that mean we won’t be allowed to exceed the limit, i.e. turning it into a hard limit?
This means that the lower paid tiers of Glide will be for small apps only
This specific app is on Private Pro plan, with a 25,000 row limit. This specific app is now at almost 73,000 rows and still somewhat functional. This means the row limit is soft.
If I understand correctly, in the future we won’t be able to get away with such an “Exceeded” situation.
I’m nowhere near the row count you have. I think I’m at around 9k rows, but I have data that is over 3 years old, so it’s no longer needed in the app. My method will keep the data in the sheet, but just changes the email so it’s ignored by row owners. What I’m doing does not affect the row count as far as glide is concerned. They are still included in my row count, but since the email no longer matches that of any signed in user, those rows are not downloaded, and the number of unnecessary calculations are reduced to make the app more responsive. My main goal was to reduce the number of rows that are downloaded for a particular user. It’s a slow archiving process (two rows archived for every row added), but yes I believe it’s slowly helping to improve speed.
I don’t anticipate ever having to restore any of these old rows. Eventually I’ll probably remove them from the sheet altogether, or build an automated script that deletes them or moves them to another unused sheet. I will always have a backup if I ever need it. For the past few years, I’ve been running an automated weekly script that creates a full duplicated timestamped backup of my google sheet. That way, if I screw something up I can easily restore specific rows or the entire database from a backup.
But, as of right now, if I need to restore anything, all I need to do is use Find/Replace to replace the archive email with the original email if needed. This will make the rows owned again, so they will download to the user’s device.
Mind you, this data that I’m archiving is mostly from one user, so if they would ever need something restored, they would just ask me and I would take care of it. I don’t have any sort of automated process for a user to request archived data.
No, Google sheets do not see user specific column data. User specific columns are linked to the signed in user, as well as the row ID of the specific row. Think of it like a relation/lookup to a special hidden internal glide table. Theoretically, I suppose you could remove a row that has user specific column data, and if you restore it, it would probably “restore” the user specific data too, as long as the same Row ID was restored with that row. What I don’t know is if Glide retains that user specific data indefinitely after a row is removed from a table, or if they eventually delete the internal user specific data as well.
That’s probably a scenerio you would have to test to see if user specific data is retained after a row is restored. My data mostly does not use user specific columns. Mostly I use user specific columns for custom forms or navigation purposes, so that data is not needed for long term storage. It’s only needed temporarily storing a value to make the form or navigation function.
For logging, you need to think out of the box a little bit. I would explore ways to keep the original live data, as well as a history log in the same row. I have seen stuff from others where they build a running audit log by appending JSON to a single column in the same row as the live data, and then if they ever need to see it in the app, they can unpack it into a working table. Or you could use a similar method to build a dynamic html table to view it as a read only audit log.
Thanks Jeff. As always, a delight to read your detailed reposes.
Can you point me towards such solution?
I was thinking about a similar solution. In which such older rows will be moved to a seperate sheet. In my use case, we’re talking about interaction with clients. Each row is a single interaction with a single client. I see two main options here:
Create a seperate “log” sheet for each client. This will than be easily accessible via a link to this spreadsheet, or even inside a web-view component.
Create one unified log sheet for all clients. Now, the problem here is that I don’t know how to easily filter data based on the user’s request. I was thinking about something like a URL parameter as part of the URL that sends the user to the sheet. This way I can construct a URL that will show the user only the logged rows for this specific client, and not the entire (rather huge) log sheet. I have not yet found a way to use these parameters. The closest I came was here
I believe this was actually a video @Darren_Murphy made during one of our discussion. Back than, the HTML log was referring to a single row. However, this can indeed be the basis for a solution to this archive challenge. Happy to hear thoughts from others about how to make this happen.
No, this is something completely different, using a technique that I’ve only recently developed.
Jeff alluded to it earlier in the thread…
It exploits the fact that the hard limit for a single cell in Glide tables is one megabyte, or roughly one million characters. I’m not ready to share it publicly yet, but I will.
Sounds promising. However, won’t this defeat the purpose of speeding up the app? Instead of retrieving one megabyte of columns, we’ll squeeze it all into a single cell. Basically, we’re moving the computation workload from one dimension (vertical) to another (horizontal).
Google Sheets is limiting to 50K characters per cell. If I understand correctly, you mean to use a Glide Table for the archiving, rather than a Google Sheet. That brings us to some challenges with data retention and accessibility from 3rd party applications/services.
Update: Sorry, just now noticed you have indeed clearly mentioned Glide Tables.
Can we just park this discussion until I’m ready to share more details?
My technique might or might not be applicable to your situation. I developed it for specific use case that I needed. It works for my use case, but in no way will it be a one size fits all as a way of working around row limits.
Sure, I have not meant to seem as if I’m pushing you to share more details. Just wanted to point out a (yet another) limit.
While I don’t expect to come anywhere near these limits soon, I do always look ahead and try to build a scalable process.
So Glide table is indeed allowing a much higher cell charcter limit, but the app is close to unreponsive when there is just one single cell like this, inside a dummy app with almost zero data.
Not sure how it will respond when we have dozens (hundreds?) of cells with 100-200K characters. Or a thousand cells with 5K characters.
I’ve been informed by Glide Engineering that the 1MB is a hard limit, and testing that I’ve done - and I believe @NFNHello has also done similar tests - bears that out. I suspect that if you try to do anything with that - ie. use it in an action to add a row or update a value - it will fail.
Probably moot, anyway. Although the theoretical limit might be 1MB, in practice it will be significantly less. For my use case, I started to see performance degradation at around 300k. But that probably depends what you do with it.
Ha, I’m not sure I understand either. I was trying to guess at what you meant by “The dark side of Glide”.
What do you mean by that? I’ve never built an app that has needed more than 100k rows, so I’m trying to learn if there is something that I should be aware of as apps grow to such sizes.
Through my lens of never needing more than 25k rows, I can always buy more rows from Glide. So, for me, more rows are just a cost issue.
