Examples/PlannerImportExport/Import-Planner-From-Xlsx.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
#requires -modules Microsoft.Graph.PlusPlus, importExcel
<#
.synopsis
    Import tasks from a .xlsx file into a teams plan.
#>

[Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseDeclaredVarsMoreThanAssignments', '', Justification="False positives when initializing variable in begin block")]
param (
    #File to import from
    $excelPath = '.\planner-Import.xlsx',
    #The team which owns the planner. The signed in user must be a member of the team. Being an owner but not a member will fail
    $TeamName  =  'Consultants'  ,
    #The name of the plan to import to
    $PlanName  =  'Team Planner'
)

Write-Progress -Activity 'Importing plan' -Status 'Getting information about the plan, its team, and the team members'
$teamplanner         = Get-GraphTeam  $TeamName -Plans | Where-Object title -eq $PlanName

#region ensure team members in the sheet are really in the team
#Get the members of the team and create two hash tables, one to get Mail from ID and one to get ID from mail
$existingteamMembers = Get-GraphTeam $TeamName -Members | Where-Object {$_.mail}
$existingteamMembers | ForEach-Object -Begin {$memberMailHash = @{}; $memberIDHash = @{} } -Process {
                                              $memberMailHash[$_.mail] = $_.id
                                              if ($_.id) {$memberIDHash[$_.id]  =  $_.mail  }
                       }

$importedTeamMembers = Import-Excel -Path $excelPath -WorksheetName values -StartColumn 12
#If any team members have no ID, and mail is not in the hash of existing users ...
# ...Look them up (assume for this demo mail = upn) and add them to the team.
$importedTeamMembers.Where({$mail -and -not $_.id -and -not $memberMailHash[$_.Mail]})  | ForEach-Object {
    Write-Progress -Activity 'Importing plan' -Status "Processing new team member '$($_.mail)'"
    $user = $null
    $user = Get-GraphUser -UserID $_.mail -ErrorAction SilentlyContinue
    if ($user) {
        $_.id = $user.id
        Add-GraphGroupMember -Group $TeamName -Member $user
    }
    else {Write-Warning "($_.mail) Doesn't Seem to be a valid user"}
}
#endregion

#region ensure the plan's 6 category labels match the ones in the sheet
#6 category lables are at I1:N1 in the Plan sheet; Import with no header so they will be P1..P6 as properties on an object. Make that into 6 objects with a name and value
Write-Progress -Activity 'Importing plan' -Status 'Checking categories'
$importedCategories  = (Import-Excel -path $excelPath -WorksheetName 'Plan' -NoHeader -StartColumn 10 -EndRow 1 -EndColumn 14).psobject.Properties | Sort-Object name
#Transform categories returned by the server into hash table of p1..P6 --> name; to compare with the imported ones
$existingCategories  = Get-GraphPlan $teamplanner -Details | Select-Object -ExpandProperty categorydescriptions
$existingCategories.psobject.Properties | ForEach-Object -Begin {$catHash = @{} } -Process {$catHash[($_.name -replace 'category','p')] = $_.value}

#for our 6 imported categories check them against the corresponding entry in catHash; if different pop in a hash table that can be splatted into Set-GraphplanDetails
$importedCategories.where({$catHash[($_.name)] -ne $_.value}) |
    ForEach-Object -Begin {$newCategories= @{} } -Process  { $newCategories[($_.name -replace 'p','Category')] = $_.value}
if ($newCategories.Count -gt 0)  {
    Write-Progress -Activity 'Importing plan' -Status 'Updating categories'
    Set-GraphPlanDetails $teamplanner @newCategories
}
#endregion

#region ensure buckets in the the sheet are in the plan
#Get buckets from the server and make a hash of name--> ID , and import the bucket list from the Values sheet
Write-Progress -Activity 'Importing plan' -Status 'Checking Buckets'
$existingBuckets     = Get-GraphPlan $teamplanner -buckets
$existingBuckets     | foreach-object -Begin {$bucketHash = @{}} -Process {$bucketHash[$_.Name] = $_.id}
$importedBuckets     = Import-Excel -path $excelPath   -WorksheetName values -StartColumn 1 -EndColumn 3

#NewBuckets here is new or changed buckets. We don't cope with bucket A being renamed, and then bucket B being changed to "A"
$newbuckets          = $importedBuckets.where({-not $bucketHash[$_.bucketName]})

#Buckets with an ID but no match in the hash table must have been reanmed, and those without an ID are new...
foreach ($bucket in $newbuckets.where({$_.id}) ) {
    Write-Progress -Activity 'Importing plan' -Status 'Renaming bucket to ' -CurrentOperation $bucket.bucketName
    Rename-GraphPlanBucket -Bucket $bucket.id -NewName $bucket.bucketName
    $bucketHash[$bucket.bucketName] = $bucket.id
}
foreach ($bucket in $newbuckets.where({-not $_.id}) ) {
    Write-Progress -Activity 'Importing plan' -Status 'Adding new bucket ' -CurrentOperation $bucket.bucketName
    $newbucket = New-GraphPlanBucket -Plan $teamPlanner -Name $bucket.bucketName
    $bucketHash[$newbucket.Name] = $newbucket.id
}
#endregion

Write-Progress -Activity 'Importing plan' -Status 'Checking tasks'
#region get existing tasks - fiddle the results so they look like what we export and we are going to import next.
$existingTasks       = Get-GraphPlan $teamplanner -FullTasks |
    Sort-Object -Property ID|
        Select-Object -Property @{n='Title'          ; e={   $_.title          }},
                                @{n='Bucket'         ; e={   $_.BucketName     }},
                                @{n='StartDate'      ; e={   [datetime]$_.StartDateTime  }},
                                @{n='DueDate'        ; e={   [datetime]$_.dueDatetime    }},
                                @{n='PercentComplete'; e={   $_.percentComplete }},
                                @{n='AssignTo'       ; e={  ($_.assignments.psobject.properties.name) -join "; "      }},
                                @{n="Checklist"      ; e={  ($_.checklist.psobject.Properties.value  | sort-object orderHint | Select-Object -expand title) -join "; "} },
                                @{n='Description'    ; e={   $_.description     }},
                                @{n="Links"          ; e={  ($_.references.psobject.Properties.name -replace "%2E","." -replace "%3A",":" -replace "%25","%") -join "; "}},
                                @{n="Category1"      ; e={if($_.appliedCategories.Category1) {'Yes'} else {$null}  } },
                                @{n="Category2"      ; e={if($_.appliedCategories.Category2) {'Yes'} else {$null}  } },
                                @{n="Category3"      ; e={if($_.appliedCategories.Category3) {'Yes'} else {$null}  } },
                                @{n="Category4"      ; e={if($_.appliedCategories.Category4) {'Yes'} else {$null}  } },
                                @{n="Category5"      ; e={if($_.appliedCategories.Category5) {'Yes'} else {$null}  } } ,
                                @{n="Category6"      ; e={if($_.appliedCategories.Category6) {'Yes'} else {$null}  } } ,
                                @{n="Task"           ; e={$_.id } }

Write-Progress -Activity 'Importing plan' -Completed # it isn't from the progress message point of view it is.
$existingTasks | foreach-object -Begin {$taskHash = @{}} -Process {$taskHash[$_.task] = $true }
#endregion
#
Import the tasks from the sheet. The Category names will be customized so use our own header names, so make them usable,  And pull other columns to match add-GraphPlanTask , Set-GraphPlanTask commands
$importedTasks = Import-Excel -Path $excelPath -WorksheetName 'Plan' -HeaderName Title, Bucket, StartDate, DueDate, PercentComplete, AssigneeMail, Checklist, Description, Links, Category1, Category2, Category3, Category4, Category5, Category6,Task  |
                    ForEach-Object {
                        if ($_.AssigneeMail) {Add-Member -InputObject $_ -MemberType NoteProperty -Name AssignTo -Value $memberMailHash[$_.AssigneeMail]}
                        else                 {Add-Member -InputObject $_ -MemberType NoteProperty -Name AssignTo -Value ""}
                        Add-Member -InputObject $_ -MemberType NoteProperty -Name CategoryNumbers -value $(foreach ($n in (1..6)) {if ($_."Category$n" -eq 'Yes') {$n} }) -PassThru
                    } | Sort-Object -Property Task

#If a task has no ID it is new, so add it. Check it has a title so if blank rows were imported, we don't try to process them
$importedTasks.Where({$_.title -and -not $_.task}) | Add-GraphPlanTask -Plan $teamplanner

#$existingTasks and Imported tasks can be compared - so compare them, show the results in Gridview to allow the user to see any which should not be modified
$propsToCompare= @('Task', 'Title', 'Bucket', 'StartDate', 'DueDate', 'PercentComplete', 'assignto', 'Checklist', 'Description', 'Links', 'Category1', 'Category2', 'Category3', 'Category4', 'Category5', 'Category6')
$comparison = Compare-Object  $existingTasks $importedTasks.Where({$_.Task -and $taskhash[$_.Task]}) -Property $propsToCompare | Sort-Object Task,sideindicator
$comparison | Select-Object  @{n='Side';e={if ($_.sideindicator -eq "<=") {'Existing'} else {'Import'}}}, 'Title', 'Bucket', 'StartDate', 'DueDate',
          'PercentComplete', @{n='Asignee';e={$memberIDHash[$_.assignto]}}, 'Checklist', 'Description', 'Links',
          'Category1', 'Category2', 'Category3', 'Category4', 'Category5', 'Category6' | Out-GridView -Title 'Review the changes - import may overwrite newer data on the server'

$Comparison.Where({$_.sideindicator -eq '=>'})  |  Set-GraphPlanTask -Confirm