cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Export Planner Tasks to Excel

02-20-2022 06:45 AM - last edited 02-21-2022 10:44 AM

Employee DamoBird365
Employee
35747 Views
DamoBird365
Employee
Employee

Export Planner Tasks to Excel

‎02-20-2022 06:45 AM

Unlike the out of the box ability to export planner to excel, Power Automate does not support this natively.  Whilst there is an action to list tasks, some of the data is referenced by GUIDs/IDs or category names, for example assigned to users, bucket ids or labels.  I have built an efficient flow that will hopefully allow you to export your planner tasks for a specific plan to a new Excel File, containing a table, ready for you to use elsewhere.  This is a proof of concept and so I highly recommend that you test.  If there are features that you feel are missing or other fields that you would like included, please drop me a message.

 

The flow is compact and looks likes follows:

 

DamoBird365_3-1645367381089.png

 

There are 4 key areas to the solution:

1. initial explanation, listing tasks buckets and plan details (for label categories)

2. retrieving all users by ID that have been assigned a task and returning their display name

3. for each task, creating an object of key/values and outputting an array

4. a basic method for creating an Excel File with Table containing the data from the Array

 

Stage 2:

I ultimately compile an array of distinct UserIds so that I can look them up and create an object of Userid/DisplayName Key/Values.

 

DamoBird365_0-1645469042499.png

 

Output:

DamoBird365_5-1645367608763.png

This enables all project users to be selected by ID and is a far more efficient way of using get user profile, as we don't need to do this for all tasks where multiple users may be assigned.

 

Stage 3:

There are 4 scopes to get the more tricky data, but here I gather individual arrays of:

1. assignedTo Display Names

2. CheckList Items

3. Categories/Labels (by bespoke name, colour or fallback category number)

4. CheckList Totals / Count

 

DamoBird365_6-1645367717932.png

The output is an object for each item:

DamoBird365_7-1645367962522.png

Stage 4:

Is a very easy method to create a new excel file, table and populate the rows using an apply to each.  You can use Excel Scripts or Graph API to populate a file if you so wish, I have examples of the former on my YouTube. 

 

DamoBird365_8-1645368023183.png

 

Please note that if you want to bulk import tasks to planner, I have a video and downloadable flow via my YouTube here https://youtu.be/n3foHWH1XpU.  Feel free to check out my YouTube for other ideas and concepts too and don't forget to like and subscribe.

 

Sample Task JSON can be seen below:

 

