How to send an automation to multiple contacts in another sheet?

SPBops

Sheet A has a column of email addresses, the quantity of which is always unknown (could be 5 rows today, 10 rows tomorrow) and the content is not always static as people change roles (email x may be changed to email y tomorrow).

Sheet B has an automation to request an update when a row is updated.

How does Sheet B sent the update request to all the email addresses in Sheet A?

MichaelTCA

Hello,

Dynamic features can throw a wrench in the gears. From what I understand, I may be moving off course, but I think this will help. Tell me if I'm wrong and maybe we can work through it.

What does unknown mean in this circumstance? Unknown until tomorrow? Not predictable and never will be?

You could set up a function that checks to see if a value is contained within the target sheet from a list of people involved in another sheet. A "duplicate" value.

I am continuously importing data daily and moving rows around. One way to manage it is to use a count function to see if the data is already contained in the target sheet and use that as a condition in an automation. Such as greater than 1.

Where am I going with this:

If you use a sheet of just contact information, Name/emails or whatever you need, then use the function to reference whether the email address is used or not and send the automation to only who is used today. Lookup functions can pull addresses or urls, but they will not work if they have a display in front like a "hyperlink" within a cell. Then you can pull the currently used emails to sheet b and forget about sheet a for the meantime.

I believe you can also send emails to entire groups.

If you need to add a group manually, go to the contact column and add the new contact through the column properties.

Automations also have their own set of permissions so if people aren't shared to sheet b or the permissions aren't set correctly in the automations, the receiver may never see the email or the automation will fail.

In the end, you will have a sheet to manage the change in roles and another sheet for change in data.

Automations will send to the "active" contacts in sheet b OR use a group and send to all contacts from sheet a.

You can get creative with the functions. I recommend trying to condense the function into 1 column, but if you need the duplicate checker as a condition in the automation then you'll need 2 columns. One for the contact and one helper column.

SPBops

Thank you so much @MichaelTCA !

Actually, sheet A is the reference sheet :) The list of email addresses in sheet A is uploaded via Data Shuttle into one column (based on the current status of criteria from an original source sheet), so the automation email in sheet B needs to be sent to all the email addresses currently in sheet A.

Having said that, I now need to investigate "Groups" and see how the "Request an update" automation in Sheet B can send an email to the group...

(Is it normal to get this excited about such geeky challenges? 😮)

MichaelTCA

No problem! Oh ya it's normal. I don't know anyone who doesn't like being creative every now and then, AND get a sense of immediate accomplishment when everything goes right.

Since you already have most of the setup I was recommending, test this out and see if it works in Sheet B. I don't know how well it will work though.

=JOIN({Range of contacts/email addresses from Sheet A}, CHAR(10))

Both the column in Sheet A and the column in Sheet B (where this function is located) needs to be a Contact List in the column properties and make sure it allows multiple contacts per cell.

Since the automations are row-by-row, with this function you can take the entire list and input it into a single cell. If both the target and source columns are contact lists, an email address should show up in the cell. Run a test automation to see if the automation sends an update request to multiple contacts within a single cell.

Keep in mind the automation permissions or the workspace permissions. If the contacts aren't shared to the sheet or automations don't allow contacts beyond your account, the notification will not be sent.

SPBops

Thank you @MichaelTCA - that worked!

Next: Is it possible to automate permissions? i.e. if an email address appears in sheet A that hasn't appeared there before, they will need "Edit - cannot share" permission to fill out a form in sheet B. Can that be automated?

Or is this a new question to ask the community?

MichaelTCA

Awesome!

I assume you're talking about submission forms for new data to be entered?

Open the form settings:

Click on Share Form at the top right and select any of these options.

You can add a link to the update request automation or embed a form into a dashboard and enter the hyperlink for the dashboard in the update request.

Or maybe have a column in sheet A that uses an automation to record the date they were added and another automation to send the link of the form to all new entries (which would most likely have todays date or blank).

Forms are special. If you have access to the link, you really don't need anything else. You can use forms to have people outside of your account submit information. They won't even need a Smartsheet account or login to use the form.

Paul Newcome

Did you test the JOIN for contacts? Unless something has changed, there is no way to pull in multiple contacts via formula from separate cells that can be used as actual contacts in an automation. The default delimiter in multi-select contact columns is also ", " (comma space) and not CHAR(10) (line break).

Paul Newcome

Also... Users do not need access to the sheet if all they need to fill out is a form. All they need is a link to the form.

SPBops

Hello @Paul Newcome - Yes, the join formula worked! Both the columns in A and B are contacts, and the automation worked with multiple email addresses separated by line breaks (not commas) in the cell, too.

Paul Newcome

Are you able to provide screenshots of everything?

I (and quite a few other people) have tried in quite a few different ways to accomplish this starting not long after multi-select contact columns were introduced and again just today incase something has changed, and nothing is going through.

Paul Newcome

@MichaelTCA and @SPBops Are either of you able to provide screenshots? I have tested a few times yesterday and today, and the JOIN function pulling in multiple contacts into a contact type column isn't working for being able to send out automations. I have tried things a number of different ways including exactly as described above, and none of them are sending.

MichaelTCA

@Paul Newcome I did mention that I didn't know how well it would work. A JOIN function converts everything to a string. Also most lookup functions return the display value and not the email address or link behind it. I tried an INDEX/COLLECT function and it pulled over 1 actual contact address, but I will need to do more testing.

**I flagged the best answer to be removed/changed.**

If you have more information @SPBops, please let us know! Sorry about flagging the response and I really appreciate the consideration!

SPBops

@Paul Newcome

Everything worked up to the email being sent!

1. User enters email addresses into sheet A text column, often multiple times.
2. Sheet A includes a column to identify unique email addresses using =IF(COUNTIF([Quality Lead]$1:[Quality Lead]@row, [Quality Lead]@row) <> 1, 0, 1) which is a CELL formula.
3. Data Shuttle with filter offloads a list of unique email addresses
4. Data Shuttle uploads list to Sheet B into a text column
5. Sheet C links to Sheet B list using =JOIN({Market Quality Leads List Range 1}, CHAR(44)) into a contacts column
6. Sheet C now includes a column that contains a list of email addresses in one cell (I tried both CHAR10 and CHAR44).
7. Sheet C runs automation successfully but emails are not received.

Paul Newcome

Right. Because your JOIN function is outputting a text string. The automation will run, but it won't send anything out because it doesn't have a valid contact. Unfortunately that is the expected behavior.

SPBops

Thanks, @Paul Newcome . A meeting with Pro Desk concluded that an enhancement request be submitted "Sharing contacts in different sheets". It was a little joyful finding a challenge that couldn't be met with existing functionality :) Thank you for your help!

Paul Newcome

@SPBops I am fairly certain the Product Idea has already been submitted. You should be able to search through them, find it, then add your vote to it.