{"id":5118,"date":"2019-04-15T15:47:05","date_gmt":"2019-04-15T20:47:05","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=5118"},"modified":"2019-04-15T16:04:48","modified_gmt":"2019-04-15T21:04:48","slug":"did-you-know-you-can-store-microsoft-forms-responses-in-a-sharepoint-online-list-with-flow","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=5118","title":{"rendered":"Did you know \u2026 you can store Microsoft Forms responses in a SharePoint Online list with Flow?"},"content":{"rendered":"

Microsoft Forms is a great way to compose simple forms and gather responses but analyzing the data in Excel \u2026 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.<\/p>\n

Note \u2013 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 \u2026 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 <\/em>not<\/em><\/strong> triggered on \u201ce-mail from my manager received in my mailbox\u201d but rather \u201cnew e-mail message received in my mailbox\u201d. 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\u2019s free cycle allotment.
\n<\/em><\/p>\n

First you need a Form. When editing your form, grab the URL and find the \u201cFormID\u201d section. Save that string \u2013 we\u2019ll need that in the workflow.<\/p>\n

<\/p>\n

Before you start<\/em><\/strong> \u2013 if you plan to convert your personal form into a group form, do that before<\/em> 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.<\/p>\n

To store information in a SharePoint Online list, you\u2019ll also need a SPO list. You can create one in a dedicated SPO site or the SPO site behind a Teams space.<\/p>\n

<\/p>\n

Record the SPO site address and list name \u2013 we\u2019ll need that in the workflow.<\/p>\n

Now that we\u2019ve got all of the components ready, we can build a workflow to connect them. Log into https:\/\/flow.microsoft.com<\/a><\/p>\n

On the left-hand navigation bar, click \u201cMy Flows\u201d.<\/p>\n

<\/p>\n

Click the drop-down next to \u201cNew\u201d and select \u201cCreate from blank\u201d<\/p>\n

<\/p>\n

Click \u201cCreate from blank\u201d to confirm your choice.<\/p>\n

<\/p>\n

Before you do anything else, give your Flow a descriptive name. Click on \u201cUntitled\u201d, type something else, and hit enter. If you don\u2019t name your flow, it will get a default name based on the actions taken within the workflow \u2026 which makes it really<\/em> hard to differentiate between five different workflows that take Forms responses and store them in SPO lists.<\/p>\n

<\/p>\n

The trigger for the workflow will be that a new Forms response is submitted.<\/p>\n

<\/p>\n

Select the \u201cWhen a new response is submitted\u201d trigger.<\/p>\n

<\/p>\n

Select \u201cEnter custom value\u201d and paste in the form ID we recorded earlier.<\/p>\n

<\/p>\n

Click \u201c+ New step\u201d to add a new step.<\/p>\n

<\/p>\n

To use the data from the Form submission, we need to get the response data. Enter \u201cForms\u201d again and select \u201cMicrosoft Forms\u201d<\/p>\n

<\/p>\n

Select the \u201cGet response details\u201d action.<\/p>\n

<\/p>\n

In \u201cForm Id\u201d, enter a custom value and paste in the Form ID. Click in the \u201cResponse Id\u201d field. In the right-hand navigation menu, click \u201cSee more\u201d.<\/p>\n

<\/p>\n

Select \u201cList of response notifications Response Id\u201d<\/p>\n

<\/p>\n

You\u2019ll see that the response ID is added to \u201cSelect an output from the previous step\u201d. Click \u201cAdd an action\u201d<\/p>\n

<\/p>\n

Type \u201cSharePoint\u201d and select \u201cSharePoint\u201d<\/p>\n

<\/p>\n

Select \u201cCreate item\u201d from the \u201cActions\u201d tab<\/p>\n

<\/p>\n

Select \u201cEnter custom value\u201d for the Site Address and enter the site address we recorded earlier. Then click the drop-down for \u201cList Name\u201d \u2013 select the list into which you want to insert Forms responses.<\/p>\n

<\/p>\n

Below \u201cList Name\u201d, additional fields will appear \u2013 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.<\/p>\n

<\/p>\n

Flow is aware of column types \u2013 if you click into a date column, it will try to present Forms information that holds dates. If you find an option missing, click \u201cSee more\u201d to see an unfiltered list of Forms attributes.<\/p>\n

<\/p>\n

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 \u2026 figuring out which<\/em> \u201cGet response details\u201d is which becomes difficult.<\/p>\n

<\/p>\n

It can be clearer if you provide a unique name for each trigger and action \u2013 the values from the LJRDemo form are clearly part of the \u201cGet LJRDemo Form response details\u201d section.<\/p>\n

<\/p>\n

To rename a workflow element, click the ellipses and select \u201cRename\u201d<\/p>\n

<\/p>\n

Type a new name and hit enter<\/p>\n

<\/p>\n

Click \u201cSave\u201d to save and activate your workflow.<\/p>\n

<\/p>\n

Submit answers using your form, then check your SPO list.<\/p>\n

<\/p>\n

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<\/em> so useful for you. Flow provides directory lookup capabilities if you want to turn the logon ID field into something \u2026 more readable. Edit your Flow. After the get response details step, click the little plus to insert a new step.<\/p>\n

<\/p>\n

Select \u201cAdd an action\u201d<\/p>\n

<\/p>\n

Search for \u201cOffice 365 Users\u201d, select it, and select \u201cGet user profile (V2)\u201d.<\/p>\n

<\/p>\n

Click in the \u201cUser (UPN)\u201d box and select the Form respondent\u2019s \u201ce-mail\u201d<\/p>\n

<\/p>\n

Edit the action which creates the SPO list item. You will now have dynamic content both from the Form response and<\/em> the Office 365 user lookup. In this case, I will use \u201cMail\u201d in the Submitter column.<\/p>\n

<\/p>\n

Save you changes. Since we\u2019ve already submitted a Form response, we can test the Flow without submitting a new response. Click \u201cTest\u201d in the upper right-hand corner of your screen.<\/p>\n

<\/p>\n

Select one of the previous runs (there may only be one!) and click \u201cSave & Test\u201d to re-run the Form response through your workflow.<\/p>\n

<\/p>\n

Now the submitter is recorded as an e-mail address.<\/p>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

Microsoft Forms is a great way to compose simple forms and gather responses but analyzing the data in Excel \u2026 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 …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[689,688,415,747,748],"class_list":["post-5118","post","type-post","status-publish","format-standard","hentry","category-office-365","tag-microsoft-flow","tag-microsoft-forms","tag-sharepoint","tag-sharepoint-online","tag-spo"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5118"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5118\/revisions"}],"predecessor-version":[{"id":5153,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/5118\/revisions\/5153"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}