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

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?

«1

Answers

  • MichaelTCA
    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
    SPBops ✭✭
    edited 07/18/23

    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
    MichaelTCA ✭✭✭✭✭✭
    edited 07/18/23

    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
    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
    MichaelTCA ✭✭✭✭✭✭
    edited 07/19/23

    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
    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).

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • SPBops
    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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • MichaelTCA
    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
    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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • SPBops
    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
    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!