List Tasks Planner - Successful Alternative to get Labels and other inaccessible fields

[Anonymous]

Hi All,

Long time reader first time poster.

I have been learning Flow and Planner for several months and been trying to get an automated download of all Planner Tasks.
All Flow approaches using "List Tasks" are limited as they cannot access all fields.
The "Export to Excel" option from the Web Version of Microsoft Planner (which provides almost all fields) cannot seem to be automated and has to be manually triggered. 

I have found a Flow alternative where it appears to be best to ignore using "Apply to Each" after "List Tasks" in Flow.

Using "List Tasks" and "Apply to Each" to write the fields back to an Excel table or similar takes up to an hour or more and the Flow times out.

However if you look at the total list of available fields (screenshot below is not all of them) ....

But then compare this list to the actual Download text generated by the prior "List Tasks" (from the results of the Flow) you find that "List Tasks" has "extra fields" that included such as total number of checklists per Task, total number of active checklists per Task, label numbers, what is selected to be shown on the card (Description, Checklists, Attachments etc) within the text. 

All the prior discussion here said seems to have stated these fields are inaccessible.

So I abandoned using "Apply to Each" and instead dumped the "List Tasks" output direct to a File in Flow.

I made a loop which did this for every Plan.
This took the download time to 10-30 seconds!!!!

I then used Power Query in Excel to pull the file apart and do data transformation and get ALL the information I would have from the "Apply to Each" method ... but also all these extra fields.
To make this work I had to convert the Task ID, Bucket ID, User ID (for Created By, Assigned By, Assigned To etc) and other ID fields back in Excel Power Query from lookup tables rather than using Flow.

But the net result is my download time is down to 30 seconds for 7 Plans of more than 1000 tasks, I get more fields than I do any other way, and the Excel Power Query processing takes less than 1 minute and the end user doesn't have to do anything.

The next wall I hit was the limit of 400 for "List Tasks" per Plan .... which also effects this text download.

https://powerusers.microsoft.com/t5/Building-Flows/Planner-List-tasks-retrieves-only-400-tasks-from-... 
So lots of votes for removing that limit please.

https://powerusers.microsoft.com/t5/Power-Automate-Ideas/List-tasks-over-400-tasks-from-planner/idi-... 

But I was very very close to finding an automated alternative to "Export to Excel".

Has any one else tried anything like this approach ?

I can post more information.
Or alternatively has anyone found a Flow or similar method to trigger the "Export to Excel" menu even via some form of click automation?

[efialttes]

Hi!
Another PA flow designer struggling with Planner here 😄
Let me see if I understood... when you said:
"you find that "List Tasks" has "extra fields" that included such as total number of checklists per Task, total number of active checklists per Task, label numbers, what is selected to be shown on the card (Description, Checklists, Attachments etc) within the text. "

You mean this extra info is available when you export manually from Planner but not on "Get tasks" output from Microsoft Power Automate (aka Flow) right? And the way to obtain it is by reading the excel manually exported from your PA flow, right?
Finally, are you asking if somebody implemented an UI flow to automate current manual excel exporting task to excel, right?

https://docs.microsoft.com/en-us/power-automate/ui-flows/overview

[Anonymous]

Thanks for replying. Yes and No.

---

@efialttes wrote:
Hi!
Another PA flow designer struggling with Planner here 😄
Let me see if I understood... when you said:
"you find that "List Tasks" has "extra fields" that included such as total number of checklists per Task, total number of active checklists per Task, label numbers, what is selected to be shown on the card (Description, Checklists, Attachments etc) within the text. "

You mean this extra info is available when you export manually from Planner but not on "Get tasks" output from Microsoft Power Automate (aka Flow) right?
-> No - I mean you can get this, AND additional Information which ISNT in the standard Planner Excel Download , by using "List Tasks" but abandoning the traditional Flow method of selecting the listed Flow Fields individually as there is more information that can be read from the "text output" version of "List Tasks"

And the way to obtain it is by reading the excel manually exported from your PA flow, right?
->  Sort of - The standard Planner Export to Excel gives a "clean" list requiring less manipulation and most of, but still NOT all the additional fields I am talking about being available above.

