Power Automate Microsoft Forms Connector tutorial with Attachments

[Music] so microsoft forms is a simple lightweight app that lets you easily create quizzes surveys and polls now i am in forms.microsoft.com this is where i can go ahead and design my forms and look at the responses to my forms my forms are basically forms that i have created shared with me are forms that have been built and shared with me group forms are forms that have been created for office 365 groups now let's focus on my forms first now let's go ahead and just quickly create a new form so let's say i'm going to call this my student information form and just by clicking on add new i can start creating my form and there are various types of columns that you can choose from choice columns text columns rating control date you can also use the new file upload option wherein users can go ahead and upload files now in my scenario right here i've gone ahead and created a very simple training feedback form and if i head over here to preview this is what the form looks like so users come in they take training for power platform and then they come here to give me feedback about the training and in this form i'm going to capture a lot of these data points and i have tried to include almost all the different types of controls that you can add in a microsoft form the goal here is to see what we could do with this data now all the information that comes through in a microsoft form so for example my training feedback form if i head over to responses all of this data is stored in an excel file and if i select open in excel it will go ahead and download this excel file for me but what if i would like to do more with this data maybe i want to take it to my system of record maybe i would like to transform this data and take certain action on it the moment you want to create some sort of automation process around your data the answer to this is power automate so let's see what we can do with responses that come in for my training feedback form in this case by leveraging power automate now my use case here is that every time a response comes into my training feedback form i would like to capture all that data and push it into a sharepoint list that's the data source that i have selected for the purpose of this demo the data source that you select can be any data source of your choice it could be sql it could be cds any other data source of your choice now typically we would go ahead and start creating the flow however there are templates available which basically enable reusability so let's go ahead and search for templates related to forms and one of the top most templates right here as you can see is record form responses in sharepoint so let me go ahead and just select this and this will use the microsoft forms connector and of course the sharepoint connector so let's go ahead and click continue and this will go ahead and set the flow up for me now the flow is record form responses and sharepoint of course mine is training feedback so i'm going to call this my training feedback flow the trigger right here is when a new response is submitted it's asking me for the id of my form now if i open this this should list up all the forms that i have created or i have access to now right here is my training feedback form so i've just gone ahead and selected that form the next step is to get the details of each of those responses so what i will do right here once again i will just pick the id of my form which is the same training feedback form and the next step of course is for me to go ahead and start creating item into a sharepoint data source before we go ahead and do that let's first try and understand what is the output that we receive from the get response details action so for now i'm just going to go ahead and delete the create item action i will add that later i will use my favorite compose action which basically creates an arbitrary object in flow and right here if i select this under dynamic content it's going to give me all the details of my form so if i head back to the questions of my form currently i have eight questions and this form because i am using the file upload option this form is only for people who are internal to my organization you can also build anonymous forms but you can't use the file upload in that case i have all those questions right here let's go ahead and start adding these questions so we can see what the outcome is so the first question here is overall how satisfied are you with the training of workshop so let's go ahead and grab that information first so i've just entered some text and i will go ahead and select that dynamic content right here now to get the information from a leica scale each of these options that i have put right here will actually be a dynamic content output so facilitator trainer overall content materials provided all of these will actually be dynamic content so if i head over here you will note that they are all right here as part of dynamic content and i will quickly go ahead and grab those values then i have used the ranking column right here where you can rank options so let's go ahead and grab this information so here's the rank next one is the net promoter score type column so let's go ahead and grab this how likely are you to recommend us so i will grab this dynamic content value next let's pick the favorite module which is the multi-choice picker column and here is the best modules choice type column right here so this is for multiple selections as you can see and finally i have the file upload control so let's go ahead and grab this and this is the dynamic content for the file upload column which is upload the completed hands-on labs content so i'm just going to pick this now this includes all the different questions as part of the microsoft form and i have all of them listed out here in the compose action now let's go ahead and save this flow so this flow will trigger whenever a new response is submitted and once it gets that it will go through the response details grab all the information and put it out right here on the compose action so we can analyze the entire response so let's go back to microsoft forms and let's submit one so i have gone ahead right here and uploaded a couple of documents and let's go ahead and click on submit now the moment i do this this information gets logged in microsoft forms if i head back to responses i have all that information available right here so the flow got triggered when a new response came in if you look at the response details i get all the details right here in the body let's look at the output of the compose action right here so overall satisfaction very satisfied that was the response that i provided here's my text column detail here is my liked response for all those three sub questions here is my overall rating the training date here's the ranking of the expertise it's comma separated my recommendation the favorite modules that i marked and finally the files information is right here now i am getting all of this information in my flow my next step is how do i take all of this information and put it in my data source now first let's look at what my data source looks like in this case which is my sharepoint list so here is my sharepoint list if i go to new i have title where i'll probably record the name of the user who filled the form out overall satisfaction is a text field how can the training be improved a multi-line text field i have other text fields right here the overall rating i'm using a number field and sharepoint training date is a date field that i'm leveraging and i have other details as well favorite modules is basically a choice column and i've made it multi-select and the files that the user is going to upload i want to add that as attachments right here to this list item so now back to the flow run if you look at the response details closely let me go ahead and copy the body and put this in visual studio code now one thing to realize here is that the response is in of course json format and i have details like who the responder is when the responders submit this information now the key here to understand is that all of the different metadata points that this json provides are all in string format if you look at it closely they're all strings like if you look at the choice column that i had the multi select even that response is a string it's not an array it's an actual string if you look at my file question response as well it is also a string even the rating which i have created in my data source as a number field microsoft forms returns that as a string you see it's not returning me as a number flow is strongly typed so when i'm actually leveraging all of these metadata points from the response and i'm trying to push it to my data source it's very important to understand how we can transform these and push them to our data source of choice and our friend in all of this is going to be expressions let's now go ahead and edit this flow use the create item action because i want to create an item in my sharepoint list pick my sharepoint site it's asking me for my list i have picked my sharepoint list and once i complete this this will list out all the different columns that i have created in my sharepoint list now for the title column maybe i want to pick the dynamic content which is the responder's email uh hyphen the submission time so that gives me the title column information now overall satisfaction it's a text column in my data source i will go ahead and pick this dynamic content value right here how can the training be improved so how could the training or workshop be improved it's a multi-line text column i've just gone ahead and picked the dynamic content right here facilitator feedback once again it's a text column in my data source overall rating if you notice right here now if i select overall rating dynamic content is not giving me the overall rating value why is it not giving me that information now if i head back over to my compose action you will note right here when i added the overall rating i was able to find that information right if i search if i go to dynamic content and search for overall rating for the training i'm getting that information but why is that information not available for me right here well the reason is because the data source in this case its sharepoint is expecting it as a number type column whereas the microsoft form is outputting it as a string so right here for the overall rating we'll go to expressions and we will pick float and we can head back to dynamic content and this time we will be able to pick the overall rating for the training so i'll just pick that and say okay training date once again note that i don't see the value for training date again the reason is very very similar in sharepoint it's a date type column if i look at the response that i'm getting from the json it's coming as a string so how do i push this in just go ahead and put training date this time i'll just copy this and just come here and just paste this right here next thing is the rank of the expertise i have created it as a single line text column so if i search for the rank i should find it right here next thing is the recommendation now again recommendation is my net promoter score type column i have created this as a number in sharepoint so once again i will just go here search for how likely are you to recommend this i will copy this go back to the recommendation go to the recommendation column and just paste it now what happens around choice columns and sharepoint now the thing about choice columns in sharepoint in my case this is a multi-select choice column now i know that i have the information coming from my microsoft form but if you look at the sharepoint column right here if i open this this is going to list out all the values and i can go ahead and start selecting these values one by one so i can select powerapps i can add a new item right here and i can say okay power automate so basically i can add multiple values in this fashion and you see this little option right here which says switch to input entire array if i select this this will actually put it in an array format now this is interesting to me because when i'm trying to push these values into my data source versus sharepoint i need to ensure that i'm pushing these values in this format so here's the output that the json is providing for me and it says powerapps and power automate more so it's just a string so how do i go ahead and grab that information and plug it right here that too in this fashion let's go back to that compose action go to expression going to not and available let's click on test and this time i don't want to resubmit the form so i'm just testing it for now so i'll just say just tested with that previous data and if i run this the flow has run successfully if i head back to my sharepoint list note right here that all the values have come through here is the title which is the email id of the responder and the date and time when they submitted it all the other data points are right here here's my overall rating which is that expression that i typed in right there and the training dates right here everything's which is a date and type column so all that information is coming in right here for me now of course the modules are not exactly the modules that i selected i just hard coded it now if i head back to my flow and if i look at that compose action now note right here because i wrote that small expression with said json the output of compose is actually an array so this is an array that i receive but this is not the format that is required for inputting it into a choice column in sharepoint so how do i import it into a choice column in sharepoint so let's go ahead and do this let's go ahead and add a data operation of type select and right here for the from property we are going to pick the output which is the array right which is the array of those choices and now it's asking me to map the key value pairs now what i would like to do right here is go ahead and click on switch to text mode and right here is where i want to perform that mapping so how do i map this let's open and close curly braces let's pick value colon and right here under double quotes i will go ahead and once again go to expression and enter the expression item all i'm doing right here going and grabbing the array that i retrieved i had to use the json function because i wanted to convert string into an array and that's exactly what it did for me and then using that array i am going ahead and transforming it into a type which sharepoint understand which is in this object format now if i head back to my create item action and these were the modules that i hard coded this time i will go ahead and switch this over once again to the text mode this is exactly what i've created in the select operation on top so now i can go ahead and clean all of this up and just pick the output of select this time let's go ahead and run this flow again now if you look at the select action you see the output body is exactly how sharepoint expects it and the create item action has succeeded and in my scenario when i filled out my microsoft form it had the values powerapps and powerautomate if i head back to the sharepoint site here's my test data that's come back in and it has stacked it to power apps and power automate so this is how you can go ahead and tag this to multi-choice type column so now let's go back to the microsoft form and let's try and add an extra question because i want to also cover choice columns which are single select so let's go ahead and add another question here so i'll say suggestions for average session time uh 40 minutes session 40 minute sessions 50 minute sessions or 60 minute session so what is their feedback and what is their suggestion for average session times so here's an additional question that i just pushed in to my microsoft form of course i'm going to head back to my sharepoint as well and i will go ahead and create a choice type column and i will click on save so in this case all i need to do is go to enter custom value and then select the dynamic content which is the suggestion that the user is going to put into place so let me go ahead and save this and i will go ahead and submit the form and let's say i pick 50 minutes i'm gonna click on submit and as you can see my flow has succeeded back to my sharepoint list here's my item that just got created all the information is right here this time i picked some additional favorite modules here's the multi-select choice type column right here and this is the new choice type column that i just created and as you can see that text data is coming right here in my choice column now in my case because this microsoft form is of type my forms all of this information is actually going in my onedrive and right here in my onedrive as you can see i have a folder created called apps and within that there's another folder called microsoft forms within that there is the folder name which is the name of my form which is the training feedback form so all the forms that i create that have associated attachments they will all start lining up right here and all those attachments that are uploaded by users they will all go in different folders depending on how many file attachment type controls i've added in my form in my case i only have one so it creates one folder which is called question and within this all of those documents that the users have uploaded will be right here it will have the name of the document and then underscore the name of the user who has uploaded that document if you note i kept on uploading the same documents as i kept filling the forms out when it finds the documents with the same name it appends a numerical value in front of that to keep it unique for a submitted form i want to grab the respective files that the user has uploaded and then move it to my sharepoint item and attach the files so let's go ahead and do that now once again looking at the json output of microsoft forms right here is the information around my attachment so as you can see it has the name of the file right here now it's a string but it has an array array of objects objects that contain what objects that contain the name of the file right here objects that contain the link to where the file has been uploaded and observe the link the link is my onedrive whose onedrive reza's onedrive because reza has created this microsoft form and here is the link right here it will always be string however within the string it will always be an array even if i upload just one file so how do i grab all of this information from my onedrive now and then move it over to that sharepoint item which is my data source now for sharepoint there is also an action called add attachment and that's exactly what i want to do i want to add attachments i need to pick my sharepoint site i'm going to pick my same list which is travel information the id will come from the create item action so i can just pick the id now comes the file name and the content of the file now once again i will go ahead and add my friend which is the compose action so i'm going to add a compose action and this time i'm going to rename it as well i will rename this to my files right because i'm going to have my files information right here now how do i get that information once again i'm just going to click right here go to expression plug in json the expression function json now notice where my pointer is it's right here i'm going to head over to dynamic content and this time i'm going to go ahead and grab those files which in my case was called upload completed hands-on labs content so i'm just going to pick this i'm going gonna select okay now the moment i do this this will output an array and now for each of the items of that array i want to loop through and start uploading attachments so how do i do that very simple i will go ahead and add a loop so i will add a control which is my apply to each action i will call this so for each of the files that i'm going to get from my files action i'm going to select here and the outputs from my files action is right here so we're going to add an action from the one drive for business connector and the action that we are searching for here is get file content now this requires the unique identifier of the file now if we look back at our json once again we have the name of the file right here and if i keep scrolling on the right hand side we have the link to the file but what i need is the id of the file and right here there's a property called id that gives me the id of that file so i'm just going to copy this i will come back right here select inside the file box go to expression and write the expression item which gives me the reference to the current item in my for loop of files question mark and i will paste the id right here this will now grab the content of the file from onedrive and then i'm going to drag this add an attachment option right here because i have the content of the file now i want to add it to my list item and there could be multiple files that the user has uploaded so let's go to add attachment i need the file name where will i get the name from i have that value right here it's called name so i will go to file name go to expressions item gives me the current running item context on the for loop and i will put the name property right here so this will give me the name and for the file content i'm just going to select file content and select save and right here as you can see the flow has triggered the flow is currently running and because i uploaded a couple of attachments if you look at my for each loop now it's running two times and right here if you look at the files action which is my compose action this now gives me the json of that string type column information from the file control so let me go ahead and copy the json output right here and paste it in visual studio code so we can look at it so right here is that json output the name of the file is right here the id is right here these were the two properties that i'm leveraging and once i have that it will go and grab the content of the file and add that file as an attachment to my sharepoint list item here is the latest item that got created and if i select this item now this is all the information right here and these are those two attachments that i uploaded so as you can see there's some amount of work that is required the magic here is in the magic of expressions all of the expressions that i've showcased in the video today i will plug it into the description of this video so do check it out and if you want to learn more about expressions john lewick and i did an exclusive series on expressions i will put that link in the description of this video as well also if i was to leverage group forms and go ahead and build a group form right here these are forms that are tied to an office 365 group so let's say i go ahead and pick one of my groups which is cat team i can go ahead and create a new form right here so let me call this my so i'm calling this my team feedback survey i will go ahead and just enter something very simple like the name and let's say i also go and pick the file upload type and i'm going to say okay and i'll call this file so let me go ahead now and preview this and quickly just enter my name and just upload a sample file and click on submit now very quickly i just created a form which is associated with my team and if i would like to go to this team and if i actually head over to the sharepoint site associated with this office 365 group and if i head over to the documents library right here you will notice that it creates the excel file for me so every response that comes in gets logged in that excel file this is where that excel file is stored in terms of group form so here's that excel file if i select this it will have that same information right here also where are the files going in this scenario when i pick group forms in this case if i head over to apps if i head over to microsoft forms it's the same concept but instead of one drive it's in sharepoint here is the name of my form and within this i just had one question which was related to file uploading so here's that folder if i select this here is my uploaded file and i can use similar concepts but this time instead of connecting to one drive i will connect to sharepoint because my data source in this case where my files are being stored is sharepoint so that's how you can leverage information from microsoft forms and put it into your data source of choice we covered all the different types of columns that are possible in microsoft forms today if you like this video then please do comment like and subscribe to my youtube channel thank you so much for watching

Channel: Reza Dorrani

Views: 39,051

Rating: 4.9122486 out of 5

Keywords: microsoft forms power automate, power automate microsoft forms, microsoft forms flow, microsoft forms, power automate, Microsoft Forms Power Automate, microsoft forms power automate sharepoint, saving attachments with microsoft forms and power automate (flow), microsoft forms and flow, forms and flow, forms and power automate, microsoft forms attachments flow, microsoft forms flow approval, saving attachments from microsoft forms with flow

Id: kbqXsKPTmZk

Length: 25min 0sec (1500 seconds)

Published: Tue Oct 20 2020