Introduction

I got asked today by a client if there was a way to automate the exporting Azure DevOps Test Plans to Excel files. They knew they could do it manually via the Azure DevOps UI, but had a lot of Test Plans to export and wanted to automate the process.

The Options

I considered a few options:

• TCM CLI - This is a command line tool that can be used to interact with Azure DevOps Test Plans. It can be used to import or clone Test Plans, but not to export them.
• AZ DEVOPS CLI - This is a command line tool that can be used to interact with Azure DevOps. Unfortunately, it does not have any commands to export Test Plans.
• Azure DevOps REST API - This is a powerful API that can be used to interact with Azure DevOps, but the documentation makes no mention of a call to export Test Plans.

But…..

The Solution

I used a process I have used before to write tools for Azure DevOps. I opened the page in the Azure DevOps UI that I wanted to automate, and then used the browser developer tools to inspect the network traffic. I found a call that was made to export the Test Plan to Excel, and then used that as the basis for my tool.

Turns out there is an undocumented API call that can be used to export Test Plans to Excel. A POST call is made to the following URL:

https://dev.azure.com/{organization}/{project}/_apis/testplan/TestCases/TestCaseFile?api-version=7.1-preview.1

with a payload that looks like this:

{
  "testPlanId": 1,
  "testSuiteId": 2,
  "testCaseIds": [3, 4, 5],
  "columnOptions": ["System.Id","System.AssignedTo","System.State"]
}

The Code

So I needed to write a tool get all the test plans and test suites in an Azure DevOps Team project, and then call the API to export them to a set of Excel files.

I usually write this type of tool in PowerShell, but try as I might, even though I appeared to get the expected data back from the API call, I could not write it to disk in such a way Excel could read it. Excel said the file was corrupt.

As I had delivered a webinar today on using GitHub Copilot to write code, I thought I should practice what I preach and use it to fix the tool. As I suspected the issue was not the API call, but the PowerShell encoding of the data, I decided to ask Copilot to convert the code to Python, a language I am not too familiar with.

So with a couple of prompts my simple PowerShell proof of concept was converted to a Python script, and as I had hoped the resultant saved file loaded in Excel without a problem.

It was then a simple matter of adding more code, via Copilot prompts, to loop through all the Test Plans and Test Suites in the Team Project, and then call the API to export them to a set of Excel files.

This is not to say I did not have to make some tweaks to the code, but Copilot got most of it correct, there were a couple of edits I had to make to API URL strings, but Copilot certainly wrote the Python code much faster than I could have done myself.

So certainly a win for GitHub Copilot, and I now have a tool that can be used to export Azure DevOps Test Plans to Excel files at scale.

For the original version of this post see Richard Fennell's personal blog at Using GitHub Copilot to write an Azure DevOps Test Plan Export Tool