Planner migration to SharePoint list
Summary
Use the CLI for Microsoft 365 to migrate an existing plan to a SharePoint Online List with this sample. You can specify the planner plan that you want to migrate and the script will generate a new list for you with the required fields and views. The current sample does migrate all tasks however it skips the following information:
- Categories
- Comments made on tasks
- Attachments
param (
[Parameter(Mandatory = $true, HelpMessage = "URL of the target site", Position = 0)]
[string]$SiteUrl,
[Parameter(Mandatory = $true, HelpMessage = "Groupname or Planner Plan name", Position = 1)]
[string]$PlanGroupName,
[Parameter(HelpMessage = "Show progress messages", Position = 2)]
[switch]$ShowProgress,
[Parameter(HelpMessage = "Skip List creation (for running multiple migrations without creating the list)", Position = 3)]
[switch]$SkipListCreation
)
$m365Status = m365 status
if ($m365Status -match "Logged Out") {
# Connection to Microsoft 365
m365 login
}
$plans = m365 planner plan list --ownerGroupName $PlanGroupName | ConvertFrom-Json
Write-Host "Found $($plans.length) plans to migrate"
foreach ($plan in $plans) {
$migrationTasks = @()
$plannerBuckets = m365 planner bucket list --planId $plan.id | ConvertFrom-Json
$plannerTasks = m365 planner task list --planId $plan.id | ConvertFrom-Json
foreach ($task in $plannerTasks) {
$taskDetails = m365 planner task get --id $task.id --query '{description: description, checklist: checklist.*.{isChecked: isChecked, title: title} }' | ConvertFrom-Json
$assignedUsers = @()
foreach ($userId in $($task.assignments | ForEach-Object { $($_).PSObject.Properties.Name })) {
$assignedUsers += "{'Key':'i:0#.f|membership|$(m365 aad user get --id $userId --query "userPrincipalName" | ConvertFrom-Json)'}"
}
$checklist = $null;
foreach ($item in $taskDetails.checklist) {
if ($item.isChecked) {
$checklist += "[x] " + $item.title + "<br>";
}
else {
$checklist += "[ ] " + $item.title + "<br>";
}
}
$migrationTasks += [pscustomobject][ordered]@{
Title = $task.title
Bucket = ($plannerBuckets | Where-Object { $_.id -eq $task.bucketId }).Name
Progress = $task.percentComplete
Priority = $task.priority
Description = ($task.hasDescription ? $taskDetails.description : ' ')
StartDate = $task.startDateTime
DueDate = $task.dueDateTime
Checklist = $checklist
AssignedTo = $($assignedUsers ? "[$($assignedUsers -join ",")]" : $null)
}
}
Write-Host "`nFound $($plannerBuckets.length) buckets and $($plannerTasks.length) tasks to migrate for the plan $($plan.title)"
if ($plannerTasks.length -gt 0) {
if ($false -eq $SkipListCreation) {
if ($ShowProgress) {
Write-Host "Setting up List"
}
$list = m365 spo list add --title $plan.title --baseTemplate GenericList --webUrl $SiteUrl | ConvertFrom-Json
$bucketOptions = $plannerBuckets.name -join "</CHOICE><CHOICE>"
$fieldXml = '<Field DisplayName=\"Bucket\" FillInChoice=\"FALSE\" Format=\"Dropdown\" IsModern=\"TRUE\" Name=\"Bucket\" Title=\"Bucket\" Type=\"Choice\" ID=\"{e5a87c1b-14fe-4a0d-b2a1-69ed51aefe0a}\" SourceID=\"{be343550-aa79-4954-95f7-e7f3e1158888}\" StaticName=\"Bucket\" ColName=\"nvarchar7\" RowOrdinal=\"0\" Version=\"5\"><CHOICES><CHOICE>' + $bucketOptions + '</CHOICE></CHOICES></Field>';
$field = m365 spo field add --webUrl $SiteUrl --listTitle $list.title --xml $fieldXml --options AddToAllContentTypes
$field = m365 spo field add --webUrl $SiteUrl --listTitle $list.title --xml '<Field DisplayName=\"Assigned to\" Format=\"Dropdown\" IsModern=\"TRUE\" List=\"UserInfo\" Mult=\"TRUE\" Name=\"AssignedTo\" Title=\"AssignedTo\" Type=\"UserMulti\" UserSelectionMode=\"0\" UserSelectionScope=\"0\" ID=\"{38a2a5a8-5518-4242-9b9b-760777f5e7ea}\" SourceID=\"{50756447-36ab-447e-9b30-859b91aba49d}\" StaticName=\"AssignedTo\" />' --options AddToAllContentTypes
$field = m365 spo field add --webUrl $SiteUrl --listTitle $list.title --xml '<Field DisplayName=\"Start date\" FriendlyDisplayFormat=\"Disabled\" Format=\"DateOnly\" IsModern=\"TRUE\" Name=\"Startdate\" Title=\"Start date\" Type=\"DateTime\" ID=\"{91ff7e76-118b-49e1-85dd-afda84856e96}\" SourceID=\"{50756447-36ab-447e-9b30-859b91aba49d}\" StaticName=\"Startdate\" ColName=\"datetime1\" RowOrdinal=\"0\" />' --options AddToAllContentTypes
$field = m365 spo field add --webUrl $SiteUrl --listTitle $list.title --xml '<Field DisplayName=\"Due date\" FriendlyDisplayFormat=\"Disabled\" Format=\"DateOnly\" IsModern=\"TRUE\" Name=\"Duedate\" Title=\"Due date\" Type=\"DateTime\" ID=\"{23f7955a-1b1b-46f9-8b0f-316dbb37b63e}\" SourceID=\"{50756447-36ab-447e-9b30-859b91aba49d}\" StaticName=\"Duedate\" ColName=\"datetime2\" RowOrdinal=\"0\" />' --options AddToAllContentTypes
$field = m365 spo field add --webUrl $SiteUrl --listTitle $list.title --xml '<Field AppendOnly=\"FALSE\" DisplayName=\"Checklist\" Format=\"Dropdown\" IsModern=\"TRUE\" IsolateStyles=\"TRUE\" Name=\"Checklist\" RichText=\"TRUE\" RichTextMode=\"FullHtml\" Title=\"Checklist\" Type=\"Note\" ID=\"{05f7b70d-eb7c-4428-afe6-ca9f2f22c5af}\" SourceID=\"{50756447-36ab-447e-9b30-859b91aba49d}\" StaticName=\"Checklist\" ColName=\"ntext2\" RowOrdinal=\"0\" CustomFormatter=\"\" Required=\"FALSE\" EnforceUniqueValues=\"FALSE\" Indexed=\"FALSE\" NumLines=\"6\" RestrictedMode=\"TRUE\" Version=\"1\" />' --options AddToAllContentTypes
$field = m365 spo field add --webUrl $SiteUrl --listTitle $list.title --xml '<Field AppendOnly=\"FALSE\" DisplayName=\"Description\" Format=\"Dropdown\" IsModern=\"TRUE\" IsolateStyles=\"FALSE\" Name=\"Description\" RichText=\"FALSE\" RichTextMode=\"Compatible\" Title=\"Description\" Type=\"Note\" ID=\"{50d7e60e-ce7d-4428-afe6-ca9f2f22c5ce}\" SourceID=\"{50756447-36ab-447e-9b30-859b91aba49d}\" StaticName=\"Description\" ColName=\"ntext2\" RowOrdinal=\"0\" />' --options AddToAllContentTypes
$field = m365 spo field add --webUrl $SiteUrl --listTitle $list.title --xml '<Field Type=\"Number\" DisplayName=\"Priority\" Required=\"FALSE\" EnforceUniqueValues=\"FALSE\" Indexed=\"FALSE\" ID=\"{a9639335-a3cf-41d2-a1fb-28d02c8ef09f}\" SourceID=\"{be343550-aa79-4954-95f7-e7f3e1158888}\" StaticName=\"Priority\" Name=\"Priority\" ColName=\"float1\" RowOrdinal=\"0\" CustomFormatter=\"{"$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json","elmType":"div","style":{"flex-wrap":"wrap","display":"flex"},"children":[{"elmType":"div","style":{"box-sizing":"border-box","padding":"4px 8px 5px 8px","display":"flex","border-radius":"16px","height":"24px","align-items":"center","white-space":"nowrap","overflow":"hidden","margin":"4px 4px 4px 4px"},"attributes":{"class":{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",1]},"sp-css-backgroundColor-BgRed sp-css-borderColor-WhiteFont",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",3]},"sp-css-backgroundColor-BgPeach sp-css-borderColor-PeachFont",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",5]},"sp-css-backgroundColor-BgGold",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",9]},"",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",9]},"sp-css-backgroundColor-BgMintGreen sp-css-borderColor-MintGreenFont","sp-field-borderAllRegular sp-field-borderAllSolid sp-css-borderColor-neutralSecondary"]}]}]}]}]}},"children":[{"elmType":"span","style":{"line-height":"16px","height":"14px"},"attributes":{"iconName":{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",1]},"RingerSolid",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",3]},"Important",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",5]},"LocationDot",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",""]},"Down",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",9]},"",""]}]}]}]}]},"class":{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",1]},"sp-css-color-WhiteFont",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",3]},"sp-css-color-PeachFont",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",5]},"",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",""]},"",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",9]},"sp-css-color-MintGreenFont",""]}]}]}]}]}}},{"elmType":"span","style":{"overflow":"hidden","text-overflow":"ellipsis","padding":"0 3px"},"txtContent":"=if(@currentField == 1, ''Urgent'', if(@currentField == 3, ''Important'', if(@currentField == 5, ''Medium'', if(@currentField == 9, '''', ''Low''))))","attributes":{"class":{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",1]},"sp-css-color-WhiteFont",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",3]},"sp-css-color-PeachFont",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",5]},"sp-css-color-GoldFont",{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",0]},9,{"operator":":","operands":[{"operator":"==","operands":["[$Priority]",9]},"sp-css-color-MintGreenFont",""]}]}]}]}]}}}]}]}\" Version=\"23\" />' --options AddToAllContentTypes
$field = m365 spo field add --webUrl $SiteUrl --listTitle $list.title --xml '<Field CommaSeparator=\"TRUE\" CustomUnitOnRight=\"TRUE\" DisplayName=\"Progress\" Format=\"Dropdown\" IsModern=\"TRUE\" Name=\"Progress\" Percentage=\"FALSE\" Title=\"Progress\" Type=\"Number\" Unit=\"None\" ID=\"{abea0d9d-83b8-4d57-9c68-8b56ad4066c9}\" SourceID=\"{be343550-aa79-4954-95f7-e7f3e1158888}\" StaticName=\"Progress\" ColName=\"float2\" RowOrdinal=\"0\" CustomFormatter=\"{"$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json","elmType":"div","style":{"flex-wrap":"wrap","display":"flex"},"children":[{"elmType":"div","style":{"box-sizing":"border-box","padding":"4px 8px 5px 8px","display":"flex","border-radius":"16px","height":"24px","align-items":"center","white-space":"nowrap","overflow":"hidden","margin":"4px 4px 4px 4px"},"attributes":{"class":{"operator":":","operands":[{"operator":"==","operands":["@currentField",0]},"sp-css-backgroundColor-BgLightGray sp-css-borderColor-LightGrayFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",50]},"sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",100]},"sp-css-backgroundColor-BgGreen sp-css-borderColor-WhiteFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",""]},"","sp-field-borderAllRegular sp-field-borderAllSolid sp-css-borderColor-neutralSecondary"]}]}]}]}},"children":[{"elmType":"span","style":{"line-height":"16px","height":"14px"},"attributes":{"iconName":{"operator":":","operands":[{"operator":"==","operands":["@currentField",0]},"CircleRing",{"operator":":","operands":[{"operator":"==","operands":["@currentField",50]},"CircleHalfFull",{"operator":":","operands":[{"operator":"==","operands":["@currentField",100]},"CircleFill",{"operator":":","operands":[{"operator":"==","operands":["@currentField",""]},"",""]}]}]}]},"class":{"operator":":","operands":[{"operator":"==","operands":["@currentField",0]},"sp-css-color-LightGrayFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",50]},"sp-css-color-GoldFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",100]},"sp-css-color-WhiteFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",""]},"",""]}]}]}]}}},{"elmType":"span","style":{"overflow":"hidden","text-overflow":"ellipsis","padding":"0 3px"},"txtContent":"=if(@currentField == 100, ''Completed'', if(@currentField == 50, ''Completed'', if(@currentField == 0, ''Not started'', '''')))","attributes":{"class":{"operator":":","operands":[{"operator":"==","operands":["@currentField",0]},"sp-field-fontSizeSmall sp-css-color-LightGrayFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",50]},"sp-field-fontSizeSmall sp-css-color-GoldFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",100]},"sp-field-fontSizeSmall sp-css-color-WhiteFont",{"operator":":","operands":[{"operator":"==","operands":["@currentField",""]},"",""]}]}]}]}}}]}]}\" Version=\"2\" />' --options AddToAllContentTypes
$viewName = "All Items"
$viewFields = @("Progress", "Priority", "Assigned_x0020_to", "Due date", "Start date");
foreach ($field in $viewFields) {
m365 spo list view field add --webUrl $SiteUrl --listTitle $list.title --viewTitle $viewName --fieldTitle $field
}
$view = m365 spo list view set --webUrl $SiteUrl --listTitle $list.title --viewTitle $viewName --ViewQuery '<GroupBy Collapse=\"TRUE\" GroupLimit=\"30\"><FieldRef Name=\"Bucket\" /></GroupBy><OrderBy><FieldRef Name=\"ID\" /></OrderBy>'
$view = m365 spo list view set --webUrl $SiteUrl --listTitle $list.title --viewTitle $viewName --ViewType2 "TILES"
}
Write-Host "Migrating tasks"
foreach ($migrationTask in $migrationTasks) {
$i++
if ($ShowProgress) { Write-Host "Processing ($i/$($migrationTasks.length))" }
$newItem = m365 spo listitem add --webUrl $SiteUrl --listTitle $plan.title --Title $migrationTask.Title --Bucket $migrationTask.Bucket --Progress $migrationTask.Progress --Description $migrationTask.Description --Assigned_x0020_to $migrationTask.AssignedTo --Priority $migrationTask.Priority | ConvertFrom-Json -AsHashtable
# Fails with an empty date so extra check to prevent issues
if ($migrationTask.StartDate) {
$updatedItem = m365 spo listitem set --webUrl $SiteUrl --listTitle $plan.title --id $newItem.Id --Start_x0020_date $migrationTask.StartDate
}
if ($migrationTask.DueDate) {
$updatedItem = m365 spo listitem set --webUrl $SiteUrl --listTitle $plan.title --id $newItem.Id --Due_x0020_date $migrationTask.DueDate
}
if ($migrationTask.Checklist) {
$updatedItem = m365 spo listitem set --webUrl $SiteUrl --listTitle $plan.title --id $newItem.Id --Checklist $migrationTask.Checklist
}
}
Write-Host "Migrating finished for the plan $($plan.title)"
} else {
Write-Host "Skipping migrating due to lack of tasks"
}
}
Check out the CLI for Microsoft 365 to learn more at: https://aka.ms/cli-m365
Important changes coming to the way you login into CLI for Microsoft 365 (effective 9th September 2024) see Changes in PnP Management Shell registration in Microsoft 365
Source Credit
Sample first appeared on Planner migration to SharePoint list | CLI for Microsoft 365
Contributors
| Author(s) |
|---|
| Albert-Jan Schot |
| Reshmee Auckloo |
| Jasey Waegebaert |
Disclaimer
THESE SAMPLES ARE PROVIDED AS IS WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.