Forgot to ask, does that solution apply to BigQuery data source as well?
Depends which part you’re asking about.
A Big Query data source cannot be the User Profiles table, so no you can’t use BQ to define multiple roles (or any roles) per user.
But you can apply Row Owners to a Big Query data source, so the roles that you’ve defined can be used as row owners in a Big Query connected table.
Does that answer the question?
1 Like
Yes -
So if my data is in BigQuery, and I would like to define multiple roles like mentioned, I’ll need to have my users table in Google sheets?
Would that work on the same app?
Yes, and yes 
2 Likes
I have another question regarding my use case, hope you can assist:
Data Source: BigQuery
Users Table: Google Sheets
I have UserA (an account manager) who can access data for all the stores in the state of NY, totaling 20 stores.
Since my data has a “State” column, I used it as a role, and UserA had access to all the relevant data.
Due to some organizational changes, it was decided that 2 of the NY stores will be transferred to another account manager, UserB. - So now userA can only have access to 18 of the NY stores data.
To tackle this, I thought of creating a custom role column instead of using the “State” column, and somehow mark all the data with this new role.
However this scenario may happen more frequently in the future, leading to potentially numerous custom roles… which would be hard to manage.
Since I use BigQuery as my data source, I will probably need to create the new role column(s) outside of Glide (which has its pros and cons).
I ruled out using role columns in Google Sheets, as it’s limited to 9 roles per user, and in my case, if I can’t use calculated columns on BigQuery data source, I would need to assign a role per company ID. As the NY example shows, 9 roles are not enough.
Maybe its possible to put an array of companyID’s inside a role column?
Is there a different course of action that you can suggest?
Thanks again!
If only that were true
Unfortunately, no.
I can’t think of a good way to deal with this off the top of my head, sorry.
1 Like
Hmmm…tough one to answer as I’m not sure on all the details, but what about using a grouping name that represents a territory rather than a specific state? So, you might have NY1, NY2, or NYNJ. If that data is in each row of the BQ data then you just have to change the user data to reflect.
If that info for territory isn’t in BQ now you can likely create it in your select statement which then shows in Glide as standard data, not computed, so will work fine with Row Owners and Roles.
1 Like