In this post, Jasjit Chopra shows you how to go from simple type form data to a secured SharePoint list in less than 20 minutes!
Imagine you have a form hosted on an internet site (perhaps a registration form) that’s completely separate to SharePoint.
Here’s the scenario:
– The people who you want to fill it do not have SharePoint accounts.
– When they fill in the form you want to validate it.
– Once valid, it posts the responses to a SharePoint list.
This used to be hard, but in this post (and attached video) I am going to show you how to do this in less than 20 minutes.
What do you need?
– A SharePoint List (to post the data to)
– An account with TypeForm
– An account with Microsoft Flow
* Show the SharePoint List form
* Show the completed TypeForm (with a link on how to create one)
* Show the MS Flow (with some steps)
* Show the finished result
- The ultimate introduction to Microsoft Flow and PowerApps!
- The ultimate guide to Microsoft Forms
- How to convert your InfoPath Forms to PowerApps
- PowerApps Training Courses
Here is the full transcript of his video:
Hi everyone, we’re going to look at within less than 20 minutes we’re going to go from simple type form data to a secured SharePoint list. A little bit about myself, I am Microsoft certified consultant in SharePoint, Azure and Office365. You can see the contact details on the right you can contact me any way you want, if you have any questions feel free to reach out.
So, in my consulting experience what I’ve seen in the past is lot of times customers they really need a very simple solution to this problem where they want anonymous form to be filled and the data that they need to collect needs to go into a SharePoint list which is secured basically so this is what our scenario is going to look like.
We’re going to create a simple form using type form which will be anonymous and will be very simple and easy to create. We’re going to use Microsoft flow this is a new service by Microsoft that has been recently available where you can go ahead and create a simple connector which will trigger automatically when a new form is submitted while using the type form platform and as soon as that gets completed and submitted you’re going to see the data come in SharePoint which can be taken further action upon and you can use all the beautiful and amazing features that SharePoint platform has to offer to you know automate your process further and make it much more worthwhile.
Alright, let’s dig into the action and see how this is going to be done. Alright so we’re going to create a registration from here, you just go to typeform.com and create an account. This is a free version account, this is not a paid one. So, we are going to create a registration form here, going to call it registration form and click on start building and you can see it’s a fairly simple process, you can just drag and drop your columns that you want.
- So, let’s start with our first question here – what is your name? We are going to make it required field, click on save.
- We’re going to have email it’s already there so just click on email from the left – what is your email? Going to make it required as well.
- We’re going to take a phone number. I am not going to make this a required field
- Next we’re going to go for another short text this time this will be for which company do you work for and going to make this one a required field for now and this is really cool in type form you can see what am going to do here – so what is your current job title at and I am going to include a variable from the company’s name so I find this really interesting, you can ask a question related to the answer that was given to the first option
- Then we’re going to do a drop down here for a country list so which country are you from? I’m going to make this a required and for choices I actually have a copy list of all the countries in my notepad and I am going to go ahead and paste this right there, alright quickly going to click save.
- We are going to ask which city a person belongs to alright, which city do you belong to and this one I am not going to make it a required field at this time
- And of course the legal part this is for our Terms and Conditions – do you agree with our terms and conditions? Right and descriptive text goes here for your terms and conditions. Alright I am going to make this a required one, click save and that’s it we are done.
All I need to do now is to go to the share option, copy this URL right here and if I try to open this form anyone can access this, this is anonymous, it doesn’t have any authentication. Let’s just go ahead and make a small change here, I’m going to go to the configuration option sorry the design option and change the background image just to make it look a little fancy alright choose this background right here and I’m going to click apply, alright let’s wait for this done and now if I refresh this you’ll see there’s a nice background to it.
Alright, let’s leave it at that let’s look at the next options.
Alright now that we have our type from created we’re going to log into SharePoint site as Emily Brown, couple of things here, she is logged into this site with edit permission levels this could also work with contribute permission levels within the site SharePoint security model actually but I chose edit permission level just so that she can create the registration form as a SharePoint list, contribute permission level from SharePoint out-of-the-box does not provide that level of access.
Alright, so we’re going to go ahead and quickly create a SharePoint list called registration and we’re going to match the same columns that we created in the type form earlier right, let’s go ahead and give this a name Registration, registration form for demo and I am going to click create, once this pops up I am going to make some changes right, we’re going to go to list settings we are going to change the title we’re going to capture email in the title and make sure its unique, call this email alright. Require enforce values now when I’m doing this what will happen is if somebody with the same email registers twice that data will not get registered and you will see a failure in the run section when you create the flow, will look into that later as well, so we’re going to go ahead and click ok at this point there you go.
We’re going to quickly create other columns as well. We’re going to mostly keep the type of information in this column a single line of text for most of our options. we’re just trying to match this exactly is what I had typed in within my type form, remember, so required this value absolutely that’s what I did there, alright. So, the presentation here doesn’t matter because we’re just collecting data here not having the end user type that alright, so phone number and single line of text this would be – what is your phone number and over here this was not a required field so skip that part.
We are going to come to another column called company this time, right, single line of text again which company do you work for? and this we definitely want as required, I’m going to click okay and our next would be the job title, next column job title, this was not required – what is your current job title?
Again, SharePoint doesn’t allow taking responses from other columns so we are going to leave the description as is.
We’re going to collect the country name now, remember in the type form I chose country as a drop-down data type but here I’m just going to leave it like this because it’s a text from that is coming from the form so I am adjusting data from there so I don’t need to worry about what comes in here because it will be filtered in a way from that drop-down list that I already created type form, which country are you from? Which I tend to do that a lot form from and for country it is definitely a required field and if you don’t match any of these settings with the type form especially with the required fields and somebody misses that in type form and the data comes in it will throw an error that particular flow run will come up as failed so make sure you keep that in mind.
Alright so I’m going to do city here and which city do you belong to? and this is definitely not required, click ok
Last but not least terms and conditions and this is a little bit tricky, usually if you were doing this all within SharePoint and not using type form you would literally go ahead and use a yes-or-no checkbox but not in this case – terms and conditions in this case I’m still going to use single line of text now what is happening in the backend the data is coming from type form will see a little bit later when we do the test it is actually sending a zero or a one, so either I could use a number here to do the differentiation but again single line of text will suit much better for indexing purposes within the SharePoint environment so I’m going to keep it to that and for whatever business logic you want the yes or no it will still work with a single line of text for many reasons – do you agree with our terms and conditions? Alright and this is definitely a required field so there has to be a value, I am not going to put any default value at this time, click ok,
Alright so basic configuration is done that took me hardly a couple of minutes to create the form couple of minutes this but again if you’re doing this from scratch you might need to get a site created where you want this to be set up for you that’s why that 20 minutes is coming I’m going to show you do it in much less than that there are some prerequisite to even with all the prerequisite this will just take around 20 minutes to get you done from end-to-end scenario.
Alright, so once our registration list is set up we’re going to see that there is no data in this at this point alright there’s nothing to show here so I can go ahead and create a flow from here as well but I’m going to go to flow.microsoft.com at this point and if you’re using flow for the first time for your Office365 organization ID that you login with, you should be clicking on sign up free and then follow the process, it’s one minute process otherwise you can go ahead and click sign in once I do that I’ll be signed into Microsoft flow as Emily Brown,
Alright so once I am there I can simply go ahead and click on my flows and start creating the flow that I want to alright oh well this is already there so I am going to quickly delete this, this was from my previous test.
I’m going to start with creating a blank flow all right and the first service that I want to use is my type form now remember I’ve done a little bit of testing here so I’m not being asked about a particular API key that comes within this much for why that first letter is not coming up here that anyways so when you click this for the first time in your type form account you need to go to your account settings and add another API key that will connect that together with this particular flow, alright so do that and then when you do that you’ll be able to drop down and find whatever forms you have within your account from there
So here I’m going to select the registration form this is coming from type form account that we saw earlier in the previous video and there are a lot of things you can do here I can either add an action add a condition and do a lot more but for this purpose demonstration all we have to do is add an action and pick SharePoint from the list of services and it will give you what all actions you can take on this,
In our case we want to take the response when it is submitted in a type form and create an item out of it right so I’m going to go ahead and click that I’m going to select which list I’m going to do
Before I do that let’s go ahead and copy that URL of the site here right now remember this URL is of the site collection and I created this list within the root of this site collection. this list could live in a first level, second level or a third level sub-site those who know SharePoint know what I’m talking about otherwise all you need to do is copy the URL of the list sorry of the SharePoint site and paste it here, once you do that you can have all the registration, all the list and document libraries that you can take action on.
In this case, we created registration list that’s where we’re going to put our data in. now this is where the magic happens, it automatically picked all the columns that I created over there and all I need to do now is match the data that is coming from the type form to this so it’s just one click there one click here and you are good to go with right.
So, country which country are you from, terms and conditions right here, phone number, your job title and then which city are you from.
Alright so that was that simple and if you want to double-check you can see your email is linked with the email, name is linked with name, company is linked with company, country with country, terms with terms, phone number job title and city and that’s it we are done.
This is all that is required to set up this whole process created anonymous registration form using type form created the flow at a click of a button oh actually few buttons but you see how simple this is like.
Alright now we’re going to do a testing so I’m going to quickly go ahead and bring in the URL of the form and we’re going to type in some data and see how this whole thing’s works right, let’s go ahead and type in some dummy data say I am John right and then my email is [email protected], my phone number is 732 555555 I work for Contoso, alright and then my current job title is Software Developer, well alright and then which country I am going to say I am from US well this is all list that was there so United Kingdom, which city do you belong to London and I’m going to click yes at this point and this is where it gives me the option to submit it
Alright now when this action is triggered is also very important to understand how this happened so when this form is created the trigger is not something that will happen right away so even though the flow says that it will trigger when an item is submitted there is uh a little bit of lag. So what I’m going to do at this point is I’m going to pause this video and we’re going to come back when an item is actually created within SharePoint.
So just to show you we just submitted it I’m going to refresh this and show you that the item is still not here so it’s not spontaneous within a couple of seconds it takes actually within two to three minutes that’s what I’ve seen in the past with my testing and if you click on this little “I” icon over there you can see all the previous flows that you created and how they ran basically, so there’s nothing right now because I deleted all the other tests flows that I created so let’s come back when we have our data ready okay.
Alright we have our result here you can see the success six seconds ago and you can see even the duration for how long this flow actually ran for so you see there is a timestamp that you can see exactly when this happened when a response is submitted, 0 seconds created time, two seconds for the flow to run specifically alright.
Let’s see here we’re going to do a refresh and see all the magic happen the data from type form which an anonymous user fed in is available to me in SharePoint and the list which is a secured list so here you go let’s check all the data that we submit right, so I have my email, my name, my phone number, company, job title, country, terms and conditions as I told you it will be 0 or 1 depending on what’s coming in from type form and that’s how you’re going to you know you can still use it in workflow in whatever way you want to automate any process.
So there you go less than 20 minutes because I had all the basics and the backend set up beforehand but as you can see it’s a very powerful thing you can use in your organization if you’re a power user and have access to these tools, so a combination of type form, SharePoint, Microsoft flow is able to give you a very simple and robust solution and this can be used as an Infopath replacement as well going forward, thank you.