Google Sheets - Userform

in this video we're gonna make a user forum in Google sheets I'm gonna be using Google sheets and materialized CSS dot-com user interface to build the actual user form so if you did watch my web app series then a lot of this is gonna be pretty easy for you to follow if not I would highly recommend you watch my web app series first either way it should not be too difficult so I have an empty Google spreadsheet open I'm gonna go ahead and open my script editor so I'll go under tools script editor that should get us here now I have my material ICSS open on this tab and I'm gonna be using some elements from here now before I get to those elements I'm gonna go back here to my project and the first thing I'm gonna do I'm gonna make an HTML file so I'm gonna go on there file new HTML file and we'll call this user forum and this HTML file is going to be the actual user interface for our user forum for that I'm gonna go to materialise CSS I'll go with getting started and scroll down a little bit so we have a couple of CD ends here we can use and before I get to using those I'm gonna scroll down until I find this main getting started template I'm gonna go ahead and copy that whole thing I'll go back to my app delete everything here and paste my HTML now I'm gonna save this project ctrl s or command s I'm gonna give this a name user form - Tori oh that's good so I need to replace a couple of things one is this javascript file so I'm gonna be using the CDM for that so I'm going to scroll up so right here see the second one is our JavaScript CDN I'm gonna cop that I'll go back and replace this current JavaScript link that's linking to a non-existent file to this one so I'm gonna just line this up a little bit and I also need to replace this CSS so I'm gonna go here copy the CSS go back here and I'm gonna replace this CSS link I'm gonna remove this comment line here - something like that so that should just give us our main UI and most of this is gonna be in the body section which is our main HTML that's gonna go there now in this body section I'm gonna make a container div and this is just to have some margins and stuff from the sides so it looks better so I'll just have a div and that's gonna be the whole container around user interface so here I'm gonna go to forms so I'll do an input box I'm gonna scroll down I guess this is fine so the actual field is see there's this real structure with fields and there's the form that goes around it I'm not really going to be using a form so I'll just copy from this row and there's this divs there's this divs until this row so this is the form this is the end of the form I just need whatever's inside of that command C or control C to copy it go back here and I'm gonna paste that right inside of that container div something like that so basically in this container Dave we are creating a row and that starts here and ends here and in this row we have one column and two columns this will basically make them two columns side by side the reason for that is this is six and this is six and total length is 12 so again if you haven't watched my web apps maybe check it out to see this in more detail but I really don't want them on the same line I want them on a separate lines so I'll just do 12 and 12 will cover the whole line and then I'll do another 12 to cover the next line so we'll have two lines I'll add one more of those column structures here and it's almost like I'm not highlighting the right thing yep because this is the div this is a row let me just comment this to make sure I understand this is the ending of this so HTML comments they go like this so we'll do and row and this will be my column structure so I'm gonna add another one of those paste and the last one I'm not gonna use all of that inside of this I'm gonna remove that save what I will do I will look for a button to use here so I'll go there components probably buttons and scroll down until I find a button there it is copy this go back to my app and paste my button good so this button we don't need to be type submit' or name all of that so that should take care of it overall I should have now some HTML and this will be my HTML in this same HTML below this I'm gonna add a script so the script tags is our user interface side script for now it's just gonna be empty we'll add some code later on so this is basically the HTML now let's try to load that here so we'll first load it as a sidebar I'll then show you how to also load it not as a sidebar if you wanted to so it's not gonna be that different I'm gonna go here and create a new function let's call this show sidebar actually let's just say show user form because I guess sometimes it's not going to be sidebar for us I'm going to create HTML service out of that template file that I'm creating here this user form HTML so I'll do HTML service and we'll do create template from file and the file name is called user form and that's pretty much it I'll just save this in a variable okay so then I'm gonna create HTML out of this so to do that we'll have to take that template and evaluate it and finally we need to somehow now use this HTML output and load it on this spreadsheet first we'll do it as a sidebar to access our spreadsheet we're gonna use our spreadsheet app get you I basically user interface dot and here we have different show methods see we have show dialogues these two there is also this show sidebar so I'm gonna start with showing sidebar and you see the object is the user interface so the user interface is gonna be the actual HTML semicolon to finish this line so let's try to now run this function to see what we get now this is our spreadsheet currently I'm gonna go back here and run this this function show user form we will have to give our regular you know permission stuff so let's go through that really quickly here then it's gonna run this let's go back take a look and now we have a sidebar so I'm zoomed in a lot I'm gonna zoom out a little bit so this looks the way it should look but you can see how we have this two input fields and then we have our submit button which really don't do anything right now but the point is we made our interface and it loads now if you look on the sidebar on top it says Apps Script application now we probably would want to change that to something different after I evaluate my HTML I can take that HTML and do this set title method and here as a title we'll just write whatever the title is supposed to be so we'll just say user form base 6 and semicolon so if I save this ctrl s command s run this again now it's gonna say whatever that says so so far we were able to load some form and this icons and all this stuff obviously we can change so let's say this is what I want to do I want to have something here that says first name last name so I'll have two fields to populate the first name and the last name maybe because it says telephone we'll just use telephone let's just do this name it will be full name I guess or something like that and this will be fun so what I want to do I want to be able to go here and type whatever the name is and whatever the phone number is hit submit and I want to add record right here below and then if I do again I want to add another record and keep going and that will be our user forum to add data to our spreadsheet so we're gonna go back here I need to grab information from the forum that's gonna be all on the user side so I'm gonna go to my HTML and start using that script thing now because I'm gonna have to grab some information from the forum I have to add some identifiers to accomplish that so the first one is this input box where we're gonna type name so I'm gonna change that ID to user name and I'm gonna copy that and I'm gonna give that same ID for this for so this has to match this and we'll just change this first name to your name this one is our telephone so I just want to say tell short so copy that for paste the type in this case I'll use text type again it will be just text input like this one and icon phone I guess that's fine ctrl s or command S that's gonna save this and finally our button right now it says submit which is basically this text over here add let's just run this so we get some of the changes which is not gonna be a lot but it's your name and it's added now I'm gonna also add one more ID before I get to the actual script so we'll do ID BTN for the button because we're gonna have to grab that button when somebody clicks on it so now I'm gonna go to my script I'm gonna create a few variables so the first variable is gonna be for whatever is the name the second one is the telephone number so I'll create the first variable I save our name what that variable is gonna be it's gonna basically be our name field maybe we should call it name box or name field I'll just call it name box so we'll do document dot get element by ID and the ID is going to be for this name box whatever I gave it which was user name so I'll copy it go back here and paste so what this is going to do it's going to basically just go inside of our document which is basically our HTML that's the way you can think about it and just search for an element with the ID username and it's gonna find this element this input box and basically we'll just save this whole input box in a variable called name box now we're gonna do this again for our second box so we'll call this foam box and the ID was tell so that should grab that other box what I want to do I want to be able to run and get some information when somebody clicks on this button so this is where we're gonna do an event listener for that button so we'll do document and again get element by ID all over again the ID for our button is BTN and we're gonna add an event listener to that button and the event we're listening for is click event so when somebody clicks on a button and when they do we want to run a function so I'm gonna call this function add record and will create that function right here so we'll do function add record let's just recap what happened here so basically we said this is this button element this month but we're saying when somebody clicks on this you're gonna run this function called add record and we create that function right here and whatever happens in these brackets for this function that's what's gonna happen when somebody clicks on the button so what we want to happen is to go to this name box and get the value out of that name box and then go to the telephone box and get the value out of that telephone box and it will save all that in a variable we'll call it data so that's the data we're gonna grab and we're gonna send it to our spreadsheet and that will be an object and in this object we're gonna have two properties one is gonna be the name which will be whatever we get from here so name box dot value will get the value that's typed in inside of that box and the second one is phone phone box dot value and that's gonna be the data now I'm gonna try to send this information to our code J s over here so we can basically process it and hopefully add it to our spreadsheet so to do that I'm gonna add this Google dot script dot run and we want to run a function and we're gonna call this app and data to this function we're gonna send our data what this is gonna do this Google script run whatever you do after this period that's gonna be the function that it's gonna run from here now that function here doesn't exist so we have to create that function and we call this function append data so we have to create that function here on our script side and now what's gonna happen when somebody clicks on that button it's gonna run this add record function and this add record function is gonna go grab the values from that form and it's gonna create this data object and we're gonna send that data object to this function append data which is gonna be this function that's gonna run now this append data is where we're gonna be able to now communicate to our spreadsheet app and add this data to it so we're gonna do spreadsheet app dot we need to get our spreadsheet in this case I guess active sheet is fine active spreadsheet actually not active sheet I will get get sheet by name so we have to decide which tab we're gonna use so we're gonna use probably this tab I'm gonna just call this tab data now we need to match that in our script so that goes as text so get sheet by name data so we're gonna go grab that worksheet will save our WS short for worksheet is that what we want to do we want to be able to add more data basically a pant row and this append row will have to do the contents of the row and what we need in this contents of the row is something that basically is an array and in this array basically the first one should be something like the name so like Joe and the second one should be a phone number but we don't want to hard-code this stuff we're just gonna grab them from this data object so we'll say data dot name and the reason it's thought name is because we just did it here we did data name and data phone so we'll go data dot name and data dot phone save this so all of this needs to be saved now let's try to rerun this because this is the old user form to try to rerun this form to get the new one we apparently have more permissions we have to give there this advanced allow okay so now we have this we have this so let's try to see how this works so I'm gonna go John Doe and we'll go here and do 3 1 2 0 0 0 0 0 0 0 click Add there it is now let's try Joe with a different phone number so that adds another record now this is a little annoying that every time we had to record this boxes still have the text in here so let's try to clear that so I'm going to go here and that's in our form side so after we run this we might want to just clear those boxes and the way we're gonna do that we're gonna say name box dot value should be empty and foam box that value should be empty this should be fine so again I'll have to reload this form with a new version so I'm gonna have to read run this function show userform this one so now let's try this again there it is now it clears much better okay so we made our form we can enter some information we hit add that adds a record to this database again we go back and do this we type a new number click add that's gonna add to this now one thing we might want to add to this form is that right now if I don't type anything here or here or maybe I just type the phone number I hit add still gonna add the record because there's really nothing stopping it from doing the same thing so I'm gonna clean some of this up so what we want to do we want to go here instead of just blindly just grabbing the data and running and sending to the script we might want to do a little bit of validation here and make sure that there is something in both of this boxes the name box value and phone value are basically not empty to do that let's just first put those things in a variable so I'm gonna say var name equals that and we'll do var phone equals this so so far we'll just store those in a variable to make this easy for us to work with this I'm gonna do an if function and I'm gonna say if name dot length and that will basically just check how many characters are in here and if it's zero characters this name dot length is gonna give us 0 we're going to check if that is zero or if the length of the phone is zero then we're gonna know that one of them is empty so let's just create a simple so we'll do a browser message box and we'll say name and phone required so again we're saying if the length of the first field is zero or the second field is zero then we know that one of them is empty so let's just do that message box for the user otherwise we know that they filled in so then we're gonna run all of this so I'm gonna indent this whole thing a little bit I started this else bracket I have to close it so that's gonna go right here close else and this one is close add record all right so let's first of all try this so we need to reload that form all over again so I go here click Add interestingly nothing happens let's try to type something doesn't do anything so apparently it did stop me from adding the records however it did not show the message box here oh that's because browser message box is gonna be from this side of course yeah so we can't really do browser run commands from here because this is our user side we can only run this from our code side to actually output that so that's why this didn't really work so we could however put this in a function here and run that function so just to show you how that would work we probably wouldn't want to do that anyways but I want to show you how it would work so we'll do function and we'll do error message and this is where we would run this piece of code and to make sure this actually runs we would have to go here and instead of doing this we would just say Google script run and we're gonna run that function from the server side which should trigger that browser box to probably show up so let's try this so I'm gonna save this which is just in case I'll reload this so let's go back here and click Add C name a phone number are required so now we're running from the right place I'm gonna hit OK now we're gonna go here and type joke and if I click add same problem and if I enter a number should be now fine so we have a little bit of validation now built in now one extra thing here is that what if we go here and hit a space or a couple of spaces and then enter a phone number well now it's no longer as zero because two spaces is still two characters so let's try to handle that too so to do that what I'm gonna do I'm gonna go back here and here we just check the name the length and if it's basically two spaces it's still a few characters so what are we gonna do we're gonna take the name we're gonna trim it and trim is gonna remove all the spaces before and after our text so we want to trim it before we check the length so I'm gonna say that now I'm gonna have to reload this because we made changes every time you make a change to this HTML you have to reload this because this is gonna change so we're gonna run this again go back hit add I get the error I do a couple of spaces I still get the error because now it's not gonna work that way so now we have to actually type something and it works let's clear this up that should do it we can X out of this now finally I want to show you how to do not a side bar but a different type of user form so I'll go here and do some changes here so see this is the UI so when we get the UI we can show sidebar or if you remember there were some other options so I'm just gonna comment that line here and if we do a period show you'll see we have this dialog boxes and the difference between this two is one of them is not gonna let you do anything in the background and the other one will so if I do this first one the first argument is gonna be again that HTML and the second one is the title for this dialog box so we'll just say new title so you can see let's run this again so show userform go here see now that user form is gonna show up like this see there is nothing I can do in the background it's basically just blocking the background but I should be able to go here and type click Add C it's adding to the spreadsheet in a background we can see and we can obviously go for another one there it is and what happens if we just don't provide all the information we're gonna get this and then that goes away because the browser message box is the same type of box so it kind of overrides this and that goes away and that's why when we start handling this errors we should probably just handle it inside of our form instead of having that message box this error box showing up but now what I want to also show you is that other option comment this and let's do that third option and this one again is gonna accept the same arguments the only difference is that now we should be able to also do things in the background to see while this form is gonna be on top so that's that you can also resize these forms and the way you would do that you would go here see how we have this HTML we could do something like set height and if I do like 500 userform now so see now it's longer and then if you want to resize it this way you can also do set width and just decide whatever this is gonna be let's see now it's a bigger form so for me I don't really want those I'm gonna take this off get back to the basic thing so the final thing I want to do I want to just handle that situation with this error message in a different way I don't want this browser message box so I'm gonna take that off go back to my form here instead of running that we're not gonna run that handle error we have this form so maybe below this add button will have some message that will pop up and give you like an error let's go see if there is something nice we can use from here so let's see what would be a good option for this we can go under CSS color well we could do something like this for example or something like this we might have something more interesting here like yeah these should be nice so for example see if I click on this it gives us this message thing that disappears after a little bit so I think this should be nice to use so basically it says we need to run this function and have the message let's try that I'm just gonna copy exactly what we have there go back in our user site we'll just do this we'll say phone are required save it I have semicolon missing here put it there save it and because I'm making changes to HTML I have to reload this so I'll go here click Add so that should I think disappeared there it is nice so that should do that so now if we type something else we click on this still gonna do that and then if we type a phone number we shouldn't get that great so now we can have an error message and you can decide whether you want this as a side bar or whichever pop-up box you prefer I'm just gonna do this one for this so we'll do side bar run this starting to think that side bar might be too short for this error message let's see no that's fine looking good so there it is now we have a user forum we're able to type some information pass it and run it and add it to our spreadsheet and that should do it for this video thanks for watching please subscribe and I'll see you next one

Channel: Learn Google Spreadsheets

Views: 124,034

Keywords: Google Sheets, Userform, tutorial

Id: PsIuSwSj3CM

Length: 35min 50sec (2150 seconds)

Published: Fri Mar 15 2019