Formula to add multiple names to one cell

ker9

Thank you to @Rick Girard , @Jeff Reisman for this post.

I used it to help me join a collection of names. Unfortunately, it doesn't want to recognize those names in a Contact Column that allows multiple names.

The weird thing I found... I changed the delimiter from ", " to Char(10) (no quotes) and it showed the names as contacts with the little badge, BUT when I saved the sheet, it converted them back to names and not contacts.

After saving, if I reset the column to text and then back to contact, it shows the names as contacts. Save the sheet and it converts them back to names. So close and yet so far. If you have found a way around this, would love to hear it.

Jeff Reisman

@ker9 Unfortunately, Contact columns in Smartsheet are pretty complex.

The only way I've found to be able to work with them successfully in formulas is one-way. In other words, if I have an email address in a Text/Number "Email" column, I can use a formula in a Contact column like =Email@row to create a valid Contact value out of the text email address. I'm doing that all over the place in a current project, where we need external users to enter email addresses on form submissions, and then we use those addresses later in automations.

Now, if you haven't set up a real Contact record with first name/last name/ email address for an email address, you can match against them using text fields or whatever. For example, if my multi-select Contact column contains "Test@test.com", I can do something like =IF(HAS(Contact@row, "Test@test.com"), "Yes", "No") and will return a Yes value.

ker9

@Jeff Reisman - thank you. Was hoping to use this in a "Current User" scenario for a report. It does work with multiselect contact columns, but not the one that has my join/collect formula, which probably means I need multiple reports. :(

ker9

@Jeff Reisman - I learned from another thread that I should not use Char(10) but that the delimiter ", " is appropriate for this column type (even though it doesn't work). (Courtesy of @Paul Newcome in this thread.)

I did try DataMesh and found that you can DataMesh the formula to a contact cell (as data, no longer a formula) BUT it won't work immediately. You have to change it to text and back to contact, but then it sticks (until you DataMesh again). Still not a perfect solution but if the data doesn't change much, it can work.

Jeff Reisman

@ker9 Do you have any experience using the Smartsheet API? I just remembered I worked on something like a few weeks ago, trying to populate a multi-picklist column in a sheet with values from an Excel file. So just now I did some digging on how I could adapt this for multi-contact columns. It's not easy, but I just got it to work! The most I had done before about two weeks ago was read other people's code, never wrote any of my own, so I've learned a lot. So if you have API access and are a little familiar with API calls, or just want to journey down the code rabbit hole, follow along below.

My process above is being done in Alteryx, which at like $5k per seat is not cheap. However, you can use Postman, which is free! If this is a regular process you'll have to do, I'm sure there's a way you can script this out in Postman.

The API Documentation is here: https://smartsheet.redoc.ly/

First thing is to get the column IDs from the sheet, using something like this:

https://api.smartsheet.com/2.0/sheets/insert_SheetID_here?accessApiLevel=1

Once you know the column IDs you want to populate, you'll want to construct a JSON string to use in a POST or PUT command. (POST is for writing a new row, PUT is for updating an existing row.) The multi-contact column's portion uses this syntax:

{"columnId": your multi-contact column's ID goes here, "objectValue": {"objectType": "MULTI_CONTACT", "values": [ {"objectType": "CONTACT", "email": "TestUser1@fakedomain.com" }, {"objectType": "CONTACT", "email": "TestUser2@fakedomain.com" } ]}}

Essentially, each contact in your list has their own little section within the MULTI_CONTACT data, consisting of an "objectType" value and an "email" value.

So put all together, the JSON looks something like this:

[{"cells": [ {"columnId": 164400025185444, "value": "This is my Smartsheet code"} ,{"columnId": 44598856859004, "value": "Interesting information"}, {"columnId": your multi-contact column's ID goes here, "objectValue": {"objectType": "MULTI_CONTACT", "values": [ {"objectType": "CONTACT", "email": "TestUser1@fakedomain.com" }, {"objectType": "CONTACT", "email": "TestUser2@fakedomain.com" } ]}}, {"columnId": 2487537454584660, "value": "Did you vote this week"} ] }]

Have fun!

ker9

@Jeff Reisman - Thank you, this is awesome! I played around a tiny bit with APIs and really want to find time to work on it more - this might be my opportunity. Thank you very much!

Jeff Reisman

@ker9 My pleasure. I had been a little peeved at the difficulty of working with these multi type cells, so this was a great chance to get it worked out.

One more thing: you can set the displayValue for the contact by adding the "name" parameter for each contact:

{"objectType": "CONTACT", "name": "Test User", "email": "TestUser1@fakedomain.com" }