Web Site Contact Us Form with Microsoft Forms, SharePoint Lists, and Power Automate

Website contact forms can be limited unless you have integration into a CRM system. Most of the time it’s an email that goes somewhere with a human babysitting process. But not anymore! Let’s build a better experience with Microsoft Forms, Power Automate and SharePoint Lists. If you have office and email from Microsoft 365 already then when you probably already have access to these tools.  

Let’s get started! 

Youtube Video

For those who want to skip all of the reading, I made a video outlining a similar process.

Microsoft 365 Portal

For these 3 tools they all start by going to portal.office.com and sign in.  

Then select All Apps.

First, we need to create a form in Microsoft Forms. 

Microsoft Forms

Go Ahead build a form with the information you want to collect. In this case I created a generic contact form with some basic branching for if the request is for a business contact and preferred contact method. 

This solution is working on the solution that this form will be on a public website, so we have to change security settings to Allow Responses from Users Outside the Organization. Go to the Settings section.

Now that the form is created, we must create the destination where the information is going to go. Go back to the Office Portal and now select lists.

SharePoint Lists

Create a new blank list and store in an existing Microsoft Teams Team or dedicated SharePoint Site. Then give it an intuitive name. 

Then start creating columns to match the questions you created in the form. In my example I create 1:1 for all the columns expect the preferred contact questions. We’re going to use Power Automate to combine the data. 

On the SharePoint List side there are 3 fields that are SharePoint Side only. 

• Title – required first field of SharePoint. We’ll use dynamic fields to create a title
• Lead Status – Track what you’ve started. I set this column to be “new” by default. 
• Submitted Date – Use the forms logging to log when everything was processed.

For all the choice options on the form, create matching items (copy and paste) for the columns like What Type of Request are you looking for? becomes Lead Category in SharePoint.

Once you have the columns made you can create views to make it easier to view if you want. I’ll have a bonus tip at the bottom of this post to easily clean up the formatting a little bit. 

Now that SharePoint is configured, need to link Forms and SharePoint together with Power Automate. 

Power Automate

Go to Power Automate and create a new Automated flow using the (Forms) When a New Response is Submitted as the trigger. 

This it’s a matter of plugging mostly dynamic content. 

• When a new response is submitted –> Select the SharePoint List
• Get response details –> Select the list and use the Response ID dynamic item to get all the details about the response.  
• Initialize ContactVar variable –> Cerate a String Variable  
• Initialize BusinessLeadVar variable –> Create a Boolean Variable 

• Condition –> Use Are you contacting TDSheridanLAB on behalf of a company dynamic field is equal to yes rule. 
• Yes –> Set Variable – Select BusinessLeadVar and create an expression for true
• No -> Set Variable – Select BusinessLeadVar and create an expression for false

Now we’re on most complicated part, processing the contact details.

• Create a Switch on a the Preferred Contact method dynamic field 
• Add an additional case 
• Case 1 -> Equals – Phone 
  • Set Variable – Select ContactVar and What is your Phone Number Dynamic Field. 
• Case 2 -> Equals – Social Media 
  • Set Variable – Select ContactVar and Select Social Media Platform – Social Media Username Dynamic Fields. Make sure to manually add a space – space inbetween the 2 dynamic fields. 
• Default -> Used for E-mail contact method
  • Set Variable – Select ContactVar and What is your Email Address Dynamic Field.

• Create (SharePoint) Item 
  • Site Address –> Select the SharePoint Entry
  • List Name -> Select the List Entry
  • Title –> Create a title that will be unique and usable. I used “Web Lead – Name Dynamic Field – Submission time Dynamic Field”
  • Lead Status Value –> Set to New 
  • Lead Category Value –> What type of request are you looking for? Dynamic Field
  • Submitted Date –> Submission Time Dynamic Field
  • Business Lead –> BusinessLeadVar 
  • Company Name –> Name of the company Dynamic Field 
  • Contact Name –> What is your name Dynamic Field
  • Contact Method Value –> Preferred contact method Dynamic Field 
  • Contact Value –> ContactVar Dynamic Field 
  • Details –> Details Dynamic Field 
  • Response ID –> Response ID Dynamic Field 
  • Follow Up Notes –> Leave Blank 

Click save and go and test. If everything is wired up correctly then you should have a list entry. 

Time for the bonus tip to format the entries. 

Make the list look pretty…

By Default, your list will be similar to this.

Between column views, JSON, conditional formatting, power automate, and power apps. you can do a lot to make a List look a lot better. fair warning it’s not hard t to go down the rabbit trail on customizations. 

But to apply some simple organization this all you have to do. 

Click on the Edit menu in the top right corner of a list entry and then click on Configure layout. 

Then change the Apply formatting to to Body.

If you click on the Learn More link, you’ll be brought to a Microsoft Document that explains some of JSON options that you can use to customize the list. To customize the body of the list this is the starting point you’re looking for. For whatever reason Microsoft put it at the bottom of the article instead of the middle.

{
    "sections": [
        {
            //give a display name for the section
            "displayname": "",
            "fields": [
                //reference your fields here using their display name
                "Title"
            ]
        },
        {
            //give a display name for the section
            "displayname": "",
            "fields": [
                //reference your fields here using their display name
            ]
        }
    ]
}

Use your favorite text editor to edit this code with your values. JSON relies on commas to break up sections and it’s very easy to have an extra one or be missing one somewhere. If the comma syntax isn’t correct the whole thing breaks. That is why I recommend using Notepad++ (Windows Only) or VS Code (All platforms) because you can set the document language to JSON and it will double check all the syntax for you. If there are any errors, the programs will point out where your typo is. 

Make sure to remove the comments (the lines that start with //) from the file. 

For this post’s example here is what I did. 
I created 3 groups of as Lists/JSON calls it sections. Lead Info, Contact Info, and Contact Request Info. Then I added the list’s column names to the corresponding section. 

VS Code Screenshot

Code block

{
    "sections": [
        {
            "displayname": "Lead Info",
            "fields": [
                "Lead Status",
                "Lead Category",
                "Submitted Date"
            ]
        },
        {
            "displayname": "Contact Info",
            "fields": [
                "Business Lead",
                "Company Name",
                "Contact Name",
                "Contact Method",
                "Contact Value"
            ]
        },
            {
            "displayname": "Contact Request Info",
            "fields": [
                "Details",
                "Follow up Notes"
            ]
        }
    ]
}

Then I copied the code into the box and clicked on preview to double check for errors and then clicked save. 

Lastly, because of the unique title requirements of lists, the title field on this JSON Layout looked a little weird. So I went back to the edit menu and clicked on edit columns this time. Then I unchecked the title column which automatically added it to the header instead of in the body. 

Hopefully this helps you get contact us requests out of a mailbox somewhere and into a centralized location.