Build a report with new, closed and open Planner tasks (Power Automate)

“Can I use Power Automate to produce a daily report of the Planner tasks? I’d like to see the new tasks, open tasks and the tasks closed today.”

---

If you use Planner to keep track of the daily tasks, it might be helpful to have also a regular report. How many tasks were created today, how many were closed, and how many are still opened from the previous days?

While Power Automate offers triggers when a task is created or closed, they might be not the best solution. These triggers will trigger a flow for each new/closed task. If there’re 2 new and 2 closed tasks per day, you’ll get 4 emails per day. It a good solution if you’re working with time critical tasks, but often one email per day is enough. One email sent by a scheduled flow.

Get the tasks

Start with the ‘Recurrence’ trigger, and add the ‘List tasks’ from your plan right after it. You can’t use any filter in this action so just take all the tasks.

But because you don’t want to include all the tasks in the report, you’ll filter them a bit with the ‘Filter array’. There’ll be one ‘Filter array’ action for each of the tasks group, and you’ll use the output of ‘List tasks’ in all of them.

Filter new Planner tasks created today

The first ‘Filter array’ will filter only the items created since the last report. The flow will run once a day, therefore, it’ll filter the action created today. Add the ‘Filter array’ action with a filter where the task ‘value Created Date Time’ is equal to today. Since the value contains also time, you must remove it with the formatDateTime(…) expression.

formatDateTime(item()?['createdDateTime'],'yyyy-MM-dd')
is equal to 
utcNow('yyyy-MM-dd')

Once you have only the new tasks, you can turn them into an html table for later use.

Filter Planner tasks closed today

When filtering the closed tasks, you’re again interested only in the tasks closed today. You don’t want all the closed tasks in the Planner. That means you’ll need multiple filters in the ‘Filter array’ action. One filter by the date, and the other one to get the completed tasks.

You can recognise the completed Planner tasks by their ‘percentComplete’ value. If the task is completed, the value will be 100 (percent).

@and(
    equals(
        item()?['percentComplete'], 
        int(100)
    ), 
    equals(
        utcNow('yyyy-MM-dd'),
        formatDateTime(item()?['completedDateTime'],'yyyy-MM-dd')
    )
)

Since you’re using multiple filters, use the advanced mode available in the ‘Filter array’. The result can be again converted into html table. You can even include a link to the Planner tasks.

Filter all open Planner tasks

The third, and for this post also last, is the filter to get all the open tasks. Every task that is still in the Planner and is not completed. As already described, the completed tasks have ‘percentComplete’ value 100. If it’s less than 100, the task is still open. Again, you can turn the opened tasks into a table.

Send the reminder email

And now, when you prepared all the tables, you can send an email with all of them.

Summary

When you use Power Automate to create report from tasks in Planner, you’ll need to understand the ‘Filter array’ action. As some other actions, e.g. Teams actions, ‘List tasks’ won’t allow you to filter right away. It’ll give you all the tasks and you must filter later. The flow in this post will report tasks with today’s date, by the design it should run after the working hours. But you can easily adjust it for “yesterday’s” report if you remove 1 day from utcNow().

Also the result html tables might need a bit formatting, depending on which task data you want to display. The most challenging will be the format of the ‘assigned’ users, but that’s a topic for another blog post.

---

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

First Name

Email address

Zero spam, unsubscribe anytime.

Leave this field empty if you’re human: