“I’m confused from all the quotes, apostrophes, brackets in the people picker update, what format must the emails follow in Power Automate?”
When you’re working with big SharePoint lists, the ‘Update item’ action might get a bit confusing. It shows you all the columns and asks you to put value in all the mandatory ones, even when you want to update only a single column. That’s when an HTTP request might be a better solution. One action, a few lines of code, and the column is updated. Unless it’s a people picker column where the format adds another level of complexity…
‘Select’ the user emails
I already explained the data format in a previous article, people picker update body might look e.g. as below.
Body:
{
"formValues":[
{
"FieldName": "Approvers",
"FieldValue": "[{'Key':'i:0#.f|membership|xxx@tomriha.com'}, {'Key':'i:0#.f|membership|yyy@tomriha.com'}, {'Key':'i:0#.f|membership|zzz@tomriha.com'}]"
}
]
}Each person is an object, they’re all in an array, and to make it even more interesting there’s a bunch of quotes. Normally you’d use the double quotes (“), but since it’s part of the JSON body, the double quotes are already taken. They define the whole body and if you used them in the list of users you’d “close it” too early.
But first things first, you need the users in the desired format. The easiest way to get them is via the ‘Select’ action. Put the ‘Key’ on the left side and the claims login on the right side.
If you have Claims available among the dynamic contents, you can use it directly…
…if not, add the claims string before the email address to make it a claims login.
That’s only the first part though as such array will contain the double quotes. And as mentioned above, you can’t keep them.
Replace the double quotes with apostrophes
Since you must differentiate what is the request body and what is the data in one of the properties, you must use different set of quotes. Double quotes for the body, the main JSON, apostrophes to format the column data.
However, here comes another complication – to replace the quotes you must use an expression, and expressions use apostrophes as separators. Similarly to the body problem before, an apostrophe can mean an end of a parameter in an expression.
To replace apostrophe in an expression you must use double apostrophes to represent the character. The expression below will take the double quotes and replace them with a single apostrophe.
replace(<value>,'"','''')As the ‘Select’ action creates an array, add one middle step to convert into a string(…). You get the expression to use in the HTTP request to update the multiple people picker column, e.g.
replace(string(body('Select_-_get_approvers_claims_login')),'"','''')
That’s it, you selected the claims/emails and turned them into claims, replaced double quotes with apostrophes, and you can update the column.
Summary
I believe that updating SharePoint columns via HTTP requests is one of the key functionalities in a flow. Most of the columns are easy to update with a few lines of code, but people picker is quite challenging, especially when Power Automate gives you only emails. Yet the process is similar to other columns – you just need the data in a specific format – and it’s only the format that takes a few more steps in this situation.
1 thought on “How to Select emails for people picker update in Power Automate”