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,
  6. # Create a Personal Access Token
  7. [Parameter(Mandatory)]
  8. $Token,
  10. # Opt the Work Items Type. (Modify as required)
  11. [Parameter(Mandatory)]
  12. [ValidateSet('Bug', 'Task', 'Epic', 'Feature')]
  13. $WorkItemType,
  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,
  32. # Create a Personal Access Token
  33. [Parameter(Mandatory)]
  34. $Token,
  36. # Opt the Work Items Type. (Modify as required)
  37. [Parameter(Mandatory)]
  38. [ValidateSet('Bug', 'Task', 'Epic', 'Feature')]
  39. $WorkItemType,
  41. # Export in your favorite format.
  42. [Parameter()]
  43. [ValidateSet('Csv', 'HTML', 'FancyHTML')]
  44. $ExportAs
  45. )
  47. begin
  48. {
  49. }
  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. }
  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. }
  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".