Overview of completed tasks per day and who completed them

Copper Contributor


Hello, I would like to receive a daily overview via the Power Automate of all tasks completed that day and who completed them (set them to completed). How can I achieve this? Thanks

9 Replies
No One an idea for this?
Hi, Thomas!
Are these SharePoint tasks in a single SharePoint task list? Or Planner tasks in a single Planner Plan? Or something else?
What have you tried so far? Are you stuck somewhere specifically?

@Sandy Ussia 


Hello Sandy, what is the difference between tasks in a single SharePoint task list? or scheduler tasks? The tasks are in Planner in different buckets. I've tried this so far but unsuccessfully, how do I get the name/userid of the user who completed the task? Thank you for your help

 

Thomas1730_0-1659191539606.png

 

No One a solution ?

Hi @Thomas1730 

Thanks for the additional info! Sorry, I've been on vacation :)

 

You're right that the completedBy person isn't shown in the Dynamic Content of the Filter Array result, possibly because there isn't always a completedBy value? But you can still retrieve it by typing it in. If you look at the output of the Filter Array action, you'll see there's a completedBy element - and even if a JSON element doesn't appear in the Dynamic Content UI, you can still refer to it manually. You can use the same syntax to get that user ID as the built-in dynamic content placeholder uses to get the createdBy user ID: that is,  ['completedBy']?['user']?['id']

If you enter that as an expression in the Get User Profile action, it will get the profile of the user who completed the Planner task. Power Automate will automatically put that into a loop (for each item in the filtered array). 

To test something like you're looking for, I initialized an Array variable at the top of the flow, and then used Append to Array within the loop to get each task's Title and the CompletedBy user's Display Name. Then outside the loop, created an HTML table from the array variable, that you could then put in an email.

SandyUssia_0-1660514053610.png

 

(By the way, I guess you'd also want to filter on the Completed Date as well as the Percent Complete, right?)

 

Does this help you...?

 

Sandy

@Sandy Ussia 

Hello Sandy, I hope you had a nice relaxing holiday. Thank you for your detailed answer, unfortunately I'm still at the very beginning and don't quite understand it yet. I've now tried to recreate your screenshot with my data, but I can't select or enter a name for the array variable, I probably did something wrong before? Can you look at that again and maybe paste your whole skein as a picture.

You also write that I should look at the output from the filter array, how can I display these? Many Thanks

 

Hi @Thomas1730 

 

Sure, I'm happy to provide more details.

 

About the array variable: Any time you wish to use a variable in Power Automate, you must initialize it before using it. This defines what type of a variable it is. I usually like to define any variables at the beginning of my Flow. So in this case, I'm suggesting that you add an "Initialize Variable" action to initialize an array called something like CompletedTaskArray, and define it as an Array-type. For example:

SandyUssia_0-1661472535740.png

About viewing the output of the Filter Array step: By this I mean that after running your Flow, you can view the result of each action. After your Flow has run, you can click on it in the Run History on the "dashboard" page of that Flow, like so:

SandyUssia_1-1661472786271.png

This will open a view similar to below, where you can see (hopefully) little green checkmarks on each step that completed successfully.

SandyUssia_2-1661472883747.png

You can click on each step to see the details of its inputs and outputs during that Flow run. Here, I've clicked on the Filter Array action:

SandyUssia_3-1661472978888.png

What I was referring to in my message was that if you scroll down through the Outputs section of the Filter Array, you can see all of the elements (e.g. planId, bucketId, title, etc) that are output from getting the tasks. This data is in JSON format, displaying each element name, and its value. One of those elements is completedBy, which contains sub-element user, which contains sub-elements displayName and id, as shown below. It's that id value that you can use to retrieve the user with the O365 Users action.

SandyUssia_4-1661473212111.png

Learning to read JSON formatted data will be very helpful for you as you learn more about Power Automate, as basically everything is JSON underneath :)  

 

Here is my entire test flow...

SandyUssia_5-1661473418904.pngSandyUssia_6-1661473467945.pngSandyUssia_7-1661473516471.png

Does this help you better...?

 

Sandy

 

 

 

@Sandy Ussia 

 Hi Sandy,
Thank you for the explanations, that definitely helps me a bit. Regarding JSON, yes, I have to and want to learn a lot more about that, but I'm a bit lacking in the approach. I've recreated your example flow and unfortunately I don't see a username, the output is always "zero". I looked at the output as you explained it, but I can't find my error, unfortunately I can't find the output of "Get user profile" either
What am I doing wrong?

iTems looks like that by me:

 

items('Auf_alle_Anwenden')?['completedBy']?['User']?['id']

 

I noticed another problem, all solved tasks are then output, but only those that have been added since the last execution of the task should be output, or if I would always run the task early in the morning, only the tasks that were solved yesterday.

 

Thank you for your patient help with a beginner.

 

@Sandy Ussia 

 

I found my misstake, so i become now the User who completed the Task.

 

So only is open, how i can only the completed Task from yeasturday and not all completed Task.

So i think, i use a second "Filter array" With value and completed DateTime, but how can i say here "Yesterday"?

 

 

 

 

Thank you for your Help