Microsoft lists and Power Automate
Creating website forms can be challenging to setup. In the past Microsoft offered InfoPath. Personally, I used JotForm for websites. For a while, Microsoft did not offer an InfoPath alternative. Then Microsoft released Microsoft Forms. Initially for the education sector only. Business customers successfully reached out to Microsoft. Requesting ‘Forms’ to be released for business customers.
You have a website that you want to add a form. You need form submissions to be automatically posted to a SharePoint list or Excel or Common Data Service (CDS, Dynamics CRM). You may possibly want to use the data to produce Power BI reports.
Microsoft Office 365 E3 licence. Alternatively, you can purchase services separately. Recommendation purchase MS O365 E3 licence
In this article you are going to learn how to add a MS Event Feedback Survey Form to a WordPress website (the above form). How to use Power Automate to automatically pass form data to populate a SharePoint list.
Part One - Create a Form and SharePoint List.
Setup Time: 30 minutes
Setup Overview: These setup instructions walk through the steps required to configure. A ‘Microsoft Form and Microsoft SharePoint Online (SPO) List.’ The lab exercise for PAF365: Power Automate Bootcamp
Log in to Office 365. Click the square dots. Top left corner and choose ‘Forms’. If you do not see ‘Forms’ click ‘Explore all your apps’ and choose ‘Forms’ see below.
You can create your own form. Just choose ‘New Form.’
In the new pop up window click ‘Untitled form.’ Name your form ‘Event Feedback Survey.’ For description type. ‘Gauge areas of success and improvement for your event. By collecting customer input around time, location, content, speakers, and so on.’
Next click ‘+ Add New’ button and in the revealed options click ‘Add all.’ This tutorial will use defaults. If you prefer you can, of course, add your own questions.
Should you choose to you can add a ‘Form theme’ (1) or simply click ‘Preview’ (2) to view your form.
Forms can be previewed either as desktop form (1) or a mobile view (2). To view the previous screen, click ‘Back’ (3).
To add the form to a website page you need iframe code. Click the ‘Share’ button (1) and click the angle brackets icon (2). Click the ‘Copy’ button (3).
Note: Text 'Only people in my organisation can respond' click the drop down to select 'Anyone with a link can respond.'
Create or edit a webpage or blog post.
How you add the iframe code is dependent upon your WordPress editor. Assuming you are using WordPress default editor then click the code block icon to add your iframe code see image below.
Next test your form. Either preview or publish to test your form. Answer the questions and click ‘Submit’ button. We require some test data.
Return to your ‘Forms’ default window. You should see your form under ‘My forms.’ At the bottom, you should see the number of ‘Responses’. You should see one response.
Click on the form. At the top of the form, you should see two tabs ‘Questions’ and ‘Responses’ and the next to ‘Responses’ number ‘One’ in your case see image below. Click the ‘Responses’ tab.
Take a little time to view results. Then click ‘Open in Excel.’
Excel workbook will download to your device. Open the Excel workbook see below.
The reason for creating an Excel workbook. We can create a list using the spreadsheet. Microsoft has published an ‘easy how to create a list based on a spreadsheet’. Certainly, easier to create an automated SharePoint Online (SPO) list. Than manually creating an SPO list.
Open SharePoint. Click the square shape dots (1) then click lists. If you do not see lists, click ‘Explore all your apps’ bottom of the screen and choose ‘Lists’ form there see image below.
Under Click ‘+ New list’ button, see image below.
In the popup window click ‘From Excel.’
In the updated popup windows click ‘Upload file.’ If ‘Table1’ is not automatically setup click the drop down and choose table one. You should see your question data see image below click next button.
- Name the list.
- Add description.
- Choose an icon colour.
- Select an icon.
5. Choose where to save your list to.
Click ‘Create’ button.
All being well you should see your SPO list.
Setup Time: 30 minutes
Setup Overview: These setup instructions walk through the steps required to configure a ‘Power Automate Flow’ the lab exercise for PAF365: Power Automate Bootcamp.
Your next step is to automate the collection of website visitor questionnaire submissions. To your SharePoint list. Once you have collected a reasonable number of questionnaire responses. You could choose to use Power BI to carry out an analysis. You may want to send an automated thank you email to those who completed the form. But that capability is for a future article.
Viewing the ‘Lists’ window you will see ‘Automate’ click and select ‘Create a flow.’
Scroll down ‘Flow’ options pop out window to click ‘See more templates.’
Power Automate opens. In the search box type ‘Forms’ (1) and press enter on your keyboard then click ‘Record form responses in SharePoint.’
A new window opens. Confirm you are logged in to MS Forms & MS SharePoint. If not, simply click to do so (1). Then click ‘Continue’ button (2).
Power Automate opens the ‘Flow.’
1. For ‘Form id’ you need to add the ‘Form’ name. Click the drop down and select.
For ‘Get response details’ add ‘Form id’ (2) by clicking drop-down and choosing your form. For ‘Response Id’ (2) place the mouse cursor in the box and choose ‘List of response notifications Response Id’ (3).
The next step ‘Get user profile (2)’you can miss. If the form is used for organisational purposes only and users are Office 365 users, this step can capture user details for example email.
Note: Users do not need to add email address. Useful if you wish to send an automated thank you email or follow up information.
If your form is to be used for a public website and you wish to capture email addresses you will need to add form user input for email.
‘Get user profile (2)’ cannot be applied to capture user details.
If the form has an email input, it will appear in the next step ‘Create item’.
‘Create item’ you need to specify SharePoint ‘Site Address’ the ‘List Name.’ All other inputs follow the same steps as previously. Place your mouse cursor in the box (1), locate the input from the submenu (2) click to add.
Note: Assuming you did not add ‘Get user profile (2)’ you will not have options for ‘Email’ and ‘Name.’
5. Save your Flow. And run ‘Flow checker.’
6. To test your ‘Flow’ you need some data. Return to your Form and answer your questions.
7. Return to your ‘List.’ You should see your data.
Note: To make three list entries more descriptive I have renamed the columns.
If your flow didn’t succeed click the ‘Start’ title to learn where your flow 'Test failed.'
Do not worry if your ‘Flow’ failed it is a good learning opportunity. Review this tutorial to identify where you may have made an error.
Congratulations for completing this tutorial.