top of page

Batch upload to Azure DevOps Add-on in Excel

I dressed up as a clown for Halloween yesterday, I even scared myself. I am not a fan of clowns. Why do I do these things?


It was hilarious though. In the area we have moved to, they go all-in on Halloween, loads of people dressed up, it was quality.




Anyway...Excel............ no................ I can hear you saying. This is supposed to be a website/blog about automation. Well, Excel does have its uses!


I spent many an hour with my head deep in spreadsheets, looking for trends in data or working out how to use VLOOKUP. Excel has been around for years, and it's because of its versatility that it is still here.


Introduction

Do you use Azure DevOps for User Stories? Yes.

Do you write them individually into Azure DevOps? Yes?


Ok then carry-on reading, and I'll show you how to use an add-on in Excel that will help you achieve batch upload gloriness (what even is that? moving on! It's definitely not a word, oh well "Add to dictionary").


Pre-requisites

To start with you need to make sure you have the TFS Office Integration Installer installed. You can download that by clicking here.


Yeah, lies, don't do that, it's not going to work. That is the wrong one! This was a major confusion on my part. If you download the one above, its for the TFS foundation server, not for Azure DevOps, oops. So...


Instead....


You need to install this one "Azure DevOps Office® Integration 2019", it can be found here


Once you have that, then you will be able to follow the following steps to enable the Teams tab inside Excel.


Config Steps

First of all, we need to make sure the Add-on is enabled, to do this:


Open Excel

Go to File > Options Select Add-ins from the left-hand menu Then in the Manage drop down, change this to COM Add-ins Click Go


Choose the Azure DevOps Add-in

Click OK




Once you have done this the Team tab in the ribbon is available.


Click on the Team tab and select New List


The first time you do this, you will have no Azure DevOps servers in your list.


Click on Servers:




Then click on Add



Then enter the URL of your Azure DevOps server, this should be in the following format


https://dev.azure.com/<Azure DevOps instance>


Then click OK.




Choose your account to login with:



Then next, click Close:




We have now successfully connected to the ADO instance.


Wahoo!


Next, we want to click on the Team Project that you want to upload to and click Connect:





Choose Input List and click OK



Click the Choose Columns button and then choose the columns that you need and move them over to the selected columns section. When done click OK.




The Excel worksheet will now populate with all of these fields and you can start building your user stories


Leave the ID blank, these will get created on upload.


You can select the Spint in the drop down on the Iteration Path column, and set the work item to user story, and then assign the owner




In the ribbon, click on Publish, an ID will then be set against the User Story.


Next, we want to add tasks to that parent user story, to do this, highlight the first user story ID field, and then click on Add Tree Level


Choose Parent-Child and click convert




This adds a Title 2 column to the worksheet, it is here that you can add in your child tasks of that parent user story:



Click on the User Story ID field, and then click on Add Child, you are then able to add the title of the task to the Title 2 column, fill in the rest of the columns, paying special attention that the Work Item Type is set to Task




Check what you have entered, and then click Publish. When done you will see the ID in the field for the task, then it has successfully been uploaded to DevOps.




I hope this blog post has helped you, as always reach out if you have any questions - jon@jondoesflow.com or please leave a comment below.


Thanks!


58 views0 comments
bottom of page