In the previous post, I added the tasks to on-premises TFS using C#. This time I will add similar data using an Excel add-in. I will also learn how to accidentally remove the link from the task to the parent element.
First things first - if you do not have Azure DevOps Office® Integration 2019 installed (you need it to work with TFS / Azure DevOps from Excel), then go to https://visualstudio.microsoft.com/downloads/ and pick it from the Other Tools and Frameworks section at the bottom of the page. Install, and you should then see the Team plugin in the Excel menu.
This time I will use my Azure DevOps collection bartekr and the AzureDevOps_APITests project. It uses a Basic process, but I also tested the process on an Agile workflow. To add the elements click New List and connect to the project. If it’s the first time you connect to Azure DevOps or TFS you will be prompted to set up the connection to the collection. After you connect pick the Input list from the options
Now you should see an empty list with information about the connection. The list is ready and you could start filling the columns Title, Work Item Type, State, Reason, Assigned To (ID is read-only).
But - we want to add not only the tasks but also the connection to a parent element. In this case, tasks should be connected to the Issue (in a Basic process) or the User Story (an Agile process). To do it we have to work with the tree, not a flat list (notice: List type: Flat on the right side of the yellow header). Click on the list and you should see an enabled Add Tree Level button. Click it.
In the Convert to Tree List select Parent-Child (the default option)
Now you should see the columns - Title 1 and Title 2 and the List type: tree. The first title is for the parent item, the second for the child.
Now - I want to add the new tasks for the Issue 2
In the Team toolbar click Get Work Items, find the Issue 2 in the opened window, select it and click OK.
The spreadsheet looks like below:
Nothing unusual. Now - in the Title 2 column add new tasks with Work Item Type == Task and push the button Publish. And that’s it!
In Azure DevOps:
If I wanted to fill more columns (like Assigned To, Sprint, Estimated time and so on) - I click Choose columns button and add them to the list.
One more small thing for the end. Let’s say you have a lot of Tasks with one parent Issue / User Story. Do not delete them from the list “because you want to have a clean sheet with just the Issue / User Story”. Deleting the tasks from the list does not delete the tasks (of course), but it removes the Parent-Child hierarchy for the deleted elements.
If you repeat the steps to get the tasks for Issue 2 and delete them, you will see they lost the connection:
In short: adding work items to TFS / Azure DevOps using Excel is easy. The trick is to keep breathing to use the Add Tree Level button.