Automate the Export of Work Items from Azure DevOps Board with REST API (Powershell)

Exporting data from Azure DevOps is a common task for software developers and project managers. It allows users to analyze and report on their work items, track progress, and share information with stakeholders. However, there are different ways to export data in Azure DevOps, each with its own benefits and limitations. Following are the different ways to export data from Azure DevOps

• Exporting to CSV: Use the built-in “Export to CSV” option to download work item data in a comma-separated values format. This option is simple and easy to use, but it may not offer as much flexibility and control over the exported data as other methods.
  (Import or update work items in bulk with CSV files — Azure Boards | Microsoft Learn)
• Excel Integration: Use the Excel integration to import and export work item data to and from Excel spreadsheets. This option can be useful if you prefer to work with data in Excel or need to perform complex data manipulations.
  (Export Work Items from Azure DevOps Board to Microsoft Excel | Medium)
• REST API: Use the Azure DevOps REST API to access work item data programmatically and automate the export process. This option provides the most flexibility and control over the exported data, allowing you to customize the output format and perform more complex data manipulations. However, it requires some programming knowledge and may be more complex than other options.

In this article we will be exploring the REST API option with a sample code to retrieve the work items, for customized requirements REST API will be the best option.

1. Define the organisation, project and board team name. Create the Personal access token, use the base64 encoded one here.
   (Check the link at the bottom for a reference on how pat tokens are created)

# Define the Azure DevOps organization and project
$organization = <>
$project = <>
$team = <>
$authHeader = "Basic <base64 pat token>"
$assigneeMailId = <user email id>

#API version used is 7.0 which is latest at the time of writing, 
#update the url if any change is required

2. Retrieve the list of recent activity in board:
https://learn.microsoft.com/en-us/rest/api/azure/devops/wit/account-my-work-recent-activity/list?view=azure-devops-rest-7.0

#Define url
$recentListUrl = "https://dev.azure.com/$organization/_apis/work/accountmyworkrecentactivity?api-version=7.0"
# Get the recent work item data list
$recentListResponse = Invoke-RestMethod -Uri $recentListUrl -Method Get -Headers @{Authorization = $authHeader}

3. Filter out the today's task based on date and required user-email-Id:

$currentDate = Get-Date  
$todaysTask = $recentListResponse.value | where {([DateTime]$_.activityDate).Date -match $currentDate.Date}  | where {$_.assignedTo.uniqueName -eq $assigneeMailId} | where {$_.workItemType -eq 'Task'} 
#Uncomment if required to obtain only the edited tasks
#| where {$_.activityType -eq 'edited'}

#For printing the recent tasks tittle, 
#check the object to retrieve any other values
$todaysTask.title

4. The steps shown above will obtain the “tasks,” or active work items. Follow the code below for more parent “story” relations and comments retrievals. Data will be outputted into a csv file at the end of the script.

Below code will iterate through each recent task and create a PowerShell object with required parent and comment information.

$table = foreach ($taskItem in $todaysTask) {
    $taskId = $taskItem.id
    $taskUrl = "https://dev.azure.com/$organization/$project/_apis/wit/workitems/$($taskId)?" + '$expand=relations&api-version=7.0'

    # Get the task work item
    $taskResponse = Invoke-RestMethod -Uri $taskUrl -Method Get -Headers @{Authorization = $authHeader }
    # Get the parent details
    $parentId = $taskResponse.fields.'System.Parent'
    $parentUrl = "https://dev.azure.com/$organization/$project/_apis/wit/workitems/$($parentId)?api-version=7.0"
    # Retrive parent data
    $parentResponse = Invoke-RestMethod -Uri $parentUrl -Method Get -Headers @{Authorization = $authHeader }

    #Retrive the top 1 comment (desc order) of corresponding work item using below url
    $commentsUrl = "https://dev.azure.com/$organization/$project/_apis/wit/workItems/$taskId/comments?" + '$expand=none&$top=1&order=desc&api-version=7.0-preview.3'
    # Get the task work item comments
    $commentsResponse = Invoke-RestMethod -Uri $commentsUrl -Method Get -Headers @{Authorization = $authHeader }
    # Remove the html tags from comments data
    $plainComment = [System.Text.RegularExpressions.Regex]::Replace($commentsResponse.comments.text, "<.*?>", "")

    # Following sections are for custom excel format, 
    # Update the below based on your custom requirements
    $descVar = if ($plainComment) { $taskResponse.fields.'System.Title' + ' | ' + $plainComment } else { $taskResponse.fields.'System.Title' }
    $StatusVar = $taskResponse.fields.'System.State'

    # Creating powershell object for converting into excel
    [PSCustomObject] @{
        "Story"        = "$($parentResponse.id) " + $parentResponse.fields."System.Title"
        "Description"  = $descVar
        "Status"       = $StatusVar
        "Assignee"     = $assigneeMailId
        "ActivityDate" = $taskItem.activityDate
        "ActivityType" = $taskItem.activityType
    }
}

5. Export into csv file based on date and time

$table | Export-Csv -Path "$($currentDate.Day)_$($currentDate.Month)_$($currentDate.Year)_$($currentDate.Hour)_$($currentDate.Minute).csv" -NoTypeInformation

Dig Deeper…

https://learn.microsoft.com/en-us/rest/api/azure/devops/?view=azure-devops-rest-7.0