Filter Data by Domain of User

[djnguyen]

Hello,

I am building a custom AppSheet that post subcontracting roles available for our vendors to submit a resume for the team to review. Our vendors will be signing into AppSheets using their company credentials and I was wondering if there is a way to show submissions that were submitted by everyone under the same domain.

Example - Bob and Amy both work for ACME and have a domain of (@acme.com). If Bob submits a candidate - I'd like to set up a way in which Amy can see his entries and vice-versa. However, Joe works at BACME (@bacme.com) and he should not see Bob and Amy's entries as he is not in the company. 

The internal team who is reviewing these submissions are admins and will see all entries.

Any thoughts on how to set up these security filters? All the resume submissions will be on a single Google Sheet.

[mohssin365 · Accepted solution]

To create an AppSheet application for vendors to submit resumes with restricted visibility, start by requiring company credential logins. Create a `UserDomain` table in Google Sheets with columns for email addresses, extracted domains, and user roles.

Use a virtual column in AppSheet to extract the user's domain from their email. Create a `Submissions` table for resumes with candidate names, submitters' emails, and company names.

Apply security filters so vendors see only their company submissions using `[Company] = LOOKUP(USEREMAIL(), UserDomain, Email, Domain)`, while admins can view all with `OR([Company] = LOOKUP(USEREMAIL(), UserDomain, Email, Domain), [Role] = "Admin")`. Finally, test the app to ensure proper access controls are in place.