Save Null Value in SharePoint Online People Picker using Power Automate

Problem Statement

To save people picker value as null or empty is always a tricky situation in SharePoint Online List dynamically. Let's see how it can be achieved when working with Power Automate.

Scenario

Here, we are going to sync the DevOps data i.e. Text, Choice & Identity column, into SharePoint Online List as Text, Choice & People Picker value.

Step 1. Request received when Azure DevOps data saved.

The previous article with the configuration steps of the request received is here.

1. Request Received
2. Parse the field into JSON
3. Initialize the variable as a string
4. Get User Profile action (Extract email id and pass)

replace(split(body('Parse_JSON')?['Custom.TechnicalInterviewBy'],'<')?[1],'>','')

 

Step 2. Set variable value as "-1"

If the user email id doesn't exist or came as a null value, Configure Run after "Fail" to set the variable as "-1"  which will be treated as user ID.

Step 3. Check for User Email and Set User ID if it exists

Create Scope and Configure the Run after as "Skipped". It means the user email id exists and gets the user ID of that email address.

The scope has two defined actions.

Site Address	https://m365x6151710.sharepoint.com
Method	GET
URI	_api/web/SiteUsers/getByEmail('@{outputs('Get_user_profile_(V2)')?['body/mail']}')

Send an HTTP request to SharePoint to get the user ID.

Set User ID 

@{body('Send_an_HTTP_request_to_SharePoint')?['d']?['id']}

Step 4. Create an Item in SharePoint Online List

Below is the parameter that needs to be configured.

Site Address	https://m365x6151710.sharepoint.com
Method	POST
URI	_api/lists/getbytitle('Resource')/items
Header	{   "content-type": "application/json;odata=verbose ",   "Accept": "application/json;odata=verbose" }
Body	{  "__metadata": { "type": "SP.Data.ResourceListItem" }, "Title" : "@{body('Parse_JSON')?['System.Title']}", "TechnicalInterviewById"  :  "@{variables('TechnicalInterviewBy')}", "AssignmentName" :"@{body('Parse_JSON')?['Custom.AssignmentName']}", "TechnicalSkill" : "@{body('Parse_JSON')?['Custom.TechnicalSkill']}" }

Note. variables('TechnicalInterviewBy') always hold "-1" or "Actual user ID". in the case of  "-1", people picker value store as Null else actual user. 

Let's execute the power automate in both scenarios to get the output.

Output scenario. Azure DevOps Identity column as blank value and sync the same with SPO List People Picker

 

Output scenario. Azure DevOps Identity column as user value and sync the same with SPO List People Picker

Hope you have learned something useful here