Handle People Picker Null into SharePoint Online 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

Extract & Sync Work Item Drop Down values with SPO List Choice column

 Azure DevOps board is used to create a task or work item with a pre-defined or custom work item template. It can consist of multiple different types of fields like Text, Multi Text, Drop Down, Identity (People Picker), and Date Time. 

If, as a developer, I need to extract all schema-defined drop-down values and need to sync with another system like the SharePoint Online List choice column.

Let's begin here with the creation of a work item and extraction using power automate.

• Resource Work Item type has the below layout, and the User can submit the detail as provided.
• Technical Skill i.e. drop-down field, has multiple values which need to extract.

Power Auotmate Steps

• Create a Recurrence flow,
• Add Azure DevOps action i.e. "Send an HTTP request to Azure DevOps".
• Relative URI: https://dev.azure.com/m365x6151710/Resource/_apis/wit/
  
  workitemtypes/Resource/fields?$expand=allowedValues&api-version=5.1
  1.           Organization Name m365x6151710
  2.           Project Name Resource
  3.           Work Item Template Name Resource 

• Add Variable of type Array and name as Technical Skill
• Add action "Apply to each" with an output of "Send an HTTP request to Azure DevOps" This will return all Azure DevOps work item fields 
• Add a Condition to restrict the specific column i.e. "Custom.TechnicalSKill". (All Custom created columns should be treated as prefixes with Custom.)
• Once the Condition is satisfied, select the allowed values and set it back to the variable.

Let's execute the power automate to get the output.

 

Once the value gets into the defined array variable, set the array variable to the SPO choice field.
Add SharePoint action "Send an HTTP request to SharePoint"

• Site Address https://m365x6151710.sharepoint.com/
• Method POST URI

  _api/web/lists/GetByTitle('Resource')/Fields/GetByID('69ca0e44-d80d-4e5b-9038-33fdc2e87c9d')

• Header

{

  "accept": "application/json;odata=verbose",

  "Content-Type": "application/json;odata=verbose",

  "X-HTTP-Method": "PATCH",

  "IF-MATCH": "*"

}

• Body

{
    "__metadata": {
        "type": "SP.FieldChoice"
    },
    "Choices": {
        "__metadata": {
            "type": "Collection(Edm.String)"
        },
        "results": @{variables('TechnicalSkill')}
    }
} 

Note:- To get the field ID into the above URI, use this REST api in the browser where "m365x6151710.sharepoint.com" is SharePoint Online Site where the list and column exist.

https://m365x6151710.sharepoint.com/_api/web/lists/getbytitle('Resource')?$select=schemaXml

Let's execute the power automate to get the output

SharePoint Online List Choice fields.

Now, these extracted values are synced with another system like SharePoint Online List, so both systems i.e. Azure DevOps and SharePoint Online List choice values, are in sync without manually updating.

Hope you have learned something useful here.

Trigger PowerAutomate on the Change of Specific Field/Column Value of Azure DevOps

 Azure DevOps also have a premium connector with power automate. It consists of triggers and multiple actions on item add, updates, delete, etc. On update of any item, we usually use the Item update action to capture the details. But the downside of this approach, Power Automate will invoke every time whenever changes happen to a DevOps work item, whether it's required to capture or not. 

It's unnecessary to increase the number of counts, which is limited per user account, and increase the flow history.

Solution Approach

Let's invoke Power Automate to change the "Sync To SharePoint Online" Toggle button only.

This sequence of steps helps to execute the power automate on change of a specific column instead of item work item,

Step 1. Select the Project Setting of the Project.

1. Select the Service hooks from the Left-hand side pane.
2. Select "+ Create Subscription," and Pop Up will appear with Title "New Service Hooks Subscription".
3. Select the WebHooks
4. Click Next to Proceed

Step 2. Update the Trigger action, Project Name, and Column Name 

1. Select the Work Item Updated under Trigger on this type of event dropdown.
2. Select Project Name under Filter sub-section area path.
3. Select Resource as Work item Type (It can be different as per the defined template name).
4. Select the field or column name responsible for triggering the Power Automate based on trigger defined action.

Step 3. The next Wizard option ends with the Power Automate Invoke trigger action url.

1. Navigate to Power Automate and create new top new trigger action as "When an HTTP request is received" and add parse json as a response action next to that.
2. Take the HTTP POST URL from the Power Automation trigger action and Paste it into the URL under Settings.
3. Click Finish to proceed and close the wizard.

Once the wizard is added, You can see the WebHook is created under the service hook and ready to execute item updates on specific column values. 

Execution Steps

Let's Change the value into Azure DevOps Work Item and validate the scenario.

Output 

Execution Happened only once, which saved the number of runs & consumption. 

Item also created into SharePoint Online List.

This is the best practice to follow defined standards and effectively utilize product-related services. Hope you have learned something useful here.

Design Feedback Card with Microsoft Teams Using Power Automate and Adaptive Card

 In this article, we'll turn our attention toward designing a feedback card using Low Code Solution. Here I am going to articulate how to collect a feedback response from a MS Teams User and update the response back into sharepoint online list as data source.

