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!