Finally, are you asking if somebody implemented an UI flow to automate current manual excel exporting task to excel, right?
-> I have tried the UI approach to effectively select "Export to Excel" from the Planner menu, and it works for a one off Plan, but i haven't yet got it to work for multiple Plans plus it is seemingly harder to control where the Export file goes to as this is controlled from the Browser. Open to ideas.

https://docs.microsoft.com/en-us/power-automate/ui-flows/overview

---

---

I hope this clarifies what I am saying and can post examples.

[Anonymous]

To explain better ....

This table is an example of what you can read from the "List Tasks" Flow output direct.

previewtype, checklistItemCount, checklistItemCount, appliedCategories, conversationThreadId (whether a conversation has been included) CAN be read from this text if use a text dump.
But these aren't selectable via standard Flow using "Apply to Each".

The standard Planner Export to Excel includes the equivalent to checklistItemCount, checklistItemCount, appliedCategories but doesn't include previewtype or conversationThreadId 

@odata.etag	W/"JzEtVGFzayAgQEBAQEBAQEBAQEBAQEBAUCc="
planId	L1vKn-eNnEyS0aluFYn0GsgAHOz4
bucketId	4_wgk59YWkepYHMopRIv0MgAL8En
title	CAS# BW & Portal Upgrade
orderHint	8.59E+18
assigneePriority
percentComplete	0
startDateTime	2020-08-03T00:00:00Z
createdDateTime	2020-01-28T02:14:08.2349793Z
dueDateTime	2020-10-01T00:00:00Z
hasDescription	FALSE
previewType	automatic
completedDateTime
completedBy
referenceCount	0
checklistItemCount	0
activeChecklistItemCount	0
conversationThreadId
id	N8SiBZCG3UCtQBSA-DYug8gAGJUt
createdBy	{"user":{"displayName":null,"id":"5cd9d9ff-fd1b-4d7a-bbf2-9eb8ab8cc855"}}
appliedCategories	{"category4":true}
assignments	{"939d15db-54c2-4f0c-bd18-0cb1c18448e3":{"@odata.type":"#microsoft.graph.plannerAssignment","assignedDateTime":"2020-01-28T02:14:08.2349793Z","orderHint":"8586214292973832250Pg","assignedBy":{"user":{"displayName":null,"id":"5cd9d9ff-fd1b-4d7a-bbf2-9eb8ab8cc855"}}}}

[Anonymous]

One of our IT guys had tried the UI Flow approach before but hadn't sorted out the Gateway issues to get it to then be triggered on a Flow schedule.

I just tried a UI Flow myself (but I had to do it on my personal laptop to install the software and Selenium IDE as my work laptop restricted by Administrator but I can sort that issue out).

It is easily possible to record and trim it back to the 3 basic steps to open a specific plan in Planner, select the ellipsis ... menu , select Export to Excel.😄

And it works like a charm.😍

All I would have to do then is to have multiple versions of this and just change the first web link to be all my Plans.

I would have to write another routine which sets the download folder and clears out the prior Excel files before initiating the Flow.

So its something I will definitely chase further with our IT department as another alternative. 🤔
But the point still stands that it is possible to get more Planner fields as per my first post.

[efialttes]

Hi
"previewtype, checklistItemCount, checklistItemCount, appliedCategories, conversationThreadId (whether a conversation has been included) CAN be read from this text if use a text dump.
But these aren't selectable via standard Flow using "Apply to Each""

Thanx for sharing! I am without my laptop today, but I will try to invoke them inside an 'Apply to each' by means of writing the following expression:
item()?['NameOfTheHiddenProperty']
Let's see what happens!

[Anonymous]

Thanks 😉 for the item()?['NameOfTheHiddenProperty'] suggestion I will have a look at that too.

Edit Update 1>

The weird thing is .... when I used item() like you said (for both the checklist fields) it came as a purple expression ... then changed to a green Planner item after saving the Flow.

