Thank you for the video, that was very helpful.
First of all, I want to mention that how you had your Projects table with an Email column as well as a Role column both set up as row owners looks correct to me. I think it did in fact have that set up correctly. So the first thing I would do is add the Role column back to that table, fill it with ‘Admin’, and make it a row owner column. In regards to that process, I see no reason why you would have to consider using google sheets over glide tables.
However, in your User table, I think you have a little bit of confusion between Row Owners and Roles. They have separate, but complimentary functions. At 2:45 in your video you show how you set up your user profiles. Everything looks to be set up correctly. The email column is the email of the signed in user, and the role column is the role that has been assigned to that user. But, in your user table you have assigned Row Owners to both the Email column and the Role column. Having the email column as a row owner is good. That means that the user that is signed in will only have access to their own user row because the email that they signed in with will match the email in only their own row. However, you have also set your Role column as a row owner. In reality, what you have done is not only assign a role to a user, but you have also granted row owner access to all other rows that have that same assigned role. By doing that, you have granted anybody with a Client role to have access to any other User table row that has a row owner column that contain ‘Client’. Ideally you would have a Role column for the purpose of setting up the Role value in the user profile, but you would also have another Role column (or maybe ‘Admin Role’) that would be populated with only ‘Admin’. That ‘Admin Role’ column would be what becomes a row owner column. So in the end, in the User table, instead of granting Clients access to all other Client user rows and Admins access to all Admin user rows…instead Clients will only have access to their own row because they are not an Admin, which would match the value in the AdminRole row owner column, but they will have access to their own row because of the email row owner column. As for Admins, they will have access to every row because their Role is Admin and every AdminRole value is Admin.
TLDR: Remove row owners from your Role column. Then create a second role column, name it ‘Admin Role’ and fill it with Admin, then set that as your row owner column. Leave the user profile settings as is.
Now, I don’t know if the above is the source of your problem in the Projects table or not, but I would at least start there. The way you initially showed your Projects table with a Role column assigned as a row owner column, did look correct.
Once you’ve corrected your User table, then try the same tests that you did before. On the surface, it possibly looks like a bug, but I know things can get weirded out when adding and removing row owners and when switching user views. Normally I’m more concerned about what the app interface editor shows regarding row owners as opposed to what the data editor shows. Things get weird in the data editor, because all data is still there, unlike the reduced set of owned rows that you would get in the app. The app interface editor is usually more accurate in respecting row owners. So usually if the app is doing what it’s supposed to do, then it’s less of a concern what the data editor is doing.
Keep in mind that glide has been going through some major changes in it’s underlying code. This is not normal for glide, but their old code wasn’t as flexible to allow growth and enhancements. This is a good change in the long run, but it has some growing pains as they work out some bugs.
(I was going to add one more thing, but I forgot. If I remember, I’ll add another post.)