I am trying to create a friend request/permission request logic in my app. Here is my scenario I am trying to solve.
Attached is a screenshot showing one of my sheets.
For each email address, I need to compile into one column all the email addresses for which status is marked TRUE (checked box). That column will determine with whom each email can initiate an action later.
In the final solution:
steverogers@gmail.com will have 2 emails (harveyspecter@gmail.com and ironman@avengers.com)
ironman@avengers.com will have 2 emails(steverogers@gmail.com and hulk@avengers.com)
harveyspecter@gmail.com will only have 1 email (steverogers@gmail.com) because mikeross and jessica have not ticked yet.
It doesn’t matter whether an email was the requester or receiver. They both count as a connection.
I could be going about this solution a long way, but if there is a shorter way, I’d appreciate pointing me in the right direction.
Assuming you have a user profiles sheet.
-
In this request sheet, create a template column joining the two emails separated by a comma.
-
Still in the same sheet, create an If Then Else column. If status is true then return the template column above, else empty. Let’s call this “Emails Joined”.
-
Continue with this, an ITE column to return the person to initiate with later on. Let’s call this “Email Initiate”. If “Emails Joined” does not contain signed-in user’s email then empty, if requester email is signed-in user then receiver’s email address, else requester email.
-
Finally, create a split text column, split the “Emails Joined” column by the comma.
-
Back to the profiles sheet, create a relation column to the request sheet, source being the user’s email, destination is the split text column.
-
Use a join text column to join the “Email Initiate” column. You should have what you want.
2 Likes
You are the best! This worked perfectly.
Thank you very much!!!
1 Like
