Send email remainders from excel data and Update excel column value using Power Automate.

hello everyone in this video let's see the new requirement here the requirement is to send reminder emails to employees from excel sheet okay and update one of the excel columns using the power automate so here we need to follow say some five steps first is to create a excel sheet table along with the fields serial number employee name emp email um subject reminder date status okay so first let's go to onedrive and create an excel sheet okay so let's go to android check that you have license first for the onedrive okay so here you are able to create an excel sheet with the selected columns and convert that columns into a table first okay first let's give all the columns here say employee name and little bit of space here and give here employee email so that our reminders will go to that particular email here this is not the sharepoint list to give the people field here okay so here give the subject about which the reminder okay so next reminder date okay so finally the column status so because once if we the employee got the email the status will be updated to yes so that it will again will not send an email to that particular employee the reminder okay so now add some data and before that i make this reminder date as a normal date in the excel sheet change the format and you add some data here fill up the excel sheet with some data so the subject can be varied uh see some people may have like vpn renewals and for some people maybe it's birthday reminders like that okay so now add one more row here and once uh you completed adding the data now it's time to create or convert that whole data into a table here you select all the data and go to insert menu insert on the menu and there is an option click table and it will ask to make the first rows as columns here header columns so yes click and click ok and now uh your excel sheet data is converted to table it's just like a sharepoint list so okay so now go to the table design and if you want to change the name change okay generally by default it will show as a table one here so once you change the table name you change the excel sheet name also in the onedrive it's presently showing book one and name it as emp data or something whatever you like emp alerts once you finish the work in excel available which is available in the onedrive now it's time to go to power automate to read the list data to read the two records okay so now go to power automate here and create the power flow from the scratch okay so here the second step is to the third step sorry uh the third step is to create the flow from the scratch okay so anyways but this reminders must be a scheduled one um monthly ones or weekly ones something like that so we need to take a uh trigger like schedule okay you can also mention the name of the flow here and you can give the time and date when this uh the flow must run and repeat every month or week okay so anyways but we will do with scratch here just schedule the uh this flow with the trigger recurrence and here give the monthly ones interval one and frequency month now it's time to get the today's date first okay so to get the today's date we need to write an expression and we have a function here format date time okay so let's add that here by using an initialize variable or compose data operations step okay so click next step and add compose here we can also use initialize variable if you want okay so here write an expression to get the today's date and check here there is a function for my date time and it's showing uh two parameters to enter like so you can since we want this uh today's date you can give utc now and you can also mentioned uh the format of the date here here i'm mentioning uh only month and date okay so here no need of here so but we got a small problem here so let's see what we can do here so maybe we missed a few things here in this writing and expression let's check what is the issue here so anyways first write here and check whether it will work something like this and but still it's showing an error here write again the expression and see whether it works so same thing and i think we need to check what exact problem here okay so let's see let's add expression again format date time utc now and we need to give two brackets here maybe that is a brackets problem previously and a comma with the date format okay let's see still the same problem so why it's opening like this let's give single quotes for the mm dd now it's right okay so now let's go to the next step so with compose we can get this today's date and we can also rename this step like today's date by clicking the three dots on the right side okay there is a rename option available in every step of the power automate here and i'm renaming with today's date so now it's time to read our excel records right so let's find any action is available in power automate so we have a an option here which can read all the rows present in the excel sheet so its name is list rows present in a table here we need to give the inputs like the location so the document library and the file so the table name so once you uh entered all these parameters uh you can easily access the records okay let's save it the flow and if you want we can have a run initial run and check whether we are getting the two records from the excel sheet just it is to confirm whether it's reading the exact number of records available in the excel sheet okay go to my flows and select the flow and run now run flow and you can see the flow history so it is succeeded and now you can see all the green mark tick marks and you can see a list was present and here if you want to see what is the data available in excel sheet you can download and you can see it's in a json format so let's check in the google is there any tool which can convert this json to table so that we can get a clear idea of how many records are available in the excel sheet so now i'm converting the json data to the table so now i can see that there are two records available here okay so now it's power automate read the all the two records exactly available in the excel sheet okay so but the problem here is the requested date is showing a different format it's showing some few numbers but it must show a date right so this is a challenge for now so let's see which can help us in retrieving the correct date instead of a random number given by the json data here and so now we got the two records and now let's uh read it one by one with the new action applied to each okay so in the apply to each give the input as output which we got from the previous step okay here it's is value list of items okay from this list of items we can get the reminder date which is a challenge for us now so we need to change the expression or we need to write an expression to get the exact date what is written in the excel because it is showing a random number right as we checked before so now take a compose action here and add the expression add days okay so that which we can by which we can get the exact date in a correct format so write the expression here and the add this uh let's mention the format of data as mm dd again and here in the place of date we can write our uh name of our excel column okay so let's see our excel column name is reminder date we just need to add this reminder date replace that here okay so now give here so that it will recognize uh this column is a date and time and it will give the exact date instead of giving a random number here now click save so our expression is correct this is a challenging part of this flow we add a function add this okay so now we got exact correct date so write a condition now so in this condition we need to check that in the today's date the excel column date must contain okay it means it is almost equal so if the if the outputs uh contains in sorry if the outputs contains in the uh today's date then if it is the expression is true then we need to send an email okay the today's date is equal to the reminder date which is mentioned in the excel sheet we need to send an email here so we are framing the uh email here and we are giving the subject and now we are writing the body of the email so let's take the employee name from the column employee emp name and here let's add some message and also the subject like say suppose vpn renewal request here so this we can use for all the remainders [Music] within the teams or within for all the employees in the office now we completed the framing of the email so let's save it once and here after this sending email we need to update a column in the excel sheet there is a column in excel sheet with the name email status so once an email goes to that particular person we need to update that saying the email reached that person and update in the excel sheet as s okay so now uh write a step update a row so here we are updating the column excel column so here the ch here the new new thing we will learn is key column and key value when compared with sharepoint update uh list updating list item updating we do we need only just the value no need of key column and key value okay so here we need to write in the key column what the serial number and the key value is serial number here and email status is yes okay once if you done this now save it the flow is perfect now let's check anything is left uh so no nothing is left here so check once the list rules present table if you want or nothing is required so let's check this key column and key value it's correct because we need some id or we need one number to update the unique uh row right that's what for that case we will take this serial number and save it and let's test and once you've done the test you can see that check the excel sheet now and first check the outlook whether the person got email or not and after that we can check the status column value now we have not received any email now so something happened to the date reminder date right so let's go and see what exactly happened in the run flow history so now let's check what is the date format we got earlier there's a problem the applied to each go to the compose and check the date oh we got here four fifty 4 15 and what's the today's date okay okay now uh the the value which we have given in the records is not matching the two-day state so that's why it's not sent an email so we need to give at least one record with today's date so that we can get the email so here we need to give 414 and once it's the column value is modified with the today's gate and it will in the flow it will again let's run the flow and now let's check it is succeeded and let's go and check the email so now you can see this uh it's successfully run okay now you can see still we have not received the email okay let's see the date first what the format we go still it's false i think maybe uh excel sheet has not been updated here uh what uh right the excel sheet uh value it's not taking so let's run it once more so now you can see here you got an email vpn renewal so now it's perf perfectly working so now let's go to the run flow history once and check the today's date 414 right and here apply to each you check the compose and you can see 4 14 so now that's why it's compared the values and it sent the email now you can also see the update happened in the excel sheet with s now go to the one drive and go to the excel sheet here and check the column is updated okay so now you can see this here this is how we can work with an excel sheet updation of column value and get the list records from the excel sheet okay so thanks for watching if you like our videos please subscribe and we will come up with more videos the power automate and power apps and we also give trainings okay so thanks for watching please subscribe to our channel thank you

Channel: Onetidbit

Views: 15,167

Keywords: PowerAutomate, Telugu, PowerApps

Id: dp_Ga7FC_0Q

Length: 22min 53sec (1373 seconds)

Published: Fri Apr 23 2021