How to create a Data Entry form in Google Sheets

When it comes to mass data entry works, it is very essential to use data entry forms in order to improve accuracy and speed. If you use google sheets there are are many advantages compared to stand-alone data entry forms like in MS Access. The default form option comes with the Google sheet (Google Form) is not suitable for mass data entry works. Instead of that, you can use Google Apps-Scripts to build a data entry form in Google Sheet itself or build web apps.

In this post, I will show you how to create a data entry form in Google Sheet itself to use for mass data entry works.

In the previous post, I explained, “How to Create a Dependent Drop-Down List in Google Sheet“. Here I am going to use those two fields as part of the data entry form.

Creating the form interface

You can simply create the form interface by taking the cells as input fields. The following form includes six input fields namely “Region”, “Country”, “Population” “GDP”, “Area”, and “Literacy” in D4, D6, D10, D12, G10, and G12 cells respectively.

You can protect the entire sheet except the input fields to prevent users from editing the other cells.

The save button

Here I am using an image as the save button. (Later I will assign apps script function to this image to copy the data into another sheet once you clicked it).

To create this image go to, Insert > Drawing and then draw a rounded rectangle using the Rounded Rectangle shape tool. Then add your text and add colors using color tools.

Apps script to copy data from the form to another sheet

Once you click the save button, the data in the input field should copy to another sheet. This task can be achieved through Google Apps Scripts.

To access Script Editor, go to Tools > Script editor. Give a name to your project. Then copy the following script to the script editor and save.

Rename your sheet which includes the form as “Form” and another sheet as “Data”. We are going to copy the data in the Form to this “Data” sheet.

function submitData() {
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Form"); //Form Sheet
  var datasheet = ss.getSheetByName("Data"); //Data Sheet
  
  //Input Values
  var values = [[formSS.getRange("D4").getValue(),
                 formSS.getRange("D6").getValue(),
                 formSS.getRange("D10").getValue(),
                 formSS.getRange("G10").getValue(),
                 formSS.getRange("D12").getValue(),
                 formSS.getRange("G12").getValue()]];
  
  datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 6).setValues(values);
}

function submitData() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var formSS = ss.getSheetByName("Form"); //Form Sheet

var datasheet = ss.getSheetByName("Data"); //Data Sheet

//Input Values

var values = [[formSS.getRange("D4").getValue(),

formSS.getRange("D6").getValue(),

formSS.getRange("D10").getValue(),

formSS.getRange("G10").getValue(),

formSS.getRange("D12").getValue(),

formSS.getRange("G12").getValue()]];

datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 6).setValues(values);

}

Assign Apps Script to save button

Now you can assign the function submitData() to the save button (actually the image). To do that, click on the image and then click the menu icon in the top right corner of the image. Then select the “Assign script” option. In the text box type your function name, submitData and click OK.

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

Make a copy of the above google sheet from here.

What next?

The above code does not clear the input fields after submitting the data. You can use clear() function to clear the required fields after submitting the data to the “Data” sheet.

The data validation part is another essential part in data entry forms. You can check the values of the field before they copying to the “Data” sheet using conditional statements. Then you can provide meaningful error messages to the user using Browser.msgBox("Error message!")function.

Even though this method is better for mass data entry works than Google Forms, there are a number of other issues. You have to grant edit permission to the user to the “Data” in order to copy the form data to it. Therefore some errors can happen during the data entry. The form is also not much user-friendly.

In order to overcome those issues, you can build your own web app using Google Apps Script HTML Service.

If you wish to create a more advanced data entry form you may read; How to Create Data Entry Form with Google HTML Service and Submit Data to Google Sheets.