Microsoft Forms is a great way to compose simple forms and gather responses but analyzing the data in Excel … well I generally prefer processes to be automated. To use Forms data in automated processes, I store the form response in a SharePoint Online (SPO) list. How do you get the form response into a SPO list? Microsoft Flow.
Note – Microsoft Flow cycles are not free. We receive a number of free cycles each month for each Office 365 license purchased. The company-wide number of free flow cycles is quite large, but not infinite. Be cognizant of what constitutes a cycle. In this case, a cycle is each new form submission … if I expect 20,000 people to submit the form this month, I am using 20,000 flow cycles. Not all trigger cycles are as straightforward as this. As an example, Flow could send me an alert when I receive an e-mail from my manager. A cycle is not triggered on “e-mail from my manager received in my mailbox” but rather “new e-mail message received in my mailbox”. For a mailbox dedicated to a specific purpose, the number of messages received per month may be exactly what you want to process. In my mailbox, system alerts, ITSM notifications, script results and such would quickly use up a significant portion of Windstream’s free cycle allotment.
First you need a Form. When editing your form, grab the URL and find the “FormID” section. Save that string – we’ll need that in the workflow.
Before you start – if you plan to convert your personal form into a group form, do that before creating the Flow workflow. The form ID changes when a form is moved between group and personal forms, and you will need to adjust the workflow accordingly.
To store information in a SharePoint Online list, you’ll also need a SPO list. You can create one in a dedicated SPO site or the SPO site behind a Teams space.
Record the SPO site address and list name – we’ll need that in the workflow.
Now that we’ve got all of the components ready, we can build a workflow to connect them. Log into https://flow.microsoft.com
On the left-hand navigation bar, click “My Flows”.
Click the drop-down next to “New” and select “Create from blank”
Click “Create from blank” to confirm your choice.
Before you do anything else, give your Flow a descriptive name. Click on “Untitled”, type something else, and hit enter. If you don’t name your flow, it will get a default name based on the actions taken within the workflow … which makes it really hard to differentiate between five different workflows that take Forms responses and store them in SPO lists.
The trigger for the workflow will be that a new Forms response is submitted.
Select the “When a new response is submitted” trigger.
Select “Enter custom value” and paste in the form ID we recorded earlier.
Click “+ New step” to add a new step.
To use the data from the Form submission, we need to get the response data. Enter “Forms” again and select “Microsoft Forms”
Select the “Get response details” action.
In “Form Id”, enter a custom value and paste in the Form ID. Click in the “Response Id” field. In the right-hand navigation menu, click “See more”.
Select “List of response notifications Response Id”
You’ll see that the response ID is added to “Select an output from the previous step”. Click “Add an action”
Type “SharePoint” and select “SharePoint”
Select “Create item” from the “Actions” tab
Select “Enter custom value” for the Site Address and enter the site address we recorded earlier. Then click the drop-down for “List Name” – select the list into which you want to insert Forms responses.
Below “List Name”, additional fields will appear – one for each column of your list. Click into a column. In the right-hand bar, you will see the Form attributes that can be placed into the List column. For each List column, select the Form component you want stored in that field.
Flow is aware of column types – if you click into a date column, it will try to present Forms information that holds dates. If you find an option missing, click “See more” to see an unfiltered list of Forms attributes.
When you are selecting values to use in an action, the possible selections are grouped by the action. For a simple form/list combination like this, using the default step names is not too confusing, but if you build more complex workflows with more data elements … figuring out which “Get response details” is which becomes difficult.
It can be clearer if you provide a unique name for each trigger and action – the values from the LJRDemo form are clearly part of the “Get LJRDemo Form response details” section.
To rename a workflow element, click the ellipses and select “Rename”
Type a new name and hit enter
Click “Save” to save and activate your workflow.
Submit answers using your form, then check your SPO list.
Notice that what Forms calls my e-mail address is actually my logon ID. This is very useful for IT folks who can easily correlate logon IDs to actual individuals. Maybe not so useful for you. Flow provides directory lookup capabilities if you want to turn the logon ID field into something … more readable. Edit your Flow. After the get response details step, click the little plus to insert a new step.
Select “Add an action”
Search for “Office 365 Users”, select it, and select “Get user profile (V2)”.
Click in the “User (UPN)” box and select the Form respondent’s “e-mail”
Edit the action which creates the SPO list item. You will now have dynamic content both from the Form response and the Office 365 user lookup. In this case, I will use “Mail” in the Submitter column.
Save you changes. Since we’ve already submitted a Form response, we can test the Flow without submitting a new response. Click “Test” in the upper right-hand corner of your screen.
Select one of the previous runs (there may only be one!) and click “Save & Test” to re-run the Form response through your workflow.
Now the submitter is recorded as an e-mail address.
