Planner migration to SharePoint list
Author: Albert-Jan Schot
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
- PowerShell
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 --output text
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 entra 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 --title $field
}
$view = m365 spo list view set --webUrl $SiteUrl --listTitle $list.title --title $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 --title $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"
}
}