如何用PowerShell批量更新Azure DevOps自定义工作项（含Lookup替换）

Is PowerShell the Right Tool? #

Absolutely. PowerShell is an excellent choice here because:

• It’s scriptable and repeatable, perfect for your monthly update requirement.
• Handles REST API calls natively with Invoke-RestMethod, giving you full control over logic.
• Easy to schedule via Task Scheduler (Windows) or cron (Linux/macOS) without extra platform dependencies.
• As a senior dev, you’ll pick up the PowerShell syntax quickly for this use case.

Comparison with Other Options #

Power Automate #

• Pros: No-code/low-code interface, built-in ADO integration, and simple scheduling.
• Cons: Clunky for large datasets (pagination logic is cumbersome), debugging complex lookup logic is harder, and maintaining the lookup table requires external storage (like SharePoint) which adds overhead. Better for small, simple updates but not ideal for bulk monthly runs.

ADO Built-in Features #

• Pros: Bulk edit via queries works for manual one-off updates.
• Cons: No native way to automate value mapping from a lookup table. Manual export/import via Excel is error-prone and not scalable for repeated monthly tasks. Extensions might fill the gap but add unnecessary complexity.

Example PowerShell Script #

First, get your custom field reference names:

• Go to ADO Organization Settings > Process > Select your process > Work item types > "Resource" > Fields. The reference name will look like Custom.ValueA (copy these for your script).

Step 1: Prerequisites #

• ADO Personal Access Token (PAT) with Work Items (Read & Write) permissions.
• PowerShell 5.1 or later (or PowerShell Core).

Step 2: Script #

# --------------------------
# Configuration Variables
# --------------------------
$orgUrl = "https://dev.azure.com/YourOrganization"
$projectName = "YourProject"
$pat = $env:ADO_PAT # Store PAT in environment variable for security
$workItemType = "Resource"
$valueAFieldRef = "Custom.ValueA" # Replace with your actual field reference
$valueBFieldRef = "Custom.ValueB" # Replace with your actual field reference

# --------------------------
# Lookup Table (Option 1: Hardcoded)
# --------------------------
$lookupTable = @{
    "ValueA_1" = "ValueB_1"
    "ValueA_2" = "ValueB_2"
    "ValueA_3" = "ValueB_3"
}

# --------------------------
# Lookup Table (Option 2: Load from CSV)
# Uncomment below and replace path if using CSV
# $lookupTable = @{}
# Import-Csv -Path "C:\path\to\lookup.csv" | ForEach-Object {
#     $lookupTable[$_.ValueA] = $_.ValueB
# }

# --------------------------
# Authenticate with ADO
# --------------------------
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$pat"))
$headers = @{
    Authorization = "Basic $base64AuthInfo"
    "Content-Type" = "application/json-patch+json"
}

# --------------------------
# Fetch All "Resource" Work Items
# --------------------------
$wiqlQuery = @"
SELECT [System.Id], [$valueAFieldRef]
FROM WorkItems
WHERE [System.WorkItemType] = '$workItemType'
AND [System.TeamProject] = '$projectName'
"@

$wiqlBody = @{ query = $wiqlQuery } | ConvertTo-Json
$wiqlUrl = "$orgUrl/$projectName/_apis/wit/wiql?api-version=7.1-preview.2"

try {
    $wiqlResponse = Invoke-RestMethod -Uri $wiqlUrl -Method Post -Headers $headers -Body $wiqlBody -ContentType "application/json"
    if (-not $wiqlResponse.workItems) {
        Write-Host "No 'Resource' work items found."
        exit
    }

    # Batch request to fetch full work item details (efficient for large datasets)
    $workItemIds = $wiqlResponse.workItems.id -join ","
    $workItemsUrl = "$orgUrl/$projectName/_apis/wit/workitems?ids=$workItemIds&fields=System.Id,$valueAFieldRef&api-version=7.1-preview.3"
    $workItems = Invoke-RestMethod -Uri $workItemsUrl -Method Get -Headers $headers
}
catch {
    Write-Error "Failed to fetch work items: $_"
    exit
}

# --------------------------
# Process and Update Each Work Item
# --------------------------
foreach ($item in $workItems.value) {
    $workItemId = $item.id
    $currentValueA = $item.fields[$valueAFieldRef]

    if (-not $currentValueA) {
        Write-Host "Work item $workItemId has no Value A, skipping."
        continue
    }

    if (-not $lookupTable.ContainsKey($currentValueA)) {
        Write-Host "No matching Value B found for Value A: $currentValueA (Work item $workItemId), skipping."
        continue
    }

    $newValueB = $lookupTable[$currentValueA]
    $updateUrl = "$orgUrl/$projectName/_apis/wit/workitems/$workItemId?api-version=7.1-preview.3"

    # Prepare patch body for ADO update
    $patchBody = @(
        @{
            op = "add"
            path = "/fields/$valueBFieldRef"
            value = $newValueB
        }
    ) | ConvertTo-Json

    try {
        Invoke-RestMethod -Uri $updateUrl -Method Patch -Headers $headers -Body $patchBody
        Write-Host "Successfully updated work item $workItemId : Value B set to '$newValueB'"
    }
    catch {
        Write-Error "Failed to update work item $workItemId : $_"
    }
}

Key Notes #

• Security: Never hardcode your PAT. Use environment variables or Azure Key Vault for production scenarios.
• Field References: Double-check your custom field reference names — using the wrong name will cause update failures.
• Pagination: If you have over 200 work items, add logic to handle the WIQL continuation token to fetch all pages.
• Scheduling: Use Windows Task Scheduler or cron to run the script monthly. For PowerShell Core, ensure the task uses the correct executable path (e.g., pwsh.exe).

内容的提问来源于stack exchange，提问作者Ben Nelson