Combine emails into a multiple-contact column / multi-contact column

Brian_Richardson

Multiple-contact columns can be problematic to use in Smartsheet. Namely, they cannot be "put together" with a formula. There's many use cases where you need to build multiple contacts into a cell based on criteria/lookups, but there isn't a way to do that in native core Smartsheet.

There is, however, a couple of methods to do that using addons. In general I highly recommend looking at Advance packages for addons, they open a world of automation and customization beyond the core application.

Below are various mechanisms for managing multiple-contact data in Smartsheet.

Note that by "multiple-contact" I mean a Contact type column with the "Allow multiple contacts per cell" switch turned on in the column properties.

Final note: thank you to @Samuel Mueller for telling me about same-sheet Data Mesh, being great with Bridge, and other general awesomeness. Thanks to @Paul Newcome for being amazing on community and suggesting this post. And my other fellow Overachievers for making Smartsheet fun on the daily!

CORE APPLICATION

The bad news is that there's no way to "build" multiple contacts into a multi-contact column with a formula.

The only thing you can do is hand-type contacts, separated with comma, semicolon, or tab.

You cannot use Automations or Formulas to build a list of contacts.

SHEET SETUP

All the methods below leverage a single sheet with three columns.

• Column 1 is a text/number column. This can be any number of formulas that produce a series of emails, separated by comma or semicolon.
  • For example, if you need to merge multiple columns of emails into one, you'd do something like = JOIN ([Manager]:[VP], ";" ) resulting in "manager@somewhere.com; director@somewhere.com; vp@somewhere.com" in your cell
  • Alternatively, you may be pulling emails together from another sheet with a formula like =JOIN(COLLECT({Email}, {Name}, HAS(Names@row, @cell)), ";") - this formula looks at a second sheet that lists names and emails, and matches the names selected on the first sheet to the names on the second sheet to combine a list of emails. The {} items are cross-sheet references to columns on the second sheet.
  • Note that you need to combine emails, not names. Smartsheet has no way of mapping names like "Brian Richardson" or "Richardson, Brian" to an email address, and therefore will not convert a name to a contact
• Column 2 is a Contact column set to Allow multiple contacts. There's no formula in this column.
• Column 3 is an Autonumber column. If you already have one, you can use that instead of setting up a new one. Or if you have another means to setup a unique identifier for your rows, use that instead. You need a unique identifier on each row.

DATA MESH METHOD

Data Mesh to the rescue! Data Mesh is a paid addon tool for Smartsheet that is very powerful. It is definitely one of the top tools to take your Smartsheet automations to the next level.

Using Data Mesh, you can copy/sync data between two sheets, or between columns in a single sheet. Which is what we'll do here.

Key Value: Data Mesh triggers on changes, so your contacts will update almost immediately. It's also pretty easy to setup.

Key Drawback: not as flexible as Bridge, but pretty close.

1. Open Data Mesh and click New Config
2. For Source: find the sheet with your three columns, select it, and click next
3. For Target: find the same sheet, select it, and click next
4. For Mapping:
   1. Lookup Values: the Autonumber / Unique ID column
   2. Data Field 1 (Source Sheet): the Text/Number column that has your emails in it
   3. Data Field 1 (Target Sheet): the Multiple-Contact column
5. For Options: set Overwrite Existing Data to Yes. Set Execution Frequency to Update Immediately. Other settings can remain as-is.
6. Give your workflow a name and hit Next, then Add

That is all the setup you need. Now, whenever your formula creates or updates a series of email addresses, separated by semicolon/comma in the Text column, Data Mesh will fire and quickly post those emails into the Contact column as separate contacts, one for each email!

BRIDGE METHOD

You can also accomplish this using Bridge. Although somewhat complex to learn, the actual Bridge workflow for this is quite simple.

Key Value: fast and flexible - this workflow can be adjusted to do many more complex things, like looking up your contacts, manipulating the data, integrating contacts with other tools, or even running Javascript to match/map the emails to additional information.

Key Drawback: Bridge doesn't alert you if there's problems (yet), so you'll have to notice if the contacts aren't getting populated as expected.

Here's the setup:

1. You will likely want to Trigger the Bridge workflow when a list of emails is changed, or something similar. You'll need to setup a quick placeholder blank workflow first, then in Triggers under Smartsheet setup a trigger for Column Values changed or maybe Row Updated, depending on your needs. Point the trigger at your new blank workflow.
2. Add the Smartsheet: Get Row step to your workflow with the following settings:
   1. Sheet: {{runtime.sheetID}}
   2. Row: if you used the Row Update trigger, use {{runtime.event.id}}. If you used the Column Values trigger, use {{runtime.event.rowId}}
