06-02-2023 13:17 PM
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:
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
I ultimately compile an array of distinct UserIds so that I can look them up and create an object of Userid/DisplayName Key/Values.
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.
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
The output is an object for each item:
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.
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:
Like others thank you @DamoBird365 for providing this flow, before I found this flow I was having trouble exporting assigned users.
This flow is giving me all fields except each time I run the flow output is limited to 400 rows (tasks), why is this?
Found the answer at https://powerusers.microsoft.com/t5/Building-Flows/Planner-List-tasks-retrieves-only-400-tasks-from-...
thanks to @ChrisAyers
I got the same error and added a condition to check if labels are added in the current task. It solves the error as the check only allows the tasks with labels added.
Is there any way to amend this flow so that, rather than creating a new spreadsheet each time, it adds new lines to the same spreadsheet, in order to be able to track the changes in a plan over time?
I tried changing the file name in the "Add a row into a table" section, but that then deleted the "Row" part and prompted me to fill in every column individually.
I'm okay with the rest of the changes but am stumped at this.
@DamoBird365 - I'm having the same problem. User not found. Old teammate that is still tagged on old tasks. Any way to ignore or filter for more recent tasks?
I've got this working with minimal mucking around. Thanks very much!!!
One thing I have noticed though is that the priority fields are all 0.
If I export the planner data directly to excel file from the planner webpage, the priorities all come up as Medium (which I think is the default level if nothing is entered).
Also the created by person name is not in the export.
Any chance for a tweak or hint of how to get this done?
Help I am Stuck. It keeps saying my "Create_Table" failed. Another table already exists with the name '2023-06-01T08:13:25Z' or the name is invalid.
I tried to create a new table, with new name, but its still saying the table already exist.
This is useful in that I know what I want to do is possible, but not useful in that I have no idea what goes into most of those fields to actually make it work.
The flow works for me so far. However, I do not want to create a new file and a new table. I want to add the data/output to an existing table using the function "Add a new row to table". But so far it does not work that I get the whole output into the Excel. I always have to add the dynamic content at this point.