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.