3. Add the String Tools: Split Text step to your workflow.
   1. Text: {{states.startstate.smartsheet.get_row.row.cells.name of the Text column with emails.value}}
   2. Delimeter: ; or , (whatever you used in the column to separate your email addresses)
4. Add the Smartsheet: Update Row step to your workflow
   1. Sheet: {{runtime.sheetID}}
   2. Row ID: if you used the Row Update trigger, use {{runtime.event.id}}. If you used the Column Values trigger, use {{runtime.event.rowId}}
   3. Cells: Key 1 = name of the Contact column in your sheet set to multiple contacts. Value #1 = {{states.startstate.stringtools.split_text}}

That's it! Now, whenever your comma/semicolon list of emails changes on the sheet, Bridge will read the list, split it back up, and post it into the multiple-contact column.

DATA SHUTTLE METHOD

If you don't have Data Mesh (get it!) or Bridge then you can also use Data Shuttle to accomplish the combination of emails into a multiple-contact column. It's a little more involved.

Key Drawback: Data Shuttle has to run on a schedule, with 15 minute intervals as the minimum. You cannot trigger Data Shuttle to run when your contacts change. And this relies on constant posting of attachments which fills up the attachments panel pretty quickly.

Key Value: it's relatively easy to use the UI for setup, and it alerts you if there's problems.

You will setup 2 workflows… the first will output the emails to a CSV attachment, the second will import that same attachment as contacts.

Workflow 1

1. Open Data Shuttle and click the + sign to start a new workflow
2. Select Offload workflow
3. For Source: select the sheet that you setup in Pre-Setup
4. For Target, select CSV or XLSX format and Attachment. Attachment should be pointed to the same sheet. (note - you can put it elsewhere, it's just cleaner to keep it all in the same sheet. if you put it elsewhere then just note that location, you'll need it for the second workflow). Check the "Add headers" checkbox.
5. Skip to the Mapping screen. Select the Autonumber column and the Text column with the email addresses and hit Next
6. Select Run on schedule and set to every 15 mins or whatever schedule you like
7. Click Next, give the workflow a name, then Save
8. Now Run the workflow that you just created. After a minute or so you should have an attachment on your sheet. You need to do this before doing Workflow 2 setup.

Workflow 2

1. Click the + sign in Data Shuttle to start a new workflow
2. Select Upload workflow
3. For Source: select Attachment, then browse to the sheet that Workflow 1 saved to. Select Most Recent. Data Shuttle should find the attachment from running workflow 1 in step 8 above. Select "This file has column headers".
4. For Target: select the same sheet
5. For Options: select Merge, and check only the Update checkbox
6. For Mapping: select the Autonumber column as the Unique Identifier. The mapping should already have mapped the Text column (with the emails) to itself, remove that mapping by changing to Not Mapped. Add a mapping to set the Source Text column (with the emails) to the Target Contact column.
7. On the next screen select Run on Attachment

That should do it! Now, every 15 minutes, Data Shuttle will grab all the email addresses from the Text column, dump them to a CSV attachment, then import that same attachment to post the email addresses into the contact column and create the multiple-contacts.

Danielle Wilson

Brilliant! I've never even heard of people mapping back to the same sheet for a target on DataMesh or thought of that before! Huge kudos to @Brian_Richardson and @Samuel Mueller!

Brian_Richardson

Thanks @Danielle Wilson you're so nice!

Neil Egsgard

@Brian_Richardson , great explanation, thank you! I needed to combine single contacts into multiple contacts for a few thousand records with the update triggered by changes to the single contacts. I tried the Bridge and DataMesh solutions and found DataMesh to be far superior.

DataMesh combining single contacts into multi contact for thousands of records

Recommended solution

1. Fast: Speed of DataMesh so typically a few seconds to a couple of minutes.
2. Handles consecutive delimiters and NULL values: Automatically handles consecutive delimiters and null values.
   1. "name@email.com;;;;;;;;;test@email.com" becomes two contacts based on the emails.
   2. "name@email.com;" becomes one contacts based on the email.

Bridge combining single contacts into multi contact for thousands of records

Not recommended solution

1. Slow: Had to use row triggers which meant the Bridge had to run for each row that had changes. If there were changes across a many rows then processing took an hour or longer.
2. Cannot handle consecutive delimiters or NULL values: Bridge errors out it consecutive delimiters or NULL values are used.
   1. "name@email.com;;test@email.com" generates an error "Failed to call update rows : The value '' was not valid for the parameter type 'contact.email'"
   2. "name@email.com;" generates an error "Failed to call update rows : The value '' was not valid for the parameter type 'contact.email'

Neil Egsgard
Business Solutions Architect
Southern Alberta Instititute of Technology

Brian_Richardson

Great additional info. I agree it’s also just easier to use Data Mesh. That said, with Bridge and a complex issue like yours you could have used some JavaScript to handle the complexity.