How Do Contact Lists work?

Options

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.

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers
    Options

    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

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!