Scenario Link to heading
Some workitems are missing important information, the information was provided via an Excel sheet, the workitems need to be updated with this information without changing any other properties, particularly the workitem state.
Restrictions Link to heading
The workitems are in done state, and the process template has rules that prevent changes to the properties that need to be updated.
The Excel integration, which might be a solution, is not working on this environment because of a permissions error.
Solution Link to heading
Using the Azure DevOps .NET Client library, it is possible to update workitems. A relatively simple console application will do the trick, especially since the documentation already provides samples showing how to do it,
Fetch work items with queries programmatically in Azure DevOps Services, and
Create a bug in Azure DevOps Services using .NET client libraries.
The issue with the process template rules remains. Those rules are enforced not only in the user interface but also using the client library or the API directly, which in the end are the same, so for this to work a permissions change is needed, for the particular identity that will be used to interact with AzDevOps via the client library.
The permission is called “Bypass rules on work item updates” and as off this writing can be found by navigating to Project Settings -> Permissions -> Users -> Find the user that will be used and in the permissions panel find this setting.
Once the user has this permission, he/she can certainly do these changes directly via the user interface, however doing repetitive tasks by hand is something to avoid.
Or not…
Certainly I have done this many times, writing a huge Excel VB Macro to move things around or implementing a GitHub action just to copy a file, but I think in the end its worth it, primarily because manual tasks are error prone, but also because of the joy of programming!
So, back to the point,
With the bypass rules permission, there is something else needed from AzDevOps, and it is a personal access token, which provides impersonation, authentication and authorization. With this token the code will be able to interact with AzDevOps, using the identity of the user for which the token was generated.
This personal access tokens have a lot of very nice security features which makes it a very good option for many scenarios, in particular the permissions can be scoped to specific artifacts and actions, and are set to expire in 30 days by default.
At the time of writing this post, the option to create a new token is available in the top right, over the user settings icon,
And then the token details screen looks like this,
With the token in hand, we can now get to the code, which is an adaptation from the examples linked earlier.
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using Microsoft.TeamFoundation.WorkItemTracking.WebApi;
using Microsoft.VisualStudio.Services.Common;
using Microsoft.VisualStudio.Services.WebApi;
using Microsoft.VisualStudio.Services.WebApi.Patch;
using Microsoft.VisualStudio.Services.WebApi.Patch.Json;
using Newtonsoft.Json;
namespace ConsoleApp
{
class Program
{
const string AzDevOpsUri = "https://dev.azure.com/<your_org>";
const string PersonalAccessToken = "<the_token_generated_earlier>";
const string ProjectName = "<the_name_of_your_project>";
static void Main()
{
var connection = new VssConnection(new Uri(AzDevOpsUri), new VssBasicCredential(string.Empty, PersonalAccessToken));
var items = LoadJson();
UpdateWorkItems(connection, items).Wait();
Console.ReadKey();
}
static private async Task UpdateWorkItems(VssConnection connection, IEnumerable<Item> items)
{
WorkItemTrackingHttpClient witClient = connection.GetClient<WorkItemTrackingHttpClient>();
foreach (var item in items)
{
JsonPatchDocument documentWithTheUpdate = new JsonPatchDocument
{
new JsonPatchOperation()
{
Operation = Operation.Replace,
Path = "/fields/Microsoft.VSTS.Scheduling.Effort",
Value = item.Total
}
};
try
{
var result = await witClient.UpdateWorkItemAsync(documentWithTheUpdate, ProjectName, item.ID, false,
true);
Console.WriteLine($"Workitem {result.Id} updated to revision {result.Rev}");
}
catch (AggregateException ex)
{
Console.WriteLine($"Error updating workitem {item.ID}: {ex.InnerException?.Message}");
}
}
}
public static List<Item> LoadJson()
{
using (StreamReader r = new StreamReader("values.json"))
{
string json = r.ReadToEnd();
return JsonConvert.DeserializeObject<List<Item>>(json);
}
}
public class Item
{
public int ID;
public string Title;
public double Dev;
public double Test;
public double Total;
}
}
}
At some point I decided to convert the Excel file to JSON, that is why the code has this LoadJson method. Reading the Excel file specially saved as comma separated values (CSV) should not have been much different except for this method.
My JSON input file looks like this:
[
{
"ID": "25071",
"Dev Effort": "1",
"Test Effort": "0.5",
"Effort": "1.5"
},
{
"ID": "44260",
"Dev Effort": "2",
"Test Effort": "0.5",
"Effort": "2.5"
},
{
"ID": "43303",
"Dev Effort": "4",
"Test Effort": "1",
"Effort": "5"
}, ...
And running the program with proper configuration and having the JSON file in the right place produce the sample output bellow
And mission accomplished, now this WorkItems have the missing effort populated, without changing the “Done” state date.