How to Sync Microsoft Excel with Microsoft Forms Responses Using Power Automate

okay you want to sync your Microsoft form responses with an Excel workbook so I'm assuming you already have your form set up and you already have a workbook this workbook is saved in OneDrive but you could also save the workbook in SharePoint now your first step in the workbook is to create the column headings that are going to capture the responses for all the questions in your form then once you've done that click into one of the column headings go to the insert tab on your ribbon and click on table tell Excel that you already have headers by ticking this little option here click ok and then give the table a name so I'm going to call this responses and press enter to store the name so make sure this is saved and once you've got your Excel spreadsheet saved on your OneDrive or in SharePoint you need to log into your Microsoft 365 account click on this app launcher button top left and then select power automate click on create here click on automated cloudflow here and then give your flow a name then you've got to choose your flows trigger and for us that's when a new response is submitted via Microsoft forms and then I click on create I need to select a form for the trigger that's this form here Excel 365 training event and then I click on new Step choose an operation search for form select Microsoft forms and then select get response details form ID you need to select the forms name again response ID you just need to select response ID down here then click on new step then select Excel online business and in this list select add a row into a table in location you need to select where you've saved your workbook so I've saved it in OneDrive for business but you might have saved it in SharePoint for example document Library again that's OneDrive or whatever library is stored in file I need to navigate to where I've saved this Excel file on my OneDrive select the workbook that you want to sync with your form and then select the table name within that workbook responses now you have to map these columns within the responses table with your form questions if I click into the first column down here you'll see all the questions on the form so I want to map the name column with the question what is your name Department with the question what is your department date with the question which date would you like to attend diet the question do you have any dietary requirements submission with the submission time then I just click on Save and I've created my flow so let's see if this works I'll fill in the form and I'll press submit might need to reopen it to get the first submission and here we are we've got the latest response from our form let's try again click on submit and again I get the latest response okay now we've got a few responses we can easily do some analysis on this data for example I might want to keep a tally of how many people have booked on each of these dates so let's add another response for the form submit it 'll be nice if this pivot table automatically updated whenever we get a new response so what I could do is create a little Office script that does that so if I go to the automate tab here I'm going to record a script I'm going to click into this pivot table right click and then refresh and then stop recording it'll give the script a name refresh pivot table So within my flow I want this script to automatically run whenever we get a new response from the form so here's my existing flow I'm going to add a new step I'm going to type script in this search box and down here I can see an option for run script location OneDrive for business document Library OneDrive file I need to navigate to the workbook that I want to run the script in and in the script drop down and select the script that I created to refresh the pivot table now I'm going to click on Save so we'll add another response via our form I'll click on submit and you can see that Bernard's record has been automatically entered here but it's also updated the pivot table the only thing I need to do here is format these dates as British States and we're good to go okay that's all I wanted to cover in this particular video hopefully that's useful if it is please give me a thumbs up and subscribe and I'll see you next video foreign foreign

Channel: Chester Tugwell

Views: 10,532

Keywords: how to sync microsoft forms to excel, microsoft forms to excel automatically, how to sync microsoft excel with microsoft forms responses using power automate, microsoft forms sync all responses to a new workbook, how to create a microsoft form with live responses in excel, microsoft automate forms to excel, ms form power automate, how to create a flow from microsoft forms, how to link an existing microsoft form to excel, how to sync microsoft forms with excel via onedrive for business

Id: zbUH6a8K7bM

Length: 7min 17sec (437 seconds)

Published: Tue Sep 19 2023