Google Sheets Form for Data Entry - Apps Script

so in this video we're going to build a simple  data entry form in google sheets so let me show   you what it's going to look like once this is  all done we have this data tab that basically   stores the data we're going to enter from  this form and then we should be able to   go to this form and for example if i want  to add a new record i can simply just type and hit save and see we got this message that  this created now i can go to data and you'll see   jane is now here and we have a new id for jane  now i can go back and modify any of these records   so if i wanted to update john doe i should  be able to go to my form and search for john and now if i want to update hit save we should be able to now verify that  john's information was updated see   now it's 45. we can also delete a  record so i can go back this is john   let's say i want to remove peter so i can select  peter that updates this and then i hit delete that will delete peter from this list as you can  see and we can basically work with this so now   this is going to update it's going to have  the list of our people you can search this   and find this and if i want the new record  i can click new record and then i enter some information here and as i click save it should add that record for  kyle so now if i go back to this list you should   be able to see that includes kyle in there too all  right so let's get started by just building a form   here so i'm just going to go ahead and rename this  worksheet let's leave a couple of rows here on top   i'm gonna go here and basically just add  my fields i'm gonna add something like id   and right next to it we'll have the box where the  id is gonna go and then we'll have maybe first   for first name and then we'll do last for last  name i'll add like state and obviously you can   just keep adding as many fields as you need  i'm gonna just add one more some sort of number   and at this point i'm just gonna add  some borders to all of these placeholders   so i'll just go here i'm just gonna add sort of  light border like this and i'll just do for this   and i'll keep doing this for all of these other  fields so as you can see we can't really seed   that well but it's gonna be here i'm probably just  gonna copy paste this because it's gonna be easier   so at this point i'm gonna remove grid lines  so we can see those there they are i'm gonna   resize this column make it smaller like this maybe  resize this one too at this point i'm just gonna   select this area above and merge all those  cells i'm going to call this data entry form let's just center this and change some background color and a font i'll also just change  the font for this entire spreadsheet should be good enough let's also just resize these  things and just make it look a little smaller so i'm pretty sure you get the idea so that's pretty much what my form is gonna  look like i'm gonna need a couple of buttons   here i'm gonna go under insert and do this and  basically we're just gonna build a button here i'm gonna call this one new record and we'll basically just center this thing and add some background color and some font color in this let's make this a little darker maybe make it bold let's just also replace the font good enough save and close so these things  usually look pixelated for some reason i'm   just gonna build another button here  that's gonna be save and finally   one more that's gonna be delete  so the same process all over again so there we go we have our buttons so at  this point i'm going to make another tab   i'm gonna call this settings and on this tab i'm gonna start  by adding our next record id   and we'll start with one for now and then  i'm gonna go here and do a list of states and we'll basically just make sure that  we have these as an option in this form   right here so i'll just click here go under  data do data validation and list from a range   i'm going to click here check this box we'll  go to our settings tab and simply just select   these i'll actually just do command shift  down ctrl shift down to select the whole   column so that we can add more states  and then reject other input hit save   command up to get back here and basically now  we should have a nice drop down here to choose   a state and then for age i also want to add  some validation and we're just going to make   sure that it's a number and the number  should be greater than or equal to zero we'll just reject other input hit  save and you can basically add   different validations for different fields  using your regular data validation here so   now that this is all done now let's  get to actually doing some scripts   and for that we're going to need a tab  where we're going to store our data and i'll just do those columns id first last so let's get to writing some  code so i'm gonna go to tools   script editor we're gonna rename this project we're gonna start by building a script to just add   a new record here and the way it's going  to work if we basically hit the save button   right here we're going to basically just grab this  information from here and save it here so for that   we're going to need a couple of things so first  we need to store references to all of these tabs   we have we got form settings data so i'll start by  creating a variable that will be our spreadsheet and we'll make three variables for our tabs so this form ws will refer to this form right here and we're gonna basically do  the same thing for the other two so first we're going to have to  grab all of these fields right here and i'm going to store the id in a separate  variable and the rest in one variable so we'll take our form ws and do get range  and the cell for that is this which is c3 and we'll do get value and for the others we'll do an array and i'll basically just comma separate  those cells and i'm going to come separate   them in the same order i have them here see  first last state age so first would be c5 then we have this which is f5 so what we'll do we'll just loop through  this list and using that loop we'll go get   all the values from those cells and  we'll store them to a similar array let's just rename this to field and what we'll do we'll take our previous array   this field range that has all these text  ranges basically and we'll map through that and for each field we'll do a callback function  which will basically just return the value of that   cell and it's going to be very similar to this  so i'm just going to copy this line paste it here   the only difference here is that instead  of the c3 we'll just pass the actual field and this line should help us just run through  all of these and get those field values   in this array so just to test this let's do a  quick console log and i'll just console log this i'm going to save this now let's just  go and type some things in those cells and let's go run the script and see  if we're able to get these values   so i'm going to go here this is  the function called my function   we're going to run that we'll have  to do all this permission stuff and as you can see we got linda smith state  and number looks pretty good in addition to   these four columns we're gonna need to store the  id for that new person and to get the id we're   gonna have to get this next record id number  from here from settings so let's go ahead and   do that so i'll do it before this console log so  that's going to come from the settings worksheet and again we'll do get range and  just point to that cell which is a2 and we'll get the value of that cell and we'll put this in a variable next id  so now what i want to do i want to add it to the   beginning of our list to this field values list  so i'll just take fields values and do unshift   and that allows us to add a new item to  the beginning which will be that next id   so if i run the same code so you can see what  this field values are after i've run this see now i got one and then the rest because we  get that id from here and we basically add it   to our list now the only thing left to do is to  just make sure that we add that information to this data worksheet to do that we'll simply just  take our data worksheet and we'll do append row   and we'll basically just pass that field  values right here i'm going to comment that   save it and then finally we're  going to rename this function i'm going to call it add record  actually let's just call it save record and we'll basically just apply that  to the button that we have so i'll   copy that function name go to this go to  this form and just take this save button and i'll simply just do assign script paste  that here press ok and now if we hit save   see it's going to run our script finished  if i go to my data now i have linda here   now what i want to happen when i add a record is  a couple of things the first thing i want to make   sure that we set our next record id to go one up  from what it was so this should have been two now   and the second thing i want to happen i want to  place that id of that person here in this box so   let's go ahead and do that so i'm going to go  back to this so we'll do a couple of things here   i'm going to rename this one to id cell and we  won't get the value here yet and then what i'll do   after we add this record we'll take that id cell  and set the value of that cell to be that next id and then at this point i want  to go to that settings tab   right here to this cell and basically just place  the next number in the cell so for that i'm going   to do a couple of changes here instead of doing  next id here i'm going to call this next id cell and then i'm going to change this to not get  the value so now that this get value is gone   i'm gonna create that next id variable here and  that would be that next id cell dot get value and that way this will be fine this will be fine  and then we just need to now take that next id   cell and set the value to be whatever the  value is right now which is called next id plus   one save this so at this point let's  go test this and see what happens   so i'm gonna go back to this i'm gonna delete linda from here so we got our next record id  still one so if i go back and hit save again see we got the id here it says one and we should  have that record here and now if i go to settings   our next id is 2 here so this means that if  we run it next time it's basically going to   now use 2 as the id of that person now next i  want to do something with this new record button   so when we click on this button  i want to basically just clear   everything from all of these cells so let's go  here and we're going to create another function i'm going to call it new record now we're going to  need to basically clear all these same cells that   we've defined here and the same id cell and since  we're going to need access to all these things   all over the place i'm basically just going to  move some of these things to be global variables   so i'll just grab all of these  lines cut them move them on top here now as i did this i want to make sure this still  works so i'm going to go back and hit save again and see now it went to 2 and now we should have   done linda twice but it seems  to work just fine so no problem so now that we have all of these globally we  should be able to just go to this new record   and basically just deal with this so we need  to basically run through all of these fields   and clear all of them so  we'll take that field range   and we're going to have to map through it so  i'm just going to make my life easier and copy   this much paste it right here and what we'll  do here instead of map we'll use for each   and for each field we're going to go find the  field and instead of getting the value here we'll   just clear content and then we also need to  clear that id cell so that id cell would be   this id cell so we'll just say id cell dot  clear content so pretty straightforward save   now i just have to copy this function name new  record go back here and assign that to this button so at this point if i click new record   that should clear all of these cells  so we can now enter some new name and hit save and now we got  peter and we got his id3   it's here and as you can see next id went  to four i don't want linda twice so i'm just   going to delete one of these and it's fine we'll  have unique ids the next one is going to go four   so now i want to have some sort of search box that  i can search and find a record so i can delete or   modify it so for that i'm going to go to data  and under data i'm going to create a new column and this column is basically going to be a formula  and the idea of the formula is gonna be to combine   the columns that you need to be searchable  so i'm gonna for example in this case combine   first and last names and i'm also  gonna add their ids but you could   literally add anything you want to search so if  i did this with a regular concatenation i would   have done equals i would take this and do like  an ampersand and add a space maybe add another   ampersand add the last name so that would be linda  smith at this point i'm not going to drag it down   so after that i can do another ampersand  and maybe add like a dash with a space   and then concatenate the id maybe we'll even  just say id colon something like that like this   so now i want this formula to automatically  just feel down to cover all these other records   but i also want to make sure  that i don't confuse my script   that i have more records here below this data so  for that what i'm going to do i'm going to first   convert this to an array formula so we'll just do  b2 colon b and then c2 colon c and then a2 colon a this should have been c so now if i do ctrl shift  enter or command shift enter that should drag   the formula down but it should also do this  thing so to avoid this i'm gonna filter this   so i'm gonna add filter to this comma and  basically i'm going to filter by id because each   record must have an id so i'm going to grab these  remove the end reference and i'm going to say   id is not equal to blank double quotes like this  and if i hit enter see it's gonna clean this up   it's only gonna do this for this records now this  is okay but if this was our first record we would   still have a problem because it would probably  push this formula down as we add the new record   so we want to move this formula here to the cell  instead of having that formula here so the way i'm   going to do that i'm going to take that formula  copied i'll go back to this what it says search   column and i'm going to make a formula and in  this formula it's going to be that same formula   for now i'm going to do this let's hit enter and  delete the formula from here now this is not in   the correct spot but what i'll do i'll put a  ray bracket around this whole thing like that   and right now it should work the same way but  this should allow me to now go and after this   just do double quotes and semicolon and add my  column name here so i'm going to say search column and if i enter c now our formula is here  however if we didn't have any records here this would give us this anything now i'm gonna  try to just do if error on this filter function   and then we'll try to run our function and see if  it adds the new record in the right spot hit enter   so now it's clean but i'm still concerned that  it's going to think there is something in the cell   so let's just test this so this is our  formula let's go to our form and click save so that adds the record looks like we're good see  it adds the record in the right spot and we got   this data auto populated for us using this formula  so now we should be able to use this column to   search and find the record we're after so for that  we need to create a field for searching so i'll   go back to this so now that i'm thinking about  this i probably should have done the search here   and this id shouldn't even be in this  section maybe we should have moved it   on top someplace so it's not that visible  i'm gonna do those changes but then   i'm gonna also adjust those in our script  unfortunately so let's just first move this   over here i'm going to add one more line here and  here i'm just going to create our search field and we'll just copy one of these boxes pretty  much and maybe i'll just do something like this   i'm going to erase peter out of here and for this   because these ids we're not going to be  manually typing i'll just do different color and i'm gonna add one more line here and do the same background so now because of all these changes i need  to update my script this cell is now c   2 for our ids so i'm going to go on top here   that's c2 and these cells are now  different too so we got c7 f7 and c9 f9 and the rest of this should be fine okay so  now let's first create our search so what   we're going to do first we're going to add data  validation to the cell so i'm going to go data   data validation list from a range we're going  to check this we're going to go to that data tab   and we're basically going to select starting  from here and all the way down if you're going   to be adding a lot of records you probably  want to add more lines to the spreadsheet   and then do this there it is that's my  range i'm going to hit ok reject input   and what should happen now based on what we  have in that spreadsheet this should have   those options here and as you start typing like  see brown it should basically show that or peter   it should show up we could also search like id  colon for if you want to find somebody by id   so now once i'm able to search and click on  one of these at that point i want to basically   autofill all of these fields with that person's  information so that means we need to search and   find whatever's here in this box in this column  over here and based on that we need to grab   all of these cells with their information  and populate our spreadsheet so let's do   that so i'm going to go to this and we're  going to create a function to search now first we need to get the cell where the  search value is which means this c5 cell so i'll just copy this  scroll down and paste it here and we'll take that cell and get the value let's correct this so now once we get value  we're going to go to this data worksheet   and grab all this data here and basically  search this column so to grab the data   we're going to get it from this tab we already  have that stored in this variable data ws so   i'm going to take that and i'm going to say get  range and the range for this is from a2 through f   all the way down so i'm going to say a2 colon f  and here we'll do get values to get all that data and i'll just save this in a variable so now that data is going to be that whole  range here including all these empty cells   now we're going to have to search this  column to find our matching record so   what we're going to do we're going to take that  data and we're going to filter that data and   we're going to take each row and we're going to  compare the information in that row in this column   which is in our array count starts  from 0 so it's going to go 0 1 2 3 4 5. so i'm going to check if that  equals to our search value actually just do double equals  here on this and if it is   it should basically just keep that record  here so we'll just say records found now this is still going to be an array so  in that array we're going to grab the first   record and we shouldn't have more than one because  we hopefully should have a unique id with their   name and last name and all of that so we're going  to take records found and in this if we grab   the first row it should give us that record  at this point but i want to only do this if   there are actually records found  if nothing was actually found   we don't really want to do anything so what  we'll do we'll just say if recordsfound.length equals zero we'll just return so we'll stop the  function and not continue otherwise we'll continue   to this line now we need to take this information  and basically just put it here so we're going to   first put the id where it belongs which should  be the first column coming from here so this   id cell we're going to grab that and i think we  stored it in a variable on top here see id cell so i'm going to say id cell dot set value and we'll take the record and in that  record we'll grab the first column   so that should get us the right id now  that's good for id but now we want to   also place the other records where they should  be and we're going to use this field range again and what we'll do we'll loop through that and  for each field we know we need to set the value   so we'll take that form  worksheet and we'll get range   of that field similar to how i did this here  and we're going to set the value of that field   from this records found here now to do that  we need to basically give it a position   like first second third and remember the first  one here which is zero is our first column   we really need to start from our second column   so that's really gonna be like one 2 3 4 5.  so the way i'm going to get that when we do   this for each loop we can actually get  two arguments here the field itself and index of that field and index is going to start  from 0 in this so it's going to go 0 1 2 3 4. what   we really need is actually 1. so what i'm going  to do if i just put this here as a set value and   change this to i that would basically first start  from zero which would be the id column in our data   we wanna start from first name column so i'm  gonna do plus one here now we're gonna find out   if this works or not so let's actually  add another record so we can test this we should probably clear this too let's move that on top here again and then when we do new record right here  we'll also clear contents from that cell   save this if i go back click new  record okay let's add another person so now we should have two people here as you  can see and if i go here and start searching we should be able to find linda and if i  search peter we have peter now i want to   run that function and see if it populates  peter's information here accurately so i'm   going to go here just that search function i'm  going to go to that function search and then run so let's go back and check what happened   so seems like it was populated correctly see  we got four peter brown illinois 44. that was   peter's information now we don't probably want  to go run this i want this to happen as soon as   we select this record here so what i want to do  i want to check if somebody changes anything in   the cell this c5 cell and if they do i want to  automatically run that search function so for   that we're going to have to do on edit let's move  this to a new file so i'm going to create a script   i'm going to call this on edit  and here we're going to create   this special function on edit and this is going to  accept this e-argument which will allow us to get   the range of the cell that was modified and  what we'll do we'll compare if that range is   this cell in this tab then we'll run that  search function so what we'll do we'll say   this doesn't give us auto complete so let me  just make something up so we have auto complete okay so if we have a range we should be able on that range to get a1 notation which is basically something  like this a1 through e2 or something like   that so we're going to check if it's that cell  right so what we'll do we'll just do this get   a1 notation on this range and we'll  simply just say if that equals to this cell which is c5 then we know we should run our function as  a matter of fact we'll just say if it's not   equal to c5 then let's just return let's not run  anything now the thing with this is that c5 can be   from this tab but you could also have c5 from this  tab so i only want this to run when it's coming   from this worksheet which means i need another  check and here what we'll do we'll just do source and this will give us a worksheet and on this we should have  this get sheet name method so i'll just do this and this time  i'm gonna check if that's not form tab then stop it return in all the other cases we're  gonna run that function search that we've created over here so now hopefully if i did this correctly  if we go and make a change it should automatically   just run our function so let's go test this so  i'm going to go here and change this to peter now we already have peter so we're not going  to know if this works so let's try linda it doesn't seem to work so let's go check  our executions and see if we get some errors so i'm gonna go under executions see we got this  on edit so since it doesn't show any errors here   that tells me we didn't pass one of these tests  so let's console log a couple of things here   so we can figure out what's going  on so i'm going to console log this and i'm going to console log this and i'm also going to just console log e   we're probably not gonna need  this e so let's get rid of this   let's just go run this and see what happens i'm going to go back to executions and take a look so we got c5 and we got form that looks  pretty good it actually gives us the values c5   form oh of course i didn't spell form right  stupid mistake there we go save this i don't   need these anymore so we'll go back now and test  this again save this go back we'll do linda smith   and as you can see that actually did add  linda's information if we do peter brown that grabs peter's information and adds it to  our form very nice the next thing is going to   be to save over our existing record so the  idea is going to be if i have this populated   with somebody's information and the way i'm gonna  go about it if we have an id here then i'm gonna   go find that id and save over our existing  record if there is no id which is what's   gonna happen when we click new record if we type  something here there is not going to be any id   so at this point when we hit save we'll just  create a new record so that's the logic i'm going   to use on this so first let's just grab one of  these see we got peter let's now go and try to see   how we go and search for this number  four find it in this data and then   basically overwrite our existing information so  we'll go back to this so we've created save record   function which is when we click on that save  button so all of this code that i did before   that was for creating a new record what  i'll do here i'll move this to a separate   function so i'm going to go below here and  create a function call it create new record and basically just grab all of  this cut it and paste it here then we can just call that function  here when we want to create   the record so for now i'm just going  to place it here and just comment it   so it doesn't do anything so now in the save  record function we're going to work on overwriting   our existing record so the way that's  gonna happen we need to go get the id   from here and that is gonna be see we have  this id cell we're going to get the value and what needs to happen if there is no id we're  going to create a new record so i'm going to   say if that id equals blank then we're going to  create that record so i'm going to move this here   and once we create that record we can  return we don't need to continue here so in other cases if id exists  we need to go find that id   and figure out which record we need to update  so we're gonna have to search for that id here   in this column of ids so that's  gonna come from this data worksheet and i'm just going to grab the whole  column a which is where ids are   and in this column we're going to search using  this text finder and basically that's similar   to doing this command f when we search only  we're going to search in this column only what we're going to search is that number  basically so that id so i'm going to search   for that id and we want to do a couple of options  here we want to make sure that we match case which   is not that important because this is really just  gonna be numbers and this part is important though   to match the entire cell because if we search  1 we don't want to match 12 because 12 includes   1 in it so we're going to match entire cell to  actually get the right match and here we should   be able to do find next to go find that cell right  there let's move all of these to separate lines it's still going to work the same way and  we're going to save this in a variable let's tap these in now we need to check  what this find next does if there's no match   now the reason we need this cell is because  once we find the cell here let's say the   cell then we need to figure out what's the  row number for that cell so we know which   row of data to actually modify so what  we'll do we'll do cell found get row and we're going to save that in a variable like that so that's good we  found the row number hopefully based on   this now before i move on i want to  do a quick test to see what happens   when we search for something that doesn't  actually exist so let's just do console log   and just do the cell found and then i'm  just going to go to this and change this id   to 9 which doesn't exist i'm gonna try to see what  happens so i'm gonna run this function save record so it just returns null so that's good now we  can do a quick check here we'll just say if this is null we'll just return so a second ago you  did see how i got an error because we kind of went   here and we tried to get the row of a cell that  doesn't exist right now hopefully when i run this   see it's not going to give me any errors  because we're going to basically just   check here it's not found and we're going to  stop in other cases we will actually go and get   the row number so this should have been 4 so i'm  going to change it back so now it's a valid id i   should be able to go back and rerun this it should  run and not give me errors and we should be able   to also just console.log that row to figure  out which row the data is supposed to be in see row two so if i go back and take a look  row two got peter so now we want to just go and   modify this data now we're not gonna do this one  because it's formula but we're gonna need all of   this so it's gonna be pretty similar in many ways  to how i did that mapping here so i'm gonna copy   this line that does that mapping so basically  all this does it basically goes to this list of   cells basically gets the actual value from  the cell and that's our field values now   that's going to be an array now to that array  in the beginning of that array we want to add   our id so the id this time is not going to  be new id it's going to be our existing id   that we have right here this id so what  we'll do we'll do fields values dot on shift   to add to the beginning of that and we're going to  add that id to this so now we should have the data   as we need it so at this point it's the time to  write it to the spreadsheet so we'll take our data   worksheet we'll get range we're going to start  with the row number where we need to do this   modification the row number is going to be that  row variable we found right here we're going to   start our data from the first column which is the  id column comma then we need number of rows so   we just modify one row at a time so just one and  then we need number of columns and that's gonna be   one two three four five this is a formula   so it's just gonna be five now we could just  do five here we can also just do fields values   dot length so however many things we have in that  array so this way it will be a little more dynamic   and then we'll set values and those values are  basically these values however it's going to need   this in array of arrays structure like  this and this field values is just an array   so that's the reason i'm gonna need an extra  square bracket set around this like this   let's go and try to run this and see what happens  so i'm gonna save this go back let's go and try   to modify peter's last name so i'm going to go  here and enter a different last name hit save right now let's go and check our data see peter  perez great so we're able to actually update the   record just fine and it seems like as we update  that record we should also update the search box   or possibly just clear the search box completely  because i don't know why it should be here   yeah i'm just gonna clear that search  box so we'll just do that see search cell dot clear content so this way when we  save it should just clear that cell this one so let's just try  to update linda two just grab   linda and let's change her last name or state hit save see that clears here if we go back here   that was updated we got our record as it should  be now if we go back here and do a new record   because this id is going to be empty  we should be able to add a new record and now see we got the id it says 6  and if i go back to this it says that   olev is here so now we have another record and our  search should automatically have that record here   all right so we got our new record we got  our save now it's time to delete a record so   we're going to add another  function here after all of these that seems to be a reserved keyboard because  of that highlighting let's just change it to   something else delete record is fine so now  for this we basically have to go and find   this id again in this column and find which  row we need to delete which is going to be   sort of similar to our added code so i'm just  going to scroll up see what we did we got this   value of the cell the id so i'm going to copy  that and i'm actually just going to copy this much   copy all of that go all the way down  here paste for our delete record function   so what i'm going to do here i'm going to do this  id i'm going to get the id then if the id is blank   we can just return we're not going to create a  record so we can just simplify this a little bit   and do this and then we can just  again search for that same id   here in this a column if we don't find anything  we'll return again we'll stop the execution if   we do find the cell we're going to get the row  number so once we get the row number we simply   just need to delete that row so we're going to do  data ws dot delete row and it's simply going to   ask us for the position which is going to be that  number of the row we just found just like that so   i'm going to save it all i have to do now is just  copy this function name delete record go back to   this and assign it to this delete button so i'll  click on this assign script paste it here press   ok and now we should be able to hopefully  delete a record let's try this so we got let's   find linda so that's linda now you can see linda's  data is here like it should be if i hit delete let's go to our data and linda is gone now  again we want to probably clear the cell   once that happens as a matter of fact because  we're deleting a record we probably should clear   all of these cells not just this one and we  already have a function that kind of clears   all the cells this new record i don't remember if  that clears this search value and if it doesn't   we should probably make sure it does so let's  just try this i'm going to click new record   it does so now all i'm going to do i'm just going  to reuse that function in this delete record see   we got this new record function that clears all  the cells so what we need to do we're just going   to run that after we delete the row just like  that and that should just clear all the cells   once the record is gone let's just try this so if  i go here and select peter i'm gonna hit delete and it's all clear and peter is  gone so let's add a person again so that should now have peter here now if you  wanted to clear the form after you add a person   you can also add that same see this  new record function after you create   the actual record so right here when we have  this create new record function you could just   add that same function below here after this  whole thing i don't actually want to do that   so i'm not gonna do it i thought people might  ask so i'll just mention that so that created   our new record as you can see all of this works  just fine the only thing is when i add a record   i would like to get some sort of message to pop up  that says you did add the record so what i'll do   when we create a new record right here all the  way down here we'll take our spreadsheet app   and here we should have this  thing called what was it called well maybe i have to do get active  spreadsheet first so let's see this one toast that's what we're going to do now  i don't know if i have a variable for this if i   do i'm going to use it this active spreadsheet  i do this ss is what it is so i'm going to go   back to that new record and replace this with  ss and here we'll just do new record created   so basically just a message and here there's the  second box where you can add below the first line id equals next id which will be this id   that's for creating a new record i'm gonna  do similar thing for saving over record   so i'll go back to this save record function  and right below all of this we'll just add this and here we'll use that id variable right  there good let's also do delete record   the same way so we scroll all the  way down this delete record thing and id would be this id that we have here  that's good i'm not sure if we need any other   messages maybe when we search we need one or not  really sure let's just try this if we do this or go to olivia maybe i  don't think that's necessary   seems just fine so let's try all  of this to make sure all this works   so now if i have see a couple of people let's  add one more person i'm gonna click new record and see now we get this thing i thought   it was gonna be the opposite of what i just  did maybe i have to go back and change this so basically just change  this order of this arguments and that's it well that was creating  a new record so now we should have   john here let's save over something  so i'm gonna go and find peter and then we'll just change  peter's last name or something hit save so we know now we made some changes  and then finally let's try to find and   delete a record so i'll just go ahead  and select olivia and then hit delete and we get this if we go to our data olivia is  gone and we have our search that shows what it   should and that should do it thanks for watching  please subscribe and i'll see in the next video

Channel: Learn Google Spreadsheets

Views: 37,366

Rating: 4.9517241 out of 5

Keywords: google sheets, form, data entry, script, crud, how to

Id: ZKYvrD-3Ksc

Length: 59min 46sec (3586 seconds)

Published: Tue Nov 09 2021