Generate and send a personalised certificate to users who pass your quiz with Power Automate!

hi everyone in this tutorial we're going to look at how to set up a process or flow in microsoft power automate that will send a user a personalized certificate via email once they have passed the quiz created in microsoft forms to complete this process you need a number of things the first and most important is a power automate premium license this is because two of the actions in the flow are only available with a premium license at the time of recording this video there is the option of starting a 90-day trial for premium features but the license cost is fairly reasonable anyway it seems appropriate here to give a shout-out to my line manager and digital services at staffordshire university for purchasing and enabling a premium license for my work account so that i can actually do this there are other things you need for this process so you need a certificate in the form of a word document you need a quiz set up in microsoft forms and you need an excel spreadsheet with a table that matches the fields in microsoft forms all of these will need to be set up or accessible to you within your microsoft account the same account that you'll be using to create the flow in power automate whether that's saved in your onedrive or in sharepoint for those documents anyway of course you'll need some form saving forms and as part of this tutorial i'll be building all of these things from scratch anyway but if you want to head start i'll be providing templates for you to use in the description of the video so let's get started so we're going to start with creating the quiz in microsoft forms when you've logged in you'll see this screen with a new quiz button at the top select the new quiz button which will open up a new quiz for you so i'm going to call this matt's tutorial quiz you can add a description as well if you like and then we can start adding in the elements of the quiz so this will be the questions and all the fields we need to collect the data from the user completing the quiz so what we're going to do to start off with is create a new section so i'm going to go to the add new button just here i'm going to select the little arrowhead to open additional options and then i'm going to go down to section and just select that this section will be for the user's information as we need their email address to send them the certificate and their name to go on the certificate as well i'm adding these questions to a section that's separate from the quiz questions to make things clearer for the user but the design of this is completely up to you as long as you enter these questions in your form or in your quiz so give your section this section a title in this instance i'll call the section your details next we're going to add a new question so we're going to add a text question i'm going to click on add new and just select text and with this question we're going to request the name their name for the certificate we also want to emphasize that they check this for accuracy so i'm going to add a subtitle in as well by clicking the three dots in the bottom right of the question editor just here and then selecting subtitle once i've added the subtitle it's just worth checking some of these additional settings so there's no correct answer to this question so i'm not going to do anything there there are no points allocated to this question so i'm not going to add anything in this little points field either but we do want to make this a required question because we don't want them to leave this field blank without them continuing the quiz just so you know all of the questions in this quiz will be required so make sure you turn this button on everywhere when you're finished click away from the question and that will save it so we can now see that it's displaying in the form without the editor and that means it's saved we want two additional questions in this section the next question is another text question and it's a request for their email address which we'll send their certificate to so we'll again need to emphasize that the user double checks this is entered correctly by adding something in the subtitle to prompt this so it's really important that the user enters their email address here correctly and that you emphasize that unfortunately there's no validation option here in forms so you need to make sure that this is accurate otherwise it will make this whole process fail again we'll leave the correct answer in the points fields blank and just make sure this is required the final question in the section is for data protection purposes we need to add in a consent question as the user's email address will be added to an excel spreadsheet as part of the process we can emphasize that the data is being collected solely for the purpose of sending the certificate and it's good practice to remove all the data from the form and the spreadsheet regularly you could probably even build this into a flow to regularly remove the data for you although we won't be covering that part of this today so for this we just need to create a choice question someone goes add new and then choice and then add the consent information into the question field we then need to ensure that only one option is available so we'll just delete option two just here and this option should be yes and we also have to make sure that it's a required question so that if they don't consent they can't proceed with the quiz and therefore we can't collect their data so this section is now complete and we just need to add another one in select add new then the arrowhead and then section this section will contain the quiz questions so i'll just call this quiz you probably have your questions for this quiz ready and it's likely you know how to use forms so i'll just go through the setup of one question and this process works best with multiple choice questions so i'm going to be using choice questions only but in theory you could also use text questions as long as you knew that there was only one correct response so i'm going to select add new and i'm now going to select choice i'll add my question into the question field and then i can add my answer options in the options fields if you need to add additional options select the add option button you need to specify which answer is correct to do this hover over the right answer and icons appear to the right select the tick icon to identify this as the correct option there are two other options to complete here first specify how many points this question is worth i'm going with 20 as my quiz is worth 100 points in total and i have five questions finally we just want to make this a required question so that the quiz can't be submitted without this question being answered all the things we've just gone through are important there's just one optional thing that you might want to consider and that is clicking on the three dots in the bottom right of the question editor and selecting shuffle options and what will happen here is every time a user comes to complete this quiz the options for this question will appear in a different order i'm going to go ahead and finish my quiz off now and then move on to the next part so i've now completed my quiz ready to send to my students or clients and next i need to create the certificates that they'll receive should they pass this test this certificate will need to be a word document design your certificate in your preferred software and ensure that the design is available as an image file all i've done to get my image file is i've altered a template available here in word to get this template set up i then exported the word document to pdf and converted that pdf to an image file in adobe acrobat but of course you can use other tools you can use photoshop canva anything like that to create your certificate design so i've got my new word document open just here and i'm just going to change the layout of it and the orientation of it to landscape just so that it looks more like a certificate and then in this document that i'll be using as the certificate i'm just going to insert my image file into it so there we go this is the image file that i've set up i'm just going to play around with some of the rulers so once the image is inserted into your document it's around the sort of size that you want it to be go to the wrap text tool and you can do it in word just by clicking on the layout options button here in the top right just the top right of the image and i'm just going to use this behind text option in the wrap text tool so that i can move it around a bit easier and so that it definitely stays behind the text that i add to the document so now i've got this to look the way i want it to i need to add in fields for the user's name and a field for the date and time stamp of course you can add in other fields as well if you want to and to do this what we want to do is we want to move the cursor and of course we'll move the image file back if where the cursor moves the image file just move the cursor to where you want it to appear on the certificate so i've just pressed enter a few times to move the cursor down of course just going to rearrange this image file again a little bit more there we go that's about right so just make sure my cursor is there i'm going to change the alignment the paragraph alignment here so it's center aligned you can see there that the cursor now is around the place where the name of the user who's going to complete the quiz is going to end up on the certificate so now the curse is there what i'm going to do is i'm going to go to the developer tab just here if you don't have the developer tab in your ribbon then what we need to do is we need to right click on a blank space in the ribbon and go to customize the ribbon just there and in these word options on the right hand side you've got a list of tools just make sure that this developer option just here is checked and if it's when it's checked click ok then you should have the developer tab up here so now that the developer tab is available select it and then in the control section just here select the second button along this is the plain text content control and this is the only control that will work with the power automate flow so make sure this is the only text tool that you're using so i'm going to click on that to add the content control to my certificate and you can see here it's entered this field just here and at the moment it says click or tap here to enter text when this is added make sure it's definitely where you want it positioned and then you can change the fonts within it so i'm just going to go to home and i'm going to increase the size of the text i might even change the font type as well to something like tahoma and i might make it bold as well so the last thing to do with the field is to go back to your ribbon go to the developer tab make sure that that field is selected and click on the properties button in the controls in these properties enter a title and a tag in this case you'll probably just put name into each and this is how power automate identifies this field in the document click on ok your name field is now set up so repeat this process if you'd like to add any additional fields and i'll do that for the date and time stamp i'm just going to move my cursor again i'm going to click on properties and i'm going to just put date in here click on ok so when your field is set up make sure your certificate is saved and that is saved to your onedrive or onedrive for business account or to a sharepoint document library so i'm going to just save mine to onedrive once it's saved into your onedrive or sharepoint it's ready to go so there's one last thing to create before we get stuck into power automate this is the excel spreadsheet that will be used by power automates to trigger a pass fail process so open excel and add the following fields into the first row which will correspond to your quiz in microsoft forms so we want name email question one question two and so on depending on the number of questions in your quiz so i'll have fi i'll just have five up here of course if you have 10 or more than that you know just make sure that you add a column for each question and then we need two additional columns so we need score and we need one that's unique id the next thing to do is to click and drag on the spreadsheets to select the cells that you've just added and the row underneath and what we're going to do is we're going to make this into a table so in the ribbon in the home tab we want to go to formatters table and we just want to select one of the table styles from here so i'll go for this blue one just here a create table pop-up will appear because you've selected cells in the spreadsheets already the first option where is the data for your table will already be populated we just want to check this box here for my table has headers and then we want to click ok so what we have here then now is we have a table set up within our spreadsheet so there's one last thing to set up here and this is to enable the spreadsheet to calculate the score for the quiz so then it triggers the pass fail process empower automate so we need to just go to in the second row in the score column what we need to do is we need to type in a formula so i'm going to go up to the formula bar just here and i'm going to start off with equals sum and that's because we'll be calculating a total of different cells and next we'll be adding conditions for each of the answer fields so we'll need to repeat this within the formula for each of the questions that you've set up this next part so type in an opening bracket and then if then another opening bracket and enter the cell in the row directly under the header for question one so in this case it'll be c2 so if c2 is our answer we want to type in the equal symbol and then we want to type in the correct answer for the question one that we set up in microsoft forms in this case the correct answer is algeria so i'm just going to add algeria in here in quotation marks so we've got c2 equals algeria in quotation marks and that's the first part of a logical test formula which you can see appearing just underneath the formula field there so the first part of this is the correct response which is basically the identifying criteria for excel and the next part is the value or points if true so the points we want to allocate to this response if it's correct so in this instance it'll be 20 points for a correct answer and then i'll add a cameron and if it's not algeria we want to allocate zero points so i'm just going to enter zero points in here so i've entered the correct response to the first question followed by the points allocation of 20 if they get it right and then a big zero if they get it wrong so after this we just need to close brackets and then we need to repeat this process for each of the question responses separated by plus symbols so i'm just going to add a plus in here because what we want is the sum of each of the question responses so i'm going to repeat this for the next answer so you can just see this again some open bracket if another open bracket we want d2 in this case so for question two equals and then we just need the response to question two so here it would be elijah would and then 20 points if it's correct and zero points if it's incorrect and then two close brackets so again i'm going to go for a plus and i'm just going to complete this now for the the other responses brilliant so now my formula is set up i can just press enter to lock that into the h2 field for the score for this table and all i need to do now is make sure that this is saved in my onedrive or onedrive for business accounts or the sharepoint document library so i'm just going to save this to onedrive it's brilliant now this is saved to my onedrive then that's the last piece of the puzzle before i can head over to power automate and put all of these things that i've just created to work open your browser or a new tab and then type in flow dot microsoft dot com when you're signed in select the my flows option in the menu on the left hand side now if you've downloaded the template i provided you can just import this and work on it but i'll be demonstrating how to start this from scratch so at the top of this page click the new flow button and select automated cloud flow so here type in the flow name so we'll call this power automate certificate tutorial and then select a trigger for which we'll use when a new response is submitted in microsoft forms if it doesn't appear in the list here then you can search for the trigger in this field above the list select your trigger and then click create you've now started your flow and you'll see the trigger that you've just added on its own at the top of this page so in this trigger we need to pick a form to use as the trigger so here we'll be using the quiz that we set up earlier so click on the pick a form field and select the quiz that you created from the list so mine was called match tutorial quiz so i've selected that and now it's populated that field so now that's done we need to create a new step so select the new step button just here and then we need to find the next step so let's use this search field to find the get response details action let's see it's just here once you've found it select it in the list and you'll now see it's been added to the flow so now we need to populate the fields in this action by clicking on them in form id if you click on that field we need to find the quiz we created again so it will be matt's tutorial quiz and in the response id field we'll be adding dynamic content so dynamic content appears once you've selected the field and it shows you all the options you can choose from related to the steps in your flow so here we just see the one option and that's the response id for the quiz so we're going to select this and it will populate this response id field so next we're going to save the flow i'm just going to do that by clicking on save in the top right here it's a good idea to save your flow regularly so you don't lose anything i won't mention this again after this point but it's just a good idea to keep on top of it so now we need to add the next step so i'm going to click on next new step again and this is the add a row into a table action for excel so i'm just going to search for that add a row just here at this point i want to say thanks to stephen taylor for his tutorials which helped me to figure out this part of the process check out steven's channel for some great ed tech tutorials so i'm going to click on this add a row into a table action just here and once i've added it we need to locate the file that we want to populate which is the excel spreadsheet that we created earlier so what we want to do is we want to click on the location field and here i'll be selecting onedrive for business because that's where i've saved the excel spreadsheet i'm then going to set the document library which here is onedrive for me my onedrive always appears as the first one in the list if you get more than one one drive click on it until the next field show what you want them to so just make sure one drive is selected in there next what we want to do is locate the file that we've just set up in this file field so i'm going to click on this little folder icon just here and it's going to bring up a list of all my folders in my onedrive so i'm going to go to the folder in which i saved the excel spreadsheet and i'm just going to select it here it's called power automation tutorial so now i've added that in the file has been located the last thing i need to do now is to select the table that i set up in the table field so i'm going to click on that field and the only table that i set up will be this one table one so i'm going to select that and populate that field now what you've noticed from what i've just done there is that the column headings for that table now appear within this step so i need to go through each of these fields now and populate them so i'm going to click on each field and i'm going to add the corresponding dynamic content from the form for each field so here in name i'm going to add please enter the name you would like displayed on your certificate in the email field i'll select please enter your email address and then i'll go through the rest of these questions important here is that we leave the score field blank but we do want to complete the unique id field so i'm going to click on that and i'm going to add the response id dynamic content just here so this step is now set up to populate your excel spreadsheet based on the responses to your quiz let's add a new step so this next step that i add is optional but i've added it to ensure that the flow has time to process and populate the excel document as following this we're going to have to draw the data back out of the excel document specifically the score that the excel document should have calculated once the fields were populated so if you want to add this search for the delay action and i'm just going to click on delay schedule just here and then i'm going to add in some buffer time so i'm going to set the count here just a 10 and i'm going to set the unit to seconds by again just by clicking on this field that drop down menu appears 10 seconds is likely to be more than enough time for the process to complete and for the spreadsheet to populate now after the delay i can add the new steps so i'm going to click on new step and this step that i'm going to add is a get a row action for excel so i'm typing get a row and we just want this here so get a row and this is how we're going to access the row and draw in the score information from the form submission this is important because this will act as the trigger or the condition for the next part of the flow so here we'll need to find that excel document again so set the location select the document library locate the file select the table now we want to specify which row we want to get from this excel spreadsheet so what we're going to do is we're going to refer to the unique id of the submitted quiz which we populated into the excel document this is so that the row and the information returned to the flow are unique every time so in the key column field select unique id you can see here this is a list of all the columns in the spreadsheet in the table so i'm going to click unique id and in the key value field i'm going to select dynamic content and i'm going to select the response id dynamic content from microsoft forms so the next part is where it gets interesting now we need to tell power automate to do one thing if the score is above the pass mark in this case 80 and another thing if the score is below the pass mark for this we need to add a new step and this step is condition control so i'm going to click new step and you see here condition control is actually right at the top this gives you branching options based on if a condition is met if yes it will do one thing if no it will do another so we need to set this condition so in order to work this bit out i got some help from the youtube channel of anders jensen it's definitely worth checking out anders videos on power automate because power automate recognizes anything added to this field as text data rather than as an integer and excuse any misunderstanding here but what we have to do is for the flow to recognize it as a numeric value is to add in an expression which we can do by clicking on the field choose a value and then where the dynamic contents options appear we now select the expression tool just here so from what i understand it's the float expression that works the magic here and the full expression is float and then it's outputs so opening bracket then outputs and then it's another opening bracket and then with an apostrophe we're going to type in get underscore a underscore row then we've got a close bracket just here then i'm going to add in a question mark and then i'm going to add in a square bracket square opening bracket an apostrophe body slash score so from what i understand the get a row part refers to the get a row action in the flow and the body score is the field from the excel spreadsheet that is go that is referring to so it's bringing in that cell from the spreadsheet bringing in that text value that text data and converting it to a numeric value instead that power automate can recognize so now i'm going to click on ok and it's added that expression to my condition so the next thing to do is to specify in this drop down menu that the condition is set to be greater than or equal to the number that i put in this last field and in this last field i want it to be 80 because that is the past mark and of course if your pass mark is lower or higher put that mark in there instead so this condition is now set up and we can add steps to each outcome based on the user's score so we'll start with what happens if the user fails you can do a number of things here but for me it's important to acknowledge the attempt and provide feedback so i'll add in an action to send an email to the user if they don't meet the passmark so i'm going to go to add an action just here and i'm going to type in send an email you can see it appears here at the top of my list of actions we can now start populating these fields in the to field you can add dynamic content to it so just by clicking on add dynamic content to the bottom right of the field and the dynamic content we need to add is from the microsoft form and it's the response to the question asking the user for their email address so i'm just going to select that here please enter your email address and that will send this email to that individual next we want to specify the subject so type in here what you want the subject of the email to be in this case i'll put test outcome and then you can type out the body of the email in this body field just here and you can add dynamic content into the body depending on what you want the email to say so for instance here i can put dear and then i can put name from the excel spreadsheet if i want to and then i can type out the rest of the email as well as adding dynamic content for things such as their name the score you could even put all their responses in here if you wanted to you can also use the formatting tools to emphasize certain parts of the email just like this and you can also add hyperlinks so for this email specifically i'm going to add a hyperlink to the please click here text it will take them back to the form if they want another attempt when you're finished with this step this part of the condition is now complete so if they don't pass they will receive this email with your feedback now let's turn our attention to the yes condition if they pass we want them to receive a certificate so we'll focus on this process now so add an action to the yes condition and this first step we want is the get file content action so i'm just going to type that in there and because we're working with a file in onedrive we're going to use onedrive for business but of course you can also use sharepoint if you've saved your files to sharepoint instead so i'm going to click get file content and then i'm going to search for the file that i set up earlier this will be the certificate so i'm going to go to the certificate template that i set up i've clicked on the field and i'm just navigating to where that certificate template is you'll then see that the file path is populated into this field so next we want to add another step this time we want to add a populate a microsoft word template step so i'm just going to type that in this is the first of the premium features that we'll be using in power automate the ones that we need a license for and we'll be using this step to add the user's name and the date and time to the certificate select that to add it use the top three fields to locate the file within one drive so i'm going to click on location specify onedrive for business document library onedrive file by clicking on the folder now once you've completed these fields if a document has been set up correctly you'll see the content control fields that you set up within the certificate in the action now so this will be the name and the date and other things as well that you might have added in here as content controls so i'm going to click on the date field first and i'm just going to add dynamic content in here so i'm going to add from microsoft forms just here underneath the heading get response details the submission time and that will add in the date and time stamp for this submission then i'm going to click the name field and i'm going to pull in the name from the microsoft form as well so please enter the name you would like displayed so this step is now complete when a user passes the quiz the template will be populated with their name and the date that they completed the test now we need to add a new step once the template is populated we actually need to create it so i'm going to add an action and i'm going to type in create file and again because we're using onedrive for business i'm going to use this one but you can also use sharepoint so i'm going to click on create file onedrive for business and what we want to do here is use these fields to specify where we want this certificate to be saved so i'm going to click on the folder of the top field i'm going to navigate to where the certificate is and this time instead of going into the folder i'm actually going to select the folder here so make sure you actually click on the name of the folder where you want this certificate to be saved so i'm going to click on power automate tutorial and you'll see that the field is now complete in the file name field the naming convention is your choice you can use dynamic content to help you give the file a unique name if you want so i'm going to click on the field and using dynamic content i'm going to add in a field for the name of the individual you could also for instance use the unique id from forms if you want here instead and then what i tend to do is i add in a space just after the dynamic content in the field in the file name field and then i'm just going to type in certificate and then i'm just going to add the file extension dot docx just to be on the safe side in the final field where it says file content we need to add the dynamic content microsoft word document just here this is the template we've just populated and what we want to do is we want to create a new file with this content so i'm going to click on that and that field is now populated at this stage of the process the user's unique certificate will have been generated as a word document and saved as a file within your onedrive so what we want to do next is convert it to a pdf just so it looks a bit nicer a bit more official so next we're going to add another step by clicking add an action and this step is the second of the premium features that we need to use and it's convert word document to pdf so if it doesn't come up if you type in the actual name of the action into the search field then the best thing to do to find this particular action and the one before where it was populated microsoft word template click on the premium button just here and then you might have to click on word online and you'll be able to find those premium actions in here so the reason that we need to create the word document before this step is that this new part of the process the conversion part of the process identifies the word document that we created as the one to convert so select the location and document library so i'm going to click on the location field and select onedrive for business and then click on document library and select onedrive and in the file field this time we're not going to click the folder to navigate to a document because the file that we've created before this flow's run doesn't actually exist so what we want this flow to do is select a file that hasn't yet been created but will have been by this stage in the process so click on the field itself to bring up dynamic content and in the options under the create file heading look for the path dynamic content just here and select it so what that will do is at this stage of the process it will pull in the word template that you've just created as a word document and then convert it to pdf so this part is now complete and the next part of the flow that i'm going to add is optional so i use this part so that a copy of the pdf is saved to onedrive as well just in case there's an error with the user's email address that way it can be sent manually if needed so add a new action and this will be a create file step i'm going to select create file onedrive for business i'm going to select the path of the folder and because we're selecting the path of the folder again we're not going into the folder i'm going to select the text of the folder this time and populate that field with that path in the file name field add dynamic content to identify the file so again i'm just going to enter the name of the user who submitted the quiz and type in space and then certificate and i'll add the file extension of pdf and now in the file content field i'm going to click on the field and i'm going to add the pdf documents dynamic contents so there we go this step is now complete purely optional but just in case you want a copy as well it's worth saving a pdf version to your onedrive so now we come to the final step of this flow which is sending the certificate to the user so here we need to create another send an email action so i'm going to click on add an action i'm going to type in send an email i'm going to click this one here office 365 outlook so i'm going to use the same approach as the other email so i'm going to click on the to field and add dynamic content and do that by clicking the add dynamic content button to the bottom right of the to field and i'm going to populate that with you please enter your email address response from the microsoft form next i'm going to specify the subject of the email and then in the body of the email add in the contents for the email make sure if you add dynamic content for the score within the body of this email that you select the right one you need to select the score from the get a row field so once you've added your email what we need to do now is attach the pdf certificate to this email so at this point i'm going to click the show advanced options item at the bottom of this step there are several additional fields here but the main ones to focus on are the ones for the attachment this is where we attach the certificate so in the attachments name field you can either type something in here it could just be generic such as certificate.pdf or you can add dynamic content so i'm going to click on the field and underneath the create file 2 heading i'm going to add the name content because that will give the document that's attached then the exact same name as the pdf that's been saved to your onedrive so in the attachments contents field next we want to click on this to add dynamic content as well and we're going to scroll down to where it says pdf document just here and click on that and this adds the pdf content to the attachment so that's it once you've finished that step your flow is complete and it's ready to go so let's save your flow one last time click on the save button top right hand side and we'll test this one out complete this form now just to test it i'm going to select my answers and i know which ones are correct so i'm going to make sure i get 100 and now i'm going to click submit the moment of truth if you go back into power automate and go back into my flows click on the name of the flow you've just created if your flow works you'll see the outcome here in the run history as successful you can see mine is currently running i'm just going to refresh it and there we go i can see that it succeeded so i'm just going to go into each of the different elements of the process and just show you what they should all look like so i'm going to go first into the excel spreadsheet and you can see here that my table has been populated with my name my email my responses has calculated the score for me and it's given me a unique id for this form so this part of the process looks like this if i go into my onedrive folder i can see that my word documents and my pdf have both been created by the process as well this is my personalized certificate and if i go into my inbox i can see that i've received an email with my certificate attached if i click on that i can see the body of my email with my dynamic contents my name and my score in there and i can also if i want to just preview this certificate to see that it's definitely personalized for me at the date and time of my submission so there we go this is to confirm that i have completed this power automate flow the flow is complete and working i hope that you found this helpful and that you get to send lots of certificates to those taking your quizzes let me know in the comments if this worked for you and thanks very much for watching

Channel: thethirdline

Views: 32,944

Keywords: Microsoft, Power Automate, Forms, Assessment, Automation, Flow, Excel, Certificate, Award, Success, Achievement, Pass

Id: yLvDFRWATfo

Length: 41min 59sec (2519 seconds)

Published: Tue Aug 24 2021