How Do Contact Lists work?

davekoni

We are trying to setup a Company Roster Template. We would use the Smartsheet API and automations to keep this template up to date. I notice that when using the API a Contact field accepts an email via the API, but then returns a Contact formatted LAST, First.

On other sheets we want to have users enter a contact into a cell and then have information like their title and Department auto fill in via a Index Match formula. When a user enters a name into a Contact List field it is formatted FIRST LAST.

I also notice that if have a column with Email addresses and then a Contact List Colum with a cell formula that = the email address the cell is formatted as a Smartsheet Contact but it is displayed as email.

All three ways to create a Contact in a Contact List Column work and appear to tie to the Smartsheet User. However, you can no use the different formats interchangeably in a formula.

What am I missing here and is there any way to make all three of these format work in formulas since they are technically all the same user?

This is API Generated Contact

This is User Entered Contact

This is Contact generated by making cell equal to an email address

All three point to the same Smartsheet User but all three are different.

Brian_Richardson

Yes it's frustrating that there's no "extract an email" function to use with contacts or any way to standardize it to first/last name formats. The name you get back is whatever the user sets it to.

I'm sure you've noticed this, but when you get a contact via API the value is the email, the displayValue is the text which varies.

There's really not much to do about this other than try to leverage email addresses in all the workflows and use API to post those emails to your sheet to be used instead of names.

Also, if it helps, there's an undocumented way to lookup user data by email.

https://api.smartsheet.com/2.0/users?email=someone@somewhere.com