But if you search for that Planner item in the dynamic option - you can't find it!

< Edit Update 1

> Edit Update 2

Despite the above being shown .... the Flow didn't download these fields so tell me if you get a different result?

< Edit Update 2

Unless I can find out a way to make the "Apply to Each" iterative process much faster ....

i.e. down from more than 1 hr to my alternative automated dump the text option (30 seconds) I will stick with that for now.

Is it only Flow Update Row in Excel that is so slow or do alternative database structures help?

I can explain how I am doing this alternative "in one hit" List Tasks" OUTPUT text dump in terms of the Flow steps and Power Query if you want to know.

It doesn't ultimately matter as I could run the 'Apply to Each' at midnight when no one cares but I expect it may become a "on demand" option eventually. I will update as I get a better solution.🤔

[efialttes]

Thanx for sharing your insights again!
My experience with 'Apply to each' performance is similar, specially when you have a second nested 'Apply to each' or even Conditions inside. As far as I remember, the default for 'Apply to each' is secuential execution, and in theory you should be able to speed the process by chaging its settings into 'paralel', but I am not sure about possible blocking collateral effects because of using Excel actions inside. Some other undesired collateral effects can raise also if you use paralle execution and variables (which are global) for calculations inside, as explained here

https://powerusers.microsoft.com/t5/Building-Flows/Apply-to-Each-Concurrency-amp-Variables/td-p/1808...

[Anonymous]

Thanks for the tip.

I tried switching to concurrent and all the Flow Excel updating stopped completely but will investigate further.

I posted a long explanation (showing the Flow steps and critical tricks involved etc) of how I am downloading without 'Apply to Each' but it got removed as spam. I am waiting to understand why from the Moderator before I repost it ... potentially tomorrow.

[efialttes]

Hi again, I think I have good news!

I managed to force PA Flow read one of the hidden properties from Planner 'Get Tasks' you pointed out by means of a Select Action Block!

As you can see in my PA Flow design, I chose to test with 'appliedCategories' ,which I cannot find on 'Dynamic Content' UI list...

...and the result is the following:

Hope this helps

[Anonymous]

I am doing similar - this is ULTRA fast (like 20-30 seconds) compared to more than 1 hour with a traditional flow.

I have an Excel table which has a table of all the Plan IDs I want to download.

I could use "List Plans" instead  but at the moment I am using the Excel table to limit it just to 8 plans (of the eventual 30 or so) and its easy to tweak for trial.

I iterate through all the Plan IDs and use "List Tasks" and generate a CSV file eliminating writing to Excel and using 'Apply to Each'.

Apart from being majorly faster this also overwrites the old CSV file on Sharepoint without me having to delete it first so it eliminates my past practice of having to wipe the data storage Excel table first. 

It becomes separate files rather than one but they are tiny and fast and can easily be combined automatically later.

I will change the Flow from Manual trigger to Scheduled later.

There are several tricks to making this work in the 'Create CSV table'

First trick .... just paste the entire 'Value' set.

Don't be selective. I am not doing them individually,

If you try to customize and use individual fields you won't be able to get them all without an expression as you discussed above.

Second trick ..... ignore the 'Value Assignments' set because this requires iteration like 'Apply to Each' to read them .... and you cant seem to do that in the middle of writing a CSV file.

The 'Value Assignments' array just gets saved in a single column in the CSV and I deal with this later to get all the info.

Then I end up with 8 CSV files (or whatever number you desire).

