Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities - Lets Get Started!
May 14th | Power Platform Community AMA: Power Apps Edition
News and Announcements icon
Community site session details

Community site session details

Session Id : ooep6jgWlOiNYvgyNicguC
Power Platform Community / Gallery / Power Automate Cookbook / Export Planner Tasks to Excel

Export Planner Tasks to Excel

34291 Views
Profile Picture Posted 21 Feb 2022 by Community member

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"
                                }
                            }
                        }
                    }
                ]
            }
ExportPlannerTask...

Categories:

Comments

  • IJ1 Profile Picture IJ1
    Posted 02 Apr 2024 at 12:16:41
    Export Planner Tasks to Excel

    Hi @DamoBird365 , how is "FileContent" filled with data? I did not unterstand where the input of this brick comes from. Could anyone explain?

     

    IJ1_0-1712060032033.png

    IJ1_1-1712060059686.png

     

     

  • filesoof Profile Picture filesoof
    Posted 10 Mar 2024 at 14:43:38
    Export Planner Tasks to Excel

    It looks like you overwrote the whole function with a string value, that is now seen as 'input'. Action ComposeStringifyLabels should look like this:

    filesoof_0-1710081681435.png

    And on the code page you should see something like this:

    filesoof_1-1710081769236.png

     

    So the function text should just be:    

    If(empty(first(outputs('ComposeArrayOfLabels'))),'\"NONE\":true',replace(replace(string(first(outputs('ComposeArrayOfLabels'))),'}',''),'{',''))
  • filesoof Profile Picture filesoof
    Posted 10 Mar 2024 at 14:39:10
    Export Planner Tasks to Excel

    For priority: see my message of 2024-03-10.

  • filesoof Profile Picture filesoof
    Posted 10 Mar 2024 at 14:16:04
    Export Planner Tasks to Excel

    Thanks @DamoBird365 for this solution! 

     

    As to the question that many already asked 'how about the priority field', I've been looking into a solution, and without not that much knowledge about Power Automate, after a lot of tweaking I figured it out.

     

    First create a list with the possible values with the Compose action, and place it somewhere in the flow (I put it in front of 'List tasks'):

    filesoof_1-1710079311328.png

    The descriptions are in Dutch, change accordingly to your own language.

     

    Then after the 'List tasks', which returns a single body with in it all tasks, including their priority, create a list SelectTaskPriority with id/priority pairs, using a Select action:

    filesoof_2-1710079574110.png

    From value: outputs('List_tasks')?['body/value']

    Map value: concat('\"', Item()?['id'] , '\":\"', item()?['priority'], '\"')

     

    Now turn this into an array (I think 🙈) ComposeTaskPriorityArray using a Compose action:

    filesoof_3-1710079823741.png

    Input: json(concat('{', join(body('SelectTaskPriority'), ','), '}'))

     

    Now go all the way down to existing action ComposeTaskObject, and add the function to fill field Priority:

    filesoof_4-1710079992576.png

    Function to use:  outputs('ComposePriorityList')?[outputs('ComposeTaskPriorityArray')?[items('Apply_to_each')?['id']]]

    Explanation: first find the priority numeric value in ComposeTaskPriorityArray by searching with the id of the task, and with that numeric value find the string value in ComposePriorityList.

  • mfurmanek Profile Picture mfurmanek
    Posted 08 Jan 2024 at 19:45:28
    Export Planner Tasks to Excel

    Hello. New to Power Automate, to a complex flow like this one. I applied the Input above  - If(empty(first(outputs('ComposeArrayOfLabels'))),'"NONE":true',replace(replace(string(first(outputs('ComposeArrayOfLabels'))),'}',''),'{','')) - on the ComposeStringifyLabels and I get a different error:

     

    mfurmanek_0-1704742975799.png

     

    mfurmanek_1-1704742997368.png

    Here is the raw inputs from the Select Labels step:

     

    {
        "from": [
            "If(empty(first(outputs('ComposeArrayOfLabels')))",
            "'\"NONE\":true'",
            "replace(replace(string(first(outputs('ComposeArrayOfLabels')))",
            "'}'",
            "'')",
            "'{'",
            "''))"
        ]
    }
     
    The planner I am exporting from also does not use labels.
     
    Any assistance is appreciated.
  • osmar11692 Profile Picture osmar11692
    Posted 27 Nov 2023 at 23:38:50
    Export Planner Tasks to Excel

    Hello @Riki, I have the same problem, how did you solve it?

  • osmar11692 Profile Picture osmar11692
    Posted 27 Nov 2023 at 23:29:36
    Export Planner Tasks to Excel

    Hello @Anonymous , I have the same problem, how did you solve it?

     

  • Chris_B Profile Picture Chris_B
    Posted 28 Sep 2023 at 23:22:02
    Export Planner Tasks to Excel

    Hello @DamoBird365

     

    your flow made my work much easier.
    It's a shame that there isn't a way to start exporting to Excel directly.

    This isn't a problem in the web interface.

     

    I can only agree with the previous speaker and would like to know how I can fill the “priority” and “created by” fields with information.

     

    If it were then possible to save the comments on the tasks, then that would be the perfect solution.

     

    Thanks in advance
    Greetings Chris

  • SamiPhoenix1 Profile Picture SamiPhoenix1
    Posted 08 Sep 2023 at 00:02:58
    Export Planner Tasks to Excel

    Great solution @DamoBird365 

     

    I notice the created by person name field is always blank. How to fix this please?

  • SamiPhoenix1 Profile Picture SamiPhoenix1
    Posted 08 Sep 2023 at 00:01:19
    Export Planner Tasks to Excel

    Hi - Did you ever get a solution to extracting the 'priority' and 'created by person name' data?