Automated Data Entry Form in Google Sheets & Apps Script - Part 1 (User Form Design & Transfer Code)

[Music] hello everyone welcome back you are watching the data labs my name is lip in this series of tutorial we are going to learn how to develop a fully automated data entry form in google sheet and google apps script so this series will have four different parts and we will cover all the required features of a data entry application and that is validating inputs transferring data search modify delete and reset the user form so once you like will be able to complete this series you will have fair understanding of google sheet and google apps script and with the help of the code and logic which we are going to discuss uh in this tutorial you will be able to design a complex data entry form for your uses okay so this would be the first part of tutorial where we will cover how to design the user interface of data entry form in google sheet and write the google apps script code to validate and transfer the data okay so before designing the user interface and writing the code let's move to the demo form which i have already developed and see how it works so here you can see that uh this is the employee data entry form and in this particular application uh we have three different worksheet like user form uh database and support sheet so here support sheet i have mentioned the department list so that we can utilize the list while creating the drop down for department and database is where we are transferring the data basically uh and this is the user uh form where user will feed the inputs and save the data to transfer from this particular form to database okay and in this employee data entry form we have a facility of search so that you with the help of like after putting employee id in uh here you can click on search button to search the record and even uh you can if you just want to modify or delete any of the records you just need to put employee id over here and then click on search it will provide the list of all the details here and you can change any of the data and then click on modify okay so that will update the existing record okay and if you just want to delete any of the record you just need to put the employee id over here and if you want to search then you just need to search so that you can see the record and then you can press the delete if you don't want to search just put the id over here and click on delete it will delete the data from database and the clear uh so this button will help you in clearing the form like suppose you have feed couple of information over here inputs and you just want to reset this form just click on it will clear entire data and this is the save button so with the help of save buttons once you will fill the info information required inputs over here then you just need to click on save and it will validate whether uh you have updated correct details or not if not then it will prompt an error message that okay you just need to update the details and then after updating that details you can just click on save to transfer the data from this form to database so uh let's uh let's transfer a data so i'm just putting employee id123 and name delip kumar and this is the technology and address new delhi okay so let's uh transfer this uh this data just click on save and it will take the confirmation do you want to submit the data click on yes and now you can see that new data saved employee id one two three click on ok so it will clear the form and let's go to the database and now you can see that we have uh one record over here so in the same way you just put one two three employee id and click on search to search the records basically so you can see that all the information are reflecting over here if you just want to modify let's change the department so here let's put it quality and click on modify we want to edit the data so let's click on yes and now you can see that data updated let's go here and now you can see that earlier it was department was quality now it has come to uh sorry uh it was technology now it has become quality so in the same way you can delete and clear the form so this is the demo file basically and we will replicate the same design and code while uh working while creating this employee data entry form from scratch so let's close this file and now to start this we just need to open the google so let's google.com and you just need to login with your employee id so i have already logged in and here you just need to open uh the drive the google drive basically and once you open the google drive you just need to create a new folder so just click on here new and then folder just select the folder and here just create uh give a new folder name so i'm just putting employee data entry form and then just click click on create so here you can see that uh employee data form has been created i think we have already uh employed data entry form so it's recently created this one let me delete this one the previous one okay let's double click uh employee data entry form to open this particular uh folder and while keeping in on employee data in reform just right click on this folder and click on google sheet and then click on blank spreadsheet so once you click on blank spreadsheet it will open a blank spreadsheet in new tab and here we just need to uh change the name so let's keep the employee data and reform so i think okay so uh so in this project basically uh we will be utilizing three different worksheet as we uh uh have already seen in demo so let's quickly add two different two uh additional worksheet over there so just one two so now we have three different worksheet let's quickly rename all these worksheets so first would be user form and the second would be database and the third would be support sheet so now we have three differentiate user form database and support sheet so let's uh create a department list in support sheet so we need to create a department list in column a so header would be department name and let's quickly put the department name over here so that would be training quality hr technology marketing and sales so these are the department which uh we will use as a drop down so we are done with the department name so let's move to the database and just create the column header so the column header would be employee id so now we are done with the column address let's quickly apply the back color for headers so and the font color would be white and let's increase the size so maybe that would be 12. okay so we are done with creating the database let's move to the user form and let's uh quickly uh create the user interface over here so first of all let's uh resize the column uh like column width basically so the column a would uh the width of column a would be uh 63 so i'm just trying to replicate uh the same same design which we have already seen in demo file the column b which should be 176. and column c will be 388 so and the column d will be triple one okay so let's quickly create the column header so here we just need to fill this column header just merge this and fill this with the blue color so maybe this one and let's put the label as employee data entry form okay and let's make it middle and center line and the font would be us let's change the form so maybe we can say we can select this this font and the size would be 24 here the font color would be white so we are done with creating the header for this particular form uh let's create the label for each and every input field so first would be employee id and then so this would be employee id so let's quickly fill this with the color so maybe we can keep 26 here and we just need to fill with light blue color and now that is the employee id and this would be for input fields for employee id and let's copy this employee id so here this this particular employee id will be utilized for search and this is to input the details like transferring the data so just copy this and replicate this for employee name and then gender and then email id and then department and that is for address so let's select this address we need to merge here for address and let's quickly apply the back color for all these data labels okay and now let's move to the view and untick the gridlines here we need to apply the border so just select the entire range and border and the border color would be maybe you can keep this one this and again we need to uh separate this employee id so this is basically this is this area is only for search and these are for input for input details basically so let's select this range and then again border and let's select this and now let's change the column uh the label heading of labels like say this is employee id that would be employee name then gender email id department address so now we are done with creating the labels and the input field here we just need to apply some validation so let's quickly apply validation on gender so gender would be drop down so just select this c11 that is for gender and go to the data and select the data validation and in data validation window you just need to uh just need to click like criteria as list of items and just provide the items with comma separator female male other and if like user will put some other value then that would be the invalid uh input basically so just select this reject and here we just need to say message over here like please select gender from drop down okay just click on save and now you can see that uh the dropdown has been created that is female male and other so we are done with gender let's apply validation on email id so just select this go to the data again and then data validation and here we just need to check this so this one will be text basically and and that would be uh is valid email and if that would be not a valid email then uh you just that it will reject the input and show enter valid email over here just click on save okay so now we need to create the drop down for department so just select the c15 that is for department go to the data again and data validation and in data validation we just need to uh select this list from range that criteria would be this one and here we need to provide the range so just uh click over here this button and just go to the support sheet and as we have already created uh created the department list just select the range starting from e2 to a7 and then click on ok if here we just need to select this reject input if someone will enter some different value then it will reject the inputs and here appearance so just so let's please select name from the drop down list okay just click on save and let's press ctrl s to save this file the changes and here you can see that we have all the dropdowns training quality and sales so now we are done with creating the drop down let's quickly add the required buttons so we need uh five different buttons so let's start with the search so just go to the ins go to insert menu and here in drawing just click on drawing and let's click on uh this one shape and then the shape would be rounded rectangle and just draw a rounded rectangle over here and right click on it edit text and then search okay just select this make this the text would be center line and middle line okay and just fill the color with maybe this one and save and close so now you can see that it has started appearing over here so let's uh okay i think this is done now let's quickly add the button for save modify delete and clear okay so we are done with creating the buttons for say modify delete and clear so our user interface design is done having all the input filled with required buttons drop downs and everything so let's uh jump to google apps script window so just click on tools and in tools just click on script editor so once you open the click on script editor it will open app script window or in the next tab so this is the app script window and here uh you can see that this is untitled project so let's uh rename this so employee data entry form form okay i'll just click on rename and here we need to apply this code dot gs google apps script basically google script uh so you can see that it has provided uh provided a by default blank function that is my function so let's delete this because we need to create a function with a relevant name basically and press ctrl s to save the changes now let's write a function in the apps script window to validate the data entry made by users and we will also utilize the same function while transferring the data from user form to database worksheet so basically what it will do it will validate all the inputs like employee id name uh gender department email id and address and if any of the input field is blank then it will prompt an error message if everything has been filled by user then it will transfer the data from a user form to database sheet okay so let's start with writing the code so let's put comment over here function to validate the entry made by user user in user form okay and let's start with the function name so that we start with function the keyword and the function name would be validate entry open and close bracket and then curly braces and create some room over here press ctrl s to save the changes and let's start with declaring a variable so that we can reference uh this particular google sheet the employee data entry form so let's uh declare a variable so let's put the command over here declare a variable and set the reference width set the reference of google sheet okay so variable and the variable name will be my google google sheet and here we need to set the reference so we are just referring on the active active sheet so spreadsheet the spreadsheet app dot get active sheet active spreadsheet and then this one now we need to declare one more variable uh that would be for referring the uh the worksheet name basically so where and sh user form and here we are just going to refer uh the user form uh worksheet basically so as we have already declared a variable that is uh my google sheet so let's refer my google sheet dot get sheet by name and the name would be so the name would be here e insert form okay now uh we just need to uh utilize you know the prompt message basically alert here so to do that uh we just need to create a variable uh or create the instance of user interface that is you know get ui function so that's variable and ui that is user interface and here is spreadsheet app dot get ui so let's put comment over here to create instance of the user interface for interface to show the alert now what we need to do we need to provide or you can say that applying the default color to the range starting from c7 to c7 c17 so let's set user form and the range would be on gate range and range here we just need to pass the range so the first field the employee id is in c7 and dot set background and here we just need to provide the color so the default color would be white so six time ff so this is the default color which we have applied on range c7 let's copy this this line of code and replicate this for employee id and the employee employer is available in c9 then gender so this would be for gender then this would be for email id department and then the last would be for address 17. so now we are done with you know assigning the default color that is white to all these input fields now let's start validating so first of all we will validate uh the input made in employer id so let's put comment here validating employee id and here we are just going to apply simple validation that is whether it's blank or not okay so let's start with if and sh user form dot get range and the range is c7 and dot is blank equal to if that is true so here is blank is a function so is blank is equal to true and then let's open the curly braces and here we just need to uh give an alert to user that okay please enter the employee id so as we have already uh declared a variable so let's apply this ui dot alert and the alert would be please enter employee id and then i search user form dot get range and the range is c7 and here we just need to activate so that user can easily identify now we need to set the back color for this so let's copy this line of code here ctrl c and just paste over here ctrl v and here we just need to uh pass like set the color as a red so four times zero in the last one four so this is the color for red and now we need to return false return false okay so this is for validating the employee id let's copy the code again uh this one and replicate this for validating employee name and that would be the range would be c9 if c9 is blank then please enter employee name and we need to activate the employee name here and apply the background color as red for 9. let's copy the code and replicate this for for gender so this one let's okay and let's change the comment so validating gender and the gender is available in c11 and let's change the alert so please select gender from the drop down and the gender is available in c 11 so let's change c 11 it's 11. now we need to validate the email id so let's copy this or maybe we can copy this one the last and replicate this for email id so validating email id okay and here the email id is available in c13 if say 13 is blank then the alert would be please enter a valid email id and we need to activate the range that is c13 and apply the red color on c30 now we need to validate the department so let's copy the code and replicate this for department so validating department and department is available in cell c 15 so if c 15 is blank then uh please alert would be please of select department name from the drop down okay and let's change this c15 and here c15 and return is false now we are pending with one more validation that is for address so let's copy copy this code this is for department and then replicate this for address and here just change the reference that would be c17 and if c17 is blank then please uh enter a valid address it would be c17 c70 so now suppose everything are like there is no pending data so what we need to do we need to return true so we are done with the code for validate entry so now let's uh so let's close this debugger uh we do not require it now so let's uh write in uh one more function to transfer the data from user phone to database sheet so let's uh start with the comment so here we need to put the command function to submit the data to database sheet okay let's create some room so that you can see it clearly let's start declaring the function so first would be function and the function name would be submit or data and then open and close curly braces just create some room so that we can write the code and here so let's uh declare the variable to refer the active uh google sheet so here just put the command declare here variable and set the reference of active google sheet so the variable name is my google sheet and my google sheet would be spreadsheet app dot get active spreadsheet now we need to declare a variable to set the reference of user form worksheet so here where sh user form and that would be my google sheet dot get our sheet by name and the name would be here the name would be a user form now let's declare one more variable that is that would be for worksheet so that would be for database sheet basically so where data sheet and my google sheet dot get sheet by name and that would be database so now we are done with setting the reference of active worksheet and here assigning the name of or referring the user form sheet and database sheet so let's declare a variable uh to create the instance of user interface environment so that we can show the message box that would be alert so here just put the command to create the in sorry the instance of the user interface environment to use the alert feature okay so let's declare a variable so variable and variable would be user interface that is ui and here a spreadsheet app dot gate ui so now we are done with creating the variable for uh user interface uh so let's uh let's prompt a message and get the confirmation from user so that user wants to transfer the data or not so for that we need to store the response in a variable so let's create a variable variable and response and the response would be ui dot alert and here uh let's submit and let's put a question here do you want to submit the data ui dot button said basically we are just going to take two different buttons say dot yes no okay let's put semicolon to break this line okay so let's uh validate whether user has selected yes or no button so for that uh let's put the command checking the user response and here we need to start with if conditions let's if and response is equal to e y dot the button no so if user has selected no no then what we need to do we need to come out from this uh this particular uh function so so return and this is for to exit from this function because we do not need to run the code the rest of the code basically suppose user has selected yes then it will bypass this particular condition and it will run the next line of code so here we just need to validate whether user has you know enter the valid data so as we have already created a function that is for validate entry so let's copy this validate entry and let's start utilizing that so again if ah validate entry is equal to true so validate entry is equal to true and then we just need to uh update update the data like transfer the data from user form to our database sheet so let's so now uh we need to identify the next blank row so to identify the next blank rule uh in database sheet let's declare a variable variable and blank blank rule and here our data sheet dot get last row so what it will do it will provide last row number but we just need to uh move to the next uh no like uh next blank row after the last row so just plus one so let's put comment over here so that you can identify the next blank rule okay now let's uh start right the writing the code to transfer the data so code to to update the data in database sheet so let's start with data datasheet dot gate range and here we just need to provide the row number and column number so as we have already row number that is blank blank row comma and the column would be r1 and dot set value and the set value we need to pass the object the value here so as such user user form dot indeed get range so i think such user form here so let's copy this as a user from here and search using form dot get range and the range would be we are transferring the data of c7 to this particular row or column the cell okay dot get value and this will be for employee id so database data sheet dot git range and then blank row this one blank row comma 1 that is column set value and the value is available in this particular cell so c7 okay let's copy this uh this line of code and replicate this for employee name so here change the comment employee name and just change the reference so column would be 2 and here this particular row number would be c9 and let's copy the code again and replicate it for gender so this is for generator and the general is available in c11 and this would be three let's copy this code and replicate this for email id so email id and the email id is available in c13 and here that would be column four okay let's copy the code and replicate it for department department name and the department is available in c15 so let's change the c15 and the column would be in raw data like the database sheet that would be five let's copy and that would be for address address and address is available in cell c17 and that would be column six so we are done with transferring the inputs provided by users to database sheet uh let's uh so here we need to update two more data set that is submitted by and submitted on so let's so here you can see that submitted on and submitted by so we need to pass date and time in submitted on and in submitted by uh we need to update the email id of users okay so let's go to update the day let's copy this entire line of code and replicate this for submitted on submit it on okay so as a input parameter we just need to pass the date and time over here so let's a new date so that would be a new date and so what it will do it will update the new date to column seven and let's change the formatting so dot set number format and the number format would be number format would be yyyy mm dd space h and then mm okay so we are done with the submitted one let's write the code to update uh submitted by details so let's copy this code and put comment over here submitted by so seven it's that would be eight and here let's remove the input the input parameter here let's remove the command so now here we need to pass the email id for that particular user so let's uh let's pick the email id from session session dot get active user dot get email okay okay so we are done with transferring the data let's uh show a alert so that user will be able to know that data has been transferred so ui dot alert and here we just need to uh like new data saved employee number and employee number would be that is available in this one sh user okay and then again concatenate operator and then so we are done with showing the alert that new data saved for employee id this and the employee id is available in c7 okay now let's write the code to clear uh or you can say that reset the user input so here let's copy the entire line of code so we can copy this code uh this one from validate entry and just go to the submit data function and here just copy and just paste the line of code and we just need to replace the set background color with clear function so what it will do it will clear the data for which are available in c7 so clear just copy this and replace all the set background color because we are clearing uh the data we have already transferred the data from user form to database sheet so now we are done with the required codes let's quickly scan the code so that we can find if the error if any so we have declared the variable and we are utilizing this variable otherwise it would be in dim color uh so data sheet so validate entry that is the function and if a validate entry would be true then we are just identifying the next blank row and on next blank row we are updating the data so state value here so i think we are done with required codes so let's save this and just copy the submit or data function and move to the employee data entry form here and then move to the user form and on user form we just need to assign uh the save data entry uh this particular function the submit our data function on save so just right click on here and just click on uh triple dot which is available on top right and just click on assign script and assign script window just you know enter the name the function name here and then just click on ok so now you can see that we have assigned the key let's uh let's click on save so that we can check whether the validate function is working or not just click on yes script attached to this may need permission so okay so in the same way like excel uh we need to uh enable the macro in the same way in google apps script we need to authorize google worksheet so that google worksheet can utilize the script uh no the google apps function or code just click on continue and select the account name here and just click on advance and just click on go to the employee data entry form okay and this is here you just need to click on allow so now we have enabled the macro you can see and just click on save so do you want to submit the data yes so please enter employer id so now you can see that validate function is working so let's quickly uh enter the data and the address would be new delhi let's click on save and now it's taking confirmation let's click on yes and you can see here new data saved for employee id one two three click on ok so now it has uh you know cleared the form and let's move to the database and you can see that employee name gender employee id department address and submitted on and submitted by so everything has been captured correctly with the help of this user form so let's move to the employee data entry form here and i think there is a spelling mistake submit the data okay press ctrl s to save so now we have done with the first part of the tutorial where we covered how to create uh the google sheet and how to like uh we designed the user interface buttons dropdowns and input fields and again we write the code to validate and transfer the data okay so thank you for watching ah let's move on to the next part of the tutorial uh please like share subscribe and comment have a great

Channel: TheDataLabs

Views: 176,653

Rating: 4.8986177 out of 5

Keywords: Employee Data Entry Form in Google Sheets and Google Apps Script, Developing Data Entry Application in Google Apps Script, Data Entry form in Google Sheets with CRUD features, Google Sheet Automation, Google Apps Script

Id: Q-Io0Y59nYA

Length: 51min 0sec (3060 seconds)

Published: Wed Mar 24 2021