How to export manual test Runs from a Test Suite into a csv / xlsx format

Question

Via the Runs feature in Test Plans, I want to identify to a third party how the outcome of a large set of tests in my test suites have changed over time when measured against releases. I need to do this via exporting all of the Runs completed to date into a file to be used for data manipulation.

However, when I open said Run feature from a test case I wish to export, there is no export function available for me.

Giving that third party access to the overall Test Plan is impractical as they wish to see all of the results in one document without needing to check the outcome of each case.

Using the GET API command with my org/project does not present the test results in a reportable format once exported.

Manually copying and pasting this quantity of data is also not practical for me given my plan consists of a large number of discrete suites and cases run over multiple iterations. In any event it seems this cannot be executed from the Runs feature even if that was the only means of extracting the information I needed.

Please assist with how I can efficiently meet my requirement above.

Answer 1

Hi Chris G

Could you please follow the below steps:

1. Azure DevOps allows exporting test cases from Test Plans into CSV or Excel. Navigate to Test Plans -> Test Suites -> Select Export to CSV/XLSX -> Download the file. However, this method only exports test cases, not historical test run results.
2. Since the GET API does not provide a reportable format, try using the following API endpoints: List Test Runs:

      GET https://dev.azure.com/{organization}/{project}/_apis/test/runs?api-version=7.1-preview
   

   Get Test Results for a Run:

      GET https://dev.azure.com/{organization}/{project}/_apis/test/runs/{runId}/results?api-version=7.1-preview
   

3. You can use Invoke-RestMethod and Export-Csv to structure and save data.

      $org = "your-org"
      $project = "your-project"
      $token = "your-personal-access-token"
      $headers = @{
          Authorization = "Bearer $token"
      }
      $url = "https://dev.azure.com/$org/$project/_apis/test/runs?api-version=7.1-preview"
      $response = Invoke-RestMethod -Uri $url -Headers $headers -Method Get
      # Convert API response into CSV
      $response.value | Export-Csv -Path "TestRuns.csv" -NoTypeInformation
      Write-Output "Test run data exported successfully!"
   

   Save the results as a CSV file.

Additional References:

https://learn.microsoft.com/en-us/azure/devops/test/bulk-import-export-test-cases?view=azure-devops

Hope this helps!

Please Let me know if you have any queries.

Answer 2

Hi Durga, thank you for the response. I'm unable to reach the test runs with the Powershell query above but I think this is user error rather than an incorrectly provided set of parameters :) . Should I be storing the output in a local file on my directory? If so, please indicate which area of the GET includes this info.

Answer 3

Hi Chris G

Yes, you should store the API response in a local file for easier handling. Here's an improved PowerShell query that Fetches all test runs and Store Data in JSON file.

$org = "your-org"
$project = "your-project"
$pat = "your-personal-access-token"
$headers = @{
    Authorization = "Basic $([Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$pat")))"
    "Content-Type" = "application/json"
}
# API Endpoint to Get Test Runs
$testRunsUrl = "https://dev.azure.com/$org/$project/_apis/test/runs?api-version=7.1-preview.1"
# Fetch Test Runs
$testRuns = Invoke-RestMethod -Uri $testRunsUrl -Headers $headers -Method Get
# Save Response as JSON (for debugging & analysis)
$testRuns | ConvertTo-Json -Depth 10 | Out-File -FilePath "C:\TestRuns.json"
# Extract Key Information for CSV (optional)
$testRuns.value | Select-Object id, name, state, automated, completedDate | Export-Csv -Path "C:\TestRuns.csv" -NoTypeInformation

When you execute the GET API, the test runs are stored in the value property of the response JSON.

Hope this helps!

Please Let me know if you have any queries.

If you found the information helpful, please click "Upvote" on the post to let us know and consider accepting the answer as the token of appreciation. Thank You.