Month: March 2019

Microsoft Planner: Considerations for Reporting-Part 3

Posted on by — No Comments ↓


First published on MSDN on Aug 16, 2017

For the third and final part of this short series on reporting against Planner data I will be taking some of the ideas from

Part 1

and

Part 2

and putting more process around them.  This is still really just ideas to make you think about your use of Planner – and not a baked solution.  I don’t for example handle the messy stuff of keeping everything in sync – and take an approach of loading a snapshot each time.  Also the tools I’m using may not be the best ones for you – but hopefully highlight the open nature of Graph and also showcase the parts that Flow and many of the Azure workloads can play in an overall solution.

This is one of the outcomes of the blog – just to keep you reading…  A Power BI report of the status of Planner tasks – for a specific set of Plans.

For the session at our internal Ready conference that sparked this series I used Python for some of the Graph reading – based on this sample –

https://github.com/microsoftgraph/python3-connect-rest-sample

which was a web app using

Flask

the that started the authentication and then I just made the necessary changes to the requested permission scopes then made the various Graph calls and manipulated the response.  I finally used the

Azure DocumentDB Data Migration tool

to push the data into Azure Cosmos DB.  It felt a little clunky.

Since then I’ve re-worked things and moved back from Python to PowerShell (mainly due to my competencies – still learning Python – come to that still learning PowerShell!) and combined Flow and Azure Functions together to move the data via REST calls into Azure Cosmos DB.

So to start – I have an Azure Cosmos DB provisioned and a database created called Planner.  I decided to store data in various collections – matching up to the data elements in Planner – with the addition of an extra one for Assignments – pulling this outside the Task for easier reporting.  I could have saved all the different json into just one collection as CosmosDB is a NoSQL store – but again for reporting and hitting it with PowerBI the collections made it easier.  In my testing I was re-loading the data many times – so I made things easier by using the Cloud Shell and a script to delete and create the collections (first time I’d used vi for a while…).

The next part was the Azure Functions – which read all the Planner data using the Graph API and then write the data to CosmosDB.  Initially I did this in one function, but even with just 30 or so plans I was getting close to the function time-out of 5 minutes so changed it so that the initial function reads most of the plan level data and then makes another function call for each of the plans to get the task data.  This could probably be improved to pass over the token information too – avoiding some re-authentication in the 2nd function.

You

pay for Azure functions

based on the time they are running and the resources they consume – and storage while they are not running.  I’ve yet to hit $0.05 on my usage during my testing for the function costs.

The first function has some setup stuff for the CosmosDB REST calls, and the authentication key is created based on the verb, resource date and the master key – so these are needed for each collection.  There is also some setup for the Planner Graph calls (including keys/accounts/passwords as this runs from the function) and then imports some modules for the Graph auth.  These modules need to be loaded into the function – which is carried out using the right hand ‘View files’ window.

I’ll add the full script at the foot of the blog – just to not break the flow of this document too much and just walk through some key parts.  In earlier version I had used the me/Planner/Plans Graph call to get the Plans, but there are some challenges with that call in that it only gets the subscribed plans – so I switched in this example and used the v1.0/Groups call (as the account I had set had access) and then used the groups/<GroupId>/planner/plans call.  This has the additional advantage that it does get the potentially multiple plans per Group that can be created via Microsoft Teams – hence my use of a ForEach when reading the plans from the Group.

One other gotcha I hit when running from the function compared to just running in PowerShell on my desktop was seeing some ‘bad json’ messages.  Adding a –Compress to the ‘ConvertTo-Json appeared to resolve these, and I suspect either trailing spaces or ‘\’ may have been the root cause – exposed by a different PowerShell version.  I also wrapped the planner loop in a try/catch – as some groups may not have plans.

After looping around the plans, plan details and members, and writing each of the sets of data off to CosmosDB using a REST call like in the following screenshot I made the call to the second function – shown in the subsequent screenshot.  You can also see in the PlanDetails section that this is where I am filtering the Plans and only pushing all the remaining data to CosmosDB if the 1st category is set to Marketing.  This is a pretty rough way to filter – but it does the job in my case as all the plans I am interested in were created from a template where category 1 was marketing.

And here is the call to the subsequent function.  I am passing in the Plan Id, and using Start-Job so this is an asynchronous call so I’m not waiting on a response.  This also necessitates the use of $using so that the async call has access to the local variables.  You probably want to add some kind of error handling here – or just keep an eye on function failures with an alert.  Notice the –UseBasicParsing too – as this is required since the call is being made in the function and the Internet Explorer engine is not available:

