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.
Priority in Planner
Have you noticed that the connector doesn’t return the priority of tasks?
Are you interested in including the priority in your exports? The following user voice item is asking for this field to be added to the API of Microsoft Planner
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.
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
You should have asked me the question yesterday then 😉
Have you looked at the Export to Excel option provided by Planner itself?
Do you mean the Export Plan to Excel option?
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’ve now added a downloads link to the article
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’ve now added a downloads link to the article
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?
Yes, it is a zip you can import.
thanks for the prompt reply
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.
Thanks for the tip! I will definitely try it.
AP
I am having trouble determining where to place the “List Buckets” command in my Power Automate. It is asking for Group ID and Plan Id, however, I have a lot of different Groups IDs and Plan Ids I am trying to pull from.
Hi Chase,
The List task action gives you the bucket ids of each task. that way you can then run the get bucket action to get the bucket name.
I’m still getting the infinite loop, keeps adding the same task over and over
Hi Andrew,
I’ve not had that myself, but I’ve seen some other people mentioning the same. Can you send me an export of your flow?
Hello,
First of all, thanks for your code!
However, I am having the same problem as Dave and Heather, I get in the “Buckets” column only IDs except that I would like the names of the buckets.
I saw that you gave a solution but I can’t get it to work … can you give me the code to make the buckets names appear directly using “List Buckets” please?
Simon
Hi Simon,
You can plaxce a list buckets just before the list tasks as shown below:
Then wherever you need the bucket name simply get it from the dynamic content of that list bucket action.
Thank you for your response!
But, I still have a problem…
What I want is to get an excel file in which each task is associated with its bucket.
However, I get an excel spreadsheet in which each task is associated with all the compartments of the planner.
If you don’t mind, can you help me with this problem or send me a link with your correct code in my case
Simon
Hi Simon,
If it is slightly more complicated than expected then it would probably need a bit of additional work. I will try to have a look at it when I have some time available.
OK…
Can you send me your email address, so I can send you the pictures of my results…
Thank’s a lot for your time
Simon
Hi Pieter,
It is probably not possible but can power automate use the “Export plan to excel” options in planner and then save the output in a specific folder and do this for a specific list of planners which is either hardcode in the flow or driven by a export setting file (excel, csv, etc.)?
Thanks for a great post.
Jonas
Hi Jonas, that is very easy. Just use the create file action and the content of the csv generated by the flow.
Hi Pieter, can you please post a photo of the flow as a whole? I can’t tell if the every ‘apply to each’ is nested or not thank you!
Yes they are all nested. I didn’t optimize this flow. If I implemented this in production I would optimize it to run a lot faster.
Hi Pieter,
Is there a way for me to get 1 task per line and have the assignees in the same line, instead of getting the same tasks multiple times because each assignee of the tasks is added as a new line in the excel table?
Pieter- was trying to upload the .zip file you linked to – have got this message several times. Any ideas?
You’ll need to resolve these conflicts before importing the package
Resource: Export Plans
Import setup: Create as new
Error
Flow save failed with code ‘DynamicOperationRequestClientFailure’ and message ‘The dynamic operation request to API ‘excelonlinebusiness’ operation ‘GetTable’ failed with status code ‘NotFound’. This may indicate invalid input parameters. Error response: { “status”: 404, “message”: “Item not found\r\nclientRequestId: 0fcdd1e2-37a6-4da5-9456-6f90987345c8\r\nserviceRequestId: 179676dc-ec6f-47db-80e2-08fdcf99aff4”, “error”: { “message”: “Item not found” }, “source”: “excelonline-eus2.azconn-eus2.p.azurewebsites.net” }’.
Hi Darren,
Did you create an excel file to export planner to? Does this file have a table configured?
Hi Pieter, firstly thank you for this, very helpful. I am struggling to get the due date in a better format, I have visited your link to the page that discusses formatting dates, but I am still struggling.
How do I get the Due Date to format using the formatDateTime?
This is what I have now –> “item/Due Date”: “@items(‘Apply_to_each_2’)?[‘dueDateTime’]”,
This is what I added as an expression –> @formatDateTime(items(‘Apply_to_each_2’)?[‘dueDateTime’], ‘dd-MM-yyyy’)
But I keep getting an error saying that the expression is invalid. What would be a valid expression?
Thanks
Hi Gary, Is the due date field always set? When it is empty I would expect that the formatDateTime function would fail. you could include an if around that function that checks if you have a due date or not.
Thanks Pieter. If I have “item/Due Date”: “@items(‘Apply_to_each_2’)?[‘dueDateTime’]” then I get results for Due Date – this is an example 2020-12-25T10:00:00Z18-12-2020
As you can see the format is not pretty, I just want day, month and year. So I tried to use formatDateTime to reformat the date. When I try to use @formatDateTime(items(‘Apply_to_each_2’)?[‘dueDateTime’], ‘dd-MM-yyyy’) — I get a invalid expression
Hi Gary,
This worked for me:
This is the full expression:
formatDateTime(items(‘Apply_to_each’)?[‘Modified’],’dd-MM-yyyy’)
In your expression do you see that @ sign in your expression editor. In which case that will be the problem. Otherwise check those single quotes as they don’t match, but that might just be a website formatting issue.
Hi Pieter, and thanks a lot for the guidance so far!
Could you be so kind as to explain briefly what made the flow run in a loop?
(I’ve tried importing your flow from downloads – but it won’t import)
Sincerely,
Mike
Hi Mike,
Not sure that I fully understand the question. Please feel free to open the chat on this site.
Hi Pieter, is it possible to elaborate on this? What do you mean with “content of the csv generated by the flow”?
Thanks!
Rather than creating an excel file you could generate a CSV file instead. It means that you could generate the csv content and create a file with that content. That option however is not used in the post as I used the xlsx format.
It seems crazy that MS almost have it perfect, in the Excel export from a plan, yet to to do the same for a cluster of plans, with PowerAutomate is a nightmare
Things are changing all the time. So one day things will be better
Hello I have tried the solution but unfortunately it did not give me a valid run as solution. So I will post my question here, hoping for a step by step visual on how to get where I want:
I have in teams the following setup
Teams Group 1
Channel 1.1
Planner 1.1.1
Planner 1.1.2
Channel 1.2
Planner 1.2.1
Teams Group 2
Channel 2.1
Planner 2.1.1
Planner 2.1.2
Planner 2.1.3
etc
Per default, per planner, I can go to each of them and manually export to excel. (those Excel files have columns with all the info of the planner and tasks belonging to the plan per default setting of export)
I would like to have this automated:
Export all the planners I own or am a member to excel.
With reference to the name of the planner, the channel, and the teams’ group, date of export
Save the exported file(s) to a dedicated folder
Each individual xlsx file (1 per planner) or 1 combined file of all planners together (depending on the complexity of the above
automation)
Then I will use this xlsx file(s) as a source into Power BI, but I guess that’s another automated flow I will look into, and for now, don’t need help in this thread 🙂
Thanks to guide me to a solution.
You can use excel is a datasource for Power BI. But why not export the planner data and place it in dataverse or sql or sharepoint lists? In the past I created emails from the planner data but you could use any datasource.
It will be a matter of stepping through the teams, channels, planners. But make sure that you avoid the nested apply to each steps as performance will be important. With nested apply to each these kind of flows can easily take hours. They shouldn’t take longer than a couple of minutes.
Hello Pieter, exactly the nested apply to each step that every other solution provided so far did nothing and no successful run came out of it.
I developed a few techniques on how to avoid the apply to each steps. For a lot of then you can either use a select action instead or use the Pieter’s method.
For more details please watch my session
https://events.collab365.com/c365_sessionroom/gc5-magic-tricks-with-power-platform/
Feel free to open a chat and we can discuss further.
Hello, I am working on making a flow for management and I was wondering if I could get some insight. So what i am currently doing is figuring out how to automate the process of exporting multiple plans from one group and getting that to be in an excel format. I am currently using this format but I can’t seem to figure out why that is can someone help me make my flow better?
Hi Erick, how far did you get with recreating the flow in this post? Do you know which part is not working?
Hi Peter,
Thanks for for this but i am having issues importing.. I selected the .zip and got to the Import package screen but the import button is greyed out.. could that be a permissions issue?
Fred
I haven’t seen this myself, but I have seen other people who have had this for a short period of time and sometimes a few days. Not sure why this would happen.
Are you importing to a default environment or to another environment. I could imagine if anything is found in the import that isn’t accepted in your environment that this would happen. So for example DLP could cause problems.
You could try and build up the flow with the steps given. If anything is not valid you wouldn’t be able to build the flow manually either.
Morning Pieter,
First and foremost, my sincere apologies on getting your name wrong. So I forwent the shortcut and built the flow from scratch and deleted it and did it again. Got it to work the second time but i am now stuck in the same loop others reported. 🙁 . I am wondering if its related to the nesting of the “apply to each” in mine “Apply to Each 4” is under “Apply to each 3” and so on.
Hi Pieter
And thank you for this great explanation, so that newbies like me can work with Power Automate as well…
I tried the flow out and it worked great…. but actually it worked a little too well since it duplicates all lines at least 5 times, and i can’t figure out why. Do you have any idea?
I have a lot of tasks so this slows it down a lot…
Hi Asbjørn,
It most likely means that one of the apply to each steps isn’t in the right place.
The easiest way to debug this is to go through the flow run and then check where the data is generated. Feel free to open a chat on my site and we can have a look at this.
Hello, I am trying to find a way to export the plan to excel including comments and attachments… Do you Have a solution for that?