How to save Microsoft Forms Responses & Attachments to SharePoint Lists or Libraries & Send Email

hello everyone reza here in this step-by-step tutorial video i will show you how we can save microsoft form responses along with multiple file upload controls to sharepoint lists document libraries folders and we will also send an email with the form responses and the attachments so let's check this video out in action [Music] microsoft forms allows us to quickly create surveys quizzes and polls that can be accessed anonymously or within the organization to start working with microsoft forms head over to office.com and in the app launcher i have the option for microsoft forms you can also head over to open all apps and search for microsoft forms i will start with a new form my scenario here is an incident reporting form i can start adding the questions associated with my forum i have different column types to choose from i'll start with the title of the incident i'll select text i will make this a required field i'll add a second question the description of the incident so i'll select text i'll enable long answer and not make this a required field next i'll add a choice column this will be the category of the incident i can add my options right here i've added four options i'll add another option for choice this will be the office location where this incident occurred i've added my three office locations here i'll add the incident date and finally i would also like to provide an option for the user to upload files so i'll pick file upload since i'm creating a standard microsoft form and since i want the user to upload files a new folder will be created in my onedrive since i'm the creator of this microsoft form and the files uploaded by the responders of this form will be captured in the specific folder i'll say okay i'll call this photos here i can decide how many files can the users upload let's say i limit it to three i can also define the file size limits for those individual files i'll make this a required field i also have the ability to go to more settings and decide which file types the user can upload in my scenario i'll restrict the user to only upload files of type image this now completes my incident reporting form i can click on preview forms are responsive this is the form on the desktop experience and this is how the form would look like on the mobile app experience you can define a theme and for sharing since i'm leveraging the option for upload file it will not allow me to make this form an anonymous form it has to be a form specific to my organization so i'll click on preview and i will fill this form out i have the ability here to upload files i have a couple of sample pictures i'll go ahead and upload these i will submit my form my response will be recorded in microsoft forms now all of these responses that are being recorded including those files are being stored in the user's onedrive the user who has created the form in my onedrive i have a folder created called apps in there i have microsoft forms this will list out all my forms here is the incident reporting form that i created those images that i uploaded are right here they are being loaded in my onedrive i also have the ability to open and see the responses of this form in an excel file and these are all the results that are captured now our scenario here is every time a new response is submitted i would like to grab the details of the response and i would like to store them inside a data source in this case the data source i will select is sharepoint to store the responses i will create a new list i'll create a blank list i will call this incident reports i will add columns in sharepoint so i can store the data that is coming in from microsoft forms i have the title column where i can store the incident title i'll create a multi-line of text column i will call it description to store the description of the incident i'll add a date column called incident date incident category and office location these were choice options inside microsoft forms i can do something similar in sharepoint by creating a choice column i've added my four choices for the category and i've added my three choice options for location the photos that are coming in through the file upload control these i will add them as attachments to the sharepoint list item itself and i will create one additional column here type single line of text i am calling this submit a email i will store the email address of the user that is filling out that microsoft form now that i have my list created my next step is to automate the process of saving the microsoft form response and those attachments directly to this sharepoint list for automation i will head over to power automate head over to templates and search for microsoft forms there are templates that are pre-created for us that we can leverage one of them is record form responses in sharepoint that's exactly my use case so i will select this template i will click continue and this creates the flow based on the template the first step is to connect to your form this will list out all your microsoft forms i have my incident reporting form listed i will go ahead and select this this is the trigger action of the flow so when a new response comes in for the incident reporting form then this loops through all the responses that are received now it could be possible that there are multiple users responding at the same time so this flow will take all those requests and it will loop through each of those requests it then grabs the details of the response that the user has provided and once again we have to provide the id of our form pick my incident reporting form and then the next step is to start creating the item in sharepoint here i will pick my sharepoint site in case your site is not listed you can head over to enter custom value and plug in the url of your sharepoint site pick the list from my sharepoint site my list is incident reports it will load all the columns in my sharepoint list this is where i need to start mapping my microsoft form controls or questions to my sharepoint list columns so sharepoint column title from dynamic content the microsoft forms action called get response details will give me all the details of the response that the user provided i will pick incident title for description i will pick the incident description incident date currently it shows me one option here called submission time that's when the user submitted the response i can head over to see more and pick other options i had a column for incident date so i'll select that category value is a choice column in sharepoint and these were the four options i provided these are the exact same options that are available in microsoft form so to pick the value coming in from microsoft form head over to enter custom value and then select the column associated with the category in my case incident category and i will do exactly the same thing for my location choice column enter custom value and pick my microsoft form column office location submitter's email that information is also provided under dynamic content it's called responders email i can rename my flow and i can click save the flow is now saved and the flow is ready to go from this point onwards any new response that is submitted in microsoft forms the flow will trigger and create an item with the details of the form in sharepoint however i would also like to upload the files that the responder has uploaded in my question called photos these files are being stored in my onedrive right after create an item i will add an action called get file content i will pick the get file content action from the onedrive for business connector here i need to provide the identity of the file if you look at dynamic content i have the photos column listed right here and there could be multiple files so this returns an array of information a collection of files plus it returns the response in string format so we need to convert this into an actual array that power automate understands and then we need to loop through each and every file start getting the content of that file from onedrive and then add it as an attachment to the item that we just created to do that right before get file content i will add an action and i will use the apply to each control this creates a loop i will rename this to apply to each attachment and to get that array of data i will head over to expression and use the json function which expects a parameter of type string for that i will head over to dynamic content and select the photos property from the microsoft form action get response details i will click ok now inside this loop i need to go and grab the files from onedrive so the get file content action i will drag it into this loop and here i need to provide the unique identifier to that file and that is available in this json array itself and to get that head over to expression item question mark under square brackets and single quotes there's a property called id click ok this will grab the content of the file i need to add it as an attachment to my created sharepoint list item i will add an action add attachment from the sharepoint connector once again connect to my sharepoint site connect to my sharepoint list i need the id of the list item where i would like to attach this file that i can directly get from the id property of the create item action so i'll select this the name of the file the json output has the name of the file as well once again expression item off the property is called name i'll click ok and to get the content of the file that we've already fetched from onedrive so i can go and select the file content property this now completes my flow i will select save i will submit this form i'll upload the files in this case pictures and i will submit the response the moment the response is received power automate has triggered for that one response it's gone ahead and grabbed the details of the response if i head over to show raw outputs this is where i can see the response details who the responder was when did they submit the response and all the other pieces of information the attachments are the files those are right here we can see how it comes in string format but it is an array that has a lot of details around the file the name of the file the link to the file the id of the file that's what i leveraged to grab the content of the file from onedrive we then created the item in sharepoint and then for every attachment i went ahead and grabbed the content of the file from my onedrive and then started uploading them as list item attachments to the incident list item that got created and if we head over to sharepoint here is an entry that has come in automatically from power automator if i select this i can see all the details and here are those files that have come in as attachments now there can also be scenarios wherein you have multiple file upload controls in your microsoft form let's add another one i'll pick file upload this one i'll call additional documents are limited to two and i will not make this a required field now my requirement is that all of these files that are being uploaded whether they are photos or whether they are these additional documents i would like to add them to that same sharepoint list item as attachments to deal with the new file upload question that i added to my microsoft form right after the trigger action i will add an action here i will initialize a variable i will call this var files this will be of type array right after getting the response details i will add an action set variable and we will set that files variable to i can go to expression json off my question which is photos which has that mandatory file upload control i'll click ok now for the additional documents since they are not mandatory i will have to add a condition to check to see if it is not null the value would be an expression json this time dynamic content i will pick my additional documents question click ok this is not equal to here i have to ensure i go to expression and enter null so if this is not equal to null i would like to add the files that have been uploaded in this additional documents question i would like to append it to my variable that i created called var files and to do that i will have to loop through all of those files so i'll add and apply to each loop here the expression json off the question additional documents and inside this loop i will use the append to add a variable action and to that variable called var files i will append the current item and to do that i'll head over to expression and use the function item i can straight away use this variable in the apply to each attachment loop so i will remove this expression of mine and replace this with the variable where files which has all the file information all the steps after that are the same i do not have to make any changes i'll go ahead and save my flow let's try and submit another incident i've uploaded one photo and i will add some sample files i've uploaded a pdf i'll click submit and here is the new entry that has come in to my sharepoint list if i select this i have the attachments that are coming in from both those attachment controls that's my image file and that's my pdf document that i uploaded now there are also going to be scenarios wherein you want to add those files to a sharepoint document library so let's create a document library i will call it incident files incident reports that are being created in my sharepoint list each list or library in sharepoint has a column called id it's the internal unique identity of that record in sharepoint since i am planning to add my files in a sharepoint document library i would like to somehow relate the list with the library and i will relate it using the id so in my document library i will add a column of type number and i will call this incident id my files that are coming in could either be photos or additional documents if i would like to even categorize them in the sharepoint document library i could do that i'll add a column here of type choice i will call this category and i have gone ahead and defined a couple of categories photos and additional documents now in power automate to add those files to my document library i will add another action here called create file i'll pick my sharepoint site and then for the folder path i'll select on the show picker option and select my document library which is incident files the name of the file would be the expression item name and the content of the file for dynamic content i can search for file content and pick the file content that is coming from my onedrive action one thing to note in a document library you cannot have multiple files with the same name so it could be possible that many users are using that incident reporting form so they could potentially upload documents with the same name so to ensure that they are unique where i have the file name added right before that i can use an expression for the date and time i can use the expression utc now or i can use the expression function good for creating a random good right before the file name once the file is created i would want to update the properties of the file because i want to add the incident id and the category associated with it to do that i will add an action update file properties connect to my sharepoint site my incident files library the id of the file that i would like to update will come from the create file action dynamic content property item id the incident id will be the id of the list item that i created so from the create item action i will pick id and the category value will be dependent upon whether the attachment or the file has come from my photos question or my additional documents question now to know from which question it has come from i will go to enter custom value and plug in the following expression if equals the equals condition first parameter is item of reference id if the reference id of the item equals to if you look at any previous flow run and the get response detail action and head over to show raw outputs those files that you upload per question here are those two file upload options these files have a specific property called reference id and these reference ids are unique for those specific questions that i added so for photos this is my reference id so i will copy this i will paste that reference id right here so my result would be photos else my result would be additional documents i will click on ok to plug in that expression and i will go ahead and save my flow i'll report another incident i've uploaded an image and a pdf document i'll click submit the response is received and once that flow runs here is the new entry that has come in to my sharepoint list the id here is five and if i look at the entries that have come in my sharepoint document library here are those two files they have the incident id mapped so i know which incident this relates to and i also have the categories depending upon which question in the form they belong to now one neat thing you could do in sharepoint to kind of relate the library and the list item together is use something known as list formatting so as an example i'll add a column here single line of text i will call it view files and save and for this column i'll go to column settings and format this column go to advanced mode i will share this json code the link will be in the video description i'm just going to paste it right here and all you have to do in this json code is replace the url here with the url of your document library so for example here's my document library so i will copy the link to my document library head back to the column formatting and paste my link right here and click save now the advantage here is this creates a link to those documents based on the id of the list item so if i'm only interested in looking at documents related to this specific entry which is oil spill if i click on view documents it will redirect me to my document library and actually apply a filter on that specific incident which is the incident id5 let's submit another response here i've uploaded one image and two additional documents and i'll submit my response as soon as the result comes in id is seven if i select view documents it will redirect me to my document library and filter out based on id seven and here is that photo that i uploaded and these are those two additional documents one more scenario is to upload it into a document library that has folders i want all of those file uploads to go into these specific folders in my attachments loop i will add an action create file in sharepoint pick my sharepoint site the folder path this is where i want to make it dynamic depending upon where the file is uploaded to photos and additional documents those are the questions i will click on the show picker my library is called incident docs i will click on show contents and let's say i pick a folder called photos so you can see how it generates the path to it incident docs slash photos but what i need here is to dynamically add that path and that is something we already did for the category value when we were checking to see the attachment was related to which question so i'll do something very similar i will select in here go to expression paste that same expression here if the attachment is related to photos so go to the photos folder if not go to my folder called additional docs so i'll just rename this to additional docs click ok the first part i will remove and right before this i want to paste the path to my library so i'll add another expression concat slash the name of my library slash and i'll concat this with the result of my expression the remaining logic is the same as before let's submit another response i've added a photo and a couple of documents i'll click submit the photo that i uploaded is in the photos folder and the couple of additional documents that i added have gone into the additional documents folder so you have full flexibility to store the response of the form store those files either in list item attachments document libraries or folders your choice now microsoft forms can also be created for specific groups if you scroll to the bottom there's a section called my groups it will list out all the microsoft 365 groups or microsoft teams that you are a part of and you could create microsoft forms for those specific groups those are known as group forms so here i can pick my specific group let's pick power team i'll click on new group form i'll call this training survey i'll keep this form simple create a text question training name and here i'll add a file upload control these files because they are group forms will be stored directly in the sharepoint site associated with the microsoft 365 group i'll say okay i'll call this training files i'll make this required now if i was to preview the form and respond to this form i've uploaded a couple of documents i'll click submit if i head over to the sharepoint site that is associated with this microsoft 365 group and if i head over to the documents library here there's a folder called apps and in here there's another folder called microsoft forms here is my training survey group form and the files that i upload are right in here let's try and automate this with flow but this time we will send the response in an email including those files i'll go to templates search for microsoft forms there is a template here to send an email whenever a response is received in microsoft forms pick the send an email template click continue i need to pick my form id you may or may not see your form listed here so here's the trick go right to the bottom and go to enter custom value go to your group forms select your form in the url itself right at the end there's a property called form id this is the id that is required so i'm going to copy this and paste that form id in here get the details of the response once again enter custom value i'll paste that form id and then i want to send an email now as part of this template there's some check to see if it's an anonymous survey or not i'll keep it simple i'll get the email action out of here and i will delete the condition at the bottom so this will send an email let's say to me as the training manager so i'll pick reza these are the details of the response that has come in i can get the dynamic properties from the details i had training name so i'll pick that now i need those files and those files are stored in sharepoint so once again i'll initialize a variable called ver files of type array and i will loop through all the files that are provided by the user in the microsoft form use the expression json and the question that was related to the file upload control was training files so i'll select that i will add an action here called get file content using path so i'll pick this my site address that would be the sharepoint site related to that group the file path i'll use the expression item of link now in an email if you go to show advanced options there's this attachments section here you can send multiple attachments now to understand the schema of how the attachments are expected to be sent to the send an email action i'll just give a sample name i'll call this my attachment name and my content will be attachment contact just entered sample text i will switch here to input entire array to see the format and this is the format that it expects an object that has a property called name that has the name of the file and a property called content bytes that needs the actual file content not a string so i will copy this object and this should be an array which i've already created a variable of type array and inside my loop i have the content of my file so let's add an action append to add a variable which is where files i will paste that object here name it's a string so right in between these double quotes i'll go to expression and use item of name and content byte that i will get from the get file content using path actions dynamic content called file content this will start adding all those files to this variable which is of type array and it has this format which is exactly what the send and email action expects so here i'll delete all the text in this very important to make sure that you have switched to input entire array and here for the attachments all i have to do is plug in that variable of files i'll save my flow let's test this out power automate training i've uploaded a couple of documents i'll click submit i'll head over to my mailbox i have received the email the email has the details of the microsoft form there is the title that the user entered and here are the attachments and these are those attachments that i uploaded to that form control this will work for any file type if you enjoyed this video then do like comment and subscribe to my youtube channel and thank you so much for watching

Channel: Reza Dorrani

Views: 193,875

Keywords: microsoft forms, SharePoint, save forms attachment to sharepoint list, save attachments from microsoft forms to sharepoint with power automate, microsoft forms attachment power automate, microsoft forms and power automate, microsoft forms attach file, microsoft forms file upload sharepoint, ms forms, power automate, how to, save microsoft form responses to sharepoint, microsoft forms send email with attachment, reza dorrani, microsoft forms and flow, microsoft forms tutorial, flow

Id: K-hiDOPAG-4

Length: 34min 38sec (2078 seconds)

Published: Mon Mar 14 2022