Post
Ask Question

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".
Next Recommended Reading PowerShell Script To Get All Site Collection, Document Library And Items Of A Particular Web Application