The second function called above is very similar once we get past the initial code that gets the Plan ID from the request.

I proceed with the REST and Planner auth stuff after that (which perhaps could be passed in the request and save some time/money), and then one added complexity was pulling out the assignments into their own collection – so it is easier to look at assignments across plans.

In my case I’ve just executed the first function manually for now, but it would make sense to have this on a schedule – and you could also tie in the deletion/creation of collections to save on the costs between reporting cycles.  If I hadn’t wanted to learn more about CosmosDB it would probably make more sense to push this data into SQL Azure – but the very cool multi geo replication possibilities with CosmosDB are certainly worth a look.  I did start spending a bit more of my Azure allocation than I’d expected after creating collections with high throughput capabilities – and writing my data to Europe which then replicated to the US and Brazil.  Lesson learned.

Monitoring the functions through the Azure Portal I can see that my current run of the initial function was successful and took just over 2 minutes.  I left some of my earlier failures in there too – the debug capabilities in Functions are pretty good!

This of course was then calling out to the other function – so monitoring that one I also see success (just showing the last few here) and they were taking around 15 to 20 seconds a time.

Here is an example of one record – part of a task – and as you can see this is stored in json format,

As I get this far I’m thinking that a Part 4 of my trilogy will be needed to dig deeper into using Power BI directly against the CosmosDB – as for now I wanted to concentrate on another Flow.  I have a scheduled Flow that runs every day and makes several calls to query documents in my CosmosDB and then creates and then updates an item in a SharePoint list.

The first Query documents looks like this – it is going to my Planner CosmosDB and mya Tasks collection and executing the query

SELECT Count(c.id) from c WHERE c.percentComplete=0

And you can test out this kind of query in the Azure portal agains the CosmosDB using the query explorer, and my answer currently is 73 tasks that have percentComplete of 0 – which means not started.:

The next step of the Flow is creating an item in SharePoint – and the step looks like the following, where I set the site address and the list name, then write a title (which I don’t actually use) and set the date to now and use an expression to write out my query result of 73.

The expression to get the value from my query was the tricky part for me – but finally this worked:

int(body(‘Query_documents’)?[‘Documents’]?[0]?[‘$1’])

The remaining steps are just a repeat of the query for 50% (In Progress) and 100% (Completed) and updating the task (using the ID) with the appropriate value.

Looking at my Flow run history I can see that I had a few failures – these were days when my collections didn’t exist.  The two recent successes where just after I added my collections while writing this blog (result = 0 for all fields! – so I deleted this row in SharePoint) and the most recent successful one where it added my correct values.

In SharePoint my list looks like this:

I must admit to cheating here and adding a bit of history manually – so that the PowerBI reports would look good – but after pointing Power BI at my SharePoint list, removing columns I wasn’t interested in and ensuring that the remaining data was being seen as a Date field and 3 integers – I could finally get to the report I showed at the top of the blog – but now with the additional values from today (I’d added a plan and updated a few progress values earlier):

I’ll do an addendum or Part 4 of the trilogy to look at using the Power BI CosmosDB direct reporting options – but hopefully this has given you some ideas.  I’ve probably skipped over some important point or other – let me know if anything needs some better explanation.

The PowerShell scripts used in the two functions follow – with some details edited where you will need to add your own accounts, keys and Urls etc.  These are supplied as-is – no warranties.  Bware of “ being turned curly and new lines where there shouldn’t be new lines.


The Initial Function

#Setup stuff for the DocDB REST calls

[System.Reflection.Assembly]::LoadWithPartialName(“System.Web”) | out-null

$global:resourceType=”docs”

$verb=”POST”

$date = Get-Date

$utcDate = $date.ToUniversalTime()

$global:xDate = $utcDate.ToString(‘r’,[System.Globalization.CultureInfo]::InvariantCulture)

$global:masterKey = “<Your master key from CosmosDB goes here>”

# generate signature for REST call

function fnGetKey ($pVerb, $pResourceId, $pResourceType, $date, $masterKey)

