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.