{"id":66806,"date":"2023-04-24T13:00:59","date_gmt":"2023-04-24T21:00:59","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/devops\/?p=66806"},"modified":"2023-04-24T13:00:59","modified_gmt":"2023-04-24T21:00:59","slug":"use-power-automate-to-update-azure-devops-queries","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/devops\/use-power-automate-to-update-azure-devops-queries\/","title":{"rendered":"Use Power Automate to update Azure DevOps queries"},"content":{"rendered":"

In this blog post, I\u2019ll explain how to set up automation to automatically update Azure DevOps (ADO) queries<\/a>. Using an automated Power Automate flow<\/a>, you save time by not having to manually update your queries for each sprint. Everyone using your queries benefits by having data and details that are always current and accurate.<\/p>\n

Solution overview<\/h3>\n

The solution uses a Power Automate flow to update ADO queries. The queries contain a sprint number. For example, the sprint query contains 2303<\/strong>. This number represents the third month (March) of the year 2023. Fortunately, the sprints that I\u2019m working with follow strictly to the month.<\/p>\n

The following query is an example of a simple sprint query:<\/p>\n

\"Simple<\/p>\n

In addition, the previous<\/strong> sprint queries contain 2302<\/strong>, and the next<\/strong> sprint query contains 2304<\/strong>. We use dozens of sprint queries for each sprint, so having an automated update process is a big time saver.<\/p>\n

I\u2019ve organized the queries into an overall folder called AllSprints<\/strong>. The AllSprints<\/strong> folder contains the PreviousSprint<\/strong>, CurrentSprint<\/strong>, and NextSprint<\/strong> folders. Each of these folders contains queries.<\/p>\n

\"Folders<\/p>\n

The flow is set to automatically run on the first of each month. Variables within the flow are created to track query paths, query folders, query numbers, and the Wiql<\/strong> value. The Work Item Query Language (Wiql)<\/a> value defines your query as a hyperlink and can easily be modified as a string variable.<\/p>\n

You use an array to loop through the sprint folders. Within this loop, you’ll determine all the queries that must be updated. Then, you use another loop to step through each query to determine if you need to update it. Updating a query involves changing the sprint number within the Wiql<\/strong> value of the query. Once all queries have been checked and updated, the flow finishes.<\/p>\n

Basic query update process<\/h3>\n

The solution finds each query ID by using the List queries within folder<\/a> action. It then uses the results to help you GET<\/strong> the details of each query by using the Send HTTP request to Azure DevOps<\/a> action (shown below). The results allow you to find the Wiql<\/code> value for each query. You then use a Send an HTTP request to Azure DevOps<\/strong> action again to UPDATE<\/strong> the Wiql<\/code> value for the related query. All of these steps are detailed in the information below.<\/p>\n

Complete query update process<\/h3>\n

The flow starts with a Recurrence<\/a> action that is set to trigger at the beginning of each month.<\/p>\n

\"Recurrence<\/p>\n

Next, the following variables<\/a> are created:<\/p>\n