But once in a CSV file there appears no method of reading direct back into Flow again (but I don't anyway).

As discussed before this is limited by the 'List Tasks' 400 max limit but so is 'Apply to Each' method. Today I moved more than 600+ tasks out of one Plan into 2 other Plans working to get us under the limit.

In Power Query (either Power Bi or Excel - I am using Excel so I can email the entire resulting file with Pivot Tables and other summaries available for Users) I stitch these back into one data set and then do all the data manipulation.

In the manipulation I then split the 'Assignments' downloaded column back into Individual Rows (separating all the multiple assignments if present) and then separate each new row into its new component column (i.e. Assigned By, Assigned Date, Assigned to etc).

I have lookup tables to convert all the Bucket IDs, Plan IDs back to descriptive text as these are unchangeable in our configuration for the next year or more.

I have lookup tables to convert the User IDs for Created By, Assigned By, Assigned To, Completed by back to a Name (this seems way way faster than using 'Get User Details (V2)' each time). As I am the only Administrator people can't add members to the team so no unknown User IDs should sneak in.

I don't bother splitting the Categories array (potential 6 Labels) apart as in our configuration we only want one Label at a time and if there is multiple that is an error.

I can post a screen shot of my data mashup logic if that helps.

Total time today to download more than 1400 tasks from 8 plans (20 seconds), read from Sharepoint, do the data manipulation and present output in Excel .... less than 2 minutes and it can be full auto. I have a checking routine which highlights user errors with a link back to each Planner task and then I can easily fix them either manually or potentially with a mass update flow.

Using Flow and 'Apply to Each' with my previous slow time performance I wouldn't have even written 50 tasks to the download in that time.

And I can do the CSV download in the background multiple times a day at that speed and then Users can just press refresh on the Excel sheet and it will read the last Sharepoint files.

I am still going to try the UI option too ... but it will take me a week or so to get through internal company IT processes.

[efialttes]

@Anonymous 

I cannot find words. Thanx for sharing all your insights with th community!

[Anonymous]

@efialttes yeah when I first tried this method I couldn't believe the time difference and almost fell off my chair.
Happy to share because I learn a lot from reading comments on here too.

Especially as everything I have read for the last 5 months said you can't get Labels out (other than using Planner Export to Excel) and this method gets this and ADDITIONAL information that is not in the Excel Export. It doesn't include Description but I will be able to get that via an automated version of the Export to Excel once I get the UI version turned on within my company.

Sing out if you want me to post any other information like the data mashup steps or similar.

[Anonymous]

@efialttes you aren't going to believe this ..... my heart is pounding.

I had an idea and have tried a similar approach to the above but for GET TASK DETAILS and extracting the entire body text rather than individual fields.

Not only can you get the Task Description, and the Checklist Items (as per the Export to Excel Planner Download) but you can also get for any Task ....

FOR each Checklist item

- Whether it is TICKED true or false!!!

- The USER ID who last ticked it!!!

- The TIME it was last ticked!!!

For ANY files attached :

- The name of the File!!!

- The USER ID who attached it!!!

- The TIME it was attached!!!

And yes I am serious! No one online has ever mentioned this as remotely possible.

I will post a screen shot of the Flow sequence once I have got it fully working.

Its not super fast like the other Flow but its worth it for all the extra information you can get.

[Anonymous]

@efialttes 

Here is an extract of one row from the data that you can get from Get Tasks if you use BODY rather than Flow fields.

Note that you can see all the References and Checklist Items - when they were last modified and by who.

For Checklists you can get true/false status.

For References you can also get file type etc. I assume it will also similarly download web links or Sharepoint files etc.

@odata.context	https://graph.microsoft.com/v1.0/$metadata#planner/tasks('7Bqj0XG8kUGwVmc4Fdda3cgAIWpL')/details/$en...
@odata.etag	W/"JzEtVGFza0RldGFpbHMgQEBAQEBAQEBAQEBAQEBAbCc="
description	MORE HERE
previewType	automatic
id	7Bqj0XG8kUGwVmc4Fdda3cgAIWpL
references	{"https%3A//qalcomau%2Esharepoint%2Ecom/sites/CommercialTeam/Shared Documents/Bucket%2520List%2Exlsx?web=1":{"@odata.type":"#microsoft.graph.plannerExternalReference","alias":"Bucket List.xlsx","type":"Excel","previewPriority":"8586207311427992024PS","lastModifiedDateTime":"2020-02-05T04:10:02.8033814Z","lastModifiedBy":{"user":{"displayName":null,"id":"5cd9d9ff-fd1b-4d7a-bbf2-9eb8ab8cc855"}}}}
checklist	{"37411":{"@odata.type":"#microsoft.graph.plannerChecklistItem","isChecked":true,"title":"4","orderHint":"8586207318@m","lastModifiedDateTime":"2020-02-05T04:01:13.4401685Z","lastModifiedBy":{"user":{"displayName":null,"id":"5cd9d9ff-fd1b-4d7a-bbf2-9eb8ab8cc855"}}},"56860":{"@odata.type":"#microsoft.graph.plannerChecklistItem","isChecked":false,"title":"2","orderHint":"8586207318[o","lastModifiedDateTime":"2020-02-05T03:56:40.7663434Z","lastModifiedBy":{"user":{"displayName":null,"id":"5cd9d9ff-fd1b-4d7a-bbf2-9eb8ab8cc855"}}},"64768":{"@odata.type":"#microsoft.graph.plannerChecklistItem","isChecked":true,"title":"1","orderHint":"8586207319459612364Pq","lastModifiedDateTime":"2020-02-05T04:01:12.6404476Z","lastModifiedBy":{"user":{"displayName":null,"id":"5cd9d9ff-fd1b-4d7a-bbf2-9eb8ab8cc855"}}},"73221":{"@odata.type":"#microsoft.graph.plannerChecklistItem","isChecked":false,"title":"3","orderHint":"8586207318I~","lastModifiedDateTime":"2020-02-05T03:56:40.7663434Z","lastModifiedBy":{"user":{"displayName":null,"id":"5cd9d9ff-fd1b-4d7a-bbf2-9eb8ab8cc855"}}},"93739":{"@odata.type":"#microsoft.graph.plannerChecklistItem","isChecked":false,"title":"5","orderHint":"8586207318<A","lastModifiedDateTime":"2020-02-05T03:56:40.7663434Z","lastModifiedBy":{"user":{"displayName":null,"id":"5cd9d9ff-fd1b-4d7a-bbf2-9eb8ab8cc855"}}}}

[Anonymous · Accepted solution]

Here is the Flow.

To get all the details you must use BODY ... otherwise all you can get is ID or DESCRIPTION.

I have posted two example outputs and both posts have been removed but I don't know why.

[Anonymous]

Screenshot examples showing data from Flow after data transformation in Power Query

Linked back to id of the Task :

Checklist Details (still have to play around to work out the sort order as per Planner view)

Reference Details

Description

I will convert the UserID to an actual Name but the names would not mean anything to anyone outside of my company

[Anonymous]

Below is the final flow to iterate through the 8 plans and 1400 tasks and get the details of Description, Checklists and Attachments.

I had to add in a variable reset to clear the array at the start of each loop.

I will keep this separate from the LIST TASKS version at that takes 30 seconds and this one, with an Apply to Each, takes over an hour.

Therefore if I want 80% of the main data I will run the short 30 second flow and if I want the last 20% I will run this more detailed flow.

[Tjj88]

Hi,

For the Get Task Values action, what do you set as the value as there are no options for me?

I'm trying to replicate this flow you attached.

Thanks

[Anonymous]

Hi @Tjj88 ,
I have improved the method described here a lot since it was posted.

The key difference in this approach is I am deliberately NOT using "Get a Task" as it is too slow especially when using an "Apply to Each" loop and you cannot get all the fields.

I am using "List Tasks" so I am passing in the "Group ID" and the various "Plan ID".

I then take the "Value" from this output and split out each individual item I want to read.

This is very important as ... if using use standard Flow some of the individual fields you want are inaccessible and won't be displayed as options.

So you have to specifically refer to them with the item()?[<field name>] method as shown

It is also important as some of the outputs are essentially arrays ... and unreadable as separate components in Flow .... so I break these apart later in the file.

I then write it back to a CSV file.

I will also say that this method was really useful ... until my plans all started exceeding 400 tasks. This is the current limit of Flow and I haven't found any way around it.

So currently I am getting a similar custom written routine using Microsoft Graph API ... which is more complicated but will do all of the above automatically to Sharepoint ... but without the limitation of 400.

Happy to explain more if required as I have got lots of help on here.