{

$keyBytes = [System.Convert]::FromBase64String($masterKey)

$sigCleartext = @($pVerb.ToLower() + “`n” + $pResourceType.ToLower() + “`n” + $pResourceId + “`n” + $date.ToString().ToLower() + “`n” + “” + “`n”)

$bytesSigClear =[Text.Encoding]::UTF8.GetBytes($sigCleartext)

$hmacsha = new-object -TypeName System.Security.Cryptography.HMACSHA256 -ArgumentList (,$keyBytes)

$hash = $hmacsha.ComputeHash($bytesSigClear)

$signature = [System.Convert]::ToBase64String($hash)

$key = $(‘type=master&ver=1.0&sig=’ + $signature)

$key  = [System.Web.HttpUtility]::UrlEncode($(‘type=master&ver=1.0&sig=’ + $signature)) # needs Snapin System.web!

return $key

}

#Setup stuff for the Planner Calls

$clientId = ‘<A registered client ID in Azure with the right access levels>’

$aadtenant = “<yourtenant>.onmicrosoft.com”

$username = “<yourname>@<yourtenant>.onmicrosoft.com”

$password = “<your password>” | ConvertTo-SecureString -AsPlainText -Force

$Credential = New-Object -typename System.Management.Automation.PSCredential -argumentlist $username, $password

$Modulebase = (get-Module MicrosoftGraphAPI).ModuleBase

Import-Module “D:\home\site\wwwroot\HttpTriggerPlannerToDocDB\Microsoft.IdentityModel.Clients.ActiveDirectory.dll”

Import-Module “D:\home\site\wwwroot\HttpTriggerPlannerToDocDB\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll”

$resourceAppIdURI = “

https://graph.microsoft.com”

$authority = “

https://login.windows.net/

$aadTenant”

$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $authority

$uc = new-object Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential -ArgumentList $Credential.Username,$Credential.Password

$token = $authContext.AcquireToken($resourceAppIdURI, $clientId,$uc)

# Blog Client ID – my Application ID from Azure Oops – dupe line…

$clientId = ‘<A registered client ID in Azure with the right access levels>’

#Get DocDB REST authkeys

$global:resourceId= “dbs/Planner/colls/Plans”

$planAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

$global:resourceId= “dbs/Planner/colls/PlanDetails”

$planDetailsAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

$global:resourceId= “dbs/Planner/colls/Members”

$planMembersAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

$global:resourceId= “dbs/Planner/colls/Buckets”

$planBucketsAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

$global:resourceId= “dbs/Planner/colls/Tasks”

$planTasksAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

$global:resourceId= “dbs/Planner/colls/TaskDetails”

$planTaskDetailsAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

$global:resourceId= “dbs/Planner/colls/Assignments”

$planAssignmentsAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

#################################################

#Get Plans (V2 – from Groups)

#################################################

$headers = @{}

$headers.Add(‘Authorization’,’Bearer ‘ + $Token.AccessToken)

$headers.Add(‘Content-Type’, “application/json”)

#$plans = Invoke-WebRequest -Uri ”

https://graph.microsoft.com/v1.0/me/planner/plans”

-Method Get -Headers $headers -UseBasicParsing

#$plansContent = $plans.Content | ConvertFrom-Json

#$planValue = $plansContent.Value

#First get Groups

$groups = Invoke-WebRequest -Uri ”

https://graph.microsoft.com/v1.0/Groups”

-Method Get -Headers $headers -UseBasicParsing

$groupsContent = $groups.Content | ConvertFrom-Json

$groupValue = $groupsContent.Value

ForEach($group in $groupValue){

try {

$uri = ”

https://graph.microsoft.com/v1.0/groups/”

+ $group.id + “/planner/plans”

$plans = Invoke-WebRequest -Uri $uri -Method Get -Headers $headers -UseBasicParsing

$plansContent = $plans.Content | ConvertFrom-Json

$planValue = $plansContent.Value

ForEach($plan in $planValue){

$docdbHeader=@{“Authorization” = “$planAuthKey”; `

“x-ms-version” = “2015-12-16”; `

“x-ms-date” = “$xdate”; `

“x-ms-indexing-directive” = “Include”; `

“x-ms-documentdb-is-upsert” = “True”; `

“Content-Length” = “0”

}

$jsonBody = $plan | ConvertTo-Json -Compress

$docdbUri = “https://<YourCosmosDBName>.documents.azure.com/dbs/Planner/colls/Plans/docs”

Invoke-RestMethod -Uri $docdbUri -Headers $docdbHeader -Method $verb -ContentType “application/json” -Body $jsonBody

#################################################

#Get Plan Details

#################################################

$groupId = $plan.owner

$uri = ”

https://graph.microsoft.com/v1.0/planner/plans/”

+ $plan.id + “/details”

$planDetails = Invoke-WebRequest -Uri $uri -Method Get -Headers $headers -UseBasicParsing

$planDetailsContent = $planDetails.Content | ConvertFrom-Json

# Only continue with this plan if the category1 matches our target paln type.

if($planDetailsContent.categoryDescriptions.category1 -eq “Marketing”){

$docdbHeader=@{“Authorization” = “$planDetailsAuthKey”; `

“x-ms-version” = “2015-12-16”; `

“x-ms-date” = “$xdate”; `

“x-ms-indexing-directive” = “Include”; `

“x-ms-documentdb-is-upsert” = “True”; `

“Content-Length” = “0”

}

$jsonBody = $planDetailsContent | ConvertTo-Json -Compress

$docdbUri = “https://<yourCosmosDBName>.documents.azure.com/dbs/Planner/colls/PlanDetails/docs”

Invoke-RestMethod -Uri $docdbUri -Headers $docdbHeader -Method $verb -ContentType “application/json” -Body $jsonBody

#GroupMember

$uri = ”

https://graph.microsoft.com/v1.0/groups/”

+ $groupId + “/members”

$members = Invoke-WebRequest -Uri $uri -Method Get -Headers $headers -UseBasicParsing

$membersContent = $members.Content | ConvertFrom-Json

$membersContent | Add-Member id $plan.id

$membersValue = $membersContent.value

$docdbHeader=@{“Authorization” = “$planMembersAuthKey”; `

“x-ms-version” = “2015-12-16”; `

“x-ms-date” = “$xdate”; `

“x-ms-indexing-directive” = “Include”;

“x-ms-documentdb-is-upsert” = “True”; `

“Content-Length” = “0”

}

ForEach($member in $membersValue){

$jsonBody = $member | ConvertTo-Json -Compress

$docdbUri = “https://<yourCosmosDBName>.documents.azure.com/dbs/Planner/colls/Members/docs”

Invoke-RestMethod -Uri $docdbUri -Headers $docdbHeader -Method $verb -ContentType “application/json” -Body $jsonBody

}

#Buckets

$uri = ”

https://graph.microsoft.com/v1.0/planner/plans/”

+ $plan.id + “/buckets”

$buckets = Invoke-WebRequest -Uri $uri -Method Get -Headers $headers -UseBasicParsing

$bucketsContent = $buckets.Content | ConvertFrom-Json

$bucketsContent | Add-Member id $plan.id

$bucketsValue = $bucketsContent.value

$docdbHeader=@{“Authorization” = “$planBucketsAuthKey”; `

“x-ms-version” = “2015-12-16”; `

“x-ms-date” = “$xdate”; `

“x-ms-indexing-directive” = “Include”;

“x-ms-documentdb-is-upsert” = “True”; `

“Content-Length” = “0”

}

ForEach($bucket in $bucketsValue){

$jsonBody = $bucket | ConvertTo-Json -Compress

$docdbUri = “https://<yourCosmosDBName>.documents.azure.com/dbs/Planner/colls/Buckets/docs”

Invoke-RestMethod -Uri $docdbUri -Headers $docdbHeader -Method $verb -ContentType “application/json” -Body $jsonBody

}

#Tasks – call out to separate function

$planId = $plan.id

$taskBody=@{“planId” = “$planId”}

$body=$taskBody | ConvertTo-Json -Compress

$taskHeaders = @{}

$taskHeaders.Add(‘Content-Type’, “application/json”)

$uri = “https://<yourfunctionendpoint>.azurewebsites.net/api/<YourfunctionName>?code=<from your function Url>”

Start-Job {Invoke-WebRequest -Uri $using:uri -Method Post -Headers $using:taskHeaders -Body $using:body -UseBasicParsing}

}

}

}

catch [Net.WebException] {}

}


The 2nd Function – called from the last part of the one above:

# POST method: $req

$requestBody = Get-Content $req -Raw | ConvertFrom-Json

$planId = $requestBody.planId

# GET method: each querystring parameter is its own variable

if ($req_query_name)

{

$planId = $req_query_name

}

#Setup stuff for the DocDB REST calls

[System.Reflection.Assembly]::LoadWithPartialName(“System.Web”) | out-null

$global:resourceType=”docs”

$verb=”POST”

$date = Get-Date

$utcDate = $date.ToUniversalTime()

$global:xDate = $utcDate.ToString(‘r’,[System.Globalization.CultureInfo]::InvariantCulture)

$global:masterKey = “<YourCosmosDB master key>”

# generate signature for REST call

function fnGetKey ($pVerb, $pResourceId, $pResourceType, $date, $masterKey)

{

$keyBytes = [System.Convert]::FromBase64String($masterKey)

$sigCleartext = @($pVerb.ToLower() + “`n” + $pResourceType.ToLower() + “`n” + $pResourceId + “`n” + $date.ToString().ToLower() + “`n” + “” + “`n”)

$bytesSigClear =[Text.Encoding]::UTF8.GetBytes($sigCleartext)

$hmacsha = new-object -TypeName System.Security.Cryptography.HMACSHA256 -ArgumentList (,$keyBytes)

$hash = $hmacsha.ComputeHash($bytesSigClear)

$signature = [System.Convert]::ToBase64String($hash)

$key = $(‘type=master&ver=1.0&sig=’ + $signature)

$key  = [System.Web.HttpUtility]::UrlEncode($(‘type=master&ver=1.0&sig=’ + $signature)) # needs Snapin System.web!

return $key

}

#Setup stuff for the Planner Calls

$clientId = ‘<A registered client ID in Azure with the right access levels>’

$aadtenant = “<yourtenant>.onmicrosoft.com”

$username = “<yourname>@<yourtenant>.onmicrosoft.com”

$password = “<your password>” | ConvertTo-SecureString -AsPlainText -Force

$Credential = New-Object -typename System.Management.Automation.PSCredential -argumentlist $username, $password

$Modulebase = (get-Module MicrosoftGraphAPI).ModuleBase

Import-Module “D:\home\site\wwwroot\HttpTriggerPlannerToDocDB\Microsoft.IdentityModel.Clients.ActiveDirectory.dll”

Import-Module “D:\home\site\wwwroot\HttpTriggerPlannerToDocDB\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll”

$resourceAppIdURI = “

https://graph.microsoft.com”

$authority = “

https://login.windows.net/

$aadTenant”

$authContext = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext” -ArgumentList $authority

$uc = new-object Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential -ArgumentList $Credential.Username,$Credential.Password

$token = $authContext.AcquireToken($resourceAppIdURI, $clientId,$uc)

# Blog Client ID – my Application ID from Azure

$clientId = ‘<A registered client ID in Azure with the right access levels>’

#Get DocDB REST authkeys

$global:resourceId= “dbs/Planner/colls/Tasks”

$planTasksAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

$global:resourceId= “dbs/Planner/colls/TaskDetails”

$planTaskDetailsAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

$global:resourceId= “dbs/Planner/colls/Assignments”

$planAssignmentsAuthKey = fnGetKey $verb $resourceId $resourceType $xdate $masterKey

#Get Tasks stuff for planId

$headers = @{}

$headers.Add(‘Authorization’,’Bearer ‘ + $Token.AccessToken)

$headers.Add(‘Content-Type’, “application/json”)

$uri = ”

https://graph.microsoft.com/v1.0/planner/plans/”

+ $planId + “/tasks”

$tasks = Invoke-WebRequest -Uri $uri -Method Get -Headers $headers -UseBasicParsing

$tasksContent = $tasks.Content | ConvertFrom-Json

$tasksValue = $tasksContent.value

$docdbTaskHeader=@{“Authorization” = “$planTasksAuthKey”; `

“x-ms-version” = “2015-12-16”; `

“x-ms-date” = “$xdate”; `

“x-ms-indexing-directive” = “Include”;

“x-ms-documentdb-is-upsert” = “True”; `

“Content-Length” = “0”

}

ForEach($task in $tasksValue){

$assignmentNames=@{}

$jsonBody = $task | ConvertTo-Json -Compress

$docdbUri = “https://<YourCosmosDBName>.documents.azure.com/dbs/Planner/colls/Tasks/docs”

Invoke-RestMethod -Uri $docdbUri -Headers $docdbTaskHeader -Method $verb -ContentType “application/json” -Body $jsonBody

ForEach($assignment in ($task.assignments | Get-Member -MemberType NoteProperty)){

[array]$assignmentNames +=$assignment

}

ForEach($assignmentName in $assignmentNames){

$assignee=@{}

If($assignmentName.Name){

$assignee | Add-Member name $assignmentName.Name.ToString()

}

Else{

$assignee | Add-Member name “Unassigned”

}

$assignee | Add-Member planId $planId

$assignee | Add-Member taskId $taskId

$uniqueAssnId = $assignee.planId + “_” + $assignee.taskId + “_” + $assignee.name

$assignee | Add-Member id $uniqueAssnId

$docdbAssnHeader=@{“Authorization” = “$planAssignmentsAuthKey”; `

“x-ms-version” = “2015-12-16”; `

“x-ms-date” = “$xdate”; `

“x-ms-indexing-directive” = “Include”;

“x-ms-documentdb-is-upsert” = “True”; `

“Content-Length” = “0”

}

$jsonBody = $assignee | ConvertTo-Json -Compress

$docdbUri = “https://<YourCosmosDBName>.documents.azure.com/dbs/Planner/colls/Assignments/docs”

Invoke-RestMethod -Uri $docdbUri -Headers $docdbAssnHeader -Method $verb -ContentType “application/json” -Body $jsonBody

}

}

#Task Details

ForEach($task in $tasksValue){

$uri = ”

https://graph.microsoft.com/v1.0/planner/tasks/”

+ $task.id + “/details”

$taskDetails = Invoke-WebRequest -Uri $uri -Method Get -Headers $headers -UseBasicParsing

$taskDetailsContent = $taskDetails.Content | ConvertFrom-Json

$docdbTaskDetailsHeader=@{“Authorization” = “$planTaskDetailsAuthKey”; `

“x-ms-version” = “2015-12-16”; `

“x-ms-date” = “$xdate”; `

“x-ms-indexing-directive” = “Include”; `

“x-ms-documentdb-is-upsert” = “True”; `

“Content-Length” = “0”

}

$jsonBody = $taskDetailsContent | ConvertTo-Json -Compress

$docdbUri = “https://<YourCosmosDBName>.documents.azure.com/dbs/Planner/colls/TaskDetails/docs”

Invoke-RestMethod -Uri $docdbUri -Headers $docdbTaskDetailsHeader -Method $verb -ContentType “application/json” -Body $jsonBody

}

Continue reading Microsoft Planner: Considerations for Reporting-Part 3→

Planner: Cloning a Plan with multiple assignments

Posted on by — No Comments ↓


First published on MSDN on Apr 17, 2017


I updated my previous posting with a rough re-write of my cloning PowerShell – but I have completed a complete re-write that handles the objects in PowerShell much better (thanks for the feedback and guidance Tarkan!).

Here is a zipped up version of the ps1 –

plannerclonemultiassignv3

*** Update 6/13/2017 – the Planner Graph API is now at General Availability – simply replacing /beta/ with /v1.0/ will work in the attached example – or this new modified version has the changes-

PlannerCloneMultiAssignV5

***

I’m starting from a similar ‘template’ as before and my plan looks like this:

Some of the tasks have dates set for start and due dates – some haven’t.  In my code if I find a date I’m adding 7 days to it for the clone just to show how you could move dates – for a real template scenario you’d probably pass in a parameter.  The PowerShell ps1 is attached – and I’ll also walk through some of the code pointing out some of the more interesting bits (at least to me!)

I won’t go over the initial part again where I get the token and use the AppId – so if you are fresh to this topic you might want to read the previous blog post first –

https://blogs.msdn.microsoft.com/brismith/2017/02/17/microsoft-planner-how-to-clone-a-plan-with-graph/

.  The main changes in the Graph for this update is the move to multi-assign – so not the assignedTo has changed to an assignment collection of plannerAssignments – see

https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/resources/plannertask

for the documentation on this.  Another big change is the endpoints now all have ‘planner’ added before the entity – so for example to get tasks you now go to GET /planner/tasks/<id> – where id is the Plan Id.

The changes in the flow of my code is that I now create all the buckets and then all the tasks and then add all the task details – rather than doing the other entities within each bucket in one loop.  I’m keeping track of the old/new Ids for buckets and tasks in hash tables, and I’ve renamed my variables to hopefully make it more obvious what I’m doing.  I’m also manipulating the returned objects more in PowerShell rather than building up new objects – so removing read-only fields or ones I don’t want to set, then setting where necessary, like new Ids for buckets, and also making sure all checklists are unchecked.  I’m also updating a few fields – like the dates as mentioned above – and also setting the orderHints as appropriate (basically adding <existingHint>P!’ to the current one should do the trick, although I’m still not seeing the order quite as I expect…).  I’ll try and get a better answer on that one.  As you will notice – not much has changed with error and exception handling…

So, on with the code.  Not all of the code is listed here – but the ps1 is attached.  I’ll jump in at the point where I have authenticated, grabbed a token and already read the template details and created the Group, added the members and created the Plan – so first thing is to set the categories I’m using on the plan (this might be a useful stand-alone thing just to stamp all your plans with a consistent set of categories!)

$newCategoryDescriptions = $templatePlanDetailsContent.categoryDescriptions | ConvertTo-Json

# Do a GET so I have the current etag

$headers = @{}

$headers.Add(‘Authorization’,’Bearer ‘ + $Token.AccessToken)

$uri = ”

https://graph.microsoft.com/beta/planner/plans/”

+ $newPlanId + “/details”

$result = Invoke-WebRequest -Uri $uri -Method GET -Headers $headers

$newPlanDetailsContent = $result.Content | ConvertFrom-Json

$Request = @”

{

“categoryDescriptions”: $newCategoryDescriptions

}

“@

$headers = @{}

$headers.Add(‘Authorization’,’Bearer ‘ + $Token.AccessToken)

$headers.Add(‘If-Match’, $newPlanDetailsContent.’@odata.etag’)

$headers.Add(‘Content-Type’, “application/json”)

$headers.Add(‘Content-length’, + $Request.Length)

$headers.Add(‘Prefer’, “return=representation”)

$uri = ”

https://graph.microsoft.com/beta/planner/plans/”

+ $newPlanId + “/details”

$result = Invoke-WebRequest -Uri $uri -Method PATCH -Body $Request -Headers $headers

The first line is just taking my categoryDescriptions property and converting to json – which is just what I need in my $Request.  I then get the current etag that I’ll need for my header – and that’s about it.  As you can see the $uri goes to ”

https://graph.microsoft.com/beta/planner/plans/”

+ $planId + “/details” with the new ‘planner’ identifier.

Next I walk through my buckets, creating a hash table so I can use this as a lookup to see which tasks should land in which buckets later.

$bucketHashTable = @{}

ForEach($templateBucket in $templateBucketsValue){

$templateBucketId = $templateBucket.Id

$templateBucket.PSObject.Members.Remove(“Id”)

$templateBucket.PSObject.Members.Remove(“@odata.etag”)

$templateBucket.orderHint = “$($templateBucket.orderHint) $($templateBucket.orderHint)P!”

$templateBucket.planId = $newPlanId

$Request = @”

$($templateBucket | ConvertTo-Json)

“@

$headers = @{}

$headers.Add(‘Authorization’,’Bearer ‘ + $Token.AccessToken)

$headers.Add(‘Content-Type’, “application/json”)

$headers.Add(‘Content-length’, + $Request.Length)

$headers.Add(‘Prefer’, “return=representation”)

$newBucket = Invoke-WebRequest -Uri ”

https://graph.microsoft.com/beta/planner/buckets”

-Method Post -Body $Request -Headers $headers

$newBucketContent = $newBucket.Content | ConvertFrom-Json

$newBucketId = $newBucketContent.id

$bucketHashTable.Add($templateBucketId, $newBucketId)

}

Similar to my newCategoryDescriptions I can re-use the object I have – and am just trimming out the old Id (keeping a copy first) and the etag – then correcting the newPlanId and the orderHint – and all is good!  I put the old and new bucket Ids into my hash table.

Tasks and assignments are all in the task object – so I can add them all in one rather than using the POST then PATCH that I did in the earlier blog post.  I end up removing rather a lot of the members of this object – and it may have been easier to just build a clean one – but thought it worth doing it this way for illustration of the members involved.  Another key part here is taking a copy of the object rather than working on the object from my ForEach, as in PowerShell the objects are just references – so removing something in the loop removes from the collection.

$taskHashTable = @{}

ForEach($templateTask in $templateTasksValue){

$tempTask = $templateTask.PSObject.Copy()

$tempTask.PSObject.Members.Remove(“Id”)

$tempTask.PSObject.Members.Remove(“@odata.etag”)

$tempTask.PSObject.Members.Remove(“createdDateTime”)

$tempTask.PSObject.Members.Remove(“createdBy”)

$tempTask.PSObject.Members.Remove(“conversationThreadId”)

$tempTask.PSObject.Members.Remove(“percentComplete”)

$tempTask.PSObject.Members.Remove(“hasDescription”)

$tempTask.PSObject.Members.Remove(“referenceCount”)

$tempTask.PSObject.Members.Remove(“checklistItemCount”)

$tempTask.PSObject.Members.Remove(“activeChecklistItemCount”)

$tempTask.PSObject.Members.Remove(“assigneePriority”)

$tempTask.PSObject.Members.Remove(“previewType”)

$tempTask.PSObject.Members.Remove(“completedDateTime”)

$tempTask.PSObject.Members.Remove(“completedBy”)

If($tempTask.startDateTime){

$tempTask.startDateTime = ([DateTime]$tempTask.dueDateTime).AddDays(7)

}

If($tempTask.dueDateTime){

$tempTask.dueDateTime = ([DateTime]$tempTask.dueDateTime).AddDays(7)

}

$tempTask.orderHint = “$($tempTask.orderHint) $($tempTask.orderHint)P!”

$tempTask.planId = $newPlanId

$tempTask.bucketId = $bucketHashTable.Get_Item($templateTask.bucketId)

$assignees = $tempTask.assignments.PSObject.Properties | Select Name, Value

ForEach($assignee in $assignees){

$assignee.Value.PSObject.Members.Remove(“assignedBy”)

$assignee.Value.PSObject.Members.Remove(“assignedDateTime”)

$assignee.Value.orderHint = “$($assignee.orderHint) $($assignee.orderHint)P!”

}

$Request = @”

$($tempTask | ConvertTo-Json)

“@

$headers = @{}

$headers.Add(‘Authorization’,’Bearer ‘ + $Token.AccessToken)

$headers.Add(‘Content-Type’, “application/json”)

$headers.Add(‘Content-length’, + $Request.Length)

$headers.Add(‘Prefer’, “return=representation”)

$newTask = Invoke-WebRequest -Uri ”

https://graph.microsoft.com/beta/planner/tasks”

-Method Post -Body $Request -Headers $headers

$newTaskContent = $newTask.Content | ConvertFrom-Json

$newTaskId = $newTaskContent.id

$taskHashTable.Add($templateTask.Id, $newTaskId)

}

As I mentioned earlier – I’m just adding 7 days – but you could pass in a value.

Finally I set the task details –and again this is by maniulating a copy of the object from the template and handling the previewType and checklists all in one loop.  You could also add the references in here easily enough – but one consideration is where the references refer to.  If they are public web references or static internal links then all is fine – but if they refer to items stored in the Group’s SharePoint site then you’n need to consider how you want to handle this.  Possibly you’d need to copy the contents across and update the references accordingly.

ForEach($templateTaskDetailContent in $templateTaskDetailsContents){

$newTaskDetailContent = $templateTaskDetailContent.PSObject.Copy()

$headers = @{}

$headers.Add(‘Authorization’,’Bearer ‘ + $Token.AccessToken)

# Getting the current etag

$uri = ”

https://graph.microsoft.com/beta/planner/tasks/”

+ $taskHashTable.Get_Item($templateTaskDetailContent.Id) + “/details”

$result = Invoke-WebRequest -Uri $uri -Method GET -Headers $headers

$freshEtagTaskContent = $result.Content | ConvertFrom-Json

$newTaskDetailContent.PSObject.Members.Remove(“@odata.context”)

$newTaskDetailContent.PSObject.Members.Remove(“@odata.etag”)

$newTaskDetailContent.PSObject.Members.Remove(“id”)

$newTaskDetailContent.PSObject.Members.Remove(“references”)

$checklist = $newTaskDetailContent.checklist.PSObject.Properties | Select Name, Value

ForEach($checkItem in $checklist){

$checkItem.Value.PSObject.Members.Remove(“lastModifiedBy”)

$checkItem.Value.PSObject.Members.Remove(“lastModifiedDateTime”)

$checkItem.Value.isChecked = “false”

$checkItem.Value.orderHint = “$($checkItem.Value.orderHint) $($checkItem.Value.orderHint)P!”

}

$Request = @”

$($newTaskDetailContent | ConvertTo-Json)

“@

$headers = @{}

$headers.Add(‘Authorization’,’Bearer ‘ + $Token.AccessToken)

$headers.Add(‘If-Match’, $freshEtagTaskContent.’@odata.etag’)

$headers.Add(‘Content-Type’, “application/json”)

$headers.Add(‘Content-length’, + $Request.Length)

$uri = ”

https://graph.microsoft.com/beta/planner/tasks/”

+ $taskHashTable.Get_Item($templateTaskDetailContent.Id) + “/details”

$result = Invoke-WebRequest -Uri $uri -Method PATCH -Body $Request -Headers $headers

}

My new clone plan looks like the following – and as mentioned I’m still trying to get the right logic for the ordering of tasks and checklist – the buckets look just fine. It might come down to sorting the items within the buckets/lists in PowerShell and then applying a new orderHint as per the documents

https://developer.microsoft.com/en-us/graph/docs/api-reference/beta/resources/planner_order_hint_format

.  I’m keen to get some good examples of that – as I see of StackOverflow that a few of you are struggling with orderHint.

Continue reading Planner: Cloning a Plan with multiple assignments→