Oct 18 2016 10:05 PM - edited Oct 18 2016 10:09 PM
I was recently playing around with ideas on using anonymous forms with SharePoint Online. There were three key requirements that I wanted to stick to:
Using a combination of a free forms service (JotForm), Microsoft Flow, and SharePoint Online, I was able to achieve this goal. Here’s how I did it.
The Challenge
SharePoint Online supports external sharing of documents with anonymous users. But what if you want to gather some information from anonymous users? The challenge is to get the data into your SharePoint environment. If you have some forms solution you are using, then one way to do this is to create some a web service or data repository that the form can write to and then have SharePoint pick up the information somehow. But that may require coding, so scratch that idea.
The Solution
There is a very popular service that all of us use on a daily basis that lets us communicate freely with Office 365 – Email! So now that I had a way to get past the authentication issue, all that was missing was the forms solution and getting information from the emails into my SharePoint list. For the purpose of this article, I created a restaurant survey form that captured the submitter's name, email, message, and rating.
Survey Form
If you recall, I had some strict requirements for my solution around complexity and cost. There are a lot of forms solution available on the web – some are free while others have a cost associated with them. One essential requirement in the selection process is that the service will generate an email with the forms data and allow you to format the email. We’ll get to the reasons why that is important in a bit.
I’ve settled on using JotForm for no particular reason beyond the fact that it met my basic requirements. With the graphical user interface, I was able to create an attractive form.
Here is what the form looks like on a desktop and mobile version. All of my essential requirements were met.
One thing that I needed to change was the format of the email. By default, JotForm was generating an HTML-formatted email with lots of tables. Here’s what the original email format looked like:
<table border="0" width="100%" cellspacing="0" cellpadding="0" bgcolor="#f7f9fc"> <tbody> <tr> <td height="30"> </td> </tr> <tr> <td align="center"> <table border="0" width="500" cellspacing="0" cellpadding="0" bgcolor="#eeeeee"> <tbody> <tr> <td bgcolor="#EEEEEE" width="4" height="36"> </td> <td bgcolor="#EEEEEE" width="30"><img style="display: block;" src="https://cdn.jotfor.ms/assets/img/builder/email_logo_small.png" alt="" /></td> <td style="font-size: 16px; vertical-align: middle; color: #f9922b; padding-top: 2px; line-height: 20px;" align="left" bgcolor="#EEEEEE"><strong>{form_title}</strong></td> </tr> </tbody> </table> <table border="0" width="500" cellspacing="0" cellpadding="0" bgcolor="#eeeeee"> <tbody> <tr> <td bgcolor="#EEEEEE" width="4"> </td> <td align="center" bgcolor="#FFFFFF"> <table id="emailFieldsTable" class="mceEditable" border="0" width="100%" cellspacing="0" cellpadding="5"> <tbody id="emailFieldsTableBody"> <tr id="row_15" class="questionRow"> <td id="question_15" class="questionColumn" style="padding: 5px !important;" valign="top" bgcolor="white" width="170">Your Name</td> <td id="value_15" class="valueColumn" style="padding: 5px !important;" bgcolor="white">{yourName}</td> </tr> <tr id="row_16" class="questionRow"> <td id="question_16" class="questionColumn" style="padding: 5px !important;" valign="top" bgcolor="#f3f3f3" width="170">Your E-mail Address</td> <td id="value_16" class="valueColumn" style="padding: 5px !important;" bgcolor="#f3f3f3">{yourEmail16}</td> </tr> <tr id="row_17" class="questionRow"> <td id="question_17" class="questionColumn" style="padding: 5px !important;" valign="top" bgcolor="white" width="170">Your Message</td> <td id="value_17" class="valueColumn" style="padding: 5px !important;" bgcolor="white">{yourMessage}</td> </tr> <tr id="row_19" class="questionRow"> <td id="question_19" class="questionColumn" style="padding: 5px !important;" valign="top" bgcolor="#f3f3f3" width="170">How did we do?</td> <td id="value_19" class="valueColumn" style="padding: 5px !important;" bgcolor="#f3f3f3">{howDid}</td> </tr> </tbody> </table> </td> <td width="4"> </td> </tr> <tr> <td style="font-size: 4px;" height="4"> </td> <td style="font-size: 4px;"> </td> <td style="font-size: 4px;"> </td> </tr> <tr> <td style="font-size: 6px;" bgcolor="#f7f9fc" height="6"> </td> <td style="font-size: 6px;" bgcolor="#f7f9fc"> </td> <td style="font-size: 6px;" bgcolor="#f7f9fc"> </td> </tr> <tr> <td bgcolor="#f7f9fc" height="32"> </td> <td style="font-size: 11px;" bgcolor="#f7f9fc"> <table border="0" width="100%" cellspacing="0" cellpadding="0"> <tbody> <tr> <td style="text-align: left; color: #9ea1a4;">Powered by <a style="border: 0;" href="https://www.jotform.com/signup?utm_source=emailfooter&utm_medium=email&utm_term=62570659309261&utm_content=email_footer_banner&utm_campaign=autoresponder_email_footer_signup"><img style="vertical-align: middle; border: 0;" src="https://cdn.jotfor.ms/assets/img/builder/email_footer_logo.png" alt="" /></a></td> <td style="text-align: right;"><a style="color: #f9922b; text-decoration: none;" href="https://www.jotform.com/signup?utm_source=emailfooter&utm_medium=email&utm_term=62570659309261&utm_content=email_footer_text&utm_campaign=autoresponder_email_footer_signup">Create your own form today!</a></td> </tr> </tbody> </table> </td> <td style="font-size: 4px;" bgcolor="#f7f9fc"> </td> </tr> </tbody> </table> </td> </tr> <tr> <td height="30"> </td> </tr> </tbody> </table> <p><br /><br /></p> <p> </p> <pre> </pre>
However, for my needs, I wanted to the email to be very as simple as possible with some tags so I can easily parse it (spoiler alert!) My version of the email was a lot shorter.
Name:{yourName};Email:{yourEmail16};Message:{yourMessage};Rating:{howDid}
Survey List
To capture the content, I create a new list in SharePoint. The only fields I needed were as follows:
Name | Type | Value |
Title | Single line of text | Mandatory field, but I’m not using it |
Body | Multiple lines of text | This field holds the body of the email. |
Full Name | Calculated Field | =MID(BODY,INT(FIND("Name:",BODY)+5),INT(FIND(";Email:",BODY))-INT(FIND("Name:",BODY)+5)) |
Calculated Field | =MID(BODY,INT(FIND("Email:",BODY)+6),INT(FIND(";Message:",BODY))-INT(FIND("Email:",BODY)+6)) | |
Message | Calculated Field | =MID(BODY,INT(FIND("Message:",BODY)+8),INT(FIND(";Rating:",BODY))-INT(FIND("Message:",BODY)+8)) |
Rating | Calculated Field | =MID(BODY,INT(FIND("Rating:",BODY)+7),INT(FIND("</p>",BODY))-INT(FIND("Rating:",BODY)+7)) |
As you can see from the list, most of the captured content from the emails body. SharePoint has a limitation, where Calculated fields cannot reference Multiple Lines of text fields. So for the Body field, I had to do a little workaround where I first created it as a Single line of text field, then configured the Calculated fields to point to it, and finally deleted the Body field and re-recreate it as a Multiple lines of text field.
Getting the email Body into the list
The final piece of the puzzle was getting the body of the email into SharePoint. To do so, I’ve decided to use a simple two-step Flow that checks for emails from a particular sender (noreply@jotform.com) and if received, copies the body into a new SharePoint list item.
Conclusion
And voila! There you have it – a code free, cost free solution to creating anonymous forms for your SharePoint Online environment. The example I provided above is only one simple example which helped me overcome the anonymous forms snag, but you can easily extend it for other scenarios.