Automated Data Entry Form – Google Sheet

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.

Data Entry Form in Google Sheet and Apps Script

Please follow the below steps to develop an Automated Data Entry Form in Google Sheet and Apps Script

  1. Login to Google Drive with your existing Google ID (1) or create a new Google Account (3) and then login to Google Drive.
Login to Google Drive with existing ID or create an account to login

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.

Create a new folder for data entry project

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.

Creating Google Sheet

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’.

Rename the blank spreadsheet

7. Create and rename three worksheets in the newly created spreadsheet as ‘User Form‘, ‘Database‘ and ‘Support Sheet‘ .

Add three worksheets in this spreadsheet

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.

Creating department list

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.

Creating columns in Database sheet

10. Let’s move to the ‘User Form’ sheet and start designing the Employee Data Entry Form with required labels, input fields and buttons.

Data Entry form designing

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.

Creating drop-down fields

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.

Creating drop-down

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.

Creating search button

16. Follow the steps 14 and 15 to create Save, Modify, Delete and Clear buttons.

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.

Tools menu

Once you click on Script editor in Tools menu, it will open the Apps Script window for Data Entry project.

Apps Script Window

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.

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.

20. Create a function to transfer the data from User form to Database sheet.

21. Write the below function to search a record basis the input provided by user in Cell C4 in User Form sheet.

22. Create a function to delete the record for the employee id available in cell C4.

23. Write the below function to edit the existing data for employee id provided in cell C4.

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!

LEAVE A REPLY

Please enter your comment!
Please enter your name here