Forms Responses to Excel and Outlook with Power Automate - Beginners Tutorial

if you're using Microsoft forms you know it's quite hard to automatically get the results out because we can go to responses and then open in Excel but we'll have to do it like once a day and then manually save that Excel sheet let's create a nice automated solution in power automate that can automatically do the trick so I go to Power automate and then I click create here I will say I want an automated cloudflow because I want this flow to run whenever we have a forms response so our flows trigger that is when a new response is submitted we can also give it a name since I want this review will be about my teaching capabilities I'll call this instructory view like this and then I'll click create now I'll create a new form that I can use here so I go back to forms then pick a new form this will be a relatively simple form but it can work on whatever form you have and it's so so nice so I'll call the form instructor review like this I will add three questions to the survey so I'll say text this one will be watch your name I'll add a new one it's not that important what's in it it's just the approach that you need to keep a close attention to here I'll say how was the instructor and finally I'll add a new one that one will be ratings that will be star and here I'll just say rate your instructor by stars and we have five different levels so that's it that's our form we can now find it in power automate so when I say pick form we can see the instructor review here in the drop down so this flow will run each time we get a new review but we also want to do something about it and I'll pick new step and I'll say get response details we need to have the information that the user provided so here I need to have a form idea and this trigger up here that produces an ID so I will say instructor review and then the ID of this one we got up here that one we can trace backwards to the instructor review so if I click here choose the response ID so now we combine the form idea that's the instructive review with a response idea to get this exact response that we got up here now let me just save it here let's close this let's manually do a test then we'll talk a little bit about the results and what we're going to do so now if I go back to instructor review then I'll say collect responses here you can see that I can copy the link out so if I go to a new window and then just paste in the address so what's your name I'll say andos it's a little bit cheat I reveal myself how was the instructor great and then I'll rate myself with 5 Stars anyway if you want to rate me let me know in the comments I'll be so glad for every feedback that you give me thank you so now I'll say submit if I go back to Power automate you can see my flow ran successfully if I go into get response detail that is the lock here you can see I have my name in how was the instructor the ratings that one will be a five star surprise and here I will also have the responders email that is important we will collect the emails if the user is signed in to Office 365 right now it's only my organization that can answer so I want to have some of this information printed into Excel so I need an Excel sheet so here I'll say new Planck workbook and it's important that it's either in your OneDrive this your Dropbox or SharePoint so we need to have access to the Excel sheet by the cloud I call it book two but shouldn't we give it a better name so this one I will call instructor view on this and I'll Place mine in documents that is documents in OneDrive I can find create it down here in the file explorer in the OneDrive if I wanted that instead but let's stick with this so I'll say submission time name email I'll watch your instructor like this and over all impression then I will just Mark a sorry I will do this and then I will just do [Music] like this so now we have our headers one thing that we need to do so we can use this Excel sheet by power automate is to format it as a table so Mark the headers we just had it I know and then go up here format as table if you use the desktop version of excel you can also Ctrl T to get from as table choose whatever design you want it's not really important I prefer the blue ones say that your table has hit us it does and then we click ok so now we can start to collect our responses into this Excel sheet so what do we want to do go over here and then click it we have the trigger then we get the responses and we can just start mapping it to its Excel it's that easy so I'll say add a row into a table make sure you pick the right one I pick Excel online for business that's my subscription that's a work account you probably have the same we'll pick that one then you want to say you want to say I need to look into OneDrive for business but here I can also go to a SharePoint SharePoint site if I have my Excel sheet there I'll say OneDrive for business that's fine a document library that will also be OneDrive and now I can find my file you can click here and since I um made it in the root of the Run Drive I'll have mine in instruction review on this that one will be here in the default directory but say that let me just delete this I can also click this here I can navigate inside my OneDrive so that one will also work but for now let me get the instructor review on this back and here you'll see I have a table that one is called table one I could have named it better and the way to do it is to go back here and then I will scroll up and find results I just thought of disappeared so here I can just click in here click table design and I can rename it here right now I will not do it because then I'll have to redesign my flow but you can fine do it if you prefer to have a not a name table one imagine you have several tables that you want to work on in a power automate flow so the submission time if I just click here you can see that this Dynamic content over here I have the submission time if this doesn't show up it might looks like this then you can just press this add Dynamic content it will show up so say submission time in name pick what's your name that was the name of the question from this one here what's your name and similarly the email well that one was it was a little bit hidden but Microsoft automatically get that email so we'll get it here if they are locked into Office 365 in your company so how was your instructor that one was radio instructor overall impression and that was rage go instruct and here you can see I mapped I just need to say the how was the instructor and radio instructor and over here it says how was instructor and overall impression so um let me just go back here so how was your instructor and that one that should be how was the instructor so and a good practice is to create similar names in Excel and forms then it will get a little bit easier but really not a problem so right now this flow this simple flow we only have three steps and I'll show you that it works and then we will refine it a bit and create a little more advanced solution so I click save I test it manually I go back here and then submit another response so this one will be my clone unders two uh heals a good but he will give five stars as well then he can click submit so here you will see that my flow is running and we added a flow into the table check mark we can go back here and you will find that we have our all our feedback we have it here so if I just do this we can see it here we have the submission time the name the email Powers you instruct and the overall impression it's that easy to automate with power ultimate but let's say that I want to get an email each time I get a bad review so say that I'm not sure I know you are not the type that giving me one star but imagine that a person comes to my course and think this is really bad I'm reviewing honest with one star since this is an online course I want to know that immediately by an email so I click edit now to collapse this I can just click here and then we want a new step so here I want to have um I need the rating because I need to I need to convert it into an integer when we get it out here it comes into in into asset text and we want to have it in an integer format so we'll convert it into an integer and then we can say if this rating is equal one then we want to send an email and let me just click new step and here I'll say compose like this and then I can click compose so right now I will just use the rating so and that one don't pick the one under Excel pick the one under get response details because that's the direct runs we got here and here I want to say rate your instructor so I'll go here and then you can see here that we have an expression and this is because um this field informs it's it is named it is in the body and then it's named r5050e31 F2 and I can show you where that comes from so let me just save it because this is an important lesson in power automate as well so if I go back here into my flows let's just pick one of the two locks that we created so if I bake this one I go in here and then if I go into gitresponse details now you can see here we have a body if I go to show row outputs you can even see it here in the body you can see these weird fields that is this is what's your name actually in the instructor view that has a code this is a static code so we can refer to that code and we refer to that by saying we want the output of this action here and we want to go into body we want to go in here in this R8 blah blah blah and then we want to get whatever we'll have here actually it's it's this one because we want the five out but the syntax is similar so let me just go back to my flow I can edit it here and we have our compose so we want this expression sometimes when you want to peek at the code it's very nice if we can get it out very very easy we can just click these three dots here Peak code and you'll find it here if I just from the O don't pick the F at and to the last part of the hard bracket Ctrl C to copy this one out then click done now we can delete the compose we just wanted the expression out because then when I click new Step here then I'll have a condition now I want to say if this one is equal to 1 then I want an email to be sent so here I'll say condition and up here I need to create a an expression because I can get a rating here but I need to convert it to an integer first and we will do that with an expression so what I will do here is that I will say it and I'll say parentheses start it automatically made the last one and then I just Ctrl V it in there's actually another way to do it because if I click Ctrl set I'm in here I can move to the dynamic content and then I can get the radio and structure you can see that this actually also gives me but sometimes that's not that's not possible so I wanted to show you where it's actually from I think that's a very valuable lesson so now I click okay we insert it here so if that one is equal to one then I want to go into the yes Brands otherwise if it's a good review I will not do something about it currently so over here I will just send myself an email so I'll say send an email like this and I'll send an email from The Office 365 Outlook usually that's the one you want to pick so make sure you pick the one from Office 365 Outlook send any so where do I want to send it to well I want to send it to myself so I can search for myself that one is here the subject well I can say bad rating and then we can make it a nice thing we can make a dynamic email so we can say you got it that rating from and then we want the name that the user specified so if I just go down here in the dynamic content over here again if it doesn't show up click this app Dynamic content you get a bad rating from what's your name that's the name that the user provided with the email and this will not always be available but we really want it so we can write that one and just say sorry do you want your money back so that's it and we can click save and see that this whole works so now if I test it let's do a manual test let's do another review so right now it runs I'll submit another response this one will be on this tree and actually it will not be honest honest will not rate these things that bad so it will be Donald tree he's not that happy about my course so let's send ourselves an email so that then we want to give ourselves one star and then send it it really hurts to give myself one stop but that's just the way it is now our flow is running you can see it's still runs because this condition is grayed out right now Ram this expression is true and let me just open pause the video open the email and show it to you I open up the email on the other screen I just didn't want to share it with you and I can drag it in that's it that's how easy it is to automate Microsoft forms with power automate the next video I prepared for you about power automate is right up here you should go click it see you

Channel: Anders Jensen

Views: 12,084

Keywords: power automate forms, power automate forms to email, power automate forms responses, power automate forms excel, automate microsoft forms, anders jensen, power automate, microsoft power automate, tutorial, learn microsoft power automate, how to use forms with power automate, office365

Id: ZzAmAgxYRxw

Length: 15min 56sec (956 seconds)

Published: Tue Oct 11 2022