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
