Google Sheets UserForm - Send Data from Form to Spreadsheet

this is a spreadsheet I have it's basically a blank spreadsheet I have this first worksheet called results this is where we're gonna store the data from our user forum as a user interface this time I'm gonna use bootstrap not materialized CSS I would continue using materialized CSS but I've been looking at their source code and they haven't done an update for a very long time now which is kind of concerning and when I asked everybody what they want to be used as a UI it seems like vast majority of people wanted bootstrap so that's what I'm gonna use in this case so for people who already know about web development in general you should be able to use whatever interface you want it's not gonna be any different right this website that I'm on is get bootstrap comm and I'm gonna be getting some web interface results out of here but for now let's go back to our spreadsheet and start building this so I'm gonna go on there tools script editor dismiss this we will be using v8 engine so that's fine name this project call it anything you want it doesn't matter so the first thing we have to do we have to build some sort of user interface for our user forum so I'm gonna go on their file new create an HTML file and give this file a name you can call this anything you want just make sure we remember what you call this I did call this you form and as an HTML I'm going to use like I said bootstrap so I'm gonna go to get bootstrap comm here I'm gonna click on get started so if I scroll down a little bit see we have this starter template so I'm basically just gonna copy all of this go back to this and replace whatever's in here with my code so I'm just gonna remove all of this paste that started code from bootstrap and that's it save this so in here we're gonna mainly work in the body section we're gonna leave the rest of the code alone so for now we just have this h1 that's fine so we'll keep this at this now let's try to render whatever this is which should be just this hello world text in here as a user form right so to do that we're gonna go to this code AS now I probably want to leave this alone so I'm gonna do file new script and here we'll basically just do a script file I'll call this one load user form so what I need to do now I need to create a function to load that HTML as a side panel here or some other panel it doesn't matter so I'm gonna call this load form and to do that we'll go inside of this function we need to return an HTML service but before we do that let's create that service first so I'm gonna create a constant here so if you're not familiar what constant is if you were using VARs before just think about this as a replacement I'll do video covering all of this at some point but for now just think about this as bar so this will be my variable name now I need to get htmlservice and we're going to use this method create template from file the file is gonna be this you form HTML so I'm gonna just place the name of that file you form you don't need the dot HTML just the name of the file just like that so now we need to create another variable here and what we'll do we'll take this object and evaluate it to get the actual output for our HTML so now we need to place this output in a sidebar so that needs to be a sidebar in our spreadsheet so we're gonna take our spreadsheet app get the user-interface it stored it in a variable again and then in this object UI we should have different methods one of which is show sidebar and see that will accept the user interface and that user interface is going to be our HTML output this one from here let's try to run this and see what we get right now I'm gonna save this this is this load form I'm gonna run this do all the permissions now to see the results I'm gonna go back to my spreadsheet as you can see this loaded now this looks really big but if I just go to regular size this kind of the way it looks like it's still pretty large but because I'm zoomed in so much it's gonna look really really big but that loads the sidebar now I don't want this sidebar to be loaded after we go here and manual running for load form what I'm gonna do instead I'm gonna add a menu here on top so that we can click on it and it will basically just load this sidebar so let's create a function to do that menu so we need to start by getting the UI again and then I'm going to take that UI and see there is this option to create menu caption for this is the name for this menu let's save this in a variable so this is just gonna create this main menu here like this format data at that part but if you click on that you need some options here so one of those is going to be to load the sidebar so we need to be able to click on that and load the sidebar so let's go and do that so we're gonna take that menu that we've created and add an item to it and it will have two options here one is what that item is going to be called and the second one is the function that when you click on it that's gonna be run so I want to run this load form so I'm gonna pass that as a string and then finally we're gonna add that to our UI so that's our create menu function now if I run that function manually that should add the menu but we want that to happen when we open the spreadsheet so what we're gonna do we're gonna use an on open special method here and this will run automatically when our spreadsheet loads so when it runs I would like to add this menu so I'm gonna do this function create menu so with this setup we should hopefully be able to refresh our spreadsheet and be able to load the user form let's see what happens let me zoom out a little bit see there is that new menu my forms if I click on this there show user form and if I click on this that should show the actual sidebar now let's continue now by adding things to our sidebar and trying to enter those in our spreadsheet so I'm gonna do a couple of columns here so basically what I need is three different boxes here to be able to enter some data which will be the name of the item the quantity received and date entered actually we're not gonna let the person choose it will basically just be entered automatically as one of the fields so two fields I'm gonna be getting from the user and date entered will be the actual date when this was entered to do that I'm gonna go to my script editor and I need to add it to my HTML so I'll go to my u form so here we don't really need this hello world thing I'll go to my bootstrap documentation let's go get some elements here I'm gonna go to components go to forms and let's try to find something that's pretty close to what we need oh that's actually pretty good let's see if there is anything better well do a drop down in a little bit for now let's just do basically two entries yeah I think I'm just gonna use the first one that was okay I'm just gonna copy this whole thing go back to this and in this body just paste that entire thing I'll tap this in to just make sure my indentations are correct to keep this organized so I don't really want this small text C right here but if you wanted something like that you could keep it so this first input field for me is going to be the item name and the second one is going to be quantity received so I'm just going to go here as a type this is going to be text and as an ID we want to enter what this is gonna be so first of all let's just put the name in there that's gonna be this right here in the label this is just text doesn't do anything just whatever you want to display and ID is gonna be important no spaces in IDs and don't start with a number no special character slider usually we will do something like item name I'll do a - you could use the - now whatever ID use here you want to make sure you use the same ID here as for that's that we don't need this part so something like that so the second thing after the item name is gonna be the quantity so this so again the type is gonna be text you could also do number type I'm gonna do text for the time being at least and ID is gonna be this and again I have to match that ID with this for that's good now I should have item name quantity received those two fields and then if you look here they have this checkbox I don't want that and that's this whole thing I'm gonna get rid of this and we have a button so the button right now is type submit' I'm just gonna say add that's just the name that's gonna show up here see it's a submit I'm just saying ad instead of submit and then the type also I don't want it to be submit so I'm just gonna remove the type just do a button like this let's just save this let's see what this looks like so I'm gonna close this and go back and run this again so we have item name and quantity received two boxes where we can basically type things it's not the best-looking form but for now let's just try to get this to work so this is what I need to happen I need to to type the item name and type a quantity he'd add and after I do that it should be adding this to here now because that was a form when I click add that automatically triggered the form entry and this is blank so we don't want that to happen either so I'm gonna remove this form thing out of here for now even though we could take care of it a different way but this should be fine actually I'm just gonna put that back and change that form to div because we're gonna need a container anyways now it will be just a division box they've invisible containers so it should not close the form okay so now that we did this now let's try to get this information from here and try to send it to our spreadsheet over here right so what we need to do we need to first create a function that will be able to add the item name quantity received and date to our spreadsheet and it's not going to be connected to this form yet so let's first of all create that function I'm gonna do this in a separate file so I'll do file new script I'm gonna call this funk so this function will be the function that will be just adding a new line to our worksheet so I'll just call this add new row we need to add row to the spreadsheet here so we need to get our spreadsheet app and in our spreadsheet app we're going to get our active spreadsheet and we'll save this in this variable SS so in that active spreadsheet we're gonna get this worksheet called results so we'll take that SS which is our spreadsheet and do get sheet by name and the name of our sheet is results now in this worksheet if we do WS dot there should be a method append row which will accept row contents as you can see so what that row contents means is that if I have let's say three things here so we want the date to be whatever the date is when we're entering this so we can get that by doing new date object so these are three things that I'm going to be entering to my row so I'm gonna do them as an array here and that will be the item name comma QT Y comma and current date and if we have more columns we can just keep comma separating those so let's try to run this function from here see what happened no errors let's go back in our spreadsheet so as you can see I was able to add that particular line to this if we run this again we add that again but now it should be a different seed time and second so that's that now I need this to be a function that works on different items so what I'm gonna do instead of setting all this stuff in here well I'm probably gonna set the current date still here because we still gonna get the date but I'm gonna pass this item name and quantity as arguments to this function so now I should be able to use that function in a different function so for example if I go here and create a new function I should be able to run that same function on top by doing this now if I change this to that function another func we have to write now if I've run that function go back and check what happens see we add the new line so there are a couple of things I want to change so I'm going to try to pass these things instead of this individual two items instead I want to pass them as an object to this function so the way I can do that instead of doing this I can just pass these as an object like this where I can say item is this and quantity is this something like this so this is the way I want to run this so the way I can do that now assuming that this is what I'm gonna pass to this now this is no longer gonna be this two parameters separately it's just gonna be all the data we're gonna pass to this so let's just call it road data so that's gonna come as an object which means now we're gonna take that raw data and in that raw data we're gonna have this item and quantity so what we could do we can just say raw data dot item and raw data dot Q T Y so this way we should be able to get this and this out of here and run this function and this is still gonna be the same so we're still gonna get our current date that's fine let's go and run this so right now let me just delete all of that try to go back and run this see what happens so as you can see it works the same way we're able to add the line now using this object alright so now that we were able to do this I don't want to run this function doing what I'm doing right here I want to be able to run this function the same way from the form because the whole idea is we're gonna enter our name and quantity received and then click this Add button and when we do that we want to actually add the new line so let's do that we've reload this so that form shows up okay so let's go back and do this so what I'm gonna do now I'm gonna move to the front and side which is the actual form so in the form we're gonna have its own scripts so I want to make sure I put the scripts all the way down below all this other scripts that come from bootstrap so I'm gonna go here and do a script now this will be the script that is gonna run in the actual forum here so what i need that script to do i need to get whatever information is typed here and here first which is in this box and in this box so i'm going to get that using our document object and here we're gonna have a method get element by ID this is case sensitive so you want to make sure you don't misspell this and have like lowercase e or B or one of these now we're gonna get this element which IDs item name this input box and we need to store this in a variable so here I'm gonna use VARs because this is front end and we want this to be compatible and I need to do the same thing for quantity now this is just gonna find our element we need the actual value in the box and what we need to create with those values is something that's similar to this so I'm gonna go back to this and just create a variable and what I'm gonna do instead of doing this hard-coded iPhone I'm gonna take this item input box and get the value out of it and then instead of having hard-coded 45 again we're gonna take this and get the value out of it that should create that road data now once we have that raw data we want to now pass it to this function add new row to run this so it adds a new line right so that's that add new row function over here where we have to pass that data and we'll do that by going back in here we close all of this stuff so I don't go back to those we're gonna do Google script run dot and then pass the name add new row which was the function name from here and to that function we're gonna pass this raw data which is over here now these are the steps that needs to happen but I don't need the steps to happen when the page opens I need all of the steps to happen when we click on this Add button so for that reason we're gonna put the steps in a function so they don't automatically run now we want this function that I called after button clicked to run when we click on the button the button is this button and I'm gonna give this an ID now that's the ID for this button it's called main button so to get the button I'm gonna find the bottom by its ID again and to that button we're gonna add an event listener the eventlistener is gonna be clicked so when we click on the button we want to run this function after button clicked so this line needs to be outside of this function so this curly bracket has to close before we run this so let's save this go back and reload the whole thing and see what happened so I'm just gonna test this let's type an item name and quantity received and click Add see what happens it works so the form works we can type in it if I click Add again that's gonna run it again it's gonna add if I change this to like 23 click add so so far so good I'm gonna end this video on this and in the next part we'll try to do some improvements one of which will change this thing that says my item to a drop down alright I'll see in the next part

Channel: Learn Google Spreadsheets

Views: 79,006

Keywords: Google Sheets, userform, data, send, spreadsheet, html, javascript

Id: yZNPkcT4PKA

Length: 23min 53sec (1433 seconds)

Published: Wed Apr 22 2020