Can I turn text into a contact?

SCSDmoore

I'm using Data Uploader to bring in data from another database, and task owners are brought into a sheet as [First Last] in text. I'd love to turn that into a contact. Does anyone know of a formula I could use? Any other ideas?

jmyzk_cloudsmart_jp

Hi @SCSDmoore

As far as I know, there is no way to create a Contact cell by formula, but you could create a helper column and paste the cell values into the Contact column to make contact cells.

Please set up your Contact List column to "Allow multiple contacts per cell" when you copy cells. Otherwise, the Contact List does not show names though it works as a contact list in automation, update request, etc.

You can disable the "Not Allow multiple contacts per cell" after you copy the cells.

A formula to create a helper column: 

 =name@row + " <" + email@row + ">"

Please take a look at the demo sheet below.

https://app.smartsheet.com/b/publish?EQBCT=2a3d97bccd6343dc978a5e608169e1d2

SCSDmoore

Thanks @jmyzk_cloudsmart_jp That's helpful. When I copy from the helper column, I have to right click and select Paste Special - Values Only, however this doesn't seem to resolve to the contact. I pasted into a text column, and then copied from the text column to the contact column and it worked. I'm afraid this will be too much manual work.

I've also played around with creating a seperate user sheet with name, email in two columns and then I applied a VLOOKUP to send the email address into the contact column. This too doesn't resolve properly in the contact column, but it gives me the email address I need to use in workflows etc.

jmyzk_cloudsmart_jp

Hi @SCSDmoore

>"it gives me the email address I need to use in workflows etc."

Unfortunately, automation by workflows requires contact cells. So, email text in text/number column cells will not work.

If you just want to use new contact in workflows, copying and pasting email addresses to contact cells will work. (Contact cells created this way do not show names, though.)

FYI, using API to add Contacts is possible but has some risks if you do not do that yourself.

curl https://api.smartsheet.com/2.0/sheets/{sheetId}/rows \

-H "Authorization: Bearer ll352u9jujauoqz4gstvsae05" \

-H "Content-Type: application/json" \

-X PUT \

-d '[{"id": "6572427401553796", "cells": [{"columnId": 7518312134403972,"image": {"altText": "New Alt Text"},"value": "new value"}, {"columnId": 1888812600190852,"value": "A"}]}, {"id": "2068827774183300", "cells": [{"columnId": 7518312134403972,"value": "desc_updated"}, {"columnId": 1888812600190852,"value": "B"}, {"columnId": 6552023773538180,"objectValue": {"objectType": "MULTI_CONTACT","values": [{"objectType": "CONTACT","email": "john.doe@smartsheet.com","name": "John Doe"}, {"objectType": "CONTACT","email": "jane.roe@smartsheet.com","name": "Jane Roe"}]}}]}]'