Send Task Summary Table to Unique Users using Power Automate #powerautomate #sharepoint #tasks

hey guys welcome back in today's video I will show you how you can use power automate flow to send a weekly task reminder summary to different users so stay tuned and also I'm wishing you all a very very happy New Year 2023 thanks for watching my videos and thanks for subscribing stay tuned [Music] [Music] foreign so in today's video I will show you how you can use power automate to send a weekly task summary to different users this method that I'm going to walk you through can apply to any task list you can use a task list created in SharePoint or you can use Microsoft to Do's or you can use Microsoft planner or even you can use in Excel if you are maintaining your task in Excel doesn't matter the only thing is matter that you need some sort of information in the task list that we are going to use to schedule the task summary email and also filter the data okay so for today's demo I'm going to use a task list that I created in SharePoint so this is my task list as you can see work progress tracker I have uh different tasks here let me switch to the list View as you can see here I have work item that is a task title some other detail key columns are assigned to who the task is assigned currently and also the progress this is my status so whether the task is completed in progress not started and some other column with due date so when you're working in your task list the column that you absolutely need assigned to and the due date because these two columns we are going to use to filter and schedule the task summary email good enough okay so the requirement is that as you can see here there are different people different tasks assigned to different people I don't want to send them email every day instead I want to send them a summary email weekly or monthly or daily whichever schedule I prefer with all the tasks that is assigned to those users excluding the one those are completed and making all making sure that I'm also highlighting any work that is past you and need to be addressed okay this is our base requirement so we have the list ready now let's jump onto the power automate and we'll start creating the power automate flow okay so I logged into Power automate flow and I'm going to create a new flow because we are creating this summary email flow I'm going to pick a scheduled Cloud flow okay because we need to send it weekly okay so this is my flow and what I'm saying that this flow will run on each Monday at 10 10 am click create okay so flow created now the next step is going to depend on what kind of task list are you using if you are using SharePoint then very much what I'm going to do you can follow if you are not using SharePoint and for example if you are using planner or Excel or Microsoft to do you just need to make slides changes to query the task information Okay click on news tab because I'm using SharePoint so I'm going to select the SharePoint you can select the data source that you are picking for your task list okay and I'm going to pick the get items because what we are trying to do we are trying to query all the tasks from the task list that we are using select the site address okay so I selected my site address and also my list name now here we're gonna be little bit creative okay so as I said at the beginning we don't want to get all the tasks from the task list instead I only want the task those are not completed because for them I need to send the reminder so then I need to use a filter query and what I'm saying in the filter query progress this will be your status column okay as you can see here my status column is progress so if you are using a column name as task status or status or whatever make sure that you are using that column internal name not equal to completed single quotes Okay now this text is what you want to exclude so in my case I'm using completed in your case you can use some other status that you want to exclude make sure that you are passing those so that's the one filter then we have the order y due date this is your task due date column so in my case I have the due date internally because I want to order these tasks by due date so I can order them correctly in my email okay that's the only thing you need this will get all the tasks that you needed perfect Okay now click on the next step so we are able to query all the tasks but if you look at the task list here there are different people have multiple tasks assigned to them so now if you if I look what I'm getting this is what the SharePoint is gonna return from my flow as you can see here I have different users with multiple tasks assigned what I need to send the email I need the unique users okay so the first thing that we need we need to get or extract all the users from the output so I need to get all of this okay and how I do that I'll click on Next Step select so what select does it's going to select a particular column values from your array and what array we are talking about the get items values this is the response that we are getting from the get item so select the value in the map Enter key so this is nothing but a name that you are giving to the output that you are collecting okay and in the value what we are mapping it to we are mapping it to the email address of the user so I'm gonna look at the dynamic content here and look for the assigned to email okay and if you look at this what it's saying item assigned to email click save and we're gonna quickly run it just to see the results okay so flow completed and if you look at the select as you can see here I am getting all of my users email from my task list okay so our half part is done now we need we don't need the entire list instead what we need we need the unique user so I'm going to click on the next tab now we need to somehow find the unique email addresses from the output of this select okay so what I'm going to do here I'm going to say compose okay and in the import go to the expression search for Union okay and this is again a function in power automate and what it does it take two different collection and it will give you an output of those two different array or collections but what it does actually it actually give you only the unique between those two okay so here what we're gonna do go to the union select Dynamic content and select the output of your select command previously and then select again yes so you are saying Union of same or a two time this is the trick to find a unique value so if you ever need to find the unique between two or a just pass those two array to the union and you will get the output click ok test now if you look at the result you can see I got all the unique users now the next thing is actually formatting our email right if we are trying to send the email we need to format the email so it looks pretty good so for that what we're going to do I'm gonna add another compose and because we are sending this task summary as a table we need to style it and how I find The Styling you can find your table style or the CSS for your summary table uh from anywhere just a very simple exam if you go to the W3 school right here they have all the different HTML and the CSS example and if you go to the CSS table and table Style and click on try it yourself this will take you to a screen where you can see the different table styling right so as you can see here an example of table here the main thing that you need to style your table is the style tag right and this you if you know the HTML and if you know the CSS you can create your own as well okay so if you have this style already with any other table that you have created previously HTML table or css table just copy that and paste it in your power automate flow if you don't have it you can start here this is an example this will give you a table like this and you can start working on that like for example changing the background color alternate row color or anything okay so what I'm going to do for our flow I'm going to use this style and if you want to copy this I will keep it available this flow so you can download and use it but this is nothing crazy going on here a simple style tag inside that I'm just giving an ID reference here so that I can use in my table and basic information like what is going to be the form but what is going to be the tasks table rows and column background color font color and all of that right so you need this to style your email email response it's still simple it's not that hard if you have this style okay The Next Step that you need you have your CSS ready but you need to create a structure for your table right that bought all different columns you want to include in your email summary and what information you want to send so for that what I what we are going to do we are going to create a variable and you need to initialize a variable I'm going to just call it okay so this is my email HTML this is type string and this is where you define your table now this also if you go here in in the example So This Is How They are defining the table a table is structure of rows and column correct so here what we are doing in our flow we are saying our table I'm giving IED task that you can very much no because I am using task ID here if you don't want to use ID you can just say table and not use this hash okay so table ID is task T hat this is the header and I'm saying these are the different headers or columns that I'm going to use in my table title that is the task title description category priority project reference due date and status and closing it so you need to Define your structures this is how you define your table structure for your email okay so far so good I'm gonna just click save so now you have all the base ready to start creating your first summary email click on news tab okay control apply to each so what we are trying to do now we are trying to go through each email address that we got in this this Union so unique users whom we need to send the different emails and find the task for those users and then send them an email okay okay so apply to each output of this compose because this is the compose that giving us the unique users okay so now we have done that now what we need we we got our unique user we got into this this Loop of going through each user now we need to filter the task response that we got for each users that we have okay so here what I'm going to do I'm going to use uh action called filter array okay what it does it just filter your array based on the filter criteria that you're providing so what we are filtering we are filtering our response the get items perfect okay and what is the criteria so what we are saying we are assigned to email that is in my list equals to the the unique email that we got okay so you need to again go back here and then search for assign to email sign to email equals to what you need to do search for the supply to each that's the name of your Loop select the current item okay now this current item is not going to give you the email it's going to give you this entire thing user column email so what we need to say give me the user so what you can do here select this entire thing copy it Ctrl C go to the expression paste it so you will get something like this items apply to each then put a question mark user click ok now this is what you need or this is what will give you the user email so now we got the task for each users in a loop now what we need to do next I'm going to set the variable okay and which variable we are setting the same one that we have initialized here and I'm going to set it with the same thing now you might be wondering why we are doing it again because we are doing this in Loop and I don't want this variable to have any of the previous informations for example it is running for V I got three tasks the variable HTML is going to have all the three tasks so when I'm sending it to the next person I want to reset that variable so that I will have the clean table okay so you need to do this step so you set the variable and and keep remember you need to remain inside the supply to each okay so now we have to set the variable now we need to go through each task for this particular user and assign it to this variable again okay so same thing I'm going to control apply to each and this time I'm going to say the output of this filter array okay this is the filter array select the body now what you are doing here you are going through each task for each users click add action again go to set variables and then what we are going to do append to a string variable so now what we are doing here for each user each task we're gonna append the task in detail to the variable that we have created for this HTML so we already Define the header now we are adding a row for each task okay and because this is HTML how are you going to do it you're gonna just start typing TR like this and in between these two TR you will put your value or information this should match with the number of column that you have now for each row you need to define the number of column that you have in your headers you know in our case we have seven so I need to create 70D okay so this is the structure for my row I have one row and I have seven columns now what we need to do we need to pass the value for each column the first column is the title right so click in between of these two TD go to expression and in the expression same thing you go to the dynamic content in the very end you will see the loop name here apply to each two if you rename it then you will find the rename name I'm going to select the current item just to show you now this current item is going to have all the columns right whatever columns we have selected we don't need all the columns so what we're going to do we're going to select this entire thing go to the expression and this will give you this little function items of apply to each two now after that you can put the question mark bracket and then you can pass the column name that you want the value from okay so my column name is title so for now you can just copy it and keep going to each of the next column so next one is the description okay the next one is the category if you are using a choice column then if you just pass the column name it won't bring the right information because it's a choice column it can apply to choice or also the lookup column okay so for choice or the lookup column what you need to do after you pass the column name you still need to go ahead and do another thing and save value so the column name then you complete the bracket another question mark another bracket and you will say value this will actually Bring the actual value of the choice or your lookup columns okay so I updated all the seven columns that I have updated my expression to pass all the value and there's a couple of things that we have discussed number one is on the title I just don't want to send the title as a text instead what I want it to be linked to the task so say if someone has got an email click on that link they should go to the task directly okay so for that what we're going to do we're going to create another function on the title here and we're gonna say concat okay and here we're gonna just do a little bit of stml so concat single code what I'm doing here I'm creating in an anchor tag here so you can follow the same thing it's href equals to double quotes and you're gonna close a single course then another comma and you can copy this same logic that we are using to get the title here another comma and instead of title we're gonna pass another column that is curly link what this column does this column will give you a direct link to this particular item okay so this way you can have your link ready and then you're gonna space single quote double quote delete the last single code if you got it and single quote back here okay and don't worry I'll paste this formula so you can easily copy and at the very end after your title value you're going to put another comma single course and you're gonna close your anchor tag and click update so this way you can have your link column ready to go okay good click save okay so this part is done there's another thing that we have discussed of highlighting a particular row if the task is passed you okay so for that what you need to do you can just go to your TR that's your row okay and just after TR put in space what we are doing here we are putting an inline CSS for each row and checking if the due date is passed or not so click there go to expression and we're going to write a quick formula here so if greater right this is what we are checking if the current date is greater than or not UTC now okay so that's the first parameter comma now what we are comparing with we are comparing it with the due date okay so same thing you are going to do items then you need to pass the name of your Loop that was applied to each to this one okay so what we are saying if a current UTC now that's the current date is greater than your due date then what should be what we should be doing if it is true right we are going to pass a inline style so single quotes Style we are setting the background curve for the entire row and you can select the color that you want here I am selecting a yellow color okay and if it is not if the condition of current rate is not greater than or past the due date then you can just do nothing single quotes click update okay so now you have everything ready to go click save so this apply to each two this should go through each task for this particular user and they should only have this append to string variable one action okay because now you keep adding the different task to this table close it okay so next step that we are going to add make sure that you are not adding inside the supply to each two so close it and then click on add an action what we are going to do now we are going to close that HTML table that we have created so search for the variables append to string variable and search for the HTML and here we're gonna just close the table that we have created okay so this part is done so now we have our HTML table ready everything is ready the only thing we need to do now send an HTML via email so for here I'm gonna search for send HTML or send email to you can easily find the two this is where this is going to your current user in your first Loop right the current user that you have so go to the email address and you can easily go to the expression click on Dynamic content expression and paste that okay subject last summary you can just put any now in the body this is where you need to be very creative okay so you can type any text here whatever you want just add the text if you want any text in your email the actual table how you're going to create so because the power automate sent an email you can understand the HTML you can start with CSS tag that you have defined remember we have created one CSS this one right this is the style the first thing you are going to select your output of your CSS or the style okay so this is coming from your compose two okay so this is your style after that you need to pass the actual HTML okay so first you will pass your style then you will pass your HTML and that's it this will gonna take care of creating this beautiful HTML team okay and then you can add some more text if you want click save let's run it okay so flow completed successfully let me go back to my email address and see if we got an email or not so we got this email and as you can see here right beautiful email with all the formatting that we have applied the the two positive tasks are highlighted I have a link if I click on this task it's going to straight take me to the task dog course if you have formatted this task list and use powerapps to make this task form looks beautiful you can directly go there now one more bonus thing I like to show you okay so this was the sending email in Outlook what if you want to send a similar table into your team's chat okay you can do that also I'm going to click another add actions and search for post and we're gonna select post message in a chat or a channel now remember one thing we are not going to use an Adaptive card so this is just without making so many changes in our flow we can still use what we have done so far and send the same table in your team's chat okay so flow bot chat with flowbot because I'm going to send it to a particular user recipient is going to be the same recipient that we are using for send email so you can just copy this and here the message is very similar to this one so no change output and your STM HTML that we have created because this message can also understand the HTML now one thing to note is this may not gonna apply all the CSS that you have applied okay but it will still send you a good looking table and we will see so click save I will log into my team so I can show you okay so I'm gonna rerun the flow again let's wait for this to complete and it will send again an email and also a message in your chat as you can see here I got one new message here let it load I also got the email as you can see here look at that so as you can see here some of the styling like background color and all of that has been lost but you still have all the information you also have the link that we have created so even from here if I click it will take me to the SharePoint for that particular task right so all the Deep linking is still working so you still have very functional message that's going into the Outlook and also going to your team's message so that's all for this video I know you might be wondering that how we can send this as an elliptic card that I will save it for the next video leave the comment if you like to see this last summary going as an Adaptive card to the team's chat stay tuned keep watching keep learning thank you

Channel: Deepak Shrivastava

Views: 9,248

Keywords: power automate, microsoft power automate, power automate tutorial, planner tasks, power automate style email body, microsoft flow email, microsoft flow, power automate tutorial 2022, power automate sharepoint, power automate format email, power automate html email, microsoft flow email body formatting, power automate html table formatting, style html table power automate, create html table using power automate, send task summary, tasks summary table email, deepak shrivastava

Id: vMeQf3cawB8

Length: 24min 45sec (1485 seconds)

Published: Wed Jan 04 2023