Web App - Google Sheets CRUD - Part 1 - Load Views

alright so in this video I'm gonna show you what we gonna build in our user forum level 3 series so what this is gonna be it's gonna be a crowd application so create read update delete so basically what we'll be doing will be creating lines updating lines deleting lines and finally just reading lines from the spreadsheet so to show you what the final application is gonna look like we're gonna have this menu here it's called custom menu I'm gonna open it and click on open form it's going to load this forum which has a search which is basically using these data in a background so if I want to find somebody let's say I want to find Paul right here I go here and I search Paul and I can search by the first name or the last name or both so for example I can do something like that and that's gonna basically give us this dynamic results as we're searching we get updates and if I want to remove Paul from our list I can just click delete I'm gonna get this dialog to confirm I want to delete this I click confirm and we should see how Paul gets removed from the background back there see it's gone so now let's say I want to also change somebody so I'm gonna go here and find somebody we can actually see in the background so let's look at Lawrence Brown here so I'm gonna go here and type there is Lawrence Brown so I'm gonna click Edit it basically loads that person's information and if we want to change it we can just go here and type something else click Save and see how it updates in a background in a spreadsheet we also get the confirmation here so we can also add a customer if you want to add a new customer to this list we just go here add a person customer and now if I check we should have that person here and it's gonna automatically assign a new ID for this person as we do this and we also have this nice counter when we search see tells us how many results we have after the search something like that so we can edit delete add basically what you would expect from an application like this so to be able to understand what I'm doing in this video you need to have some good understanding of HTML selectors you need to have good understanding of event listeners if you don't understand these I recommend you watch my HTML Javascript series I'm gonna link to that series in the bottom of this video if you want you can watch that you also need to understand array methods in JavaScript which again I'm gonna link to series covering all of those in the bottom of this video too a lot of this is also gonna be based on what I've covered in my previous user forum series so there's user forum level 1 level 2 and this is gonna be the third one which is level 3 so I would recommend you watch those level 1 level 2 2 to make this easier now in level 2 I also did a lot of other things that you could implement here that I didn't implement so for example I didn't do any validation in this forms so it's not gonna be checking if I'm entering any information or whether it's correct information or not if you want to add those type of functionalities to this app refer to my level 2 series and you can see how all of that can be done so to build all of this we're gonna be using Google sheets will be using bootstrap as our framework basically that's get bootstrap calm and basically just writing our own JavaScript code all right so let's get started so in this video we're gonna primarily take care of the problem of loading different views in our user forum or a web app you can apply the same concept for both cases now let's start by doing some initial setup so I'm gonna go ahead and go tools script editor so by the way I have this data this is the data I'm gonna use for this it's pretty simple it's basically first last names and phone numbers and we have customer IDs which you need to have some unique column of customer IDs so you can just go one two three four five six now in this particular data set I have about 6,000 lines of data now if you're following this and you're trying to replicate this you don't really need a database of this size you can just do five six lines and that should be enough to work with this I'm just doing this larger data set to see how the app reacts to this and just to see if there are any speed issues with this so again after tools script editor we have our script editor we're going to name this project as usual you can call it anything you want I'm gonna go ahead and create a script file I'm gonna name this one load form again you can call it anything you like and I'm gonna create another HTML file here I guess I'll just call it main that's good so for now I'm not gonna worry about this part I just want to make sure we can add a menu here on top that we can click on and load the user form so let's start with that I'm gonna go to my load form file and let's create a function here this is what I'm gonna call this I'm gonna first create an HTML service and we're creating a file from template and the template is this HTML so it's called main now we're just going to evaluate that so we get some HTML back then I like to add this to our user interface and we could add it for example as a sidebar using this show sidebar but I think I'm gonna need more space than a sidebar so I'll go with this dialogue so it's asking me for the user interface so that's gonna be this HTML and it's gonna ask me for a title this is just text we'll just say edit customer data so this is it for that now let's also create a function to add a menu here so we can actually trigger this and open this now here we need to get our UI again just gonna copy paste that then we need to add a new menu to this so we'll take that UI and create a menu I'm gonna call this custom menu that's just a text for the menu you can do whatever you want then I'm gonna take that menu and add an item to it and I'm just gonna call this open form and what I want this to do when we click on this I wanted to run this load main form function to basically just load our user form now we need to make sure we add this menu to our UI and that should take care of it so now this create menu should create a menu here now we want to make sure this function Browns so that menu is created and we're gonna do it on open now I'm not explaining all the steps as much as I usually do because I did cover all of these in previous videos so if you don't understand exactly what I'm doing in this lines go back and watch previous levels of user form series so it will make a lot more sense so here we're simply gonna run this function create menu now out of these three functions that I'm gonna do I'm gonna actually make the ones that are not supposed to be public private like this create menu we don't need to call that function from anything external than our scripts so I'm gonna add this underscore and that will make this function private which means when we call that we're gonna call it here now all open is a special method we cannot make it private so that's gonna stay the way it is and load may inform that one needs to stay public because we want to call this function not from our scripts here but from here when we open the menu and click on it so I'm gonna save this now if I reload my spreadsheet it takes a little bit but see we have this custom menu now if I open that there is open form if I click on this it's gonna ask me to do all the permission stuff and now let's try it again and you can see how this thing loads now there's nothing in it it's empty but we have a form now I want this to be a little bigger because I'm gonna need more space than this so let's go resize this so let's open the script editor so to change the size of this let's go to our load form we can do it by taking this HTML that was rendered here and setting some dimensions on that by using set width and set height save that now I should be able to reload this without having to refresh the entire spreadsheet so if I just go here and do this more permissions so now we have more space to work in here all right let's continue let's go back to that main HTML and put some HTML in there I'm gonna be using bootstrap for this so that's where I go to get bootstrap comm and click on get started scroll down to find the starter template copy all this code here and just paste it in here I'm not gonna need this title I'm gonna remove that and below this entire thing right here I'm gonna add a script and it's important to have that script in the bottom so I'll go right here and do a little bit of container around this entire thing so let's go find a container you inside of this container I'm gonna do a div and give it an ID app and then above that ID app I'm gonna add a little bit of navigation alright so for navigation we'll go here and find our navigation examples I'm gonna go into components navs you can also do this other one I'm just gonna scroll down until I find something I'm gonna like Gooding up for this this is actually pretty good I'm just gonna copy that go back to this and paste this right here in this container above the app I don't want these to be links so I'm gonna change all this ace to divs and I'm also going to remove this disabled I'm gonna remove this hyperlink references just some labels nothing special here saved us go back and take a look so we have this now I want to change this to have a little hand when you roll over this instead of this cursor so that is this nav link class let's style that and we'll just make the cursor:pointer which means now I get this hand alright so we got a little navigation happening here now we need to figure out how we're gonna load different views here depending on which one will click on let's give these some IDs you so I want to just partially load whatever view needs to load inside of this app div so let's create some views so I'm going to do one for search now one for add customer so just HTML files for those now these are not going to be full htmls these are just gonna be parts that are gonna be loading inside of this main view inside of this div app so for now let's just keep it simple and put some aged ones now let's see how we're gonna be able to do this so we need some way that we can just go here click on search and load that search view in here and add customer load the add customer view here just below so let's create a function for that I'm gonna keep these functions separate from this load form I'm gonna close this as a matter of fact than this one to create a new script some name for that script now again we're gonna need to use HTML service so let's go ahead and activate that and we're gonna evaluate it to get back HTML but this time I don't actually want HTML as HTML service I want that HTML to be just text so I'm gonna do that by doing this get content method on this now since this is gonna be happening on different views and it's gonna be pretty much the same thing over and over again one for search one for add customer I'm just going to create one function that I'm gonna be able to reuse so I'm just gonna go here and say partial and call this load partial HTML and then just pass that partial here and return this content just like that so this way we should be able to reuse this function so I'm going to create a few functions here and we should be able to just use this and instead of partial we'll just pass the HTML we need to be returned which for well actually let's do this for search for now I want this to work or add customers to it's pretty much gonna be the same thing and this needs to match to the file name for your HTML you have here that's it this doesn't have to be a public function because we're not gonna be needing this outside of this so I'm gonna make it private these are gonna be public because we're gonna have to get these results back in our user form so we can use those all right so now that I have this partial HTML strings we need to add those to our form so to do this I'm gonna go to my main HTML and simply go to the script and add a function and an event handler and we're gonna call that when we click on that button here that has decide the search link so we're gonna do that by assigning an event listener you so when we click on this button by button we mean this navigation link in this case search link we're gonna just trigger this function load search view and what that function is going to do it's gonna call that back-end server side function which is what we called load search view so we're gonna do that by doing Google dot script dot run and call that function now what this function is going to do it's going to return that partial HTML now we need to get that return and work with that so we're gonna add a success handler here that's gonna accept a callback function right here so I'm gonna put the function right inside of this without creating a separate function and that's gonna accept that HTML string that's gonna come back from here so now we should be able to take that HTML s shrink and put it inside of this div with an ID app I'm just gonna call that HTML so we're gonna set the innerhtml to whatever HTML we get back so let's save this and test this does this work so right now if I click on a search that loads that content from search page now we need this to also work for ad customer and that code is gonna be very similar so we basically have to repeat this entire thing again now for that reason instead of repeating the whole thing all over again let's create a function that can be reused to load all these different views so we're gonna call that function something like load view and parameters that are going to be passed to that function if you look here the part that's gonna change this part is this load search view we're gonna have to pass which function is it that we're gonna have to run from the back end to bring back the information we need so that means we need to pass a function I'm actually just gonna do this as options and we'll pass that as an object to this so just to give you an idea how I want this to work once this function works I want this to be like this so I can do load view and then pass an object and say func for that function should be this load search view and that should basically do what's done in here so that means we're gonna have this property inside of these options that we should be able to use and mainly we're gonna do this so I'm just gonna copy and paste that but instead of this because this is a function I'm gonna assign this as a property to this so instead of doing this dot notation I'm gonna go with this and as a property to this I'm gonna assign that function that's gonna come from this so options dot func and notice this is not a function that's being executed it's just gonna sit there and wait so we want to be able to execute this and we should be able to do that by simply adding parentheses like this to execute that so let's see now if I can just now take this line and put that instead of all of this and have it do the same thing so I'm gonna save this go back and test so that loads the search view just fine good now as another option I want to pass here I want to be able to also sometimes do this and say where do I want these results to be placed so for now it's going to app but I want to be able to also provide different containers if I want to and say load it inside of this other thing and give it a different ID or something like that as a matter of fact we can also just instead of ID do a selector so it can be any type of selector but I think I'm gonna do I decide these are specific but I don't want to pass the app ID if it's just app so I want this to be default but only in case we pass something else I want to use that for that I'm gonna go here and check if this ID is defined so I'm gonna set a variable here I'm gonna save our ID and the way I'm gonna set it I'm gonna check if this ID is defined inside of this options so I'm gonna say if the type of this equals to undefined so that would just mean we didn't pass this if that happens if it's undefined we'll just set it to app otherwise we'll just set it to whatever it is so I'm gonna just do this so if nothing is passed we'll just use app as ID otherwise we'll use whatever is passed to this so now we'll just take that idea and plug it in here and this should now work the same way now I should be able to just not pass this and if I run it it should work exactly the same way because the default should be app cool okay so that's good now let's also do something as a callback so what may happen as we load this after it loads we may need to run some functions so similar to when the page loads but there's no page anymore so we cannot do the Dom loaded anymore we have to figure out when the view loads then let's do this other thing so I want to have some sort of callback as an option here which means we can define like go back and say that should be this other function that needs to run when this happens so again we have to check if that's defined so I'll just call it cb4 callback and I'll say if that options dot callback is undefined so if there is no callback function let's go ahead and assign an empty callback to this like this otherwise we're going to use the actual function that was assigned now we want to run this call back right after the view loads which is gonna happen right here we're gonna put that HTML in our page and right after that we're gonna run that callback whatever function we pass to it to make sure this works let's test this so let's say I want to view this search view and then I want to go find this h1 let's give it an ID h1 I want to find this and change this text from search to something else to see if that whole thing works so we'll do this callback other func which will now have to create and what that should do go find that h1 element text content we'll be something else now we'll say new search so I'm gonna go back here reopen this click on search and see how it says new search so the page loads and then we go and find that element and replace it good now finally the last thing I'm gonna do with this load view I want to also have an option to pass some parameters to this callback function basically to this function I want to be able to pass some arguments from this initial function so that I can use it in here that we can set as something like params and we'll send some parameters to it as an object I guess so for example let's send the text we want to change to instead of just having hard-coded text so I'm gonna set a parameter I'm gonna call this Mon maybe title and again these are not mandatory so I'm gonna have to check if that was defined or not in this case params so I'm gonna say if that is undefined then we want to basically run the callback here with no parameters passed to it so I don't really need this line here that means I'm just gonna do this directly in the bottom there so right in here instead of just blindly running the callback like this let's do the check and say if that's undefined question mark then we're gonna do this callback without any parameters otherwise we'll do the callback and send those parameters to it I think that should complete this let's just test this and make sure this works so to test this now we're gonna get this object here maybe we'll just call it params 2 and then we'll take that and grab the title out of it and that should fill this up so if this whole thing works this should now replace that h1 with this text another search cool alright so we got our function so this was the hardest part here is to create this function that we can execute right now I'm not trying to pass any parameters or anything so it's gonna be pretty simple we're simply just gonna say this but we're gonna need those functionalities later so if I also want to load let's say that add customer view then it's gonna be this we're gonna take this and create another callback and we're gonna basically do this again and I have to go into back-end and see what the function was called just for having this in the correct order let's put this on top so if I go back and open this that you do this if I click on search that opens that if I click on add customer that didn't work so let's see what the error message says no HTML file named ad customers was found okay what did I call it ad customer okay so this should be in our back-end this file name I called it ad customer not customers try this again search load search at customer loads customer good so now we have an option for us to load view with bunch of other things we can do with it that we're gonna be using later now let's also just quickly build our search view the way it needs to look like so the search view I want this to have an input box here so let's go on their forms and find an input something like this so I'm just gonna copy this and instead of doing this maybe we'll keep that h1 right there on top I don't need the ID now for this I don't need the label I don't need this small text I don't want this to be type email I don't care about maybe we'll add a placeholder I do need some sort of ID for this so that will be the actual search input box where people are going to be typing to search for things now let's also put some results below that search input box I'll use a table for that you I'll use this one that has this hover effect I like that so I'm going to copy that whole thing go back here and paste now for me I'm gonna need what in my search results it's gonna be ID first last and telephone number well telephone number I'm probably not even gonna use in search results so first last and customer ID so there's this fourth column here oh this is nice it's cold exactly with the same names now the last column here that I'm gonna do I'm just gonna remove all this other rows just to keep one for now we will need to repeat this row for every other search result and then I want some sort of edit button and delete button as to other columns so this column we already have so I'm not sure what I'm gonna call this I'm just gonna leave this empty for now let's just create those columns on top here and then I'm gonna create those columns down here too so this one should have a delete button I'll use this design copy this go back paste that in here they should say delete maybe the first one will be added actually let's do that and then I'm gonna do another one which is gonna be our Delete something like this let's save this and see what this looks like looks good enough for me now we're gonna also add little customers this add customers views let's do a forum for that so go to this add customer and customer guess we'll go back and find again a forum I'll just copy this change it up a little bit so we don't need this small text so when we're adding a new customer we need to add first name last name and telephone number so we need three input boxes so this should be a good model by so I'm just gonna delete these other two or whatever else we have there and copy and paste two more of these you the button I'm just gonna replace this submit and finally this I'm not gonna do a forum I'm just gonna convert this to a div give this a class of some sort I think that should do it let's just go take a look the search loads this if I click Add customer that's going to load our form good and for now on their home let's put edit form so I'm going to add another HTML and I'm just gonna copy and paste this and when we're editing we are going to have again first name last name phone number only this button is gonna say save and we're gonna have a button to cancel changes at its customer form as a class for this div that holds this whole thing in again we have first name last name phone number now when we're adding we also have this customer IDs so I'm gonna add that field on top all right so that's that so we have customer ID all of this I'm gonna save that now we need to make sure we have a back-end function that loads this this added customer and match the file name and then we can now call this function on our front-end so if we go to this one we can create another link in here which is gonna be for this home link for now we're not gonna keep it in there but we just want to make sure we can see what this looks like that's the function that's gonna be cold when we click on this home link that's gonna go here now this one is gonna be that function I just made so now we should have this view as well so if I open this it is not gonna load when the page loads but if I click on search its gonna load if I click on add customer it's gonna load if I click on a home that should load this added customer view we're not gonna keep it on their home anyway so that's not a concern and that's pretty much all the setup we need so initially we're able to do this view loader which was the main thing we wanted to do in here and starting from the next video we'll go into searching our results and populating this results table but for this one that should be it thanks for watching please subscribe and I'll see you the next one

Channel: Learn Google Spreadsheets

Views: 102,154

Keywords: webapp, web, app, google sheets, crud

Id: 6zFowiTNhqI

Length: 36min 47sec (2207 seconds)

Published: Fri Jun 12 2020