When it comes to mass data entry works, it is essential to use data entry forms to improve accuracy and speed. This tutorial will show you how to create a data entry form in Google Sheets. With Google Sheets, it has many advantages over data entry forms like MS Access installed on your local computer.
The default form option that 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 Sheets or to build web apps.
In this post, I will show you how to create a data entry form in Google Sheets itself to use for mass data entry works.
In the previous post, I explained, “How to Create a Dependent Drop-Down List in Google Sheets“. Here I am going to use those two fields as part of the data entry form.
Table of Contents
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 Extensions > Apps Script. Give a name to your project. Then copy the following script to the script editor and save it.
Rename your sheet with the form as “Form” and another sheet as “Data“. We are going to copy the data in the Form to this “Data” sheet.
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 out the form and copy the data to the “Data” sheet by clicking the save button.
You can make a copy of the Google Sheets with the above form and the script from the following link to your Google Drive. If you are new to Google Apps Script, copying and modifying the Google Sheet will be easy for you.
Make a copy of the above Google Sheet
Go to Extensions > Apps Script to view the code
What next?
The above 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.
The data validation part is another essential part of data entry forms. You can check the values of the field before they copy 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 for Google Sheets you may read our other tutorial on web apps.
Wrapping Up
In this tutorial, I showed you the easiest methods that you can use to create a data entry form in Google Sheets. However, as mentioned above, there are some disadvantages to using this method. Some of these can be overcome by creating Sidebar & Modal Dialog forms in Google Sheets.
can i have a drop down list in the data entry. to tell whether to save all the entries in data(sheet) or data2(sheet2). Its like having 2 datasheet.thanks a lot inadvance
Assuming your sheet name dropdown is in the “G4” cell.
function submitData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formSS = ss.getSheetByName("Form"); //Form Sheet
var datasheet = ss.getSheetByName(formSS.getRange("G4").getValue()); //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);}
Sheet with the modified code. Make a Copy to your drive.
https://docs.google.com/spreadsheets/d/1Y1V3qfSqmhV4q7yZrvvaA0snJ9Ip98u_miGm7j6mfMk/edit?usp=sharing
Hi,
How can you add an alert popup when the data is submitted? Also, It would be of great help if you can show how to use the Clear data option once the form is submitted.
Is it possible with the suggestion made earlier of sending this data to a different spreadsheet that I could have the data associate itself to its corresponding Sheet within the spreadsheet? For example each time ID: 1 is selected in the form all the data entered would go into the Sheet labeled 1 and so on?
Thank so much for your help it has been invaluable
Yes, it is not impossible. However, you can simply grab data from the datasheet to other sheets using Google Sheets QUERY function.
Thank you very much very useful. I am trying to build a form that has some dynamic content. That is users after a service will have to fill up products they have used so these product lines will be an exit from product inventory. It can be from one to ten lines so I need to use some kind of dynamic range. Any idea how this can be done? Thank you very Much
This is great, thank you. I’m trying to figure out a way of including a timestamp every time data is entered. Do you have any suggestions. Many thanks.
You can add
new Date()Example:
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(),//Add a ","
new Date()]]; //Add new Date() function
datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 7).setValues(values); //Increase number of columns by one (Eg. 6 to 7)Is there a way to send data directly onto a different spreadsheet ?
Yes, you can.
Replace line 4 (“dataSheet” variable) with the following.
var ssData = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1AhWYCSWz7ZW5JvGZ9VBCwgSRoc9v4MWXmbYQh0ZvpBc/edit#gid=0"); //Add your Spreadsheet URL herevar datasheet = ssData.getSheetByName("Data"); //Data Sheet
You also need to authorize to run the code in your google account.
i have some question in google sheet, maybe some buddy can help me
https://support.google.com/docs/thread/40520402?hl=en
Is there a way to email the form to another person? We would like one department to receive the data on the form -because it’s clear and concise to read off of, and the other department would keep the data they entered on form for their records.
You need to give “Edit” permission to those who enter the data using this method. If the other department only wants to see the data, you can simply share the sheet with “View” permission.
You can also create an HTML form to do data entry work as explained in the following post.
How to Create Data Entry Form with Google HTML Service and Submit Data to Google Sheets
How do you create a clear script after the data has been submitted?
where do you create the clear function in the script or in a new script?
Works well. But all my items are lined up horizontally. what if I would like several Items with item code and quantity to line up vertically and will return the same customer name, date of purchase order and other several detail. I am making data entry for purchase orders. would appreciate if you could help with the script.
Thank you for this. However, I encounter a problem. After saving the data, it goes to the last row (A1000). How to fix it? Many thanks!
AWESOME ….!! Good Job
can this form be filled in android phone via app/browswer ?
You can, but it is not that user-friendly. You may try How to Create Data Entry Form with Google HTML Service and Submit Data to Google Sheets
See my enhanced solution on YouTube: https://www.youtube.com/watch?v=YZ3Mz6thD2Q
Thank you for your post. Very helpful
Thank you!
Very Helpful, thank you.
Exactly what I was looking for. Thank you!
It’s my pleasure
Very Helpful Content