How to: Task Board Customize Columns Mapping on Azure DevOps

This script and report extract all Task Board Customize Columns within the Azure DevOps organization

Many customers that use Azure DevOps prefer to change Task Board Columns, but when you have different configurations, it’s more complicated to standardized our task boards and verifies that everyone uses these same settings. Thinking about mapping Task Board Columns on Azure DevOps, the following report was created:

An original script is available on my GitHub repository. See below this script:

Let’s go understand each command used.

1. PowerShell script will receive the following parameters:

• $PAT = Personal Access token to connect on Azure DevOps;
• $Organization = Organization URL to list all Task Board Columns on Azure DevOps organization;
• $Connstr = connection string to Azure SQL Database that stores the report information. To create this report, it’s necessary to create previously a Azure SQL Server and Database and run a script below:

2. Projects List = uses this REST API to list all Team Projects on Azure DevOps organization

$AzureDevOpsAuthenicationHeader = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$($PAT)")) }
$UriOrganization = "https://dev.azure.com/$($Organization)/" 
$uriProject = $UriOrganization + "_apis/projects?`$top=500"
$ProjectsResult = Invoke-RestMethod -Uri $uriProject -Method get -Headers $AzureDevOpsAuthenicationHeader
Foreach ($project in $ProjectsResult.value)
{
    Write-Host $project.name
}

3. Teams Get All Teams = uses this REST API to list all Teams on each Team Project

$uriTeams = $UriOrganization + "_apis/projects/$($project.id)/teams"    
$TeamsResult = Invoke-RestMethod -Uri $uriTeams -Method get -Headers $AzureDevOpsAuthenicationHeader    
Foreach ($team in $TeamsResult.value)    
{
    Write-Host $team.name
}

4. Taskboard Columns Get = uses this REST API to list all Task Board Customize Columns on each Team

$uriTeamTaskboardColumns = $UriOrganization + "$($project.id)/$($team.id)/_apis/work/taskboardcolumns?api-version=6.1-preview.1"        
$TeamTaskboardColumnsResult = Invoke-RestMethod -Uri $uriTeamTaskboardColumns -Method get -Headers $AzureDevOpsAuthenicationHeader        
Foreach ($teamTaskBoardColumn in $TeamTaskboardColumnsResult.columns)        
{
    Write-Host $teamTaskBoardColumn.name
}

5. After extract all Task Board Columns, this information is stored in a table in Azure SQL.

6. After insert information into a table, I connected this database on Power BI:

• Team Project (1) = Filter report using Team Project field;
• Team Name (2) = Filter report using Team Name field;
• Task Board Column Name (3) = Filter report using Task Board Column Name field;
• Task Board Customize Columns (4) = List all information about Projects, Teams, and respective Task Board Column Name. If the respective Task Board Customize Column is mapped on the respective Team, the same will be marked (green ticks on a grid). If you mouse over on the respective cell, respective work item types mapped to this column will be shown.