If yoda were a developer…

Many times a custom tool that is created to overcome a particular “issue” ends up morphing into something that is greater than the initial idea. Case in point, I had come across many instances where the sheer bulk of Sprint Backlog Items that had to be entered in to TFS almost made me break out in to a cold sweat – it is an arduous task to do it via the Team Explorer Window when you are doing the entries one by one:

So, typically for large volumes of Sprint Backlog Items I would prefer to use the Export to Excel capability of a TFS WorkItem Query:

I could then enter the bulk of Sprint Backlog Items entering the required data in each row (representing each SBI) and then use the Publish feature of the TFS Excel Team Add-In:

The only issue with this approach is that I’d have to go back and link each SBI in Team Explorer with the correct PBI!

So I created an Add-In to overcome this. At first the Add-In would only pull back the PBI Id and Title and allow the user to type in a new PBI Id and publish the link information, but then I realized it would be great (for planning purposes) to be able to pull back whatever field from a PBI that I wanted. So I created a mapping between the PBI Field name and a Column name that the user would type in to Excel (the red shows the mapping from the Excel Column text; the purple shows the mapping from the TFS Template definition for a Product Backlog Item:

I can then pull back whatever I like and be able to see how it relates to the list of Sprint Backlog Items I have. I also created an informative video to illustrate how to use the add-in.

You can get the latest version from:

http://www.petrellyn.com/jaspware/ExcelTfsAddIn

Recently I had been working on creating Sprint Backlog Items in Excel and then using the Team Explorer add-in for Excel to publish the changes back to the TFS server. It was great to be able to view tasks in Excel and update similar tasks through a spreadsheet manner rather than swapping back and fourth in Team Explorer of Visual Studio. The only issue was the linking to Product Backlog Items; there wasn’t any way to link the published SBI with appropriate Product Backlog Items – it meant I would have to go back to Team Explorer and do it through there. I had already worked on an Excel add-in to calculate work remaining for each team member:

The functionality behind this feature is based on certain fields being defined in the SCRUM template (Estimated Effort (Scrum), Work Remaining (Scrum)). The Excel Add-In exposes Formulas that can be utilized within a cell:

GetTotalHoursRemaining() provides the calculation of Total hours remaining in a given Sprint for one or more team members:

Returning to the problem at hand, I realized that it would be simple enough to add functionality into the existing Add-In to be able to “pull” the PBI links down to the sheet and even “push” new links back up to the server; after all each PBI has an ID that can be represented as text, and the process of adding a link to any TFS WorkItem is accomplished through the following call:

workItem.Links.Add(new RelatedLink(linkId));

With a constructor signature for a RelatedLink instance accepts an int:

RelatedLink(int relatedWorkItemId)

This meant that I could scour the ID column of the Sprint Backlog Item in Excel and can pull off the IDs as integers, then scouring the column that had the Product Backlog Item IDs I could update the Sprint Backlog Item internal Links:

By adding a further column (in the Spreadsheet) that can be identified by the Add-In I could pull or push the links to the Product Backlog Items:

I created a simplistic installer (it makes assumptions that you are running Office 2007 and that you are using a suitable template such as the Conchango SCRUM for TeamSystem):

http://www.petrellyn.com/jaspware/ExcelTfsAddIn/index.php?lang=eng