Flow connecting azure devops work items with sharepoint list

[malwissek]

Hello everyone,

I'm would like to make a flow that will connect "work items" from azure devops to my sharepoint list using power automate. How can I transfer that data into sharepoint list using power automate? I want things such as: ID, title, description, status, priority, assigned to, date etc. Moreover, I would want the flow to  "sync" work items with sharepoint list, so that the data is updated, for ex. every hour. 

How can I achieve that? Can anyone help?

[AlexEncodian]

@malwissek 

I have built something similar syncing DevOps with Excel and unfortunately its not super easy.

First, you need to set-up a DevOps query. You can then run that DevOps query via Power Automate and get the results in JSON. 

You then get all list items into Power Automate in JSON.

Then you need to compare the two JSONs / arrays to get the three category of changes:

• Items in DevOps, not in SP -> create item in SP
• Items in SP, not in DevOps -> delete item in SP
• All other items, loop through and update the SP list

Fortunately, the flow is simpler than you might imagine as comparing arrays is quite easy if you follow these instructions:
https://youtu.be/4IphRdADJBc?si=C9ygWRno966_jQt9

[malwissek]

Hi @AlexEncodian !

Thank You for your answer! Indeed it sounds like no easy task.

Maybe you could show some screenshots of your flow? I would have a better understanding of how does it work 🙂  I really appreciate your help !

[AlexEncodian]

@malwissek 

The overview of the flow is something like this:

1. Trigger

2. Run DevOps query

3. Compose operation of DevOps results
4. Select operation to create DevOps IDs as array of strings
- I am using DevOps ID as they key to sync Excel and DevOps
- But DevOp IDs are integers and the same ID value in Excel is a string in my data
- So here I am converting DevOps IDs to an array of strings to be able to compare the values to the Excel IDs later

5. List rows in Excel (you would do this for SharePoint instead)
6. Select operation to create array of Excel IDs pre sync

7. Filter array action: goal to get DevOps IDs not in Excel (see youtube video) 

8. Add those IDs to excel

9. Filter DevOps IDs for items already in Excel:

10. Update those IDs in Excel

11. Filter Excel IDs for those that do not exist in DevOps ID array:

12. Delete those Excel IDs

[malwissek]

Thank You so much, that's a huge help!