How to create an Automated Data Entry form in Google Sheets and Apps Script?
Google provides a default data entry form that is a Google Form. It can be utilized for a basic data entry job. When it comes to complex and large scale of data entry works, it is always preferable to develop an Automated Data Entry Form to make the task user friendly and improve accuracy & speed.
Google sheet has many advantages over data entry form in MS Excel, MS Access and other applications installed on your local machine. It allows you to use most advanced spreadsheet application without having installed it on your computer.
As Google Sheet is available on cloud, you can easily access it from anywhere and anytime without any limitation. You can share and collaborate with your colleagues, friends and others. In addition to all these, Google Apps Script allows you to add advanced features in existing application and automate all the repetitive task and data entry job.
In this tutorial, we will learn how to create an automated data entry form in Google Sheet with the help of Apps Script.
Please follow the below steps to develop an Automated Data Entry Form in Google Sheet and Apps Script
- Login to Google Drive with your existing Google ID (1) or create a new Google Account (3) and then login to Google Drive.
2. Click on New and then click on Folder to create a new folder for our project.
3. In new folder window, just enter ‘Google Sheet Automation‘ and then click on Okay.
4. Now open the Google Sheet Automation folder to create Google Sheet in it.
5. Right click in Google Sheet Automation folder and then select Google Sheets -> Blank spreadsheet from the pop-up menu.
Once you will click on Blank spreadsheet, it will create and open blank spreadsheet in new tab of the browser.
6. Rename the spreadsheet with ‘Employee Data Entry Form’.
7. Create and rename three worksheets in the newly created spreadsheet as ‘User Form‘, ‘Database‘ and ‘Support Sheet‘ .
We will utilize ‘User Form’ sheet to create a data entry form, ‘Database’ sheet to store the data transferred from data entry form and ‘Support Sheet’ to hold the list of departments.
8. Let’s move to the Department sheet and create a list of department in Column A.
9. Let’s jump to the Database sheet and create the required column headers in row 1 starting from Column A to H e.g. Emp ID, Emp Name, Gender, Email ID, Department, Address, Submitted On and Submitted By.
10. Let’s move to the ‘User Form’ sheet and start designing the Employee Data Entry Form with required labels, input fields and buttons.
Here, we have one field to search the data and six input fields are for data entry job. Emp ID (C4) is input field to search the record for a particular employee with Emp ID.
Employee ID (cell C7), Employee Name (cell C9), Gender ( cell C11), Email ID (cell C13), Department (cell C15) and Address ( cell C17) are the main input fields which will be updated by user to transfer the data from this from the Database sheet. In these input fields, we will utilize Gender and Department as a drop-down.
11. To create a drop-down for Gender, just click on Data menu and then select Data Validation.
12. In Data Validation window, select ‘List of items’ from the drop-down of Criteria and enter ‘Female,Male,Other’ in text box. Tick the ‘Show dropdown list in cell’ and ‘Show validation help text:’. Enter the help text in given text box as ‘Please select Gender from the drop-down.’ Now click on Save to create the drop-down for Gender.
13. Repeat the same step to create the drop-down for Department. For Department drop-down, we will utilize the list available in Support Data sheet.
Select ‘List from a range’ from the drop-down of Criteria and give the reference of list items available in ‘Support Data’ sheet.
In this form, we have 5 different buttons e.g. Search, Save, Modify, Delete and Clear to perform the relevant actions as per the given name.
14. To create a button, just click on Insert menu and then click on Drawing.
15. In Drawing window, just draw a rounded rectangle and fill with desired color and give the caption as Search. After creating the button, click on Save and Close to insert the button on sheet.
16. Follow the steps 14 and 15 to create Save, Modify, Delete and Clear buttons.
Now, we have done with designing the form, creating drop-downs and inserting the required buttons. Let’s move to the code window.
17. To open the Apps Script window, just click on Tools menu and then click on Script Editors.
Once you click on Script editor in Tools menu, it will open the Apps Script window for Data Entry project.
18. Let’s write a function to clear the data entry form. We will assign this function on click event of Clear button available on User Form sheet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
// Function to Clear the User Form function clearForm() { var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm = myGoogleSheet.getSheetByName("User Form"); //declare a variable and set with the User Form worksheet //to create the instance of the user-interface environment to use the alert features var ui = SpreadsheetApp.getUi(); // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also // close the dialog by clicking the close button in its title bar. var response = ui.alert("Reset Confirmation", 'Do you want to reset this form?',ui.ButtonSet.YES_NO); // Checking the user response and proceed with clearing the form if user selects Yes if (response == ui.Button.YES) { shUserForm.getRange("C4").clear(); //Search Field shUserForm.getRange("C7").clear();// Employeey ID shUserForm.getRange("C9").clear(); // Employee Name shUserForm.getRange("C11").clear(); // Gender shUserForm.getRange("C13").clear(); // Email ID shUserForm.getRange("C15").clear(); //Department shUserForm.getRange("C17").clear();//Address //Assigning white as default background color shUserForm.getRange("C4").setBackground('#FFFFFF'); shUserForm.getRange("C7").setBackground('#FFFFFF'); shUserForm.getRange("C9").setBackground('#FFFFFF'); shUserForm.getRange("C11").setBackground('#FFFFFF'); shUserForm.getRange("C13").setBackground('#FFFFFF'); shUserForm.getRange("C15").setBackground('#FFFFFF'); shUserForm.getRange("C17").setBackground('#FFFFFF'); return true ; } } |
19. Create a function to validate the data entry made by users. We will utilize this function while transferring data from User form to Database sheet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
//Declare a function to validate the entry made by user in UserForm function validateEntry(){ var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm = myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet //to create the instance of the user-interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); //Assigning white as default background color shUserForm.getRange("C7").setBackground('#FFFFFF'); shUserForm.getRange("C9").setBackground('#FFFFFF'); shUserForm.getRange("C11").setBackground('#FFFFFF'); shUserForm.getRange("C13").setBackground('#FFFFFF'); shUserForm.getRange("C15").setBackground('#FFFFFF'); shUserForm.getRange("C17").setBackground('#FFFFFF'); //Validating Employee ID if(shUserForm.getRange("C7").isBlank()==true){ ui.alert("Please enter Employee ID."); shUserForm.getRange("C7").activate(); shUserForm.getRange("C7").setBackground('#FF0000'); return false; } //Validating Employee Name else if(shUserForm.getRange("C9").isBlank()==true){ ui.alert("Please enter Employee Name."); shUserForm.getRange("C9").activate(); shUserForm.getRange("C9").setBackground('#FF0000'); return false; } //Validating Gender else if(shUserForm.getRange("C11").isBlank()==true){ ui.alert("Please select Gender from the drop-down."); shUserForm.getRange("C11").activate(); shUserForm.getRange("C11").setBackground('#FF0000'); return false; } //Validating Email ID else if(shUserForm.getRange("C13").isBlank()==true){ ui.alert("Please enter a valid Email ID."); shUserForm.getRange("C13").activate(); shUserForm.getRange("C13").setBackground('#FF0000'); return false; } //Validating Department else if(shUserForm.getRange("C15").isBlank()==true){ ui.alert("Please select Department from the drop-down."); shUserForm.getRange("C15").activate(); shUserForm.getRange("C15").setBackground('#FF0000'); return false; } //Validating Address else if(shUserForm.getRange("C17").isBlank()==true){ ui.alert("Please enter address."); shUserForm.getRange("C17").activate(); shUserForm.getRange("C17").setBackground('#FF0000'); return false; } return true; } |
20. Create a function to transfer the data from User form to Database sheet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
// Function to submit the data to Database sheet function submitData() { var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet //to create the instance of the user-interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also // close the dialog by clicking the close button in its title bar. var response = ui.alert("Submit", 'Do you want to submit the data?',ui.ButtonSet.YES_NO); // Checking the user response and proceed with clearing the form if user selects Yes if (response == ui.Button.NO) {return;//exit from this function } //Validating the entry. If validation is true then proceed with transferring the data to Database sheet if (validateEntry()==true) { var blankRow=datasheet.getLastRow()+1; //identify the next blank row datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("C7").getValue()); //Employee ID datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("C9").getValue()); //Employee Name datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C11").getValue()); //Gender datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C13").getValue()); // Email ID datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("C15").getValue()); //Department datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("C17").getValue());// Address // date function to update the current date and time as submittted on datasheet.getRange(blankRow, 7).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On //get the email address of the person running the script and update as Submitted By datasheet.getRange(blankRow, 8).setValue(Session.getActiveUser().getEmail()); //Submitted By ui.alert(' "New Data Saved - Emp #' + shUserForm.getRange("C7").getValue() +' "'); //Clearnign the data from the Data Entry Form shUserForm.getRange("C7").clear(); shUserForm.getRange("C9").clear(); shUserForm.getRange("C11").clear(); shUserForm.getRange("C13").clear(); shUserForm.getRange("C15").clear(); shUserForm.getRange("C17").clear(); } } |
21. Write the below function to search a record basis the input provided by user in Cell C4 in User Form sheet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
//Function to Search the record function searchRecord() { var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet var str = shUserForm.getRange("C4").getValue(); var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable var valuesFound=false; //variable to store boolean value for (var i = 0; i < values.length; i++) { var rowValue = values[i]; //declaraing a variable and storing the value //checking the first value of the record is equal to search item if (rowValue[0] == str) { shUserForm.getRange("C7").setValue(rowValue[0]) ; shUserForm.getRange("C9").setValue(rowValue[1]); shUserForm.getRange("C11").setValue(rowValue[2]); shUserForm.getRange("C13").setValue(rowValue[3]); shUserForm.getRange("C15").setValue(rowValue[4]); shUserForm.getRange("C17").setValue(rowValue[5]); return; //come out from the search function } } if(valuesFound==false){ //to create the instance of the user-interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); ui.alert("No record found!"); } } |
22. Create a function to delete the record for the employee id available in cell C4.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
//Function to delete the record function deleteRow() { var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet //to create the instance of the user-interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also // close the dialog by clicking the close button in its title bar. var response = ui.alert("Submit", 'Do you want to delete the record?',ui.ButtonSet.YES_NO); // Checking the user response and proceed with clearing the form if user selects Yes if (response == ui.Button.NO) {return;//exit from this function } var str = shUserForm.getRange("C4").getValue(); var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable var valuesFound=false; //variable to store boolean value to validate whether values found or not for (var i = 0; i < values.length; i++) { var rowValue = values[i]; //declaraing a variable and storing the value //checking the first value of the record is equal to search item if (rowValue[0] == str) { var iRow = i+1; //identify the row number datasheet.deleteRow(iRow) ; //deleting the row //message to confirm the action ui.alert(' "Record deleted for Emp #' + shUserForm.getRange("C4").getValue() +' "'); //Clearing the user form shUserForm.getRange("C4").clear() ; shUserForm.getRange("C7").clear() ; shUserForm.getRange("C9").clear() ; shUserForm.getRange("C11").clear() ; shUserForm.getRange("C13").clear() ; shUserForm.getRange("C15").clear() ; shUserForm.getRange("C17").clear() ; valuesFound=true; return; //come out from the search function } } if(valuesFound==false){ //to create the instance of the user-interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); ui.alert("No record found!"); } } |
23. Write the below function to edit the existing data for employee id provided in cell C4.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
//Function to edit the record function editRecord() { var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet //to create the instance of the user-interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also // close the dialog by clicking the close button in its title bar. var response = ui.alert("Submit", 'Do you want to edit the data?',ui.ButtonSet.YES_NO); // Checking the user response and proceed with clearing the form if user selects Yes if (response == ui.Button.NO) {return;//exit from this function } var str = shUserForm.getRange("C4").getValue(); var values = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable var valuesFound=false; //variable to store boolean value to validate whether values found or not for (var i = 0; i < values.length; i++) { var rowValue = values[i]; //declaraing a variable and storing the value //checking the first value of the record is equal to search item if (rowValue[0] == str) { var iRow = i+1; //identify the row number datasheet.getRange(iRow, 1).setValue(shUserForm.getRange("C7").getValue()); //Employee ID datasheet.getRange(iRow, 2).setValue(shUserForm.getRange("C9").getValue()); //Employee Name datasheet.getRange(iRow, 3).setValue(shUserForm.getRange("C11").getValue()); //Gender datasheet.getRange(iRow, 4).setValue(shUserForm.getRange("C13").getValue()); // Email ID datasheet.getRange(iRow, 5).setValue(shUserForm.getRange("C15").getValue()); //Department datasheet.getRange(iRow, 6).setValue(shUserForm.getRange("C17").getValue());// Address // date function to update the current date and time as submittted on datasheet.getRange(iRow, 7).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On //get the email address of the person running the script and update as Submitted By datasheet.getRange(iRow, 8).setValue(Session.getActiveUser().getEmail()); //Submitted By ui.alert(' "Data updated for - Emp #' + shUserForm.getRange("C7").getValue() +' "'); //Clearnign the data from the Data Entry Form shUserForm.getRange("C4").clear(); shUserForm.getRange("C7").clear(); shUserForm.getRange("C9").clear(); shUserForm.getRange("C11").clear(); shUserForm.getRange("C13").clear(); shUserForm.getRange("C15").clear(); shUserForm.getRange("C17").clear(); valuesFound=true; return; //come out from the search function } } if(valuesFound==false){ //to create the instance of the user-interface environment to use the messagebox features var ui = SpreadsheetApp.getUi(); ui.alert("No record found!"); } } |
Now, we have done with writing the functions which are required to Reset the form, Validate the entries made by users, transfer the data from User form to Database sheet, edit and delete the existing records. Let’s assign all these function to the respective buttons available in user form sheet.
24. Let’s right click on Search button then click on ellipsis (triple dots) available on the right side of button and then click on ‘Assign script’
25. In ‘Assign script’ window, just enter the function name and then click on OK button.
26. Follow the steps 24 and 26 to assign the Apps Script functions (e.g. submitData, editRecord, deleteRow and clearForm) to the respective buttons (e.g. Save, Modify, Delete and Clear).
So, we have done with developing an automated data entry form in Google Sheet with the help of Apps Script.
Please watch the step by step tutorial on YouTube.
Please watch the second part of the tutorial (Search Function).
You can follow the below like to open the User form and see the coding.
Link of Form (editable)
https://docs.google.com/spreadsheets/d/1UboTdtkC_rnwhyY29b01ML2_VEK2BZ2kMpU9VugHcW8/edit?usp=sharing
Link of Form (view only)
https://docs.google.com/spreadsheets/d/1-w3J6DWVs7kbyo587wW2sH2VUsaHvWFrDCMQ6kKP4XU/edit?usp=sharing
Please share your feedback and question in comment section. Thanks!
