Examples/PlannerImportExport/Create_Planner_Template.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
#requires -modules Microsoft.Graph.PlusPlus, importExcel
<#
.synopsis
    Use an existing team's planner to create a .xlsx file as a template for tasks to import
#>

param (
        #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 base the template on
        $PlanName  = 'Team Planner' ,
        #Path for the Excel file to create as the template.
        $ExcelPath = '.\Planner-Template.xlsx'
)

$myteam       = Get-GraphUser -Teams         | Where-Object -Property title -eq  $teamName # assumes user is 'me'
$teamplanner  = Get-GraphTeam $myteam -Plans | Where-Object -Property title -eq  $PlanName # my team's planner named above

#region export Plan buckets and team members to a "Values" sheet in the workbook, and insert categories as column headings in a "Plan" sheet" but well to right; then put the other column headings in to the left of them
$excelPackage = Get-GraphPlan -Plan $teamplanner -Buckets |
                    Select-Object @{n="BucketName"; e={$_.name}},PlanTitle,ID |
                         Export-Excel -Path $excelPath -WorksheetName Values -ClearSheet -BoldTopRow -AutoSize -PassThru

$excelPackage = Get-GraphTeam $myteam -Members |
                    Select-Object @{n='User';e={$_.displayName}},Jobtitle,mail,ID |
                        Export-Excel -ExcelPackage $excelPackage  -worksheetname Values -StartColumn 12 -BoldTopRow -AutoSize -PassThru

#Hide IDs: we can spot new team members if they don't have an ID. and if a bucket is renamed in the spreadsheet, we can update it if we have the ID
Set-Excelrange -Range $excelPackage.Workbook.Worksheets['Values'].Column(15) -Hidden
Set-Excelrange -Range $excelPackage.Workbook.Worksheets['Values'].Column(3) -Hidden

#Now export the catgegories - create a new worksheet named 'plan' and put them on the right in the top row.
$excelPackage = Get-GraphPlan  $teamplanner -Details |
    Select-Object -ExpandProperty categorydescriptions |
        Export-Excel -ExcelPackage $excelPackage -WorksheetName  Plan -ClearSheet -StartColumn 10 -NoHeader -BoldTopRow -FreezeTopRowFirstColumn -Activate -PassThru

#put the fixed column names in on the left of the top row in the 'plan' sheet
$planSheet = $excelPackage.Workbook.Worksheets['Plan']
$col = 1 ;
'Task Title' , 'Bucket' , 'Start Date', 'Due Date', '% Complete',  'Assign To', 'Check list', 'Description' ,'Links' | ForEach-Object {
    $Address = [OfficeOpenXml.ExcelAddress]::new(1,$col,1,$col).address
    $PlanSheet.Cells[$address].Value = $_
    $col ++
}
#endregion

#region set column widths, number formats and data validation rules on the "plan" sheet
Set-ExcelRange -WorkSheet $PlanSheet -Range '1:1' -Bold
$PlanSheet.Cells.AutoFitColumns()
Set-ExcelRange -Range $planSheet.Cells['A:A'] -Width 35                                    #Title
Set-ExcelRange -Range $planSheet.Cells['B:B'] -Width $excelPackage.Values.Column(1).width  #Make Bucket column as wide as the bucket-name column on the values sheet
Set-ExcelRange -Range $planSheet.Cells['C:D'] -Width 11 -NumberFormat 'Short Date'         #Format Start-date and Due-date columns as dates
Set-ExcelRange -Range $planSheet.Cells['F:F'] -Width $excelPackage.Values.Column(13).width #Make Assign-To column as wide as the email-address column on the values sheet
Set-ExcelRange -Range $planSheet.Cells['G:H'] -Width 20 -WrapText                          #Check-list and Description columns
Set-ExcelRange -Range $planSheet.Cells['I:I'] -Width 35 -WrapText                          #Links - tried setting a smaller font but excel applies its own hyperlink style when you add one.
Set-ExcelRange -Range $planSheet.cells -VerticalAlignment Center
$params = @{'ShowErrorMessage'=$true; 'ErrorStyle'='stop'; 'ErrorTitle'='Invalid Data'; 'worksheet'=$planSheet }
Add-ExcelDataValidationRule @params -Range 'B2:B1001' -ValidationType List    -Formula 'values!$a$2:$a$1000'         -ErrorBody "You must select an item from the list.`r`nYou can add to the list on the values page" #Bucket
Add-ExcelDataValidationRule @params -Range 'F2:F1001' -ValidationType List    -Formula 'values!$M$2:$M$1000'         -ErrorBody 'You must select an item from the list'               # Assign to
Add-ExcelDataValidationRule @params -Range 'J2:O1001' -ValidationType List    -ValueSet @('yes','YES','Yes')         -ErrorBody "Enter Yes or leave blank for no"                     # Categories
Add-ExcelDataValidationRule @params -Range 'E2:E1001' -ValidationType Integer -Operator between -Value 0 -Value2 100 -ErrorBody 'Percentage must be a whole number between 0 and 100' # Pecent complete
#endregion
Close-ExcelPackage $excelPackage