Bulk modify items in Visual Studio Team Services

I just had a need to add around 500 work items for a new project in Visual Studio Team Services. We use VSTS for keeping track in our projects and use the Scrum template. In this case, it was a bit different from a normal project since 98% of the work items were defined before hand and most of the priority was also set. I had a list of all the work items and needed to populate the scrum board with these in a quick way. Still, what I learned can be very useful in other projects as well, but helped me tremendously in this case.

I have heard about the export/import functionality to Excel earlier, but were curious on how to do it in VSTS today. The documentation found online was a bit off and didn't quite tell me what I wanted. Most pages and articles found pointed me to old versions of TFS and Visual Studio. This post is for me (and others) to use as a reference in the future.

Start out by creating a custom query in VSTS that return all objects you want in a tree view format. In my case I want absolutely everything, but you could always filter out specifics to work with if you'd like.

Then make sure to enable the Team ribbon add-in in Excel. I had several versions installed (most likely due to multiple installations of Visual Studio) so I enabled the one with the highest version number.

Go to Options and then Add-ins in the left menu bar. At the bottom you'll find a drop-down where you can select COM Add-ins, then click Go.

If you don't have the Team Foundation Add-in in the list, you can get it by either installing Visual Studio or install the Team Foundation Server Office Integration from here (a bit down in the downloads list).

Then create a new Excel book and connect to VSTS via the newly enabled Team ribbon by selecting New List and connect to the VSTS and then select the new "All items" query.

Add those columns you are interested in. In my case it was Remaining Work and Effort, since I already had those numbers.

Also, in order to be able to work with items on different levels, you need to make sure that you have Title-columns that correspond to the levels. In this case, Title 1 is used for Backlog Item and I have to add a level for the Tasks. Click Add Tree Level in the ribbon and you will get a new column for Title 2 that will contain the Task title.

Then start to populate the table in a normal Excel way. Don't miss out on utilizing the filtering functionality in the table to quickly select items and set properties on those. One example is to filter out everything with "deploy" in the title and set the Activity property to Deployment by using Drag-and-fill. This is WAY faster than doing it in the web GUI, even if you there can select and edit multiple items at once.

Then it is just a matter of Publishing to VSTS to populate the board with the items. So click Publish in the ribbon.

Jump over to VSTS and refresh the page to get your updated backlog.

Just be vary of others updating the board while you work. Changes may be overwritten and/or messed up.