Using outlook rules, json and powershell to link TFS 2017 work items to a parent

We use a TFS user story as a Support inbox and wanted to simplify creating new work items for common issues. We created templates for work items based on existing work items which had the parent required but discovered that the parent link does not get created for the new items.

Since our team doesn't have admin access to the TFS server, I decided to see if I could use the TFS notifications to trigger a custom outlook rule script which would send the task id to a local powershell script which in turn does the actual link creation. I decided that would be easier than trying to work out how to access REST services through VBA since there are more powershell examples out there than VBA examples.

I chose to go with json to define the parent link instead of doing it through code. It seemed simpler to me for maintenance purposes. The relevant json documentation was just a little confusing for a json novice because it looks like there should be double curly brackets under attributes. However doing that gave me the error:

"You must pass a valid patch document in the body of the request."

The final json looks like this

[
  {
    "op": "add",
    "path": "/relations/-",
    "value":
    {
        "rel": "System.LinkTypes.Hierarchy-Reverse",
        "url": "https://tfs.myCompany.org/tfs/DefaultCollection/_apis/wit/workitems/259355",
        "attributes":
        {
            "isLocked": false 
        }
    }
}
]

The Outlook rule looks for all emails with "Task" in the subject and "create Task" in the body.

You may need to enable the developer tools in outlook first. Once it is enabled, click on the Developer Tab and click on Visual Basic to the far left. This will open the VBA editor.

The outlook rule script itself is very simple.

Sub SendToTFS(MyMail As MailItem)
 If InStr(1, MyMail.Subject, "Task") = 1 Then
   Dim taskid As String
   taskid = Mid(MyMail.Subject, 6, 6)
   scriptCmd = "powershell.exe -NoLogo -NonInteractive -File ""e:\scripts\TFSLinkParent.ps1"" -argumentlist " & taskid & " > ""e:\scripts\TFSLinkParentLog.txt"""
   Shell scriptCmd
 End If
End Sub

The code first checks that Task is right at the beginning of the subject line to avoid reacting to forwards and replies.
The task id is then extracted from the subject line and sent to the powershell script.

Finally the Powershell script to do the job looks like this:

$TaskId = $args[1]

$taskItemURL = "https://tfs.mycompany.org/tfs/DefaultCollection/_apis/wit/workitems/$TaskId"
$taskItemRequest = $taskItemUrl+'?$expand=relations' 
$taskItemJson = Invoke-RestMethod -uri "$taskItemRequest" -Method get -UseDefaultCredentials -OutFile E:\scripts\TFSLinkReqLog.txt

if(!($taskItemJson.relations))
{
    $result = Invoke-RestMethod -uri $taskItemURL"?api-version=1.0" -Method patch -UseDefaultCredentials -ContentType application/json-patch+json -InFile E:\scripts\JsonTemplate.txt  -OutFile E:\scripts\TFSLinkLog.txt
}

The script starts with downloading the json for the work item and checks that it doesn't already have any relations. Child relations would also get caught by this check. If there aren't any relations then the current work item is attached to the parent.