If you have a set of goals that you want to rollover into a new scorecard, or just want to bulk copy goals across scorecards, this utility script can save you some time by automating the copy process.
What gets copied:
– Scorecard name, contact, and description (when duplicating scorecards)
– Goal definitions (name, owners, dates, status rules). The relationship between parent/child goals is preserved as well.
What is not copied:
– Scorecard settings like column configuration and custom statuses
– Goal level permissions
– Goal queries to source report
– Goal values history and notes
Important: This tip is meant for advanced users, only run the script if you understand its implications
Usage:
– Copy the script to a new PowerShell file and save it. For example, “copy-scorecard.ps1”
– Open a new PowerShell window, and run the script
Note: the script will install MicrosoftPowerBIMgmt module if not present on the machine
– Follow on screen instructions to duplicate a scorecard or copy goals
$ErrorActionPreference = "Stop"
$publicEndpoint = "https://api.powerbi.com"
if (!(Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt)) {
try {
Install-Module -Name MicrosoftPowerBIMgmt -AllowClobber -Confirm:$False -Force
}
catch [Exception] {
$_.message
exit
}
}
Login-PowerBI
$token = (Get-PowerBIAccessToken)["Authorization"]
function GetApiUrl() {
$response = Invoke-WebRequest -Uri "$publicEndpoint/metadata/cluster" -Headers @{ "Authorization"=$token }
$cluster = $response.Content | ConvertFrom-Json
return $cluster.backendUrl
}
$api = GetApiUrl
function GetScorecard($scorecardId) {
Write-Host "Retrieving scorecard..." -NoNewLine
$response = Invoke-WebRequest `
-Uri "$api/v1.0/myOrg/internalScorecards($scorecardId)?`$expand=goals" `
-Headers @{ "Authorization"=$token }
Write-Host -ForegroundColor Green OK
$scorecard = $response.Content | ConvertFrom-Json
return $scorecard
}
function CopyGoal($sourceGoal, $destinationScorecardId, $parentGoalId) {
Write-Host "Copying ""$($sourceGoal.name)""... " -NoNewline
try {
$newGoalRequest = $sourceGoal | select -Property name, startDate, completionDate, unit, owner, additionalOwners, valuesFormatString, datesFormatString
if ($parentGoalId) {
Add-Member -InputObject $newGoalRequest -Name parentId -MemberType NoteProperty -Value $parentGoalId
}
$response = Invoke-WebRequest `
-Method Post `
-Uri "$api/v1.0/myOrg/internalScorecards($destinationScorecardId)/goals" `
-Headers @{ "Authorization"=$token } `
-Body ($newGoalRequest | ConvertTo-Json) `
-ContentType "application/json"
$goal = $response.Content | ConvertFrom-Json
Write-Host -ForegroundColor Green OK
if ($sourceGoal.statusRules) {
CopyStatusRules -goal $sourceGoal -destinationScorecardId $destinationScorecardId -destinationGoalId $goal.id
}
return $goal
} catch {
Write-host -ForegroundColor Red "Could not copy goal"
throw
}
}
function CopyStatusRules($goal, $destinationScorecardId, $destinationGoalId) {
Write-Host " - Copying status rules... " -NoNewline
$response = Invoke-WebRequest `
-Uri "$api/v1.0/myOrg/internalScorecards($($goal.scorecardId))/goals($($goal.id))/statusRules" `
-Headers @{ "Authorization"=$token }
$rules = $response.Content | ConvertFrom-Json
$response = Invoke-WebRequest `
-Method Post `
-Uri "$api/v1.0/myOrg/internalScorecards($destinationScorecardId)/goals($destinationGoalId)/statusRules" `
-Body ($rules | ConvertTo-Json -Depth 100) `
-ContentType "application/json" `
-Headers @{ "Authorization"=$token }
Write-Host -ForegroundColor Green OK
}
function RecursiveCopy($sourceGoal, $goalsByParentId, $destinationScorecardId, $newParentId) {
$goal = CopyGoal -sourceGoal $sourceGoal -destinationScorecardId $destinationScorecardId -parentGoalId $newParentId
if ($goalsByParentId[$sourceGoal.id]) {
foreach ($childGoal in $goalsByParentId[$sourceGoal.id]) {
RecursiveCopy -sourceGoal $childGoal -goalsByParentId $goalsByParentId -destinationScorecardId $destinationScorecardId -newParentId $goal.id
}
}
}
function CopyAllGoals($sourceScorecard, $destinationScorecardId) {
$goalsByParentId = @{}
$topLevelGoals = @()
foreach ($goal in $sourceScorecard.goals) {
if ($goal.parentId) {
if (!$goalsByParentId[$goal.parentId]) {
$goalsByParentId[$goal.parentId] = @()
}
$goalsByParentId[$goal.parentId] += $goal
} else {
$topLevelGoals += $goal
}
}
Write-Host "Copying $($sourceScorecard.goals.Length) goals to scorecard $destinationScorecardId..."
foreach ($goal in $topLevelGoals) {
RecursiveCopy -sourceGoal $goal -goalsByParentId $goalsByParentId -destinationScorecardId $destinationScorecardId -newParentId $null
}
Write-Host "Done"
}
function DuplicateScorecard($sourceScorecard, $destinationWorkspaceId, $newScorecardName) {
Write-Host "Creating scorecard in workspace $destinationWorkspaceId..."
$newScorecard = $sourceScorecard | select name, groupId, description, contact
$newScorecard.groupId = $destinationWorkspaceId
if ($newScorecardName) {
$newScorecard.name = $newScorecardName
}
$response = Invoke-WebRequest `
-Method Post `
-Uri "$api/v1.0/myOrg/internalScorecards" `
-Headers @{ "Authorization"=$token } `
-Body ($newScorecard | ConvertTo-Json) `
-ContentType "application/json"
$destinationScorecard = $response.Content | ConvertFrom-Json
Write-Host -ForegroundColor Green "Created scorecard $($destinationScorecard.id)"
CopyAllGoals -sourceScorecard $sourceScorecard -destinationScorecardId $destinationScorecard.id
}
function ShowHelp() {
Write-Host -ForegroundColor Yellow "This script can help you clone all goal definitions from an existing scorecard to a new or existing one"
Write-Host -ForegroundColor Yellow "Before running the script, please make sure you have contributor permissions on the source scorecard and destination workspace"
Write-Host -ForegroundColor Yellow "The following properties will not be copied:"
Write-Host -ForegroundColor Yellow " - Scorecard column settings, custom statuses"
Write-Host -ForegroundColor Yellow " - Goal permissions"
Write-Host -ForegroundColor Yellow " - Goal connections to source reports"
Write-Host -ForegroundColor Yellow " - Goal values history"
Write-Host -ForegroundColor Yellow " - Goal notes"
}
function ShowPrompt() {
while ($true) {
Write-Host -ForegroundColor Yellow "Scorecard cloning utility"
Write-Host "Choose action:"
Write-Host " Copy goals to a [n]ew scorecard (default)"
Write-Host " Copy goals to an [e]xisting scorecard"
Write-Host " [H]elp"
Write-Host " [Q]uit"
$action = Read-Host -Prompt "Choose action or press enter to duplicate a scorecard"
if ($action -eq "h") {
ShowHelp
} else {
break
}
}
if ($action -and ($action -ne "n" -and $action -ne "e")) {
if ($action -ne "q") {
Write-Host -ForegroundColor red "Invalid action"
}
return
}
$scorecardId = Read-Host -Prompt "Enter source scorecard id"
if (!$scorecardId) {
Write-Error "Invalid scorecard id"
}
$sourceScorecard = GetScorecard -scorecardId $scorecardId
Write-Host -ForegroundColor Green "Scorecard: $($sourceScorecard.name). Workspace: $($sourceScorecard.groupId)"
if ($action -eq "n" -or !$action) {
$newScorecardName = Read-Host -Prompt "Type new scorecard name (leave blank to reuse existing scorecard name)"
$destinationWorkspaceId = Read-Host -Prompt "Enter destination workspace id (leave blank for 'My workspace')"
if (!$destinationWorkspaceId) {
$destinationWorkspaceId = "me"
}
DuplicateScorecard -sourceScorecard $sourceScorecard -destinationWorkspaceId $destinationWorkspaceId -newScorecardName $newScorecardName
} elseif ($action -eq "e") {
$destinationScorecardId = Read-Host -Prompt "Enter destination scorecard id"
if ($destinationScorecardId) {
if ($destinationScorecardId -ne $sourceScorecard.id) {
CopyAllGoals -sourceScorecard $sourceScorecard -destinationScorecardId $destinationScorecardId
} else {
Write-Host -ForegroundColor Red "Source and destination scorecard must be different"
}
} else {
Write-Host -ForegroundColor Red "Not a valid scorecard id"
}
}
}
ShowPrompt
Be the first to comment