Forum Discussion
Update a collum in Excel from a Microsoft Form Submitted
I'm wondering if this can be done please and if so, how?
I have a list of people that contains their name, number, email etc..
I then message them asking them to fill out a Microsoft Form to book an appointment with me.
Instead of filtering through the form and spreadsheet manually, I want the form to be able to maybe put an X or something next to a persons name in my spreadsheet to say that they've booked/filled out the form or even input the date they have selected so then I know who to chase up if they haven't booked.
So I want excel to cross match the form responses and update each individual automatically.
I've tried looking on flows/automations but not a clue where to start!
Is this possible? If so can someone explain how please?
It is possible to update a column in Excel based on the responses submitted through a Microsoft Form, so far I know :). You can achieve this using Microsoft Power Automate (previously known as Microsoft Flow). Here's a high-level overview of the steps involved:
- Set up a flow in Microsoft Power Automate.
- Configure the trigger for the flow to be when a new response is submitted in Microsoft Forms.
- Add an action to get the details of the submitted response.
- Use an Excel connector action to update the corresponding cell or column in the Excel spreadsheet.
Here are more detailed steps to accomplish this:
- Open Microsoft Power Automate (flow) in your web browser.
- Create a new flow and select "Automated - from blank" as the flow's template.
- Set the trigger by searching for "Microsoft Forms" and selecting the trigger "When a new response is submitted".
- Connect to your Microsoft Forms account and select the form you want to monitor for responses.
- Add an action by searching for "Excel" and selecting the action "Update a row".
- Connect to your Excel account and select the workbook and worksheet where you want to update the column.
- Configure the "Update a row" action by specifying the cell or column that needs to be updated with the corresponding value from the Microsoft Form response.
- Save and activate the flow.
Now, whenever a new response is submitted through the Microsoft Form, the flow will trigger, retrieve the response details, and update the specified column or cell in the Excel spreadsheet accordingly.
Please note that the specific steps and options may vary slightly depending on the version of Microsoft Power Automate and Excel you are using, as well as the interface changes over time.
- Hello! I've just run a test flow and keep getting an error message come back...
So I've selected the location, document library, file, table, key collum and in the key value it has Response id.
How does it search for example an email address on the form which matches an input in my spreadsheet with that same email address to then put an X or something in that specified row?On the flow first is "Get response details" step. It returns Response Id for the triggered answer. Returned value for the filed will be like outputs('Get_Response_details)?['body/field_name'] which could be inserted from dynamic contents on next step.
If with above response you'd like to add new row in some table in other Excel file, step will "Add a row into table" where you map fields in this table on the values received on previous step. For the each field that could be inserted value from dynamic content or some expression based on it.
If you'd like to update some row in that table, when you need to have Key column and key values within it. Steps will be Get Row and Update Row.
Above assumes form wasn't created from Excel Online and you don't receive responses into that file automatically.