"value": [
            {
                "@odata.etag": "W/\"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBAUCc=\"",
                "planId": "i3wrx3DH-02sFBH13CJnGZcAHLpa",
                "bucketId": "zKpc66eK0EWJhsp6bGDpapcAPxtH",
                "title": "New Task 2!",
                "orderHint": "8585562450483505876",
                "assigneePriority": "8585562450483505876",
                "percentComplete": 0,
                "startDateTime": "2022-02-15T00:00:00Z",
                "createdDateTime": "2022-02-20T12:57:17.1269931Z",
                "dueDateTime": "2022-02-17T00:00:00Z",
                "hasDescription": false,
                "previewType": "checklist",
                "referenceCount": 0,
                "checklistItemCount": 3,
                "activeChecklistItemCount": 3,
                "id": "hEajbK2TQky8t9xvrHsSm5cANPgt",
                "createdBy": {
                    "user": {
                        "id": "6c646262-4f6f-4bfb-88c7-86b3d1252cac"
                    }
                },
                "appliedCategories": {
                    "category2": true
                },
                "assignments": {
                    "6c646262-4f6f-4bfb-88c7-86b3d1252cac": {
                        "@odata.type": "#microsoft.graph.plannerAssignment",
                        "assignedDateTime": "2022-02-20T12:57:17.1269931Z",
                        "orderHint": "8585562446249239777P?",
                        "assignedBy": {
                            "user": {
                                "displayName": null,
                                "id": "6c646262-4f6f-4bfb-88c7-86b3d1252cac"
                            }
                        }
                    },
                    "b386871c-6057-4d62-9167-b3c33af0e46d": {
                        "@odata.type": "#microsoft.graph.plannerAssignment",
                        "assignedDateTime": "2022-02-20T13:05:20.6941642Z",
                        "orderHint": "8585562446249239777PS",
                        "assignedBy": {
                            "user": {
                                "displayName": null,
                                "id": "6c646262-4f6f-4bfb-88c7-86b3d1252cac"
                            }
                        }
                    }
                },
                "_assignments": [
                    {
                        "userId": "6c646262-4f6f-4bfb-88c7-86b3d1252cac",
                        "value": {
                            "@odata.type": "#microsoft.graph.plannerAssignment",
                            "assignedDateTime": "2022-02-20T12:57:17.1269931Z",
                            "orderHint": "8585562446249239777P?",
                            "assignedBy": {
                                "user": {
                                    "id": "6c646262-4f6f-4bfb-88c7-86b3d1252cac"
                                }
                            }
                        }
                    },
                    {
                        "userId": "b386871c-6057-4d62-9167-b3c33af0e46d",
                        "value": {
                            "@odata.type": "#microsoft.graph.plannerAssignment",
                            "assignedDateTime": "2022-02-20T13:05:20.6941642Z",
                            "orderHint": "8585562446249239777PS",
                            "assignedBy": {
                                "user": {
                                    "id": "6c646262-4f6f-4bfb-88c7-86b3d1252cac"
                                }
                            }
                        }
                    }
                ]
            }
ExportPlannerTaskstoExcel.zip
Labels:
Message 1 of 71
35,747 Views
AdrianT1
Frequent Visitor

‎08-26-2022 01:28 PM

What steps might I take if I want this to update in a near Live function? (When a task is updated or modified) 

Planner -> Excel-> Power BI 

Message 41 of 71
1,447 Views
0 Kudos
AdrianT1
Frequent Visitor
In response to AdrianT1

‎08-26-2022 01:57 PM

Id imagine we could use a condition if a file is present then "update or insert new row" would occur. Something along that line. 

Message 42 of 71
1,443 Views
0 Kudos
JozefF
New Member

‎09-12-2022 10:28 PM

Hello, can I prove that I can also export the comments and the checklist that have been made? Well thank you

Message 43 of 71
1,410 Views
0 Kudos
ishesb
Advocate I
Advocate I
In response to JozefF

‎09-13-2022 05:48 AM

@JozefF As far as I know, no, you cannot export the comments as this is Flow does not have a way to do this.  I hope I can be proven wrong at some point soon to allow these as that'd be handy.   Checklists you can, it is actually somewhere in this process that Damo created.

Message 44 of 71
1,399 Views
0 Kudos
Anonymous
Not applicable
In response to mshirley

‎09-28-2022 05:56 AM

Have you found a way to add the categories "Priority" and "Late" to the importer?

Message 45 of 71
1,336 Views
Douieloherty
New Member
In response to DamoBird365

‎10-04-2022 03:20 AM

Hi, 

 

I updated the scope applied categories as above, however I'm now getting the below error message. Is there a workaround for this? It would be massively help if I could get this flow to work!
Thanks a million

Louis 

Douieloherty_0-1664878792762.png

 

Message 46 of 71
1,321 Views
Yellow300zx95
New Member

‎10-19-2022 08:00 PM

Hi, where can I download the steps. Or do I have to create each one, one at a time in PowerAutomate?

Message 47 of 71
1,236 Views
0 Kudos
KRONOS
New Member

‎10-27-2022 12:08 PM

Hi DamoBird365, I have a problem with the flow, could you help me to solve it?

 

KRONOS_0-1666897586269.png

 

Message 48 of 71
1,206 Views
SRH96
Regular Visitor
In response to DamoBird365

‎11-04-2022 04:58 AM

Hi Damien, 

 

SRH96_0-1667562710768.png

I'm receiving the error message above. The error is only occurring with some of the tasks and from having a look at them I think it is because they are not assigned to a bucket. I'm still fairly new to expressions - what can I amend the expression to for the BucketName in ComposeTaskObject to handle unassigned tasks?

Message 49 of 71
1,177 Views
0 Kudos
GregStaruck
New Member

‎11-22-2022 08:56 AM

Hello,
I'm new to Power Automate, I was looking for exactly that in being able to extract the Planner to Excel, add it to Sharepoint to later use it in Power BI.
I tried to use this solution by creating (copying and pasting) a new scheduled flow from the cloud, but when modifying it, I got an error in "Filter array Remove Empty Results", because it indicates that the formula "@not(equals(item(), )) " it's wrong. Would you help me ? because I want the extraction to occur every X hour.

Message 50 of 71
1,094 Views
0 Kudos