A Python script to export all Azure DevOps Test Suites in a Team Project as Excel files (replicated the Azure DevOps UI Export button)

export-ado-testsuite.py

import requests
import base64
import os
import argparse

def download_testcases_excel(organization, project, pat, test_case_ids, test_plan_id, test_suite_id,  output_path):
    """
    Download test cases as an Excel file from Azure DevOps Test Plan API.
    """
    api_url = f"https://dev.azure.com/{organization}/{project}/_apis/testplan/TestCases/TestCaseFile?api-version=7.1-preview.1"
    payload = {
        "testCaseIds": test_case_ids,
        "testPlanId": test_plan_id,
        "testSuiteId": test_suite_id,
        "columnOptions": ["System.Id","System.AssignedTo","System.State"]
    }
    base64_pat = base64.b64encode(f':{pat}'.encode('ascii')).decode('ascii')
    headers = {
        'Authorization': f'Basic {base64_pat}',
        'Content-Type': 'application/json'
    }
    response = requests.post(api_url, json=payload, headers=headers)
    if response.status_code == 200:
        with open(output_path, 'wb') as f:
            f.write(response.content)
    else:
        print(f"Request failed: {response.status_code} - {response.text}")



# --- Argument parsing ---
parser = argparse.ArgumentParser(description="Download Azure DevOps test case Excel file.")
parser.add_argument('--pat', required=True, default=None, help='Azure DevOps Personal Access Token')
parser.add_argument('--project', required=True, default=None, help='Azure DevOps Project Name')
parser.add_argument('--org', required=True, default=None, help='Azure DevOps Organization Name')
args = parser.parse_args()

# --- Configuration ---
organization = args.org
project = args.project
pat = args.pat 

# --- Get list of test plans ---
plans_url = f"https://dev.azure.com/{organization}/{project}/_apis/testplan/plans?api-version=7.1-preview.1"
plans_response = requests.get(plans_url, headers={
    'Authorization': f'Basic {base64.b64encode(f':{pat}'.encode()).decode()}'
})
if plans_response.status_code == 200:
    plans = plans_response.json().get('value', [])
    print("Test Plans:")
    for plan in plans:
        print(f"  Plan: {plan['id']}, Name: {plan['name']}")
        # Get and list test suites for this plan
        suites_url = f"https://dev.azure.com/{organization}/{project}/_apis/testplan/Plans/{plan['id']}/suites?api-version=7.1-preview.1"
        suites_response = requests.get(suites_url, headers={
            'Authorization': f'Basic {base64.b64encode(f':{pat}'.encode()).decode()}'
        })
        if suites_response.status_code == 200:
            suites = suites_response.json().get('value', [])
            print(f"    Suites:")
            for suite in suites:
                print(f"      Suite: {suite['id']}, Name: {suite['name']}")
                # list the test cases in the suite
                # have to leave the -preview.1 extension off the URL as it doesn't work with the test case API
                testcase_url = f"https://dev.azure.com/{organization}/{project}/_apis/testplan/Plans/{plan['id']}/suites/{suite['id']}/TestCase?api-version=7.1"
                testcase_response = requests.get(testcase_url, headers={
                    'Authorization': f'Basic {base64.b64encode(f':{pat}'.encode()).decode()}'
                })
                if testcase_response.status_code == 200:
                    suitedetails = testcase_response.json().get('value', [])
                    # Download the test cases to an Excel file if there are any test cases in the suite
                    if suitedetails:
                        print(f"        Test Cases: {len(suitedetails)} found")
                        # list the test cases
                        test_case_ids = []
                        for item in suitedetails:
                            test_case_id = item.get('workItem', {}).get('id')
                            if test_case_id is not None:
                                test_case_ids.append(test_case_id)
                        # Download the test cases to an Excel file
                        filename = f"{project}_Plan_{plan['id']}_Suite_{suite['id']}_TestCases.xlsx"
                        print(f"        Downloading test cases for Plan ID: {plan['id']}, Suite ID: {suite['id']} to {filename}")
                        download_testcases_excel(organization, project, pat, test_case_ids, plan['id'], suite['id'], filename)
                    else:
                        print(f"        No test cases found in Suite ID: {suite['id']} so not file exported")    
        else:
            print(f"    Failed to get suites: {suites_response.status_code} - {suites_response.text}")
else:
    print(f"Failed to get test plans: {plans_response.status_code} - {plans_response.text}")

exit(0)  # Exit after listing test plans