Importing requirements from Excel

In Scrum, the taskboard is a visual display of the progress of the Scrum team during a sprint. It presents a snapshot of the current sprint backlog, allowing everyone to stay synchronized and focused on the work to be done. Most of the time, smaller teams are distributed across multiple locations, and in these situations, tracking work with a digital tool helps distributed teams synchronize more effectively. Some of us are lucky enough to land on green field projects, which gives us the opportunity to start tracking the requirements of work items from inception. Other times, projects are planned in tools that don't natively support integration with Azure DevOps Server. Luckily, most planning tools allow you to extract the data to Excel. Azure DevOps Server natively supports importing work items through Excel, but the challenge is mostly working out which fields in the spreadsheet should map out to work items in Azure DevOps Server. In this recipe, we'll learn how to import requirements from Excel into work items and refresh updates from work items back into Excel. 

Getting ready

If you don't have Office Excel, install it. For Azure DevOps Server 2019, you'll need Office 2013 or a later version. The Excel plugin for Azure DevOps Server is installed by installing one of the latest editions of Visual Studio or the Azure DevOps Server Standalone Office Integration installer. Azure DevOps Server Standalone Office Integration supports connecting to Azure DevOps Server from Excel, Microsoft Project, and the PowerPoint-based storyboard tool.

If you don't intend to install Visual Studio but need Office integration, download and install Azure DevOps Server Standalone Office Integration (free) from https://www.visualstudio.com/downloads/. Once the installation is complete, the Excel Plugin will show up under the Team ribbon in Excel, as shown in the following screenshot:

If you don't see the Team ribbon, perform the following steps to enable it:

1. Click the File tab in Excel and choose Options.
2.  In the Categories pane, click Add-ins, and verify that Team Foundation Add-in shows up in the Disabled Application Add-ins section.
3. In the manage box, select disabled items and click Go.
4. Select the Azure DevOps Server Add-in and click Enable. Finally, exit the dialog by clicking Close.

If you are continuing to run into issues with Add-in not showing up in Excel, you may be able to resolve the issue with the procedures provided at the following link: https://docs.microsoft.com/en-us/vsts/work/backlogs/office/tfs-office-integration-issues.

How to do it...

Now that we have the Azure DevOps Sever excel plugin installed, in this section we'll learn how to use it. 

Start by performing the following steps:

1. Launch Excel and start with a blank sheet. Navigate to the Team ribbon.   
2. Click on New List to connect to your project in TFS. 

3. If you are connecting to Azure DevOps Server from Excel for the first time, you will have to add your server details to the list of recognized servers. The steps for this are shown in the following screenshot:

4. Select the PartsUnlimited team project and click Connect:

5. When asked which type of work item list you want to create, choose Input list. An Input list gives you a blank template that is linked to your team project:

6. Your worksheet will now be bound to your team project as a flat list. What this means is that you can add work items to the team project from the worksheet or add work items to the worksheet from the team project. Fill out the details of the work items you want to add and their work item type. The Excel plugin defaults the list type to flat, but you can change it to a tree list if you wish. A tree list allows you to create and view hierarchically linked work items, like so:

7. Publish the changes by clicking the Publish button from the Team ribbon.

You can add more work item fields as columns to this template. Right-click within the table mapped to Azure DevOps Server, and then from the context menu, select Team | Choose columns. 

How it works...

To validate whether the changes have been synchronized to Azure DevOps Server, launch the web portal in a browser, and navigate to the work hub in the PartsUnlimited team project. The newly added work item should show up under the features backlog, as shown in the following screenshot:

Follow these tips to keep your work in sync:

• When you first open a saved worksheet, use the Refresh button in Excel on the Team ribbon to download the latest data from the data store
• Enter data for additional fields by adding columns to the worksheet using the Choose Column icon in Excel on the Team ribbon
• To avoid data conflicts, publish your additions and modifications often
• To prevent loss of data before you publish or refresh, save your workbook periodically

The Azure DevOps Server Excel plugin uses the Azure DevOps Server REST APIs, which are wrapped into an SDK. This allows for safe and secure bulk editing of work items. The plugin supports two-way updates, and changes that are made to work items in Azure DevOps Server web portal can be refreshed back into Excel by clicking the Refresh button. Refreshing the data does not overwrite any calculations or formatting that you may have applied to the worksheet. If you spend a lot of time using Microsoft Project, you'll be excited to know that the Azure DevOps Server plugin can also be used from Microsoft Project.

There's more... 

The marketplace features the Azure DevOps Open in Excel extension (https://marketplace.visualstudio.com/items?itemName=blueprint.vsts-open-work-items-in-excel). This is a free extension that was created by Microsoft DevLabs, and adds the option of opening work items in Excel from various access points, such as work item queries, backlogs, and selective work items:

Another noticeable extension in the marketplace is the Requirements Integrator (https://marketplace.visualstudio.com/items?itemName=jgarverick.RequirementsIntegrator). This is an open source extension that was created by Microsoft MVP Josh Garverick, which introduces the capability of mapping external requirements into Azure DevOps Server to create a traceability matrix with work items. This extension introduces a new sub-tab called Requirements in the work hub, which allows you to import external requirements into TFS using a predefined Excel template:

The extension allows you to do the following:

• Manage requirements to work item mapping
• Display a sprint view that shows the requirements covered by a sprint
• Display a traceability matrix, including gaps, for requirements that are imported and mapped to WIs
• Restrict import usage to non-CMMI process templates
• Requirement visualization (visual traceability)
• Export requirement information to Excel 

I encourage you to look at the marketplace (https://marketplace.visualstudio.com/) as it has a range of extensions that enhance the experience of planning, tracking, and managing work items. While this extension isn't necessarily a replacement for the Excel add-in, you'll find that it enhances the work planning, tracking, and management experience.