Power Automate has below actions under Microsoft Teams Connector (which is in preview mode as of now).

1. Post an adaptive card as the Flow bot to a Teams user, and wait for a response
2. Post an adaptive card as the Flow bot to a Teams channel, and wait for a response

Once the adaptive card posted into MS Teams User or Channel with above defined actions. Power Automate instance will wait (async call) unless user didn't respond.

Power Automate  documentation maximum waiting time for async call is 30 days. 

 

Prerequisites

1. Adaptive Card Designer refernece
2. Power Automate i.e. Create Automate workflow reference
3. SharePoint Online Cusotm List (Ticket Details & Feedback). Based on status of ticket i.e. closed  power automate post adaptive card respone to MS Teams User. Once User respond , user input will get store into data source i.e. feedback sharepoint online list.
4. Microsoft Teams access 

Lets start with power automate creation

1. Login to https://flow.microsoft.com
2. Create an instant flow with trigger "create or update sharepoint online list item"
3. Create two SharePoint List i.e. Ticket Details & Feedback Response 

On ticket detail status change trigger the feedback card.

 

Column Name	DataType	Default Value
Title	Single Line Of Text
RequestorName	People Picker
Ticket Status	Choice	new, assigned,in progress on hold,resolved,closed cancelled
Ticket Description	Multi line text  (Plain)

 

Another List name Feedback Response,

 

Column Name	Data Type	Default Value
Title	Single Line Of Text
Feedback Comment	Multiple Line Of Text
Raiting	Number
Ticket Id	Single Line Of Text

 

Add an action "When an item is created or modified: in SharePoint Online List.

Note

In this scenario, As item get updated, power automate will get trigger and check the condition ticket status has closed or not. If ticket status has closed, then condtion will be true and it will trigger the rest of action to continue

 

Add another action "Post an Adaptive Card to a Teams user and wait for a respnse"

• Add Requestor Name (it can be find in ticket table, who has created a ticket)
• Enter the Messgae 
• Select "Should Update Card" Yes

Click Create Adaptive Card and it will prompt adaptive card designer within same context.

Select Card Payload Editor and Paste below JSON or You are free to design the card as per your requirement. 

1. {  
2.     "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",  
3.     "type": "AdaptiveCard",  
4.     "version": "1.2",  
5.     "body": [  
6.         {  
7.             "type": "TextBlock",  
8.             "text": "Ticket ID : @{triggerOutputs()?['body/ID']}",  
9.             "size": "large",  
10.             "weight": "bolder",  
11.             "wrap": true  
12.         },  
13.         {  
14.             "type": "TextBlock",  
15.             "text": "Ticket Detail: @{triggerOutputs()?['body/Title']}",  
16.             "isSubtle": true,  
17.             "spacing": "None",  
18.             "wrap": true  
19.         },  
20.         {  
21.             "type": "TextBlock",  
22.             "text": "We would love to hear your thoughts, concerns or problems so we can improve.",  
23.             "wrap": true  
24.         },  
25.         {  
26.             "type": "Input.ChoiceSet",  
27.             "id": "Feedback",  
28.             "value": "",  
29.             "choices": [  
30.                 {  
31.                     "title": "Bad",  
32.                     "value": "1"  
33.                 },  
34.                 {  
35.                     "title": "Poor",  
36.                     "value": "2"  
37.                 },  
38.                 {  
39.                     "title": "Moderate",  
40.                     "value": "3"  
41.                 },  
42.                 {  
43.                     "title": "Good",  
44.                     "value": "4"  
45.                 },  
46.                 {  
47.                     "title": "Excellent",  
48.                     "value": "5"  
49.                 }  
50.             ]  
51.         },  
52.         {  
53.             "type": "Input.Text",  
54.             "id": "Comments",  
55.             "placeholder": "What can we do to make it better?",  
56.             "maxLength": 250,  
57.             "isMultiline": true  
58.         }  
59.     ],  
60.     "actions": [  
61.         {  
62.             "type": "Action.Submit",  
63.             "title": "Send Feedback"  
64.         }  
65.     ]  
66. }  

Create Item based on user response into Feedback List

Comment and Rating value will be populated from teams response, If value doesn't exist in your case , you can use below formula

 

Comment ;- @{body('Post_an_Adaptive_Card_to_a_Teams_user_and_wait_for_a_response')?['data']?['Comments']}

Rating :- @{body('Post_an_Adaptive_Card_to_a_Teams_user_and_wait_for_a_response')?['data']?['Rating']} 

 

Validate and Save with user friendly name. 

 

Once ticket status gets closed or user can manually chnage the status in the backend for testing purposes, Feedback Card will be post to indiviual user based on status.

 

Flow Bot will post the feedback card to ticket creator. 

Now user needs to respond or provide the feedback within 30 days (time out limitation for power automate)

User can select the rating from dropdown and provide the relevant comments. Once user submits the response, user input will get stored into feedback response list.

I hope you enjoyed and learned something new in this article.  Thanks for reading and stay tuned for the next article.