Adding an array of emails to row owner column

Hi all

I’d like to pull off something like the below, if someone is please able to assist:

  • A user is able to add items to a list in the tab ‘Entries’
  • In another tab, ‘Contacts’, they’ve got a list of contacts grouped by ‘inner circle’ and ‘outer circle’ (i.e. close contacts vs not-so-close contacts)
  • When they add a new item to ‘Entries’, they can indicate who they want to share it with (“just me”, “inner circle”, “all”) before they submit the item

So what I’ve done so far is the below…

  • 3 tables: Entries, Contacts, Entries_working (the latter is used when the user adds a new entry, i.e. a single row table)

  • In Contacts:
    -an ITE column that pulls out the email address of the user’s contact if the owner of that contact is the ‘signed in user’
    -a joined list that pulls all of the email addresses from the ITE column, separated by a comma
    -a split text column that splits the joined list based on the comma, so there’s now an array of the user’s contacts’ email addresses

  • In Entries_working:
    -there’s a relation column that links Contacts and Entries_working based on the ‘User Profile’ Row ID
    -a lookup column (‘all user contacts’) pulls out the split text column with the array of emails, from Contacts
    -once an entry is submitted, all of the values in this table are added to the main Entries table (except ‘all user contacts’…an area I’m stuck on)

  • In Entries:
    -there’s a ‘User’ column which is an array of emails, and it’s a row owner column so only those users can see that entry

So there are two areas I’ve gotten stuck:

  1. How can I add the ‘all user contacts’ array of emails in Entries_working to the Entries table in the ‘User’ column? I can’t find the option to add this array as part of the custom action logic, maybe it’s incompatible…
  2. I’m trying to work out how to modify the logic above so that when a user is adding a new entry, they can indicate their preferred privacy setting and only the relevant email addresses are added as row owners (i.e. just the user…or inner circle contacts…or all the user’s contacts).

I imagine the above might be a bit confusing, so please let me know if there’s anything I can do to help clarify it a bit more :slight_smile: Looking forward to your thoughts!

So in short, you want a dynamic list of row owners but seems like you’re using calculated columns in Glide to do it?

At the moment, calculated columns can not be set as row owners.

As you want an array of emails, I think the long con here is to switch everything to Google Sheets. Every time a user posts an item, you will then send to a third party automation tool (Make/Zapier) the user’s email and their intended privacy.

Then, in Make/Zapier, lookup all emails that match the two params above in your data (contact of user and matching privacy). Join that list together and write it to the destination table.

You will then have to use an arrayformula with split, and an arrayformula to dynamically name the columns so that Glide can read it as an array.

It is a complex method, and the down side is if the user adds someone new, the old items won’t be shared with them.

If you want it to update, then I think you can add a JOIN and FILTER combination of formula in a “row owners” column every time you add a new row with the method above via Make (they can add formula, not just values). The reasoning for this is that join & filter don’t work with arrayformulas.

2 Likes

I was thinking you can use a roles setup to help, still in Google Sheets, creating a column to concatenate the “contact owner” with the “privacy settings” (close/not-so-close), but it gets a bit confusing when I thought an email can be a contact of multiple users, and you have to let contacts become a part of the Users table to have the roles setup…

Thank you for your answer Thinh! Yikes so I guess it’s not so simple. I’m looking forward to digging into the above solution and giving this a go.

I wanted to clarify - contacts are a part of the users table as well, i.e. they need to be a user for another user to be able to share their entries with them. Does that make the roles setup possible? Or, like you say, a contact could be the contact of multiple users, so not sure if that’s a show-stopper or not.

Also, if there’s a way to have a list of email addresses to be row owner, where the email addresses are the contacts of a user, without using calculated columns…I’m open to a solution like that too. Maybe I’ve overcomplicated it with the calculated column method I’ve used?

Just testing out Make at the moment, using just the need to pull a joined list of user’s contacts into Google Sheets (without worrying about privacy settings just yet).

I get the below error - guaranteed this is a rookie mistake I imagine, but isn’t ‘watch rows’ a trigger?