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 
  1. <#  
  2. .SYNOPSIS  
  3.     A PowerShell function to export Visual Studio Team Servies work items.   
  4. .DESCRIPTION  
  5.     A PowerShell function to export Visual Studio Team Servies work items.   
  6. .EXAMPLE  
  7.     PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT -WorkItemType Bug  
  8.     Yields all the bugs in from the work items table.  
  9. .EXAMPLE  
  10.     PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT" -WorkItemType Task  
  11.     Yields all the task in from the work items table.  
  12. .EXAMPLE  
  13.     PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT" -WorkItemType Epic  
  14.     Yields all the epic in from the work items table.  
  15. .EXAMPLE  
  16.     PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT" -WorkItemType Epic -ExportAs Csv  
  17.     Exports information as csv  
  18. .EXAMPLE  
  19.     PS C:\> Export-VSTSWorkItem -Instance "Fabrikam" -Token "PAT" -WorkItemType Epic -ExportAs FancyHtml  
  20.     Exports information as a fancy html  
  21. .NOTES  
  22.     @ChendrayanV  
  23. #>  
Parameters are my best friend as always,
  1. param (  
  2.         # Visual Studio Team Services Account Name  
  3.         [Parameter(Mandatory)]  
  4.         $Instance,  
  5.  
  6.         # Create a Personal Access Token  
  7.         [Parameter(Mandatory)]  
  8.         $Token,  
  9.  
  10.         # Opt the Work Items Type. (Modify as required)  
  11.         [Parameter(Mandatory)]  
  12.         [ValidateSet('Bug''Task''Epic''Feature')]  
  13.         $WorkItemType,  
  14.  
  15.         # Export in your favorite format.  
  16.         [Parameter()]  
  17.         [ValidateSet('Csv''HTML''FancyHTML')]  
  18.         $ExportAs  
  19. )  
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.
  1. $Authentication = (":$Token")  
  2. $Authentication = [System.Text.Encoding]::ASCII.GetBytes($Authentication)  
  3. $Authentication = [System.Convert]::ToBase64String($Authentication)  
Using switch statement, modify the WIQL as illustrated below.
  1. switch ($WorkItemType)  
  2. {  
  3.     "Bug"  
  4.     {  
  5.         $Body = @{  
  6.             Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"  
  7.         } | ConvertTo-Json  
  8.     }  
  9.     "Task"  
  10.     {  
  11.         $Body = @{  
  12.             Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"  
  13.         } | ConvertTo-Json  
  14.     }  
  15.     "Epic"  
  16.     {  
  17.         $Body = @{  
  18.             Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"  
  19.         } | ConvertTo-Json  
  20.     }  
  21.     "Feature"  
  22.     {  
  23.         $Body = @{  
  24.             Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"  
  25.         } | ConvertTo-Json  
  26.     }  
  27. }  
