Categories: Microsoft 365

Export Planner to Excel using Power Automate

Planner can be a great tool to manage your tasks, but how do you export planner to Excel files with Power Automate?

Yesterday I was asked exactly this question.

First of all, you should always ask yourself, do I really need to export data or could I just use it where it already is?

The Planner Structure

The structure for tasks in Planner is quite simple.

A Team in Microsoft Teams is connected to one group. A Group has none, one or more plans. Each plan can have tasks in it.

Tasks can have a start date, a due date, a description and they can be assigned to people and there is quite a bit more, but I don’t want to go into the details of that now.

Building the Export planner to Excel flow

First things first, To Export planner to Excel, I will need to list all my teams. This will give me a list of groups that I want to look at. I’m using a manually triggers flow but you could also schedule this on a regular basis.

Using this list of teams we can now collect all the plans. Remember the teams are connected to groups. And the groups hold the plans. Using the List plans for a group action will give us a full overview of the plans within the organisation.

For the Group Id we can use the group Id supplied by the List Teams action. The will appear as dynamic content for you, so nothing complicated here.

Then for each of the plans we can collect all the tasks using the List Tasks Action. All we need to supply again is the group id and the plan Id. Both of these are readily available from the action that we added earlier on.

The Plan Id is supplied by the list plans for a group action. So far it is all quite easy to collect all the plans..

If we now added an Excel action to Add a row into a table we would probably get something like this.

That is ugly!

We want to see people’s names not ugly IDs. Also the Due date could do with cleaning up.

Using the Get user profile action we can collect the user’s details. The Get user profile action can take the user id that has been supplied by the List tasks action.

All we need to do is use the value assignments Assigned To User id property. Yes, that isn’t a very nice display name of a property.

Now we can use the display name of the user profile to update our Excel file to give us a nice and clear picture of the users that have been assigned to the task. In this post I’m not going to look at merging all these people into a single piece of text, however this is of course possible. In my case I will create multiple entries in my excel file.

In my example I’ve only used Task, Assigned To and Due Date. But you could of course use any data found in the tasks. I could imagine that start date, name of team and other fields would also be of interest.

This now results in the following listing of my data in Excel

That looks a lot better!

Now there is just one thing left to do and that is format the date nicely. For this please have a look at my post about Formatting dates in Power Automate.

In this post I didn’t consider performance. There are quite a lot of changes that I would probably make to make this work for larger organisations with many plans and many tasks. This post however will help you get started with the beginning of the export Planner to Excel.

Download the solution

In general I prefer to just post the steps rather than giving a download, but this time I will make an exception.

If you want to download the full solutions then please visit the downloads page.

Share
Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using contact@veenstra.me.uk.

View Comments

  • Hi Pieter,

    Less than 24 hours ago I was in a meeting where the use of Planner for simple task management was rejected because you cannot export the tasks. Were you listening in, because this post solves the issue ;-)

    Many thanks

    Nigel

  • Have you looked at the Export to Excel option provided by Planner itself?

  • I am trying to replicate this and the flow is not terminating. Would you be able to provide a screen capture of what the final flow looks like?

    • Hi Timothy,

      Do you means that your flow keeps running forever. It might be that you have a lot of plans to go through.

      One thing you could do is open the excel in Excel online while your flow is running. Do new entries appear and keep appearing?

  • It is stuck in an infinite loop. When I have the spreadsheet open, it keeps on listing the same tasks over and over.

  • I too am suffering from the infinite loop. While the excel document is open, the tasks repeat. I’ve killed the process up to 4 minutes run time with the same outcome.

  • I got this to work but its pulling the Bucket ID rather than the name. I looked through all of the options and don't see anyway to pull the name from the list. Can you think of a way to do this? There are over 100 plans its pulling. Thanks

    • Hi Dave,

      It is possible to get the bucket name using the List bucket action. This will probably slow down the flow a bit as it will require another Apply to each

  • First, thank you! I have gotten this flow working and it works well. Second, is it possible to get the bucket name rather than the bucket ID? I am pulling over 100 planner hubs so getting the bucket is pretty important and I can't figure out how to accomplish that. I also don't see a download link in the article. Thanks again!!

    • Hi Heather,

      It is possible to get the bucket name using the List bucket action. This will probably slow down the flow a bit as it will require another Apply to each

  • This may be a silly comment but what do I do with the downloaded solution? Can I import it with PowerAutomate?

  • Hello Pieter, did I miss something or both Tasks and Buckets are linked to Group and Plan ID? When I try to pull both Bucket Name and task title I get duplicated lines in my Excel Table. Is there a way to pull "merge" both into one array that contain each task once and the associated Bucket name.
    That would be ideal, other than that, this post really saved me some times!
    Regs,
    AP.

    • Hi Alexandre,

      I don't think that the bucket really adds much as each task seems to also have the details of the bucket that it is part of anyway. I guess if you wanted to check in a flow or app which buckets exist in a team them that might be a use.

      • Agreed, I'm only missing the "Bucket Name" or a way to represent a list of task by Bucket...Seems to be a challenge to export in excel.

        • You can run a list buckets action to collect the bucket details. Then use a filter array action to get the single record that you want. Once you have a single record get the name of the bucket as a single text and use this in a select action to merge the tasks and the bucket name.

          Now you have your array with all the details needed to export to Excel.

1 2 3

Recent Posts

Calculate the Sum for a SharePoint column in Power Automate

Last week Shane Young asked me about calculating the Sum for a SharePoint column in…

2 days ago

Object must implement IConvertible in Power Apps

In Power Apps when you do a Patch to create a new item or to…

6 days ago

Get started with adaptive cards in Power Automate

Getting started with adaptive cards can be difficult. In this post i have written some…

2 weeks ago

Create PDF documents from data in Power Automate

In this post I will look at how to create PDF documents from data. Use…

2 weeks ago

Unnest nested arrays in Power Automate

We all know this problem, you have a nested array in Power Automate but how…

3 weeks ago

Advanced settings not loading in Power Platform

Yesterday one of my clients showed me an issues where the Advanced settings didn't load.…

4 weeks ago
%%footer%%