Power automate question (multi-contact list)

jtadhs

我用力量盟tomate to wire up some info into smartsheets. I have everything working so far but multi contact list and multi pick list. The contact list error I get is this: The value "John smith" could not be saved in column "Assignment". This column is restricted to MULTI_CONTACT_LIST values only. The name is definitely there. Have tried email addresses, arrays, objects, strings, nothing works.

What format are these columns expecting data in? I can't find anything usable anywhere.

Jeff Reisman

Getting the same exact thing via Insomnia:<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/09DIRZTL4EB8\/image.png<\/a><\/p>

What type of data is it expecting? How is it to be formatted? Who knows! It's not documented anywhere.<\/p>","bodyRaw":"[{\"insert\":\"Getting the same exact thing via Insomnia:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/09DIRZTL4EB8\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/09DIRZTL4EB8\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"What type of data is it expecting? How is it to be formatted? Who knows! It's not documented anywhere.\\n\"}]","format":"rich","dateInserted":"2023-08-14T22:36:46+00:00","insertUser":{"userID":164958,"name":"jtadhs","title":"Title","url":"https:\/\/community.smartsheet.com\/profile\/jtadhs","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfNpbIYWnLk6ZczJ1ClRDc4ET2YJ_HxgNiq2jl7URt0RjU=s96-c","dateLastActive":"2023-08-16T23:05:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390588#Comment_390588","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/390588#Comment_390588

Is your Multi contact list column restricted to only values that are in the contact list for the sheet? If so, you might want to turn that off.

Also make sure the column isn't locked.

Also add a "level" parameter to the API call with value of 2, that should enable multi picklist and multi-contact list functions.

Jeff Reisman

@jtadhs你格式化你的这些复杂的数据如何column types? Are you including them as objectValue references or just values?

Cell objectsretrieved through the Smartsheet APIs have two main attributes representing cell values: Cell.value, and Cell.displayValue. A third attribute,Cell.objectValueis currently used only foradding and updatingpredecessors, or formulti-contact or multi-picklist details, such as email addresses or values in a multi-picklist.

https://smartsheet.redoc.ly/tag/cellsRelated#section/Cell-Reference

jtadhs

for the multi-contact, I'm passing in the display name or the email address, as strings, and this fails. I'm using integrations w/ power automate, so there is no lower level option to configure cell.objectvalue or .displayValue, or however that works.

for multi picklist, I'm passing in a string which matches exactly options on the list. no go.

example:

The Assignment column is my multi-contact list. For that column, data is coming in purely as strings from a JSON object which I am parsing.

jtadhs

Getting the same exact thing via Insomnia:

What type of data is it expecting? How is it to be formatted? Who knows! It's not documented anywhere.

Jeff Reisman

Getting the same exact thing via Insomnia:<\/p>

https:\/\/us.v-cdn.net\/6031209\/uploads\/09DIRZTL4EB8\/image.png<\/a><\/p>

What type of data is it expecting? How is it to be formatted? Who knows! It's not documented anywhere.<\/p>","bodyRaw":"[{\"insert\":\"Getting the same exact thing via Insomnia:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/09DIRZTL4EB8\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/09DIRZTL4EB8\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"What type of data is it expecting? How is it to be formatted? Who knows! It's not documented anywhere.\\n\"}]","format":"rich","dateInserted":"2023-08-14T22:36:46+00:00","insertUser":{"userID":164958,"name":"jtadhs","title":"Title","url":"https:\/\/community.smartsheet.com\/profile\/jtadhs","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AAcHTtfNpbIYWnLk6ZczJ1ClRDc4ET2YJ_HxgNiq2jl7URt0RjU=s96-c","dateLastActive":"2023-08-16T23:05:43+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390588#Comment_390588","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/390588#Comment_390588

Is your Multi contact list column restricted to only values that are in the contact list for the sheet? If so, you might want to turn that off.

Also make sure the column isn't locked.

Also add a "level" parameter to the API call with value of 2, that should enable multi picklist and multi-contact list functions.

jtadhs

I got this to work, however, I had to remove the restriction on the column. It is not possible to update the column via API or integration if the multi-contact column is restricted. And even w/ level=1 (for multi-contact list) and include objectValue on the query string, it still does not update the contact properly. It'll show the name or the email in the column but not the object, until the checkbox is manually clicked. Jeff, thanks for your suggestion. Spent a lot of time on this one field issue, unfortunately.

jtadhs

To add: what I had to do to get this to work in power automate, was leave off the multi-contact/multi-pick list fields when inserting a row and update it in a different step. You can only populate those kinds of columns via HTTP trigger, because of the poor support integrations has been given. Supply these values on an HTTP trigger step:

Following Bearer is your API token value.

Also, bring in a Parse JSON step to break down the body from the insert row, because you will need the row id in order to update those fields. For folks who don't want the headache of doing all of this, convert the column to a text/number and you should have no issues.

Jeff Reisman

@jtadhsGlad you figured it out! I will probably have to use this at some point, since my VP loves his power automate and wants us to use our Microsoft 365 for more things.

Another thing I was going to suggest, though not ideal, is to add your contact to a text/number field, then use a formula in the multi-contact field to bring in the email value from the text/number field. This doesn't work with multiple emails in one field, though. But it is something I use quite a bit, especially when collecting responses or entries in a form where I don't know who the respondents will be, they don't sign into Smartsheet, and so I could not maintain a contact list; I prompt the user to enter their email address, use the email format validation in the form, and then just use a simple =UserEmailTextField@row to bring that email into the Contact List field.