Simply, we are converting the hash table to JSON using ConvertTo-Json cmdlet and with no more theory, below is the full script! 
  1. function Export-VSTSWorkItem  
  2. {  
  3. <#  
  4. .SYNOPSIS  
  5.     A PowerShell function to export Visual Studio Team Servies work items.   
  6. .DESCRIPTION  
  7.     A PowerShell function to export Visual Studio Team Servies work items.   
  8. .EXAMPLE  
  9.     PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Bug  
  10.     Yields all the bugs in from the work items table.  
  11. .EXAMPLE  
  12.     PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Task  
  13.     Yields all the task in from the work items table.  
  14. .EXAMPLE  
  15.     PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Epic  
  16.     Yields all the epic in from the work items table.  
  17. .EXAMPLE  
  18.     PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Epic -ExportAs Csv  
  19.     Exports information as csv  
  20. .EXAMPLE  
  21.     PS C:\> Export-VSTSWorkItem -Instance 'Fabrikam' -Token "PAT" -WorkItemType Epic -ExportAs FancyHtml  
  22.     Exports information as a fancy html  
  23. .NOTES  
  24.     @ChendrayanV  
  25. #>  
  26.     [CmdletBinding()]  
  27.     param (  
  28.         # Visual Studio Team Services Account Name  
  29.         [Parameter(Mandatory)]  
  30.         $Instance,  
  31.  
  32.         # Create a Personal Access Token  
  33.         [Parameter(Mandatory)]  
  34.         $Token,  
  35.  
  36.         # Opt the Work Items Type. (Modify as required)  
  37.         [Parameter(Mandatory)]  
  38.         [ValidateSet('Bug''Task''Epic''Feature')]  
  39.         $WorkItemType,  
  40.  
  41.         # Export in your favorite format.  
  42.         [Parameter()]  
  43.         [ValidateSet('Csv''HTML''FancyHTML')]  
  44.         $ExportAs  
  45.     )  
  46.       
  47.     begin  
  48.     {  
  49.     }  
  50.       
  51.     process  
  52.     {  
  53.         $Authentication = (":$Token")  
  54.         $Authentication = [System.Text.Encoding]::ASCII.GetBytes($Authentication)  
  55.         $Authentication = [System.Convert]::ToBase64String($Authentication)  
  56.         switch ($WorkItemType)  
  57.         {  
  58.             "Bug"  
  59.             {  
  60.                 $Body = @{  
  61.                     Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"  
  62.                 } | ConvertTo-Json  
  63.             }  
  64.             "Task"  
  65.             {  
  66.                 $Body = @{  
  67.                     Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"  
  68.                 } | ConvertTo-Json  
  69.             }  
  70.             "Epic"  
  71.             {  
  72.                 $Body = @{  
  73.                     Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"  
  74.                 } | ConvertTo-Json  
  75.             }  
  76.             "Feature"  
  77.             {  
  78.                 $Body = @{  
  79.                     Query = "Select * from WorkItems WHERE [System.WorkItemType] = '$WorkItemType'"  
  80.                 } | ConvertTo-Json  
  81.             }  
  82.         }  
  83.         $RestParams = @{  
  84.             Uri         = "https://$Instance.visualstudio.com/DefaultCollection/_apis/wit/wiql?api-version=1.0"  
  85.             Method      = "Post"  
  86.             ContentType = "application/json"  
  87.             Headers     = @{  
  88.                 Authorization = ("Basic {0}" -f $Authentication)  
  89.             }  
  90.             Body        = $Body  
  91.         }  
  92.         try  
  93.         {  
  94.             $Id = (Invoke-RestMethod @RestParams).workitems.id -join ","  
  95.             if ($Id -ne $null)  
  96.             {  
  97.                 $Fields = @('System.Id''System.Title''System.AssignedTo',   
  98.                     'System.State''System.CreatedBy''System.WorkItemType') -join ","  
  99.                 $RestParams["Uri"] = "https://$Instance.visualstudio.com/DefaultCollection/_apis/wit/WorkItems?ids=$Id&fields=$Fields&api-version=1"  
  100.                 $RestParams["Method"] = "Get"  
  101.                 $RestParams.Remove("Body")  
  102.                 $Result = Invoke-RestMethod @RestParams  
  103.                 if (! $PSBoundParameters['ExportAs'])  
  104.                 {  
  105.                     ($Result.value.fields)  
  106.                 }  
  107.             }  
  108.             else  
  109.             {  
  110.                 Write-Warning "No Items are available in $WorkItemType"  
  111.             }  
  112.           
  113.             switch ($ExportAs)  
  114.             {  
  115.                 'csv'  
  116.                 {  
  117.                     $Result.value.fields | Export-Csv .\WITReport.csv -NoTypeInformation   
  118.                 }  
  119.                 'HTML'  
  120.                 {  
  121.                     $Result.value.fields | Export-Csv .\WITReport.html -NoTypeInformation   
  122.                 }  
  123.                 'FancyHTML'  
  124.                 {  
  125.                     Add-Content  ".\style.CSS"  -Value " body {   
  126.                     font-family:Calibri;   
  127.                     font-size:10pt;   
  128.                     }   
  129.                     th {    
  130.                     background-color:black;   
  131.                     color:white;   
  132.                     }   
  133.                     td {   
  134.                     background-color:#19fff0;   
  135.                     color:black;}"  
  136.                     $Result.value.fields | ConvertTo-Html -CssUri .\Style.css | Out-File .\Report.html  
  137.                 }  
  138.             }  
  139.         }  
  140.         catch  
  141.         {  
  142.             $_.Exception.Message   
  143.         }  
  144.     }  
  145.       
  146.     end  
  147.     {  
  148.     }  
  149. }  
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".