Use Excel for mass Azure DevOps Work Item updates

One of my favorite features of Azure DevOps is its ability to integrate Work Item updates with Office Excel.  This means you can run any canned ADO query from Excel, update the retrieved work items and then publish the updates back to ADO all from inside Excel.  

Microsoft Guidance

See the following pages

Install Azure DevOps Automation

Download and install Microsoft Office Integration tools by installing 2019 dev-ops automation 
This installs a TFSProtocolHelper that ADO can use to open Query results in Excel

Create an ADO query that will execute and fill Excel

Click on the Queries in the Boards Menu
Create a Query and save it to create a named query
  • I tend to create two-tier queries for Features first-level children, usually User Stories.  This lets me edit user stories and change their parent relationships.
Select the additional columns you want to see in Excel.  Some columns like Acceptance Criteria or long Description fields may be difficult to layout or edit from inside Excel.  Some drop list fields may not be editable.

Open Excel from inside ADO

I have only seen this option while on Enterprise subscriptions.  I don't have the option in my subscription.  So instead we will initiate from the Excel side

Configure Microsoft Excel to talk with ADO

Click on New List
Add your server
Log in using the prompted integrated login panel
Close the panel

Run the Query

Select the Team Collection and Project and click on Connect

Select the previously saved query

Edit in Excel

This screen resulted from my Feature/Child work item query.
  1. The Title columns specify the relationship.  
    1. Items with Title 2 are children of the closest Title 1 line above them.  
  2. You can reparent by moving a row under a different out-dented row
  3. State, AssignedTo, Tags and others select lists can be set in Excel
    1. You cannot change a work item type in Excel
  4. You can create new blank roles.  
    1. The system will apply IDs when the spreadsheet is published to the server.

Publish Changes to ADO

Press the Publish under the Team menu to publish changes

The page will refresh with any ID or other changes

Created 2021/07


Popular posts from this blog

Understanding your WSL2 RAM and swap - Changing the default 50%-25%

Installing the RNDIS driver on Windows 11 to use USB Raspberry Pi as network attached

DNS for Azure Point to Site (P2S) VPN - getting the internal IPs