Skip to main content
Power Automate
    • Connectors
    • Templates
    • Take a guided tour
    • Digital process automation
    • Robotic process automation
    • Business process automation
    • Process advisor
    • AI Builder
  • Pricing
  • Partners
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Business process and workflow automation topics
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Using Connectors
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • Process Advisor
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Connector Development
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Galleries
    • Community Connections & How-To Videos
    • Webinars and Video Gallery
    • Power Automate Cookbook
    • Events
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Community Engagement
    • Community AMA
    • Community Blog
    • Power Automate Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    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: 
    • Microsoft Power Automate Community
    • Galleries
    • Power Automate Cookbook
    • Export Planner Tasks to Excel

    Export Planner Tasks to Excel

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

    Microsoft DamoBird365
    Microsoft
    16308 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    DamoBird365
    Microsoft DamoBird365
    Microsoft
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    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:
    • Labels:
    • Scheduled flows
    • Solutions
    Message 1 of 57
    16,308 Views
    12 Kudos
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • 2
      • 3
      • …
      • 6
    • Next »
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-02-2022 12:55 AM

    Hi, this look great. In scrip I have error in section 3 - ScopeAppliesCategories - SelectLabels. Any idea how to solve it??

    Screenshot 2022-05-02 at 9.48.38.png

     

    Message 2 of 57
    12,574 Views
    1 Kudo
    Reply
    MosK
    MosK
    Regular Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-13-2022 08:10 AM

    This is an amazing flow that has worked for me but it's giving an empty cell for Created By. How can I go about it to include the created by names just like the assigned to names.

    Message 3 of 57
    12,347 Views
    0 Kudos
    Reply
    DamoBird365
    Microsoft DamoBird365
    Microsoft
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-17-2022 08:14 AM

    Hi @Anonymous I wonder if it's because you don't have category descriptions?  Try changing the expression from ['body/categorydescriptions'] to ['body']?['categorydescriptions'] and it should hopefully return null, rather than error.  

    Message 4 of 57
    12,277 Views
    0 Kudos
    Reply
    DamoBird365
    Microsoft DamoBird365
    Microsoft
    In response to MosK
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-17-2022 08:16 AM

    Hi @MosK, created by is based on the AAD.  You should be able to check the history of the flow for the select as follows:

    DamoBird365_0-1652800517121.png

    This is part of the scope from stage 2.  If you are not seeing anything, it would be worth exploring the history of this scope to understand why you are not getting the users back.

     

    Damien

    Message 5 of 57
    12,277 Views
    0 Kudos
    Reply
    AIU
    AIU
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-18-2022 12:02 PM

    Hi Damien,


    Thanks for sharing this flow. It helped a lot building one that I need. The only issue I am having up to now is that some tasks do not have labels and in those cases the flow stops with an error. How can I solve this?

     

    Regards,

    Arthur

    Message 6 of 57
    12,208 Views
    0 Kudos
    Reply
    Melles
    Melles
    Frequent Visitor
    In response to DamoBird365
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-10-2022 02:24 AM

    Hi @DamoBird365,
    I have the same problem as @Anonymous, and indeed the missing categories are the problem. But I can't find the expression ['body/categorydescriptions'] I should change. Can you help me finding it in the flow?

    Message 7 of 57
    11,512 Views
    0 Kudos
    Reply
    ishesb
    ishesb Advocate I
    Advocate I
    In response to Melles
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-21-2022 01:07 PM

    First of all, @DamoBird365 this is amazing! I love the way you structured it, I have a WAY easier time understanding what is happening here. ❤️

     

    @DamoBird365I am having the same problem as @Anonymous, @Melles, and @AIU where there are no categories and it fails the entire process.  If I have categories it works, but any single failure (non category task) does not let the process continue on to "ComposeTaskObject"

    ishesb_0-1655842714759.png

     

    Seems like I have to find a way to deal with the empty labels . Coalesce will return the first non "NULL" it finds, but if you have nothing labeled, it will not return anything, hence "" (empty). I need to rewrite the function to accommodate non labeled tasks 😕

     

     

    I did try changing it from ['body/categorydescriptions'] to ['body']?['categorydescriptions'] but I get the same error,  "cannot be evaluated because property '' cannot be selected"

    ishesb_0-1655841969891.png

     

    @Mellesthe expression is under "SelectLabels" step, [fx] 'coalesce(...)' under "Map" double clicking lets you edit it if I understood @DamoBird365 correctly, that is what I changed at least.

     

    Message 8 of 57
    11,226 Views
    0 Kudos
    Reply
    DamoBird365
    Microsoft DamoBird365
    Microsoft
    In response to ishesb
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-21-2022 01:56 PM

    Hi @ishesb 

     

    Thanks for taking a look.  The easiest way to resolve if categories/labels are not a requirement would be to remove the "scopeappliedcategories" and also update "composetaskobject" to remove the "Labels" key/value.  I understand why you are getting the error where no label exists, my bad for not testing that scenario 🤣

     

    Alternatively, if you are happy to test and feedback, try updating the following:

     

    DamoBird365_0-1655844929021.png

     

    If(empty(first(outputs('ComposeArrayOfLabels'))),'"NONE":true',replace(replace(string(first(outputs('ComposeArrayOfLabels'))),'}',''),'{',''))
     
    If the arrayoflabels is empty insert a string "NONE":true, otherwise perform the previous expression that was in that compose action.
     
    Let me know if either of those options work for you.
     
    Damien
    Message 9 of 57
    11,218 Views
    4 Kudos
    Reply
    ishesb
    ishesb Advocate I
    Advocate I
    In response to DamoBird365
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-21-2022 02:38 PM

    This worked!  Thank you! Now to customize the spreadsheet a bit more for my use! This is wonderful!

     


    Alternatively, if you are happy to test and feedback, try updating the following:

     

    DamoBird365_0-1655844929021.png

     

    If(empty(first(outputs('ComposeArrayOfLabels'))),'"NONE":true',replace(replace(string(first(outputs('ComposeArrayOfLabels'))),'}',''),'{',''))
    Damien

     

    Message 10 of 57
    11,214 Views
    4 Kudos
    Reply
    • « Previous
      • 1
      • 2
      • 3
      • …
      • 6
    • Next »

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing

    • © 2023 Microsoft
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices