How to create a Data Entry form in Google Sheets

When it comes to mass data entry works, using data entry forms is crucial for improving accuracy and speed. In this article, we will explore how to create a data entry form in Google Sheets. Unlike MS Access installed on your local computer, Google Sheets offers many advantages for data entry tasks.

The Limitations of Google Forms

The default form option in Google Sheets, known as Google Form, is not suitable for mass data entry works. Instead, you can utilize Google Apps Scripts to build a data entry form directly in Google Sheets or to create web apps.

In this post, we will focus on creating a data entry form within Google Sheets itself, specifically for mass data entry works. We will also draw from the previous post on creating a dependent drop-down list in Google Sheets.

Building the Form Interface

Creating the form interface is simple. We can use cells as input fields. For example, the form includes six input fields: “Region,” “Country,” “Population,” “GDP,” “Area,” and “Literacy,” which are located in cells D4, D6, D10, D12, G10, and G12 respectively.

Figure 01: Data entry form

To prevent users from editing other cells, you can protect the entire sheet except for the input fields.

The “Save” Button

To serve as the save button, you can use an image. Later, we will assign an Apps Script function to this image to copy the data into another sheet when clicked.

To create the save button image, go to Insert > Drawing. Then, use the Rounded Rectangle shape tool to draw a rounded rectangle. Add your text and use the color tools to customize the button.

Using Apps Script to Copy Data

Once the save button is clicked, the data from the input fields should be copied to another sheet. This task can be accomplished with Google Apps Scripts.

To access the Script Editor, go to Extensions > Apps Script. Give your project a name and copy the provided script to the editor. Be sure to rename your sheet with the form as “Form” and the other sheet as “Data.” We will copy the data from the form to the “Data” sheet.

Assigning Apps Script to the Save Button

To assign the submitData() function to the save button (the image), click on the image and then click the menu icon in the top right corner. Select the “Assign script” option. In the text box, type your function name, submitData, and click OK.

Now, by filling out the form and clicking the save button, you can copy the data to the “Data” sheet.

You can make a copy of the Google Sheets including the form and script by following this link.

What’s Next?

The provided code does not clear the input fields after submitting the data. You can use the clear() function to clear the required fields after submitting the data to the “Data” sheet. Additionally, data validation is crucial for data entry forms. You can use conditional statements to check the field values before copying them to the “Data” sheet and provide meaningful error messages to the user using the Browser.msgBox("Error message!") function.

Although this method is better suited for mass data entry works compared to Google Forms, there are still some considerations to be aware of. Granting edit permission to the user for the “Data” sheet is necessary, which can lead to potential errors during data entry. Additionally, the form may not be very user-friendly.

To overcome these challenges, you can build your own web app using Google Apps Script HTML Service.

If you wish to create a more advanced data entry form for Google Sheets, you can read our other tutorial on web apps.

Wrapping Up

In this tutorial, we explored the easiest methods for creating a data entry form in Google Sheets. However, as mentioned, there are some limitations to this approach. By creating Sidebar & Modal Dialog forms in Google Sheets, some of these limitations can be overcome.

For more information and guidance on Google Sheets and other topics, visit Mr Reviews.