Web App Example w/ Google Sheets, Tabulator, JavaScript, Apps Script, HTML, CSS Tutorial

in this video we're going to make a web app using   google sheets as a back end  and tabulator as our interface so i finally received the 100 000 subscriber  youtube play button and honestly this new play   button doesn't look as cool as the old buttons  used to look anyway by the time this arrived now   the channel has 200 000 subscribers which honestly  i never expected when i started this channel   i wanted to thank each and every one of you for  subscribing to this channel and i want to ask you   to help this channel by liking and commenting  on upcoming videos it's not something i enjoy   asking but the reality is that it's the only  way this channel is going to continue to exist   the way youtube currently works new  videos published with 200 000 subscribers   get as many views as i used to get when i had 20  000 subscribers so this video is gonna be long   usually when i do something like this i  would do probably like 10 part tutorials but   a lot of people have hard time finding  different tutorials when i do this so   that's why i'm just gonna put all of it in a  single video so we're gonna build this web app   it's gonna have this functionality to load  the data from our spreadsheet like you see   right here we're gonna have options to add records  to our existing sheet by clicking add record   and that should add a new record here we can just  go here and type any new information about this and we're going to have some of these  fields to be just regular entry fields   some of them are going to be drop down fields  like this so you can choose some of them will be   auto complete drop downs meaning  that you can basically search in the field and select an option we're gonna  have some check boxes like this so we can use   we're also gonna have validation in fields so  for example if i go to this quantity field and   i try to enter something that's not a number  or it's not a whole number it's not gonna let   me do so so i only can do some numbers that are  just whole numbers like this and that's gonna   go in and same sort of thing is gonna go with  price it has to be in that particular format   we'll have pagination that goes through  different pages if you have data that goes over   a certain number which you can modify you can  sort these columns by name quantity price pretty   much any column you want and we can also search  our records using the search in this case using   this name column but again if you watch the video  you'll see how you can modify this to pretty much   do any column you want and all of that  information is going to our spreadsheet over here   and this is that new record i just did  and for those of you who are on a google   workspace account you're not going to have this  warning that shows up on top if you don't have   a workspace account there's a workaround  and i'm going to mention how to do that   and obviously to do all of this we're going to  use html css javascript and we're going to use   this tab later to build the grid and make it easy  for us to work with our grid so let's get started   so let's see how we can make that happen so  i'm going to go ahead and open apps script so name this project and create do get function and our do get function  for our web app needs to return an html service and for that we'll take html service and  create template from a file this needs to   be an html file that doesn't exist we'll fill  that in in just a second i'm gonna do evaluate   and then we'll also add meta tags and here we'll  have our responsive meta tags like viewport and   stuff like that and finally we need to return  that html service so let's create an html file i'm going to call it main.html and then i'll go  to my file and refer to that file main referring   to this html so that should be my main structure  for this so i'm going to leave it at that for now   i do want to get a function to grab this data  from the spreadsheet for now we'll just do a basic   pool of data and then we'll figure  out how we're going to deal with this   so let's create a function maybe separate  it here i'll create another script file and call it get data maybe we'll take our  spreadsheet app and get our active spreadsheet save this in a variable and then  we'll get the tab the worksheet and we're going to use get sheet by name   and the name of the worksheet in the spreadsheet  for this data is called in my case customers   so i'm gonna copy and paste that here now in  this i'm gonna grab the data by going to this   first cell and doing command a to grab this  entire data set and in order for me to do that   i'm going to create another variable called  data range and we'll take that worksheet and   point to that a1 cell and from that a1  cell we're going to do get data region and that should do that control a thing and  at this point we can get the actual data   and we'll do that by taking that data  range and doing get display values   values not value there it is so  at that point if i do console log   of that data if i just quickly run this so you  can see what happens that should pull this data   over here so let's do that and see what this  looks like so this is that get data function run do all this permission stuff and as you can see we got our data as an array  of arrays first row being our headers let's   quickly separate our headers from the data  below and to do that we'll add another line here and we'll take that data and do shift   which is going to remove the first row in  that data and save it in this variable headers   so at this point if i console.log headers and  then another console.log data which is this you'll see that first headers console log gives me  an array of headers here and then we have the data   here separate from that so so far so good we're going to come back to  this function and make some modifications but   now i'll move to html and let's actually go  to that tabulator website here and see how   we're going to do this so i'm going to go to this  documentation section on top and let's try to see   see getting started we have quick  start let's click on that scroll down   and we have all this mpm installs but we're just  gonna use cdn here so we're gonna grab this two   lines right here copy them and go  to my project and paste it in here   now the link to the css file is going to be  here but the script i'm going to move it down   here below the body and then i'm gonna add my  own script tag here so we can actually work here now the version i got here is 5.2.4 so if you  copy paste do you have a different version   and you want to match exactly my version just  change the link to exactly mine and you should   have exactly the same version i'm working with or  you can test your lock with newer versions which   can have more features but if something doesn't  work exactly the same way just know that could be   something with a version so now that i got  those imports let's go back to their docs   and scroll down and see what  the rest of the setup looks like so if you look here they made a diff with  this id example table so we need this   so i'm going to copy that go to our  html and we'll paste it in the body   we probably want to use a different id for  this but for now i'm going to leave it the same   we'll go back here the next thing they do see they  have this data table with their data this is that   table data they got and again for now i'm just  going to copy exactly what they got scroll down to   my project and just paste it in the  script section because this is javascript   at this point i will redefine this var as const remove that semicolon let's go back and see what  else we're dealing with here we have this next   section when they actually create this tabulator  object and then they also define this on click   event so let's just grab all of that copied go  back to our code and basically just paste it   i'm just going to align all the stuff so you don't  want messy code because then it's impossible to   keep track of what's going on especially  when you have to work in this environment   when you cannot split your code in a  very good way now if you're working with   clasp and you can split your javascript files  then you're not gonna have to deal with it but   here i'm just gonna keep it simple so remove  semicolon there then i'm gonna indent this   and finally remove this semicolon so so far  i got these now for now the only thing i'm   going to change here is this id right here so  i'm just going to rename this to data table like that and then i'm going to match that  over here see this pound this so that pound   stands for id which is this and data table  is this which we're going to match here so as of right now this is not going to use  any of our spreadsheet data obviously but   we want to see if any of this actually  works so i'm going to deploy this app   to see what actually happens so i'm going  to go under deploy and do new deployment go over here and do a web app we'll call  it head and we'll use our settings for me   i'm just going to do only myself but obviously  you could set this however you want to set it so that's going to give me this dev url  and if i click on this dev url it's going   to open it in a new tab it says meta tag you  specified is not allowed and that's because   i did set this meta tag here but i forgot to  actually add those tags so let's just quickly get   those i'm just going to open a new tab  and just search responsive meta tags whatever shows up here is fine i'll  just click on this w3schools i guess but   this is what i'm looking for so viewport so that's going to be the first one and then this will be our second part again all of that is just a single line  but it's going to push me to the next   line because i'm zoomed in so that's that now  we could redeploy this but we should have our   dev link now so if i go under deploy and see we  have this test deployment and we have this web app   the dev link i'm going to open that and close the  other one we'll redeploy it once we have something   now apparently this is the default look  of this that looked different on their   tabulator here i'm going to assume there's  probably some template settings you can change   to make it look dark and nice looking like  this but for now i don't really care about   the dark look that much what we  should be able to see however is that   it actually works we were able to grab  that data and we have these columns   now the last thing i want to check if you remember  there was this part if i go to this this trigger   when we click on one of the rows i want to  see if this works it should alert something   which means that if i go back to this and click on  one of these see it gives me like row two clicked   and if i click on this one it's going to tell me  row 5 clicked so that works this works fine if i   click on this see we can sort this table great so  at this point let's see how we're going to be able   to load our data instead of loading this and for  that we're going to need to do a couple of things   first we need our data in this format now notice  the format here is that we have an array and then   in that array we have an object for each row  and each row has the column and it has the   value that goes right after that and then we got  again the column and we have the value and so on   now let's try to get our data in this particular  format so for that i'll go back to that function   this one currently if i run this our data is in  this format which is just this array of arrays   so let's convert it to that other format  and for that we're going to basically   loop through that data and the data would be data  variable and we're going to map it to a new array   and for each row in the data which i'm  going to just call it r for row short   we're going to do some actions now once we  map this data we're going to get a whole new   variable new array so i'll just  call it json data or something   or maybe we should call it js data like javascript  data something like that so this is going to loop   through this data row by row so for each row we're  going to basically create an object so i'm going   to first create an object so i'll call it temp  object and by default it will be an empty object   so in this object i have to do all those columns  so if you remember the structure here is column   value column value i need to do all these  properties with those column names and   their values and in order for me to do that i'm  going to take headers which are the column names   and i'm going to loop through those  using a for each loop and for each header we're going to set a new property within  that object so i'm going to take that temp   object and set a property with that header  like this and that would be the column name   and we need to set the value and that's  going to be equal to the value in the   position of that header so this is going  to loop through those headers going like   id gender name state birthday company in that  particular order and that's going to match the   positions we have in this row so the first is  going to be this one then it's going to be this   one then it's going to be this one and because  of that position match we can basically just   add an index to this loop like this  and use that index to get the value   from the row so we'll take the row that comes from  here which is this data row and grab it in that   position of the header like this and then  finally we need to return this temp object and that's going to return that data now let me  just console log this so you can see what this   looks like i'm just going to console.js data  i'm going to comment these two lines and i'm   going to run this so you can see what happens  so if i scroll up you can see now i got this   and then i got these column names like  this and their values next to them   let's see what happens if we have a space in those  column names so for example if i call this one customer name or something and rerun this okay so we do set the value so as long  as you don't have any duplicate column   names that should be fine i don't want  those spaces so i'll just keep it simple and have a name like so good so that actually  does convert our data to the shape so let's just   push some of these up a little  bit there we go so we got our   get data i'm going to add a comment that  this is the end of that get data function all right so let's go now take a look at our  object again so we basically made this so now   we're gonna have to use that to build this and  if you look here basically they create this   object and they use that table data variable  this one here as the data when they create this   and then in columns they set what column name  matches to what field and the field is basically   here in this object what it's called like name  like let's look at a different example like date   of birth so see we want the column here to say  like nice date of birth but here the field is   d-o-b which is basically this right here so let's  do this so i'm going to go back and what we need   to do we need to basically call this function  to get this data and we're just going to return   this data so we can use that data so now we're not  going to console log it anymore we're just going   to return it maybe i'll just keep that console  log here just in case we need to come back and   do something with this so now because we have this  get data function we should be able to call it   in our front end and we're going to have  to figure out when exactly we're going to   call that for now we'll probably call it when  the page loads so we'll go back to our html   to work within our javascript here and we'll  add a function here we'll call that function   i'm gonna push all this stuff down a little  bit and i'm gonna call this function page load   let's not forget that it's a function  and we want that function to run when   the page actually loads and for that we're  going to do document add event listener and this is going to be our  event when the page loads   and we're going to run that page load function  when the page loads this function now when the   page loads we want to at that point run that  function in our back end that's called get data   here and to run that function we're going gonna  use google script run and that function get data   now this is gonna have two methods  in it one of them is success handler   i'm just going to push them down to the next  line like this so it's easier to manage this   and then there is another one for failed requests like so and each one here is going to accept a  function like when it's a success and when it's   this so for now let's just do a couple  of functions here so i'll do one function   on this one and that will accept the data  that comes back from a server i'll just   call it js data and for this one we'll  do a function as well and that would be the error message like so so all of this  could be just continuation of the same line   i'm just going to do this so it's easier to manage  this as a matter of fact i'm going to move all   of this to a separate function we'll just call it  function load data and we'll move all of this code to this function and then within this page  load we'll just call that function load data   right here just in case we need to do other  things on our page load we don't want to have   all that code here so at this point hopefully if  this works and it loads our data accurately here   within this brackets we should have the js data  which is the data we have coming from our server   from this function getdata  which is going to be this which is essentially this table data so  i'm going to go ahead and comment this out and then when that happens here  we need to then do all of this what we've done in this portion so  for now i'm going to move all of that i'm going to cut this go up here within these brackets here and here   i'm going to paste it before i paste it  i'm going to add a couple of comments so i'm going to type the comment  and do command question mark or   control question mark to make it a comment just so i know where this starts and ends i'm  gonna paste the rest of this here and again i'm   just gonna align this to keep this organized  so first i'm going to remove this note here   because it's taking too much space  or just make it shorter maybe and basically here this table data is going  to be this js data so i'm going to copy that   variable name and put it right in here to add that  js data now we do need to match our columns see we   have like name name age age etc so now i'm gonna  figure out what our column names are and i did   make our call names the same as these so  that's what i'm gonna use so like name the field is actually name so i'll match  that this is what we actually see so we   can call this like customer's name or  something but for now name name is fine   the next is age here so here is called birthday  it's not age actually oh actually we have date of   birth maybe we could use that so that's  birthday and we'll leave data birth as   how it should be and we need the rest of our  columns right so so far i got name and date   of birth which is fine this this too let's add  gender state those columns so i'm gonna do gender and this is not going to be progress and maybe we'll get rid of this alignment  too favorite color so instead let's use state copy paste this one let's also add company you should have these in the  order you want them listed so   now we do have ids here which  we want to include here somehow let's add that in our first  column so that's just called id something like that so the rest i'm just gonna  leave as is let's see if any of this works so far   before we go any further so i'm gonna  save this go back and refresh this thing and again it took a second but it did load the  data as you can see and i got that data loaded   in this table and again if i do click on one of  these it should give me this it says row undefined   collect so we're going to have to figure out why  that's undefined let's go back and take a look so   it gets the id and i assume our id is uppercase  id let's try to change this to id uppercase let's see if that changes that reaction to this  so i'm going to refresh and click on one of these   and then see it gets us the actual id   of the record we're clicking on that's good  we're gonna need that id at some point probably   but for now that's good enough so now let's go see  what settings we can change here so we may want to   have like pagination at some point like maybe  after a certain number of records i don't know   if there's a default but we're going to have to  find out so i'm going to open their documentation   let's try to find pagination here set up options  maybe see pagination see there's like pagination   then the default is false so i guess we'll  have to set it to true and then there is   pagination size which would be the number we like  let's actually click explore and see what it does   does it give us an example here so apparently  there is a pagination here let's click view source   and see how that's done so pretty much the way   i thought that would be except that it says  pagination local instead of pagination true   not sure what the difference is but  let's just copy these two go to our javascript code and here in these options we're  going to have to add those so i'm gonna add a new   line here a couple new lines i guess let's just  align these and i'm just gonna set it to true   like the docs set to see what  happens and we'll do pagination five so i'm gonna refresh this all right so it seems like we now have  five records and we have page one page two   see we can go looks like we got eight records  total so let's just take a look yeah nine and   the first one is headers that's correct  so that's it now we got working pagination   just like that our sort should hopefully  work too seems like it does let's see by date i don't think this date sort works  we're gonna have to figure out why   maybe we'll look into that whole date situation  later but now let's try to see how we change   the options so we can actually modify one of  these records actually let me comment the line   that makes this clickable that's kind of annoying  at this point so i'm just going to go here and   grab this block and comment it out like this again  that's control or command question mark to do that and now it shouldn't do that whole thing when  i click on this so now i want to make this   gender modifiable so we can maybe select  between male and female let's see how we're   going to do that we're going to go back to  this and i'm going to assume it's here again as a matter of fact let's just go back to the  beginning where we had an example on the first   page this one is there an example on this  well we seem to have an example right here   let's try to see how it's done so right here see they have  this gender it sets the size   it says editor select and then it gives us this  which are the options of what they can actually do   so that's exactly what we're going to need so  i'm just going to copy width is probably going   to be pretty good too i'm just going to copy  that too we're going to go back to our own code   and find our gender column right here i'm going  to add a comma and paste it within this object   apparently there's too many commas now let me  just erase that like so so again all that is just   single line like this so that's our gender column  so let's save this go back and take a look at this and see if we can still modify this now  okay so i can modify that as you can see but obviously that's not going to do anything here  in our data so if i go and change all of these to   female that should not affect our data at this  point so we're going to want to somehow trigger   a function to make sure that this updates  when we do this so let's see if we can do   that next now to be able to do that we need to  basically grab the id of the record that was   changed here like for example if we change to mail  we want to know that this is the id of the record   and basically based on that we want to go  here find that record in this list and update   this gender so we need some sort of event  when that drop down changes in our list   and for that let's go and look  at their documentation again under docs let's try to find if they have  like events section or something there to see   events on the left so it's probably either cell  events or row event so i'm going to assume that   sell events so let's open that and  see what we got see we got cell click   double click we don't care about any of that  let's find if there is an option when it's changed   mouse over cell editing it says event is  triggered when a user starts editing a cell   we don't want that when a user aborts we don't  want that to event is triggered when data in the   editable cell is changed i think that's what  we need so we're going to copy this and go   back to our code and right here remember how  there was that section to add an event handler   i'm just going to paste it right  below we'll keep the old one too   now this should work because the variable up  here it's called table and then table on similar   to how it's down here only now it's on cell  edited so let's actually do a console log here and console.log that cell variable that shows  up here and see what's in it remove this   semicolon so i'm going to save this  go back reload this thing we need to   open the console log in our front end  so right click inspect go to console clear all of this i'm gonna  turn off this mobile thing   so now i got this console log let's  go ahead and try to click on one of   the cells hopefully nothing happens see  no consoles let's try to now modify this apparently it says the select editor has  been deprecated please use the new list   editor apparently they have a list editor  too so once i changed it see it gave me this   apparently it's an object let's  see what's in it we have get   so it says there's a get method on it that  has three arguments e t and i and then we   got the cell here which gives us apparently some  information about that cell including see what the   old value was and what the new value is apparently  now that's mail now it does say the select   has been deprecated let's go find out what the  list is i don't know why it was in their docs   if we're supposed to use a list but hey it is  what it is so let's just search on top here list see apparently it says editor list and then values  maybe we should just copy this and change that   in our code instead of this being select  just be a list let's see if that's enough   so i'm going to save this and reload  this clear this let's now again click this time we don't get that error if i change  to mail that renders this now i want to see   if that also console logs something let's clear  this if we don't change it so if this is female   and we open and go back to female again does  it cancel log again no so it only cancel logs   when this is changed that's  great so now let's try to get   the actual information from that cell so again  if i open one of these let's open this object   and let's try to see inside of that  we have this and then we got the cell   and within that cell we have the value   that it is right now which is male now that's  great but it would also be nice for us to get   the id and maybe this row is going to get us there  see we have the row and within that row we got cells and in that cells maybe we can get  the column we want see this is the value of   this but i was hoping i wouldn't have to go  with zero index like saying the first column   i was hoping i could go with column name  so let's see if that's a possibility here so apparently we have this row and then data and  then we have id so that should get us there so   let's try to console log this so i want to console  log this id and i want to console log the actual   value so let's see if i'm going to be able to get  there so i'm going to go back here and when i do   this cell so basically based on that element there  was this thing underscore cell within that object   let's see what that gives us so i'm going  to save this go back and reload this thing   and i'm going to clear this again we're going  to open one of these and change it to mail   so now it logs this now within that we should have this value so let's   console log it on a separate line dot value which  should be the value of the cell after change   and then we should also have the row so here  i'm gonna do dot row and then within the row   we have data so dot data and then within  that data we have the column id so dot id   so let's see if these two work so hopefully  this will console log the id of the record   and this would be whether it's male or female  so i'm going to save that go back and reload and let's just clear this and open one of these so  right now this is female let's change it to male   and you can see we got the number and we got  the actual value we might also want to get   what column was modified because if we have  modifications that we do in different columns too   we want to know that this modification was done in  this gender column and for that let's just go back   and console.log that cell again and see how we're  going to get that so save this go back and reload i'm going to clear this change  this to mail let's open that   and let's take a look apparently we  got this column let's see what's in it and then we got field which is gender so that's  pretty much what we're after so if we do column   field gender that should give us  that so column dot field dot gender   oh actually just dot field i think gives us the  field like this yeah so let's try this finally so   save go back reload clear all the stuff now  i'm going to change this to mail and you can   see how we got this this and this so now we can  say if it's the gender column that was modified   then let's go to our data and find this record  with this id and change it to this value now   first let's add that if statement and say if it's  gender this is what that is that gives us gender   maybe we'll just store all of these in a variable and we'll say if that field equals  gender then we're going to do something   and what we're actually going to do we're going  to basically call our backend and update our value   so we need a backend function that's  going to accept the id and the value   and based on that it's going to update our record  so i'm gonna go to my back end and we're gonna   create that function right below this one so i'm  gonna do function edit i'm gonna call it gender   at some point it's probably best to just have a  function that accepts the field and the rest so   you can just reuse the same function for all the  fields but right now we'll just go with editing   gender only we need this function to accept an  object i'm just going to call it props properties   and those properties is going to be  basically the value and the actual id   and based on that we're going to have to go search  for that id in this column and find which row   that's in so let's try to search in this  column a and find the accurate row number   and the way we're going to do that  we're going to take our spreadsheet   well actually let me just copy these two  lines here so i don't have to type them   we're gonna take our spreadsheet then  we're gonna get our worksheet customers   and in this customers worksheet we want to search  in our first column so i'm going to do ws.getrange and the range is going to start from a2 and go  all the way down so we're going to start searching   from here and all the way down here so within  this we want to search so we're going to do create   text finder and we're going to search for the  id that was passed here and that's going to be   props dot id so that's from this properties we're  going to send id here we don't have that props id   and the way we're going to pass that props  id is by calling this edit gender function here so we'll do that google dot script dot run   and we'll call that edit gender function  and we're going to pass an object here which   will be that props and within this object  we're going to pass the id which will be   this id from here which since it's the same name i  could have just done id without this but i'm just   going to keep it like this and we're going to pass  the value which will be the value from here so   that's the way we're going to call that function  with these two properties now that id now will be this props dot id and then props.val  will be the value so now within this   we're gonna do find next and that  should search that column and return see   the new cell that matches that criteria  so i'm gonna call it const id cell matched so if it does match a cell it's going to give  us that cell otherwise it gives us null so   we'll just say if id cell matched equals null  then we're going to throw some sort of error and we're going to say no matching record  otherwise then we have a cell that matched   which means from that cell we're going  to get the row number of the record and we can get that by grabbing  that cell and do get row so now this record row number should give us  the row number in which we're able to find   that particular id so like six if it finds this  one nine if it finds this one and then once we   find the right of row then we need to go to this  gender column and modify that cell in that row   and we'll do that by taking our worksheet  and do get range and we're going to get   the record in that row which we just  called record row num number actually   so we're gonna match this so that's the row  number from this worksheet and then the column   for this gender is column number two  here and i'm going to do dot set value and then we're going to set that  value to the value that was passed   here to this props which would be props  dot val which is basically this right here and at that point hopefully it will go to our  spreadsheet and do these modifications in our   spreadsheet now let's try to see if this works i'm  gonna delete this line just to make this a little   smaller i'll go back to my records here refresh  this so right now if you look here we got monica   female right and if i go here obviously we're  gonna get monica female and then we got donna   female let's try to modify one of those maybe  so i'm gonna take donna and switch it to mail   and let's go and see what happened in our  spreadsheet so as you can see donna's record   was modified by making that change so now let's  go to apparently there's another donna here   which donna did i do this one donna the other one  is probably in the second yeah here let's try to   change this one too let's see if that again  works accurately so as you can see no problem   now let's go back and modify donna's back  to female like we should maybe we should   do something else other than male and female  in this column because this is kind of weird   but nevertheless uh it works so we do this as you  can see it does modify our records as it should   now we may want to have some sort of message for  our users to know that it's saving the record and   then the record is saved something like that  because right now the user doesn't really know   even though it happens in the background but  you can see our modification actually works   so i do want to add some sort of messaging here  that basically gives us the opportunity to see   that it's saving this record and then changes  are saved because it doesn't actually happen   immediately after we change this it takes a little  bit for this to process and sometimes it could   actually fail and we want our user to know so for  that reason we're going to go back and instead of   just having this edit gender like this we're gonna  add that success and failure within this two so   i'm gonna push this down a little  bit we're going to grab that with success handler and i'm going to add it here and then we also need the failure there and again for each one of those we're  going to pass a function now this first function   doesn't return anything so we'll just do this  and this one is gonna get some sort of error we're probably not gonna use that  error anyways so basically now i need   some messaging for our users and for that what  i'm going to do i'm just going to add a div below the table someplace so let's scroll up this  is the table let's go below maybe or above it's up   to you i'll just do below let's actually just copy  the div from above and let's just call it alerts and now let's grab that alerts id so let's define  a constant on top here we'll just call it elements and then when the page loads we'll go  get that element and save it within that constant let's actually make this an object so right here we're going to do  document dot get element by id and the id of our element is alerts   and we'll just save it in this object so  we'll say elements dot alerts equals that so what we'll do we'll just set  some sort of text within this   div box right we'll just scroll down here so when  we do this editing thing before we do this google   script run we want to let our user know that it's  about to save some changes so we'll take that   alerts and actually it's not value it's  text content on this because it's a div and we're going to set it to saving changes and then if it's successful  here we want to change it to changes saved like this and if it's not  successful we want to say error saving changes now the thing with these messages is that once  we set them they will never go away if we have   this the way we have it now but right now let's  take a look and see how this works so i'm gonna   refresh this and if i go here and change this  to mail see it says saving changes and then   changed saved apparently didn't type  that right change is saved there it is   and if we have some sort of error it  should have said error saving changes   but it's basically just stuck with this messaging  right so if i go back and change this one   see saving changes again and saved again we may  want this message to disappear at some point   and for that we're going to do some time  out message to basically clear this thing   and let's create that in a separate  function and then we'll call it here so i don't need this anymore let's get  rid of that i'm going to create a function clear alert and in this function we're  going to use set timeout and this is going to accept a function in number  of milliseconds so let's first create a function   and then milliseconds like five seconds and  basically after five seconds it's gonna do   whatever is here what i'll do i'll just pass the  element to this clear alert function and then   i'll take that element and i'll make  the text content of that element blank   like this and then what we  could do we could use that   here we could say clear alerts and pass this  element here and then do that same thing if there is some sort of error  message to clear the error   so if i save all of that go back  and take a look hopefully now   we're gonna have a situation when we open one of  these drop downs we change saving changes change   is saved and then hopefully in five seconds this  is gonna disappear and it did and then same here maybe that should be shorter than five  seconds so maybe i'll change this to   like two and a half seconds and that's that we  got our form that loads based on our data here and   then we can actually modify the gender information  in this form and as we modify that it updates on   our spreadsheet and we get a notification that  that actually happened all right next let's try   to see if we can take care of this issue with  sorting so we can sort by this other columns but   this date did not sort let's try to see  if we get any console log errors maybe   it appears that we do see there's this  not defined error it's probably some sort   of library or something see there's this date  time date sort so this is that sorting related   let's go see their docs again so here i'm  gonna search sort sorting data let's see let's look at date dependency required see we need a dependency   let's look and see what this library  is do we have a cdn here quick tour all right doesn't look that way let's  actually try to search the library and add cdn and that should work cdnjs.com i'm gonna go there  let's grab this right here so i'll take this   script tag copy that go to our  project and let's add it to our   html right above i guess the script tag so this one will be that date library   so save that let's see if that's all we  need maybe so i'm gonna go ahead and refresh and try to sort i don't think that  works so let's go check their docs again   so once we got the library  then we got an example here see   so we have this field sorter date and then  we have this so let's grab all of that   copy without this last bracket go back to our  code and find that field which is this date so this sorter was already a part of that  let's actually put this after this alignment   so i'll comma and paste the rest  here let's just tap this to align it so we need to probably provide the right  format so this is the format example here   now our date format is where is it so it's month  day year and month and day are single digits so   we're gonna go to our script and  we're gonna match it so it's m and then d and then four digit year and we  have this forward slashes as our separators   so let's do that save and see if that works so  i'm going to save this click on this date of birth   i think that got sorted now see  1973 83 86 and then it goes up   so if i go the other way see now we start from  the lower amount and we go up and apparently   in their sword blanks go on top i'm not sure  if there's a way to control it by default and that's probably this align empty  values and it says force empty cells   to the top of the table or to the bottom of  the table so i guess we can do this bottom   so let's grab that oh we already had that we just  have to change this top to bottom so i'm going to   copy this go back and right here instead of top  we're going to do bottom and at this point what   should happen if i refresh it should basically  hopefully if i sort put our sorted dates on top   and then empties go all the way here okay fair  enough so that takes care of this date problem   next let's see if we can change this to look  like a dark table instead of this bright table   so i'm gonna go to their documentation  again let's look for style i guess so there is this section for custom themes  let's look at this so standard teams we   got so we got simple midnight modern and  sight is there an example of any of that not that i can see maybe examples on top here   will help us this is just a regular  example i guess of different tables but   hopefully there are some template examples too  oh there it is so this is the standard i guess   that's the one we got i'm just probably zoomed  in a lot simple one midnight so it's the css should be like that so that's dark one it doesn't appear like the actual black  one they got is available but let's try   to see if we can do this we probably just  have to change the css so i'm gonna go   to our html and right here when we have the  css import let's change this tabulator to   what that was like this and save and hopefully  now if i refresh yeah that takes that template   so these are fine we can probably do our own  styles to make it better but for now this is good   enough so the next thing i want to try to do is  to make one of these other fields editable too so   maybe like their names so we can type a different  name and save that in the database so let's see   how we can do that so first let's go grab the  docs and see how we make a field editable so   i think if we go to the home page here i think i  could see type over it which basically means that   if we look at this example and we'll look at that  column name we should be able to see how it's done   so as you can see here under columns we  have this name field and editor is input so   i'm going to copy that go back to our own script  find this name column and add that to this field   and at this point if we go back and refresh this  hopefully i'm going to be able to type something   here yes i am now the key here is again to save  that to our spreadsheet because it's not going to   now let's just quickly take a look at our logs clear this and let's go take a look at  that function that we used to use when   we actually change this gender drop down and  that was the cell edited and here we checked if   the field is gender then we're gonna do something  let's actually just add a quick console log here   just to make sure this actually  works on that field too   and i'm gonna do that cell dot underscore cell save it go back and refresh clear that so now hopefully if i go to this  field and change it to just monica hit enter   see that gives me this console log meaning  there was a change and we get the value monica   and hopefully if i go in and don't change  it and keep it monica and hit enter   it doesn't give me another log so it didn't  good so i think this should work pretty much   the same way we did this so we have the value c  monica which is our new value we're gonna have   the row where the data is and then we have  the data where we're going to get the id   that's the same and we got the column and if we  look at that field it tells us what field it was   so what i'm gonna do i'm gonna go back and  modify my previous function i did for this   in the back end to hopefully work with this field  too and then with other fields in a future as well   so for that what we want to do here what we  do here we pass the id and we pass the value   we want to add the field as one of the things we  pass here which is going to be this field up here now we're going to rename this function so we  called it before edit gender now we're going to   call it edit cell maybe and then let's go and  rename this function in our server side to be   called added cell and it's still going to accept  these props now here what we do we go find that   customer and we find our matching record and  then we go to this column two where we have   gender information and we update that info now  that column two is the part that's gonna change   so we wanna figure out depending on the field  that was sent which column do we need to update   and for that we're going to do something similar  to this except that we're going to search in this   headers to find which column has the header now as  i did this i've just realized that this actually   has a problem because i didn't do an exact  match here i just did contains by default this   text finder does contains so we want to do this  match entire true and possibly even make this   case sensitive which is match case i'm not exactly  sure if we should make it case sensitive so i   guess we should let's actually do that so that's  a little bit of update to our previous script   so now let's try to search and find that column   so i'm going to copy paste this line and i'm  going to change the variable name here to   column cell matched and we're going  to search in what i think is row 1 here so that's fine we're gonna do create text finder  we're gonna look for not the id but the field   that we pass and it's still gonna be exact match  so entire cell and match case now then we need   to do one of these checks so we're going to  say if that is null then we're going to say invalid field and then if we do get a match then we need to do this so we're going to say record row actually  column not row on this one column number   and here we're going to say get column instead  of saying get row and we're going to get that   not from this id cell but from this  column cell so that should give us   the right column number and then  we're going to pass that to this and that's going to be the accurate column  so if that ends up being gender it's gonna   find the column number plug it in here if  it's a different column same thing applies   so i think this should now work for if not all  then a lot of our fields so we'll save this   now i'm going to go back and update this so  right now this only runs when the field is   gender so let's actually do this for a list of  fields so i'm going to do an array and do gender   comma and this new field was name if i'm not  mistaken this one yeah the field is actually this   so that's what i need to match so name  and then i'm gonna do dot includes   which is an array method to check if that list  contains that field so we're going to say if the   field that's being changed is in this list then  let's go ahead and run this and we're gonna pass   that field to our function and hopefully that's  gonna work so i'm gonna save this let's test this   and see what we got so i'll go ahead and refresh  this so i got monika here let's change that to   monica z like this so it said saved changes  let's go back and take a look hey look at that   the name is updated so let's go back and change  it to monica smith and then maybe change it to   john smith and change this gender to mail and let's go back take a look hopefully we got  it now updated see mail here john smith there   very nice so we should be able to pretty  much apply this to other fields too so   if i want this company to be also  one of those cells we can modify   we should be able to do that very easily now  by simply going to our script and just add the same editor input thing on this company  like that and then if we just simply add that   company field here like so just by doing  this i should now have that company cell 2 that's not read-only anymore so we got all  of these let's go change john smith to john   brown and let's try to assign company and let's  see if this worked so i'm going gonna go back   so see google and it's john brown excellent and  obviously we can add another one for our states   hopefully you can see how that's  gonna go it's gonna be pretty much   this only here instead of values being male and  female it will be a list of states comma separated   so let's actually do that really  quickly here so i'm going to copy this   for the state column i'm going to paste it  here and here i'm going to do some values and then we need to add the  state to our list of states here i suppose we could also instead of doing this  we could go through this object over here and   then within that object check where we have this  editor defined and based on that we could say   that's a field that's modifiable and by the way  we need to add this editor to our new state column let's make sure the comma is in the right  spot save this and go back and if i refresh   now we should have this apparently that goes up  for some reason and we can't see half of this list   that's interesting can we control this let's go to  our docs and look at our list documentation again all right let's take a look and see what we got  here hopefully we have something that controls it   so determine how to use up down arrow  keys i don't think that's gonna work so we probably need to mess around with css to   fix that as far as i can tell so if you know how  to do a quick fix on this leave your comment below   the video so what i'm going to do now is do  this autocomplete true thing that i saw here   so we can at least search and narrow it down  to an option so here we should be able to   go to the state editor and see this params values  is one of them and we're gonna do a comma here   and do autocomplete true save that go back and  reload this so now even though if i click on this it goes possibly outside of the screen i  should be able to type like something like   this and narrow it down c to illinois and grab  that and see it's saved now so let's change   this one too i'm gonna look for florida  there it is grab that saving changes so   now i should be able to see that  john brown is in florida state   and that is actually correct now if i reload  this page hopefully that reloads just fine   and it does so we're able now to modify multiple  columns now very easily some of them are drop   downs some of them are drop downs with a  search with an autocomplete like the state   and we also can just simply type in a couple of  these columns like company and name column so jack brown that should modify that in our data  just like that next i want to add a couple of   other type of fields here so if we  go back and look at their examples   i'm gonna go back to their home page see they have  this task progress and they have for example this   checkbox sort of driver thing we could also  add this ratings but i think i'm gonna skip   that one but i think based on my examples  you'll be able to figure out how to do that   too let's look at this task so if we scroll  down see we want to see what's in the   column here so that would be this progress  this second column and see basically it's a   value like 1 12 100 and i guess 100 would  be this one that goes all the way through   so basically it's just a number and then if we  look at this driver which seems to be this last   one it says car and it's either one or it's  true or it's true as text and for these two   it seems to be missing that's interesting so i'm  going to try true and false for this field and   i'll try this progress from one to a hundred  so we'll go to our data let's add a column   progress i guess you could obviously call it  whatever you want and here i'll enter some numbers and let's also add that extra column and for some of these let's do true and let's also try false as a value and leave the  rest like this let's actually leave one of these   blank too so we can see what happens in that case  all right so now that i got these two columns my   function should automatically grab this so at  this point we want to add this progress and   complete as a column here in this columns list and  for that let's actually open their documentation   and find progress first so i'm going  to scroll down and that would be this copy go to our file and right after this  field we'll go to the next line and paste and   this field should match our column name and our  column is called progress within uppercase p here and then i also need this complete so let's  go check that out so that would be this driver   field let's copy that and again i'm  gonna go to my script and add this   there's already a comma here so we should be able  to just paste it now the field for me is called   complete so i'm gonna match that and  we'll match the title also to be the same   i'm gonna leave the rest exactly the way it  is now these two are editor true meaning you   could modify those right now i'm going to remove  those and maybe we'll change that to true in a bit   so let's save this and go back and  reload and see what this looks like all right so that seems to work we got our  progress c like 25 50 75 100 and then i had   510 and then a blank and that goes  to our next page this is our five   this is our 10 and our blank as you can see  is empty now these are all unchecked let's   go to page one these are also all unchecked  so i was expecting some of these to be true what i'm going to do here i'm going to try to  console.log this js data to see what it looks like so i'm going to refresh this  and open our console log and this array is that data and let's open one  of these rows and complete is true but it's   text and this one is blank this one is false so  it doesn't actually give us a value true false   it gives us text true false so i guess based on  that to simplify what we do we should probably use   ones instead of trues and falses so i'm going  to go back and try to modify these truths to   once and false to zero and see what happens  and based on that if i go and refresh this   see i got some checkboxes here so that's good  so now it seems to load those checkboxes now   let's try to make this checkbox clickable  so we can actually change that value   so for that we'll just go back and  do that editor true thing for this so that should now make this clickable  column and then now we want to add that   field to this list of fields and let's see what  happens now i'm gonna save that go back and reload so at this point if i try to check donna let's see  if that actually worked in our spreadsheet so i'm   gonna go to my spreadsheet look at donna and it  did set it to true the problem with this is that   if i refresh this it's not gonna be checked  because based on our previous discovery that did   not work okay so what i'm thinking we could do we  could possibly check what's the type of the field   let's see if we get any information about the  field type when we actually console log the change   so that if i open this let's go  under column and see what we get definition yeah this tick cross that would be  i guess that type of field so let's   go with this logic so i'm gonna go  here and what i'll do when we do this   function let's in addition to all of these get  what we're going to call the type of the field and that was column dot and  definition and formatter and then let's actually  move this value below these and we'll basically set this  conditionally so we'll say if this type equals whatever that was this tick cross thing then we're going to do something otherwise  we'll just leave the value as is so what   we're going to do that value is going to  be something like true so let's take that and cast it as actually not a boolean we  need to cast it as a number so hopefully   it will take the true and convert it to one so  then at that point we'll pass this value here   and it will be zero on one hopefully in our back  end let's try this save this go back and reload so apparently i got all unchecked so now let's  check a couple of these boxes so now i got donna   and ronnie let's go back and take a look so now i  got donna and ronnie c once and now because these   are bonds when we go back let's convert these true  to zero zero when we go back and refresh it should   keep those checkboxes cool so let's check a couple  of more of these like this first five and we'll   leave these three unchecked let's go back and take  a look that works all right cool i'm not a big   fan of this whole like red x is there any way to  not have that red x all right let's just do tick cross again there it is tick element cross  element custom html for the cross element   if set to false will not be shown okay so i  guess we're going to have to set this to false   if we don't want that red thing so i'm going  to go and do that so i'll go here find this   tick cross and where exactly do we set that formatter per rams so we're gonna have to create that and it's going to be probably an object  and we're going to take that cross element and   set it to false i forgot a comma here before i go to  sorter save that go back and reload so now i'm going to be able to still check the  box or uncheck a box i just don't have to look   at those red crosses so four ones this is zero  and i guess if you want that to look like that   in google sheets two you should be able to just  take this and do some sort of data validation   and do some checkbox and then  use custom values and for checked   it will be 1 and for unchecked it would be 0  i guess and there we are so now if i go back   and check one of these these check boxes should  check and uncheck so if i uncheck the first one   if we go back and take a look see  it's unchecked here if i uncheck the third one that should uncheck that   and then it should also go the  other way but it's not going to be   reactive so i'm going to have to refresh to c but  if i for example check these and then i've reload i should be able to see see  the first four are checked   and then three unchecked and  then there is another last one checked again apparently we got more pages  it is now grabbing those empty of rows   below here because i did those check boxes which  we could filter out from our data by going by id   if we wanted to or we could just delete these  extras like that and that should take care of that   okay so finally we got the task progress  going we got this complete column going and you have the column size you can set if  you want to change this like right now if that   state is too big i could go here and find that  state column and add this property to it too and then set it to something a lot smaller and if i reload see that makes that state column much smaller  and you can play around with the sizes   and see what you like so next let's try to modify some styles here so i  want to get that darker look on this if possible   so let's go to their website and see if we can  get those styles and figure out how they set those   so like similar to this i'm gonna right click  on what they've got here and inspect let's   just scroll up and find their main this is their  table and this their styles see background color   that's going to this app.css so i'm going  to click on that here are some styles they   set so i'm going to try to set those styles now  this is their table called exampletable that's   their class for the table now for me that's not  what my table is called but i'm going to have to   adjust so i'm going to scroll up and we'll  go on top here we'll add a style container and i'm going to paste that now  let's add a class to our table here we could just call it the example  table but i guess i'm just going to   call it data table the same as the id  and then i'm going to pass that here   now i need to go find what are some interesting  styles they set and try to grab those tabulator header that's probably setting the  header section background color i'm gonna   want that so i'm gonna go back and paste  that and change this to data table again i'm going to add a couple of more and then test  and see if any of this actually works before i   continue so i want to see if there are any of  row columns here someplace there should be some there you go row and even throw and then  hover so i'm going to grab all three of those   go back to our file and paste them and then i'm  going to adjust the class name to be a data table   you could probably just search and replace this  instead of doing this the way i'm doing it so i'm   going to save this let's just see if this actually  does anything on our table hopefully it does yes it did so we got darker colors   i do need a different header on top here and a  different footer let's go find where those are set this is also setting like cell color  we might want to have a lot of these   i'm gonna grab these three two these are not   really footers or headers but those would  be like cell borders and things like that this seems to be some footer  on top here let's grab that and this let's check those changes i'm going to have to  replace this example table stuff with data table   so i'm going to do command f i'm going to search  for that example table and i'm going to replace   it with data table when you do this you want to  make sure you don't have other example tables   on a page that you shouldn't touch so i'm  going to save that let's go back and reload   so we got this nicer footer now see this part  is looking better now this next the styling   here there's this green now thing that  shows up when we modify a cell i don't   exactly like this background how it works and i  don't think that's the way it was on their page   let's move this to the right so see if i open this  it has this nice like background and hover effect   this green now i got that green  but i didn't get that hover effect   i want to see if we can figure out  what particular css they have used here   so if i go here see that css here says tabulator  underscore site let's try to switch to that so   i'm going to go back to our html and remember  how we got this midnight let's change it to   that site let's see if that's what they got so  i'm gonna refresh this okay it seems like it is so   we got the backgrounds to work and if i click on  this it has that nice hover effect but now i lost   all the font colors here but i think if we go  and look at those styles we could figure out how   to get the font color right too and we should  be in a good shape so i'll go here and try to   open this again let's move it back to here  let's try to find where the font colors are so example table at some point there's going  to be color instead of background color   like this well that seems to be headers  but we should probably use that anyways   it looks like like most of these we should just  actually grab that and put it in our actual file but i don't want this to become a css  class so i just want to basically get the   bare minimum done to make this look decent so  let me go and find the font color hopefully that could be it the thing with css is  that it's so difficult to keep track   of what's happening when you're dealing  with libraries that somebody else made i don't see any more so let me just do search  and replace for this example table again   command f we got this example table  let's replace it with this data table   okay save that go back and refresh this looks  like we got it so we got our colors now see   that's much better than what we had i still  don't like this whole pop-up situation going up   let's actually change the height a little  bit to make this taller so if we go back to   our initial setup see this is the height we had  set maybe we should just change it a little bit good and if you have more records  obviously you want this probably to go   much lower than this so i'll  do like 500 pixels for now   so now we got much better looking table and  functionality wise it should work the same way   so let's just see and make sure that  actually works see it updates the table   like it should if i go here switch this to female  that should update here to female like it should   so it works now we have our styling going on we  got our css we have this sorting the way it should that seems to be fine all right so at this  point what we don't have are some number columns   so let's add a couple of number columns and see  how we can deal with that so i'll go back to my   data add a new qty for quantity column and add  some numbers here maybe leave some of these blank let's now try to add this column to our  grid again so i'm going to go back and   our data should automatically  pick it up but we do need to add   that number type of column  here so it's going to be   similar to this input so i'm going to grab  that name copy below we'll name this one qty match that column name here input type  is fine and then we need to add this   to our list of fields that we want to be  able to modify so i'm going to save that   let's go back and reload and see  what this looks like so that was this pretty good i want my numbers to be  aligned right so i'll go back and let's find   that field that was this one i thought  we had some alignments here see here horizontal alignment center so let's add that to  this quantity field and just do right instead of   center save that go back and refresh now hopefully  we have our numbers aligned right so we should be   able to go here and add a number and that should  save to our table as you can see very good   now i'm going to assume that this is just a text  field right now so if i try to for example enter   hello it will still probably let me do that  and that will save it in our spreadsheet and   as you can see it does so let's see if we  can force this to only accept numbers so   i'm going to go back to our documentation  here let's search in the docs validation   so there we go data validation  let's open that and we have this   built-in validators let's click on that  see we can do like a required field   unique so you can read what this does now for  our quantity i want whole numbers so this would   be integer so that's what i want i'm going to  copy that go back and add that to that field   like this save if i go back and reload this   we have our numbers so i should be  able to still go and change my number   and go back and that should update now let's  see what happens if i try to type hello   see i'm trying to hit enter it does not apply  so it doesn't let me actually save this so what   if i do 3.5 again doesn't work because i said  integer so it should be a whole number so if   i do something like 12 then it's fine so now we  have validation built in in our field so it's only   going to force whole numbers to be entered in that  column and i assume you should be able to still   erase one of these numbers and just do blank  and there it is see i erased it and if i go back   that's gone very good so that's our quantity field  pretty straightforward now let's try to add some   sort of dollar amount next like price next to  this so i'm gonna go back here add another column   and i'll call it price and  we'll enter some numbers so maybe even apply some number formatting i don't think we should do dollar formatting  here because we don't want to pick up the dollar   sign i think we're gonna format it directly in  our software but we'll find out it's possible   we should also remove this comma separator let's  actually do that let's remove all the formats and   go with this and then we'll see how we're going to  deal with this so let's go back and add this field so what i'll do i'll basically just copy this  quantity and change this to price and price   and editor input alignment right validator it's  not going to be integer is there a validator for currency there is float there is numeric  valid numbers so float would be like a   decimal point number but it will not  force currency format like two digits we do have regular expressions so  i guess if nothing else we could do   this for now let me just remove the  validation we'll come back to this   after we have this field working now we want to  see if we can format this as currency so let's   go back and search in the search box format so  now we got this cell formatters let's go here   built-in formatters plain text money so that  should be good see this is what we're looking for   i'm going to copy that go back to our field price  add a comma paste that formatter let's align this   a little bit so that's money formatter and this  is parameters so our decimal is actually a period   and our thousand separator is a comma our  symbol would be the dollar sign symbol after   p i'm not sure what that means so i'm  gonna have to check their documentation   precision should be two decimal points for  currency now let's go check what this p   is hopefully there's some  explanations symbol after symbol after position the symbol after the number  default is false so i guess this symbol we have   like the currency do we want it after or before  i'm not sure what that p is but if we want it   before i guess this should be false so we're gonna  do false so with this settings let's go back and   reload actually we should also add this column  in our list of fields that we allow to change right there in this if statement and now we  got this new price field hopefully based on   this column let's go back and reload and see  what happens so there is our quantity price   that's weird did i not put a dollar sign no i did  this of course let's put a dollar sign in there okay so it did format it see like this number  it did add a comma here i didn't have the comma   so it formats our numbers for us here so  let's try to go back and change some of these   so what if i for example go to this and  add like something like that i hit enter   see it formatted the number now i'm curious  how it saves the number in our spreadsheet   okay it saves as just a number excellent so  the number goes to our table with this format   after it's saved but internally the number  is just a number so that's actually good   so we should be able to do this and again  i guess the issue here is that right now   we didn't have any validation so if i  enter this that will probably save that   see in our database so there's no validation  to force for this entry to actually be a price even though we do get that formatting now let's  see how we're going to force that so based on what   i checked before validation data validation there  was no money type here interesting that there is   a formatting money type but there's no validator  money so where is that regular expression thing this so let's try to use this validator  so it says allow string that ends with dot   com so this is basically what  dot com ends with dollar sign   what is this is this escaping this in  javascript string i guess probably so   let's copy this validator and see how we're  going to deal with this so i'm going to go back   again for this i'm gonna add it here i  guess in the beginning of this whole thing   so here instead of endswith.com we still want  that dollar sign so i guess this is the prefix   to start regular expression and this is  the actual regular expression itself so   dollar sign means ends with so we need to force  it to end with two characters so that would be the   curly brackets two so two of this character and  because it looked like we have to escape this   we probably have to double that so that  would be two numbers as last two digits   now we do need a period before that so i'm  gonna do again double this and period so we   need to escape the period in regular expression  otherwise it has a special meaning so period   and digits and then before the period we would  have one or more numbers so we would do a number   plus so one or more digits and nothing else  so i guess in the beginning we'll just do   this so starts with one or more numbers and then  period and then two digits in the end so i think   that should work so let's save this let's try how  this works so i'm going to go back and refresh   so let's go here and try to type hello first  of all hit enter doesn't work let's now try   to enter a valid number so that went through  that's a good sign and hopefully that saved   yeah the value it did now let's try to  enter something else so what if we do dot   and 3 characters see it doesn't let me one  character still doesn't let me so if i do   2 that's good so now it lets me do two  characters if i go and add more characters here   that should work good and if i try to add like usd  or something it won't let me enter anything else   so basically now we can enter a number in that  particular format only excellent so we got our   quantity and price working let's try to sort  this quantity so that went empties first then 12   and then 44 then 65. yeah i'm not sure about  these numbers we probably have to test it with   a different number there too let's actually  go back and add like 23 and then try to sort let's see what happens when we  sort so if i go to my first page   1 to 12 23 that seems to be correct so it is  sorting my quantities correctly not as text but   as a number that's good and then this way it sorts  highest to lowest what about price so 2205 3456. it looks like it's fine so let's  just add one more let's add like 5   34. so hopefully it's not going to go  in the middle of this 2 when i sort   no so that takes care of our fields i'm  just going to make these a little smaller   quantity and price this let's make it a  hundred and this make it a hundred two i think we need to set a size for this last  column to be a little bigger so we can actually   read the title so i'm gonna go ahead and do that  let's grab this and find our checkbox here it   looks like it already has one so let's actually  just change this to 120 or something and reload good seems to work just fine oh and this also  got fixed apparently this used to just go up   there i'm going to assume because we made the  size of this bigger the height it just works now   nice okay so next i want to add some  sort of search functionality on this   so let's try to do something where we can  search by name and narrow this list down   so let's go to their documentation and see if  they have search see there it is search data so if we look here it says search rows as you  can see allows you to retrieve an array of row   components that match any filter you pass in it  accepts the same arguments as set filter function   so let's look at that set filter function and  see what it does so this one is called search   rows it retrieves an array of row components  what about this one so if we scroll down set filter set filter so there's a like contains  string and case insensitive so maybe this is   what we need to actually do set filter and  it seems like it's done on the table object and apparently there is a lot more here you can do  multiple filters at the same time as an array that   would be useful if you're filtering by multiple  columns in our case we're just going to search by   their names so we don't need this array of filters  we just really need a single filter so i'm going   to assume something like this should work for us  so let's go back to our script and open this now   we need to be able to refer to our table variable  right here we create this table instance now we're   probably going to need access to that in other  spots so for that reason instead of doing this   const table let's actually store it within  this elements object so what i'm going to do   here i'm going to change this to be elements dot  table and then i'm going to scroll down and find other table instances like this one and i'm  going to replace it with that elements.table i believe that's all we got let's make sure  everything still works the way it should   but now hopefully we will have access to this  elements dot table so let's just reload this   looks like we got our results that's a good sign  so now let's try to apply that set filter to   that elements.table and for that  we're going to need someplace to   type our search so i'm going to go above i  guess the table and i'm going to make a div now inside of this div let's actually add  possibly another div just in case we need one   and then within this div i'm gonna add a label and we'll do an input element type text and our label is going to be search i guess  and our actual input should have some sort   of id so we can refer to that i'll just call  it search and maybe we'll add a placeholder so at this point i'm gonna need access to this  search box so for that when we load the page right   here we're going to do another line like this so  i'm going to copy this and i'm going to call this   search and we'll refer to that id search which  i did right there so now we're gonna have access   to that element and to that element we're gonna  add an event listener so i'll do it right here   when our page loads we'll add an event listener  and the event is going to be input so when we   type in that box we want something to happen and  that something is going to be running a function   so we're going to call our function search  data now this function doesn't exist so   we're going to have to actually create  that function so i'm going to scroll down and i'm going to create that function search data that function is going to accept  e as an argument the event and that should allow us to get what's inside  of that box now here what we'll do we'll apply   that set filter function so if we go to their  example here this is what they had so i'm going   to copy that go back and paste and here instead  of a table it's going to be elements dot table   set filter this is going to be the field we're  filtering by that's what it looks like so if we   scroll up the field we're looking for is this  one it's called name so i'm gonna match that   and it was called like or something  let's go check out their documentation there it is like and this would be the value  so i'm going to go back and that should be   like and this would be the value we're trying  to search so let's just do e dot target   dot value i believe that should get us  what's entered within that search box   so let's see how this reacts so i'm gonna go  back and reload so you can see i got this type   here i'm going to click here and i'm going to  search for jack and just like that it worked   as i enter i'm curious what happens if i just  erase everything it actually goes back to our   full table which is exactly how i want  this to work let's just do another test   so in the second page we got donna and here we  also have donna let's search for donna so that   should narrow down to those and if we delete d it  should still narrow down to those so it contains   this we may want to style this search box let's  try to do a little bit of styling not a lot   so i'm gonna go back and we're gonna scroll  up we're gonna find those divs i just made   so this is the div that holds the whole  thing so i'm gonna call that class search box outer and let's add another class for our  inner div and we'll call it search box inner   then we'll have our label and our  input inside of this search box   so let's add these classes  to our list of classes here so that's the outer box this is my inner box and inside of that inner box  we're gonna have an element input   and also input would be this and  label here so let's do a label as well   so for my outer box which is going to hold  this whole div i just want to add some   margin below so it's going gonna push the  table down a little bit so we'll do margin   dash bottom and i'll do something like one  rem let's try to see what's here on top so see we have this data  table this background color   right here so i'm going to  apply it to that whole inner section like this will add some padding to  this too maybe 0.5 ram and then for my input   element well we want the color of the text  so we're going to need something like this to be that white color and our  label should also have that   color so let's just quickly go back and see what  that looks like so far so as you can see we got   now this darker background we got this area  and we got white text i want a little space   between this and my label so i'm going to add  a little margin to the right of this label i'll do margin right change the background color for this input   i'm not sure if we want this  color or a little more gray color like maybe this other row color let's try that so  i'm going to copy that and go to our went a little   too far go to our input and as a background  color i'm going to use that let's save this   and see what this looks like now see now we have  some space here there's this weird borders we got   let's get rid of those and we probably also  want to add some padding to both the label and   the input box itself kind of space  them away from the border itself   so i'll do it here and here and for our  input box we want to change the border   now i'm probably going to use that green  sort of border let's see where we had   something like that it should be one  of their classes here it could be this so let's try one pixel solid and that color  let's go back and check what this looks like yep that's that green color i'm not sure that  0.5 padding for this was actually enough oh   actually the problem was probably not the 0.5  but the part that i forgot to do the measurement so let's save that and see  if that makes a difference   yeah that's more like it and i'm gonna  add a little bit rounded borders here   for this search box to finish this so i'll go  back to this border and we'll do border radius and we'll try something like this maybe that's a little too much so 0.3 let's try a little less 0.2 i guess that's good  enough so you can take it from here but at this   point i got a search box and if i enter jack that  should just narrow down to jack and i can erase   this click away and here we go we got our results  so now we have a proper search box so finally   let's also add an option for us to add a new row  right now we can modify our data but we can't add   anything to our data so the way we're going to  do that we actually need to add a new id to our   table of data when we click on a  button in our user interface now   first we're going to have to create that  back-end function to actually add a new id   below this ids so i'll go back to our script  open our server side functions and we're going   to scroll all the way down and we're going to  create a function i'm going to call it add record and for this we're going to need the same  spreadsheet and worksheet so that's the same so   what we're going to need we're going to need to  create a new id now i'm not going to go too crazy   about how i create that unique id if you want  100 accuracy of unique ids i have some videos   when you can store your last id number and go  off of that to create a unique id number in this   one i'll just get a timestamp and basically just  use it as an id which should be fine for this so   i'm just gonna do const time stamp and the way we  can get that we'll just do a new date object and   do get time that should give us a timestamp now i  want to work with that and maybe add some dashes   similar to this dash here for that  reason i'm going to convert it to text   so i'll do two string so right now if  i just do console.log that timestamp let's try to see what it looks like so i'm gonna  open this add record here i'm gonna run that   and see it gives us this and now if i  run it again it should not be the same   see it's different so that's what i'm gonna do so  i'm gonna need to break this number down in two   parts the number needs to be five digits before  the dash and the rest i'm gonna put after the dash   so i'm gonna go back here and to get the part  before the dash let's just call it new id   we're gonna take that timestamp and use  substring because it's text we're gonna give   it a starting point zero and i need the first  five characters and then i'm gonna add a dash   and then i'm gonna add the remaining characters  by doing another substring and start from   five and grab the rest like this so at this point  if i look at this new id save this and rerun you'll see we got five digits dash and then  the rest of these characters good enough for   me so that's what i'm gonna use as that  id so instead of console logging here   we'll take that let me zoom in a little  bit we'll take that ws the worksheet   and do append row and here we'll pass an array  with that new id and that will basically add   this new line with this new id right  below this so it's just going to add an id   the rest of these fields are going to be  blank for this which is exactly what i want   now i want to be able to work with that id  in my grid so i'm going to return that new id right there so this is going to be my backend  function to add a new record and return the id   of this new record so once we got this  now i'm going to go to the front end and   we're going to need a button to click on so we can  add a new record and i'll just use i guess this structure i'll just copy paste that div  and here instead of search box we'll do   add record box and then do the same for  this inner and here instead of a label   we're going to have a button which  is going to basically say add   record and we're going to need some sort  of id for this button which we'll just call   add record i guess so now that we got this html  let's store a reference to this button so i'm   going to scroll down see i have this i'm going  to copy paste and call this add record button   and we'll just do that add record to match  the id of the button that i just made here   so now we're gonna have that button and  for that button we're gonna need an event   listener so again we'll copy this instead  of search this will be add record button   and the event instead of input will be click  so when we click on that button we want to   add a function and the function is going to  be add record this function doesn't exist   here we're going to have to make that function so  i'm going to scroll down and create this function so right here we'll make this at record  function and what this function is going   to do it's basically going to call this  backend function to actually add a record   so we're going to need one of those google  script run things so i'm going gonna go back   let's actually find one and copy paste instead  of me doing this whole thing see like this   is kind of similar to what we need so i'm  gonna copy that go to this new function and   paste it here i'll just align this like that i  don't need these lines i don't need these lines   i basically just need this main structure of this  and here we're not going to pass any arguments   and the function we're going to have to call  here is going to be this backend function   called add record so we're going to match that  here now when we call that function at record   if it's successful here it's going to return  that id or new id whatever i called it which is   this now we're going to take that new id  and do something in our front end here   to find out what exactly we're going to do  let's actually go to their documentation and   see how we add a new row to our grid so i'm  going to go here to search let's do new row there's no data or something  maybe let's call it add row   okay updating data add row this seems  to be good see we got this table add   row function and apparently it has this true  let's see what that means the second argument   is optional determines whether the row is added  to the top or bottom of the table true will add   to the top otherwise it's going to add to the  bottom so basically do we need to add that new   line in our grid on top or below the whole thing  true is fine i'll put it on top you can do false   to put it below it's up to you but in the end of  the day this is pretty much what we're gonna want   to do so i'm going to copy that go to our code  and paste it here remove the semicolon actually   i'm going to remove this one too and instead  of table it's going to be elements dot table   add row and here we're not going to have  these instead the field we have is called   id so it's going to be id with an upper case  i and just to remind you that's going to match this field right here id so that's that now if  your field has spaces you would have to probably   do this but i'm just going to do this that's fine  now all of these i don't have so i just have the   id which will be that new id which i pass from  here over here so we'll run that backend function   we'll add that new row here and then we'll  grab that new id and we'll add it to our grid   over here so first of all let's see if this works  just before i do that let's also just add console   log and i'll just say error adding the row now  you could do instead of this console logs use   what i've done here before where i did this  alerts and i cleared them so maybe you can have   below the table like new record was inserted  or there was an error inserting the record   you already have an example of all of that  here so i'm not gonna go over all of that   again i'll just keep it simple like this so let's  see if this works i'm going to save it go back   and we're going to refresh our table now we have  this add record button let's see what happens when   i click on it so i'm going to click on this so  it did add this new line here and as you can see   this new id was inserted on top which means it  should also now happen in our data so if we go   back to our data see there's a new line here  with these empty columns here and now because   we have this edit function we should be able to  work with it find this id and fill the rest in   so this should just work as is i should be able  to just go here and add a name for this person so basically just add the information  if i go back and take a look   it's done so just like that we have a function  now to add a record let's just style that button   finally a little bit to make it look closer  to this styling so i'm gonna go back and add   some classes i'm gonna scroll up so we have this  button already here we have these few new classes   so i want that same sort of background  as i did for this search box   and say margin so i'll just copy this  and for this outer i'm just gonna do a   comma and stack this other one here just to apply  the same margin to this one too otherwise you   could just create another new class below and add  your own margins and stuff but this is good enough   i want the inner box to have the same  styles too so i'll grab this inner scroll up and comma and do dot that class so that should  apply that same background now let's add a button style here so that would be the  button that's inside of this   so we'll basically just add that inner record and inside of this we'll have a button   now for that button i'll probably apply  these same things i did with my label so like same color text same paddings same  margins save this let's just take a look   i'm gonna reload this okay i guess  we're gonna need some sort of background   color for that button too to be this  gray background i did before which was i think this so maybe i should have done these  classes here instead of doing the label so let's   do that let's replace this with that so save  that the ones we did for our input and reload yep   now it doesn't give me that hand when i roll  over the button let's add a cursor for our button so pointer would mean a hand for  us so if i go back and reload this see now i got a button and it  has the style and when i roll   over it it gives me the hand so now if  i click on add record it adds the record   and that should also happen in our spreadsheet  and we could go here and add some information and finally add one more record so i got john doe i also think i had see jane doe so now if i just search in this  box it should just narrow it down to those   so our search works our add record button  works we now have a way to create new records   now this entire time we did all the work under our  dev url now assuming we're happy with our results   and we want to finally deploy this application  we now will go back to our script editor   go back to deploy and we're going to create a  new deployment now if you want to create a whole   new link for this deployment you could do new  deployment if you want to keep the same url as   the last deployment you can do manage deployments  i have a separate video covering differences here   if you want to learn more exactly how this  works so i will just go manage deployments   and under this i'm just going to click edit and  switch to new version i'm going to type the 1.0.0 then i'm going to scroll down and we're going to  have to set the correct account so if you want   only people who you shared your spreadsheet with  to have access to this and be able to work on this   then you probably want to switch this to user  accessing the web app so whatever account they   use to log in that's the account that's gonna  run the app which is the one that needs also   permissions to the spreadsheet so you're gonna  have to share that spreadsheet with that person   as well now otherwise if you want anybody  to have access to this you can also just to   anyone or anyone with google account meaning they  have to be logged into their google account but   it doesn't matter if it's a specific person just  anyone so if you do this this one then probably   you would do any one with a google account so  this way it's gonna be under their permissions   or in my case i'm just doing this for this  tutorials i'll do only myself if you're doing   this again like a public thing you can do any  one at this point i'm gonna do that and click   deploy and whatever url link i have here that's  my final url so if i open that that will be the   web app that i get here if you have a workspace  account you shouldn't have this top part if you   do not have a workspace account but you want to  get rid of this you could just embed this as an   iframe inside of a different page and get rid of  that message i have a video in my original web   app series that shows exactly how to do that if  you want to find out how to do that but for this   this should be good enough so i'm going to close  see this dev is when you work you want to be able   to see your changes you're going to be able to  do that but for me once this is done this is my   final url right here for my app so everything  should be working the same way i should be able   to go here and search i should be able to do  pretty much everything else i did including   changing my records so if i want to go to this  john doe record and change maybe the company hit enter that should again modify   in my spreadsheet as you can see and everything  else should work exactly the same way and that   should do it for this video thanks for watching  please subscribe and i'll see in the next one

Channel: Learn Google Sheets & Excel Spreadsheets

Views: 104,488

Keywords: Web App, Google Sheets, Example, JavaScript, Apps Script, html, css

Id: QAt2RfosxVQ

Length: 146min 51sec (8811 seconds)

Published: Mon Jun 20 2022