Migrate a Planner plan to a List using CLI for Microsoft365
I love that feeling of being part of a community! You get to learn something new every day. So not to long ago there was a 🧵 thread on twitter. Loryan Strant pointed out some of the things missing in Planner as compared to lists, and a follow up post on how to migrate to Planner 📦. As a CLI for Microsoft 365 fanatic I figured I could give it a thought and see if we could migrate a full plan easily using a sample script. What follows is the sample and how it is build.
What follows was a few days of playing around with the CLI commands available. Creating a bunch of new issues and improvements to get all stuff we need. Consulting with Twitter and friends to make sure we do the right things and finally a migration sample that results in the following:
How it was build
There are a few things you need from Planner: the plan, the buckets, and the tasks (including additional details). We had commands for those things, so the first step is to retrieve all data we require.
The second step was to figure out what we were storing and where. The SharePoint list does require columns where we can store our data. Fairly straight forward as well. I did struggle a bit with some of the field definitions and escaping of characters, but I got it working.
The last step is to move each planner task to our newly created list. Most of the data can be pushed over without any additional thoughts. But in my demo, I did want to push over the checklist items that were present. Most of the boards we use as a company use subtasks. I wanted to make sure we at least got those items. With some HTML skills I made sure each item was added to a new line.
Some values might be confusing
A few remarks when working with the Graph results, or the CLI for Microsoft 365 responses for that matter. The task results that are being returned provide you with a progress and priority field. The priority field is part of the beta response, something that is handled for you by the CLI for Microsoft 365. If you are using Graph requests yourself, make sure to hit the beta for results.
Both the progress and priority fields are of type integer. As a result I was slightly confused as there is no documentation around what the values correspond to. In the first iteration I choose to implement a switch that translates those values to nice looking labels. I just created four tasks with the different values to see what integers would map to what value:
$taskProgress = switch ($task.percentComplete) {
0 { "Not started" }
50 { "In progress" }
100 { "Completed"; }
default { "Not started" }
}
$taskPriority = switch ($task.priority) {
1 { "Urgent" }
3 { "Important" }
5 { "Medium" }
9 { "Low" }
default { "Medium" }
}
When working with the data it dawned on me that there was a reason to return integers; you can use those to order your list, so sorting from small to large you can order by priority and progress. Something that cannot be done using labels. So I resorted back to storing the integers in the SharePoint list and implement column formatting to apply nice looking colors and text labels. The formatting looks a bit complex due to the nice looking colors, but the translation of numbers to text itself is straight forward:
=if(@currentField == 0, 'Not started', if(@currentField == 50, 'In progress', if(@currentField == 100, 'Completed')))
If you are looking for more inspiration there are some awesome PnP Samples available.
The script
The script can be saved as a ps1 file and called as follows: .\cli-migrate-planner.ps1 -PlanGroupName "Sample" -SiteUrl https://contoso.sharepoint.com/sites/sample. It will create a new SharePoint list with the name of the plan and will copy over all tasks. It does assume that you have permissions to the Planner plan you are trying to move.
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 -eq "Logged Out") {
# Connection to Microsoft 365
m365 login
}
$migrationTasks = @()
$plan = m365 planner plan list --ownerGroupName $PlanGroupName | ConvertFrom-Json
if ($null -ne $plan) {
$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 details get --taskId $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 "Found $($plannerBuckets.length) buckets and $($plannerTasks.length) tasks to migrate"
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
}
}
}
Room for improvement
This is a first version of the sample, and there is obviously room for improvement. A few remarks of things I would love to implement in the near feature:
- Categories are currently not migrated. In order to get that implemented we need to implement a new command 🆘
- Comments made on tasks are not migrated either. In order to get that implemented we need another new command 🆘
- Attachments are not migrated; while we can retrieve those values with
m365 planner task details get📎 - The card view still shows the number values instead of a fancy lay-out 🖌️
- My tenant did not have a kanban board available as list view; as soon as that is present that should become the default view 🦾
So this does call for a V2 option soon 🚀, and we are always looking for new contributions for CLI for Microsoft 365; if you see anything that can be improved in the sample or in one of the commands let us know!