Use Google Forms to Auto Fill Google Sheets with Data

so well all are not welcome to the computer lab so in this short video I'm going to show you how to automate data import into Google sheets using Google's forms [Music] so if you used to use in Excel you might know that there is an option in there to use forms where you can automate the importing of certain criteria and certain data into an Excel spreadsheet Google Docs has something that's similar but it's actually created in a separate program called forms where you can automate a filling in another form and that updates a spreadsheet and that can be done remotely via the mobile network or whatever it is on your phone and it will update the sheet that we're going to create in this tutorial so if that's something that's interests you or if you're just generally interested in how to manage data using Google's free suite free office suite that they offer to anybody that has a Google account then please do carry on watching I'm gonna be using it in Google Chrome as that's probably the best program to use the Google office suite and forms in so before you start striving follow along to this make sure that you are signed in to a Google account by going up into the top right hand corner and just checking that you are signed in once you signed in then you can do a quick search for Google Forms or go into the drive section but if you go into Google Forms you can also go into the Google Docs or sheets and slides it's all accessible from the same app page can get to it by clicking on the left on the side and the corner where I'm indicating there where it says that sheets home they can bring a list up down there or I'm just gonna use on a separate tab and you can see the option there forms if you have a business account with Google then you will be logging in it's slightly different but the chances are you'll be using the personal account so where it says go to Google Forms click on that and that will take you to this home page for Google Forms from here we need to then click on where it says blank and this will create a new blank form so what we're going to be doing we're going to be replicating this that you can see in Google sheets you might have one that you want to replicate in Google Forms to automate the import process so the first thing we need to do is title at the forum so click where it says untitled form and then fill in what you want to call your form in google thorne I'm going to call it customer info and then if we click in the top left hand corner that will automatically fill in the name of the form and then we can go to the next section down we need to delete this one this one that's already preset so just highlight it then click on the bin icon and that will get rid of the first field that's automatically filled in and then we can put our custom fields in it to the correct places what I think we'll do I think I'll separate the Google sheet to the left and then how the Google form on the right so you can see exactly what I am doing and how I'm gonna replicate the input fields to automatically fill in on sheets so helmi forms click on the plus icon and this will give us our first field to fill in so I'm gonna call it this one full name and then on the right hand side where it says short and so we have a multiple options in there I'm gonna leave it on a shorter answer because that's what it's gonna be you can also click which we required and you can also tell it if there is certain criteria that you need it to me so if you need to be a number or if you need to be letters or things like that and I will just briefly go onto that on one of the other questions but on this one I'm just gonna leave it blank for this why we get going so click on the plus icon again to give us another field to fill in and obviously looking at what we're doing on the left on the side our next question is the surname again I've got multiple different options I can pick I'm gonna leave it as a short answer and I'm gonna put these surname as required so if somebody tries to submit the form without filling anything in it won't let them do it until we actually fill in it the actual surname and again if I want to validate that I can it then tell it that I want it to be txt it must contain text in the surname and you can also put um if somebody tries to type in numbers for example in there you can put an error message that pops up to them and says this must be letters or whatever it be on next one is address or again + click on the + icon yours might be slightly different you do it different I'm going to type address in and then I'm going to have this as paragraph because it might be a long address that somebody's put it in its long enough that as paragraph and I'm going to put that was required and then I'm gonna hit the plus sign icon again next one is postcode or zip code if you're in America I'm you case or postcode but I'll put ZIP code in as well yours this could be anything can this feel this is purely me copying and what I am trying to replicate and then in this one again I'm gonna have your girl for short answer I'll go for short answer and then do we have a response validation so in here we could have a well because this is this it could be a text or it could be numbers I can't really validate that because a mixture of both well it is in the UK but I'm gonna put it as required because that will give us part of the address or know if the address is correct and you can tell if they the information is required week has a little red asterisks next to the side of the actual field that we fill in so again I'm just going to hit the plus icon and then put landline telephone and in this particular one I'm gonna leave it as a short answer I'm gonna put response validation and it's already picked up that it's a number so I'm gonna leave that as a number and I'm gonna put is a number as the validation required you can pick from them if it's different type of number you're recording so number is a number and I'm gonna hit plus icon again and go to our next field which is mobile telephone and again I'm gonna you can see there it's already put the validation on it they actually sees it's mobile telephone so I'm really that number is number and I'm happy with that I'm gonna put that is required so we have some form of contact for this customer and the next one is email so hit the plus icon again and I'm going to type in email and then for this one you can see it's in that I've used the word email in the text and it's an enable email collection which you can do later on in the settings isn't actually drone this part of the form and then once you get the email back it will show up in the responses when you click in responses over there obviously is nothing and being collected yeah so there's nothing in there as we speak but near the end of the video I will show you how the responses fills in and how it fills the actual spreadsheet in okay so obviously it's short answer it knows it's an email do any required yes we do and then from here we now have the form as we expect to see it so that's filling in all these fields or it will do when we when we send the link out and we start to generate a spreadsheet from this information so you can also format the sheet I'm not going to go into this too much but if I minimize the sheet I'll show so you can format the form I'm gonna minimize the sheet and then bring the forms in and I'll just briefly touch on the customization that you can do in the form but in mind that you you're probably using this or you might be sending it out to somebody so that doing sales for your something it's reception south on the reception and that you want to fill this form in so maybe you look make it look pretty maybe just leave it in the standard colors but I will show you how to do is actually open your form open in the top right hand corner you have the theme options by clicking on there clicking on the little icon there and you can pick up different things on here you change the banner page you can have a header image you can change the background color and basically sort of play around in there but you come format it to certain degree so the form looks nice and presentable when it's being filled in and it's been sent out okay so let's get to the how we automate this you click on the send icon in the top right hand corner and then you you are presented with a box in the middle then you've got a call it for an options here you can e-mail this out if you want to email it to something that's obviously not in the same office as you or not so at the same computer what we're going to do is actually click on the link and this is the link we're going to generate on our desktop so you can copy the main link there which is the full link where you can shorten the URL by clicking on a shortened URL and that will give you the shorter version of the link so make sure you copy that let's just go through how this works I'm gonna open a new incognito window so Google didn't recognize me or shouldn't recognize I'm signed in so I'm gonna need to paste in the shortcut that we just copied and then click and two to go and then now we are presented with the form that you could send to anybody to fill this in and now we need to put some information in here now am i have to blank out in certain bits here because this being Google it will try to automatically fill in some other bits so we see a blank box appear it just means that is putting information that I do not want on at this video showing up so yeah fill in the information as we've got so you can see anyway there's a red Asterix that is that way we've ticked that we need it as a required information and any of the input fields that don't have the red Asterix means that it's not required for this particular form now you might have it certainly - all these are required so it can't be sent to you or sent to the Google sheet without everything being filled in but yes only that the red asterisks is required so fill in the information as your form is set and once you've got all the information in that we're gonna just go down to the bottom where it says submit in blue and then we click on the submit and it tells you your response has been recorded so for this particular exercise I'm going to submit an over respond so I'm gonna fill in another form so all quickly go through this again if you see any blurs or anything then that'll be because it's blowing out something don't wanna see and you saw it then go red that was basically telling us this is required he can't sign the form until this information is in the particular form so we'll quickly go through this and I'll speed this section up so again just fill in the bits that your form requires so I'm just putting an old telephone number email and all the other bits and pieces in once we've done that we can then hit the submit again and then that submits that form to Google Forms but at this point we still haven't added it to a spreadsheet so we need to have a look how we get into and create the spreadsheet from this that one on the left that I'm showing at the moment that is purely one that I've created so you can see how we want it to look so that's not the actual that's not pulling any information from Google Forms at we haven't told Google Forms what we want to do with that and then pieces of information so when we initially was creating this form Google asked is when we put email in if we would like to collect the email addresses and you do this by opening a form page up on the free dots click on them go down to preferences and then there's a box in preferences it says collect email addresses so you can actually collect the email addresses and it works better this if you've got a business account but we are going to be collecting the email addresses anyway but we're going to be putting it in a sheet but that's how you collect the email addresses if you are wanting to do it through the Google side and you also notice in here we have the responses button but in here is the most important thing which is where we've got the create spreadsheet and you see where I'm hovering my icon there we then need to click on that in a second but in the response you can see I've submitted two of the forms and you can see all the different bits of information in there that has been inputted into the form okay so if we quickly have a look at the information that's been harvested from the two forms I've filled in and you can scroll down here and obviously as you start to build up more forms that come into this this information will build up so let's do the magic we need to click on where it says create spreadsheet and make sure that accepting responses is ticked we have two options in here where I want to create a new spreadsheet and we have one that says select existing spreadsheet so if you've already got one that's R up and running you can add to it but for this exercise we're going to create a new spreadsheet and then click on create this will learn that create a new sheet within Google Docs with the information that we have harvested and then each time a new format comes in that under that same link that we created before then it will be added to this sheet within Google Docs and you can see it puts it in what's a timestamp on there as well as all the different bits of information that we've titled from that original form that I created before so let's open google chrome in incognito and let's just put another set of information in so I'm just gonna type in here any way that you see the blur like before it's just because Google is trying to pre-fill even though I'm in incognito mode so I'll blank out obviously the basically try to pre-fill with some personal information so that's the only reason I'm blanking out on this otherwise you wouldn't see it so let's stick name for name a surname put a tape putting the address in there and then oil puts page just a postcode just so it's got it obviously have a zip code if you're doing that my made-up number in there I stick a number in the mobile and again it's from a pre-filter just blow that out and then or just stick an email in here and then this information as soon as we have created this information we can then submit it and what you should see then on the sheet in magitek it a second to refresh once I click on the submit button from this form but as long as you're using the same link that you created originally and that's the only one that you're using I mean don't get me wrong you can use different forms and create different forms for different sheets but then you'd obviously just label them as as you need them to make sure that you don't get mixed up but once you clicked on submit it might take it a while any math just refresh the screen just to get it to show up but as you can see as I have clicked on refresh at the actual sheet is still only saying its last that it was two minutes ago so it's still not even though I've clicked on refresh it's still not updated the sheet so I'll just click back on and click back in to sort of place on forms I was click back in sheets and see if that gets it to wake the the sheet that's collecting the information still showing two minutes ago and hopefully it will refresh it on there we go so at last that it was made seconds ago and there we go and there's the the form that we submitted is now updated normally won't be sat there looking at either both for them they've normally doing it automatically so you'd be sending it then maybe checking it in the evening to make sure all the information was there or pulling information off it so yeah that's it that's how to get the form to automatically send information to a sheet in Google that you can then process in the office or keep a customer information data sheet or collect any bits of information automatically from a Google Form so the next thing that we need to do is create a link to this form that we have created and put it on to our desktop so if we go up to the top right hand corner of the form you've got the three little dots and just next that you've got the send button and if we click on the send button then we get some options from here we can email it or we can click on the chain link click on the chain link in the middle and you can also shorten the URL which just gives you a link that's shorter and as a shorter description and then make sure you click on a copy and then from here we can then it cancel out of here and then attest the links working so right click in it the searchbar pasting go and then see make sure that your former shows up so we know it's working then we highlight it all in blue in the search bar and drag it on to the desktop and that then gives us a link which is just snap to the right hand side on my screen I'll just close the windows down in chrome and then we'll go over to the link and then double click on a link and then hopefully it should open up in the form that we've created and there we go so that's now the form and the link working on on our desktop so now every time that we want to input some fresh information we just click on the link and then fill the form in and then that will also populate on to the sheet and all the information is safely recorded so that is it that's how to create a form to auto populate a sheet in a Google Docs hope you enjoyed the video if you did please do hit the subscribe button it all helps please also hit the bell icon and hit me with any comments below and thanks again for watching at the computer lab on YouTube

Channel: The Computer Lab

Views: 180,185

Keywords: google forms survey tutorial, google forms advanced, google forms for teachers, auto populate data in google sheets, auto populate data in excel, auto populate data in excel based on dropdown selection, using google sheets as database, google sheets autofill, google sheets automation, customer data google sheets, data collection google sheets, data collection google forms, data collection using google forms, the computer lab, tutorial google forms, how to google forms

Id: jxqlkxRO5Aw

Length: 16min 40sec (1000 seconds)

Published: Thu Oct 03 2019