Examples/Create_Planner_Template.ps1

param (
        $TeamName  = 'Consultants'  ,
        $PlanName  = 'Team Planner' ,
        $ExcelPath = '.\Planner-Template.xlsx'
)

Import-Module msgraphAPI -Force
Import-Module ImportExcel -Force

$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 6 categories as column headins in a "Plan" sheet" but well to right; then put the other column headings in to the left 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 as wide as the bucket name column on the values sheet
Set-ExcelRange -Range $planSheet.Cells['C:D'] -Width 11 -NumberFormat 'Short Date'         #Start date and due date
Set-ExcelRange -Range $planSheet.Cells['F:F'] -Width $excelPackage.Values.Column(13).width #Make Assign to As weide as the email address column on the values sheet
Set-ExcelRange -Range $planSheet.Cells['G:H'] -Width 20 -WrapText                          #Check-list and description
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