Export Visual Studio Team Services Work Items Using PowerShell

It's easy to use Excel add-ins or extensions from marketplace to export the work items, right? Then why build a script in PowerShell?

 

To answer this in short, I manage multiple instances for multiple projects so I use PowerShell, which is a quick way for me to explore work items in one go! 

 

Comments 

<#
.SYNOPSIS
    A PowerShell function to export Visual Studio Team Servies work items.
.DESCRIPTION
    A PowerShell function to export Visual Studio Team Servies work items.
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT -WorkItemType Bug
    Yields all the bugs in from the work items table.
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT" -WorkItemType Task
    Yields all the task in from the work items table.
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT" -WorkItemType Epic
    Yields all the epic in from the work items table.
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT" -WorkItemType Epic -ExportAs Csv
    Exports information as csv
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT" -WorkItemType Epic -ExportAs FancyHtml
    Exports information as a fancy html
.NOTES
    @ChendrayanV
#>

Parameters are my best friend as always,

param (
        # Visual Studio Team Services Account Name
        [Parameter(Mandatory)]
        $Instance,

        # Create a Personal Access Token
        [Parameter(Mandatory)]
        $Token,

        # Opt the Work Items Type. (Modify as required)
        [Parameter(Mandatory)]
        [ValidateSet('Bug', 'Task', 'Epic', 'Feature')]
        $WorkItemType,

        # Export in your favorite format.
        [Parameter()]
        [ValidateSet('Csv', 'HTML', 'FancyHTML')]
        $ExportAs
)

We use a personal access token so it's a must to convert the token to base 64 string. As a system admin, it took some time for me to understand but the below snippet makes it happen.

$Authentication = (":$Token")
$Authentication = [System.Text.Encoding]::ASCII.GetBytes($Authentication)
$Authentication = [System.Convert]::ToBase64String($Authentication)

Using switch statement, modify the WIQL as illustrated below.

switch ($WorkItemType)
{
    "Bug"
    {
        $Body = @{
            Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"
        } | ConvertTo-Json
    }
    "Task"
    {
        $Body = @{
            Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"
        } | ConvertTo-Json
    }
    "Epic"
    {
        $Body = @{
            Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"
        } | ConvertTo-Json
    }
    "Feature"
    {
        $Body = @{
            Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"
        } | ConvertTo-Json
    }
}

Simply, we are converting the hash table to JSON using ConvertTo-Json cmdlet and with no more theory, below is the full script! 

function Export-VSTSWorkItem
{
<#
.SYNOPSIS
    A PowerShell function to export Visual Studio Team Servies work items.
.DESCRIPTION
    A PowerShell function to export Visual Studio Team Servies work items.
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Bug
    Yields all the bugs in from the work items table.
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Task
    Yields all the task in from the work items table.
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Epic
    Yields all the epic in from the work items table.
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Epic -ExportAs Csv
    Exports information as csv
.EXAMPLE
    PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Epic -ExportAs FancyHtml
    Exports information as a fancy html
.NOTES
    @ChendrayanV
#>
    [CmdletBinding()]
    param (
        # Visual Studio Team Services Account Name
        [Parameter(Mandatory)]
        $Instance,

        # Create a Personal Access Token
        [Parameter(Mandatory)]
        $Token,

        # Opt the Work Items Type. (Modify as required)
        [Parameter(Mandatory)]
        [ValidateSet('Bug', 'Task', 'Epic', 'Feature')]
        $WorkItemType,

        # Export in your favorite format.
        [Parameter()]
        [ValidateSet('Csv', 'HTML', 'FancyHTML')]
        $ExportAs
    )

    begin
    {
    }

    process
    {
        $Authentication = (":$Token")
        $Authentication = [System.Text.Encoding]::ASCII.GetBytes($Authentication)
        $Authentication = [System.Convert]::ToBase64String($Authentication)
        switch ($WorkItemType)
        {
            "Bug"
            {
                $Body = @{
                    Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"
                } | ConvertTo-Json
            }
            "Task"
            {
                $Body = @{
                    Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"
                } | ConvertTo-Json
            }
            "Epic"
            {
                $Body = @{
                    Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"
                } | ConvertTo-Json
            }
            "Feature"
            {
                $Body = @{
                    Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"
                } | ConvertTo-Json
            }
        }
        $RestParams = @{
            Uri         = "https://$Instance.visualstudio.com/DefaultCollection/_apis/wit/wiql?api-version=1.0"
            Method      = "Post"
            ContentType = "application/json"
            Headers     = @{
                Authorization = ("Basic {0}" -f $Authentication)
            }
            Body        = $Body
        }
        try
        {
            $Id = (Invoke-RestMethod @RestParams).workitems.id -join ","
            if ($Id -ne $null)
            {
                $Fields = @('System.Id', 'System.Title', 'System.AssignedTo',
                    'System.State', 'System.CreatedBy', 'System.WorkItemType') -join ","
                $RestParams["Uri"] = "https://$Instance.visualstudio.com/DefaultCollection/_apis/wit/WorkItems?ids=$Id&fields=$Fields&api-version=1"
                $RestParams["Method"] = "Get"
                $RestParams.Remove("Body")
                $Result = Invoke-RestMethod @RestParams
                if (! $PSBoundParameters['ExportAs'])
                {
                    ($Result.value.fields)
                }
            }
            else
            {
                Write-Warning "No Items are available in $WorkItemType"
            }

            switch ($ExportAs)
            {
                'csv'
                {
                    $Result.value.fields | Export-Csv .\WITReport.csv -NoTypeInformation
                }
                'HTML'
                {
                    $Result.value.fields | Export-Csv .\WITReport.html -NoTypeInformation
                }
                'FancyHTML'
                {
                    Add-Content  ".\style.CSS"  -Value " body {
                    font-family:Calibri;
                    font-size:10pt;
                    }
                    th {
                    background-color:black;
                    color:white;
                    }
                    td {
                    background-color:#19fff0;
                    color:black;}"
                    $Result.value.fields | ConvertTo-Html -CssUri .\Style.css | Out-File .\Report.html
                }
            }
        }
        catch
        {
            $_.Exception.Message
        }
    }

    end
    {
    }
}

Enjoy PowerShell!

 

Soon I will connect with you all with a series of detailed blog posts under the umbrella of  "Work with Visual Studio Team Services using REST API in PowerShell".