Google Sheets Userform - Add a Dropdown

all right so so far we were able to build this form that we can just basically type something and click Add and it's just gonna add to our spreadsheet let's try to replace this input box that says my item with a drop-down instead now before I do the actual drop-down let's try to do a little container around this box so that it doesn't go to the edge of this like this so I'm gonna go back to bootstrap documentation and let's look at this layouts I want to keep this form so I'm gonna right click and open this layout section let's see what we can do here we have the grid and we have containers see there's the regular container there is the small container let's try this one just regular container so I'm just gonna put this div around my content I'll copy this go back open the script editor let's go back and get our user form this is our HTML so this is that body section I'm gonna go here paste that code I don't need this and then just take this deep closing and put this all the way down here after this ending of this div save this let's see what this looks like so I'm gonna go back and redo this short form so now that's nicer we have some edge to this all right let's convert this to a drop-down so let's go check our documentation again this our forms now at some point here there's gonna be an example of a drop-down there it is if I scroll down a little bit see that's that select here so I need to make sure I get this entire deve right here copy that with the label select options all of that go back to my code and this is where I need that to happen I'm gonna keep this for a second and paste this below then I'll go ahead and remove this in just a little bit so if I paste this let me just tab this in so it looks nicer okay there it is so now we should have a drop-down so if I go back and reload this again clicking on this should do it we should have a drop down here C Mon two three four five now I don't want this to be one two three four five I want this to be some particular options going back to this if I want this to have let's say iPhone and my item as two options I would have to go here replace all these options and replace this one saying like iPhone and this one is my what was it my item now I want this quantity received to be this so I'm gonna copy this quantity received text place it in here and I'm gonna just take this ID that was for quantity received and put it for this and we also have to do it for four part as well now once I did this replacement I'm gonna have to remove this we cannot have two things with the same ID I'm gonna get rid of this and we should have now a drop down so I'm save this go back and load the form again and now I have item name and quantity this should have been the opposite shouldn't it this should be the item okay boy I think I'm just gonna replace the IDS here this here this there so this should be the item name it should go here and this should be quantity received for this and I'm gonna do quantity received here as well an item name okay so I think that should fix what I messed up before so let's go back and rerun this and now this is quantity received this is our item names now if you wanted this to be on top of this one you would just basically grab this whole thing and put it above this alright let me actually do that so take this and position this one right below just like that so now if I go back and reload this thing see I have this so now let's see if we can actually work with this just like this without changing anything else so I'm gonna take this type some number click Add and as you can see that went in here iPhone 5 if I change to my item and change it to 23 click add that works so now we have a drop-down instead of a regular input box but it works just fine now one thing I should probably do after we do this entry we should probably make sure we clean this quantity box so it doesn't show up I'm gonna go back to this so in our code we did this that we run this script and we basically just get that data entered now this is assuming that everything works well and it actually goes through there's a chance that it may not actually go through well so we have to do some handling and make sure that we only erase what's in here if clicking on this add was successful and we were able to add the item in here so in order for me to do that I have to make sure that this add new row function actually has a return so if I go back to my functions to make sure that this function has a return I'm just gonna go here and after all of this we're just gonna say return true so if that all goes successfully we're gonna return true that's good enough so what this is gonna allow us to do it's gonna allow us to go to our front and and then do Google script run and give this with success handler just like this and this is gonna accept a callback function so basically we're gonna say if that function is successful and it returns true let's just run this other function now we have to create that other function so I'm just gonna call that function right here I'm gonna call this one after submit and now we have to create that function so I'm going to go below here and create that function I'm gonna do this argument in here I'm probably not gonna be using that but I'm gonna do that anyways and that E is basically gonna be the return value which in our case is basically going to be if you remember the function true so basically this is gonna run if this is successful so if that's successful would like to clear that input box and to get to that input box I'm gonna get to this element again quantity and then I'm gonna make sure that I set the quantity value equal to blank so I'm going to save this go back reload this now we have this I'm going to do 33 click Add so that didn't go very well let's go check what we did hmm I didn't type function right function [Music] save this let's try this again go back and reload this thing all right let's try this again hopefully this time it will go better okay so we were able to enter that and then this cleared now if I do this 22 again it adds to the spreadsheet first then it removes it in here very good now the next logical step is gonna be to have this list of items come from some place in our spreadsheet instead of being hard-coded in our user form so what I'm gonna do I'm gonna add a new worksheet coldest options now I'm going to list those options here the first part is going to be to create a function to get this list of items so in order for me to do that I will have to go back and create a function not in the form itself this is going to be a back-end function to get the options first so I'm going to go to functions and create a function this one we don't need anymore it was just a test now this function is going to be a function to return our list of items so let's give this a name so this function needs to go to this options worksheet and get this range so for that I'm just gonna copy paste some things from here we need to get the spreadsheet again in that spreadsheet we're gonna get the worksheet called options I'm gonna take the worksheet and do get range and we need to start from Row 2 column 1 number of rows is gonna be however many of rows we have minus one so if the les rois for the number of rows is gonna be three so to get to that we're gonna do WS dot get less row minus one and then number of columns is gonna be one column at least for now so we'll do get values to get the array I've decided to call this actually drop-down options all right so once we get all of this we need to return these results so I'll just have to do return here to get those results back so this will give us this function return drop down array we're gonna go back to this to our user form and initially we'll just keep this drop down with this options empty so I'm going to remove all those options from this select drop down and what we'll do we'll get those options by calling that function and once we get those options will populate the Select box so we need to figure out when we're going to call this so we're gonna call this after this whole thing loads so to do that I'm gonna go back at a function that's gonna run after everything loads so what's gonna happen after this loading we're gonna run that Google script run all over again I'm just gonna copy this line and this time we're gonna run that function that'd be called return drop-down array actually let me rename this to get drop-down array that's probably better I'm gonna copy that don't forget to save this come back here and that will be get drop-down array now that function doesn't accept any arguments on like this one so we don't need to pass anything to it however it will have a success handler because what's going to happen is that after we call this function it's going to return this range back to us so because of that we need this success handler and we need to give it a different name so this is going to be the name for this function let's create that function this function is gonna accept those values that we're gonna get back from this spreadsheet that's gonna be an array of a race and we basically have to just loop through this array and place those values wherever they belong so they belong to this select box that has the ID item name so let's go grab that select box now what we going to do we're going to take this array of a race and we're going to run for each method on this and this will accept a callback function and parameter is gonna be passing is one of the lines in that array I'm just gonna call it a row are actually so every time we get and you've row what we're gonna do we're gonna create one of those options that we need in here in the Select box so let's create that element and the element is called option so that is this option element inside of that option element we need the text content to be whatever is the value in that array so we're gonna get that by doing that bro and getting the first item in the row it's gonna be array of arrays which means this row is gonna be the first row and in our first row it's just one column if we had multiple columns it would be 0 1 2 3 but now it's one column so it's just going to be 0 which is the first item now once we got that text content in this option we want to place that option in our list of items so I'm gonna do item dot append child and we're gonna append the option to it so I'm going to save this now this will hopefully add those elements to our list however we need to make sure that this function runs so for this function to run this has to run and get a success handler to get those options but for this to run this needs to run to now there's nothing in our code running this after sidebar loads function so we're gonna add an event handler to run this function when our sidebar loads when the whole thing loads so I'm gonna do that in here so we're gonna take the document and add an event listener notice I'm not adding this event listener to an element in this particular case I'm adding to the entire document and this will have an event which is Dom content loaded and that is basically when the whole page loads and after that happens we want to run that function the one that's called after sidebar loads so I'm gonna save this so basically this will trigger this function which will be this this will run that script in a background on our function side this that will return the range of values from our spreadsheet and then after that successful it's gonna run this function and send that information to that function where we're gonna take that information and loop through that and get all those to that item let's see if we made any mistakes so far so save go back to this I'm gonna go ahead and run this so let's see if we have any items yeah so you see it took a second for it to load because it does this after the page loads but we have our options apples bananas grapes so now if I do this enter 3 click Add C it clears now it's still adding two results so if I go to results I should see apples 3 and I can go here and do bananas and do four and that's gonna work now if I want to add more to this list I'm gonna have to go to this drop-down options and add another option oranges now I have to reload this because it's not gonna reload automatically so I'm gonna do my forms show user form again and now I should have oranges in this list too so that gets us the drop down and we'll continue in the next video and maybe we'll do dependent drop down so we'll have two drop downs and one is gonna be lets say fruits or vegetables and then depending on which one you select you're gonna get a list that's gonna give you either different fruits or different vegetables to choose from but for this one that should be it thanks for watching please subscribe and I'll see you next one

Channel: Learn Google Spreadsheets

Views: 32,851

Keywords: Google Sheets, form, Userform, Dropdown

Id: cKksZpsuJJM

Length: 17min 57sec (1077 seconds)

Published: Fri Apr 24 2020