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.
Very good tutorial. I’ve tried make a form using this. But if i lock the sheet “data”, then the form can not be filled. Google said,”you’re trying to modify protected sheet.”
I want people who can access the form only can view the data sheet. I dont want they edit the data sheet. Any suggestions? Thank you
I setup very similar form to add expenses to end of another worksheet. However, I cannot get the button to work on my smartphone?
Can we submit data on a different spread sheet ?
Any help will be appreciated
Yes, you just need to specify the Spreadsheet ID of the sheet where you want to save the data.
Hay a want to ask, what if I want to place value (setValues(values);) not in last row but in last row that contain certain word (or specific cell)
datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 6).setValues(values);
like I want to put “FINISH” if certain cell contain “GOOD”
Thanks
How can I get the entries to begin at a certain cell in the Data tab? For example, start at A50 instead of A1.
Hi there,
Replace line 14 with the following two lines of code.
var dataCount = datasheet.getRange("A50:A").getValues().filter(String).length;
datasheet.getRange(datasheet.getRange("A50").getRow()+dataCount, 1, 1, 6).setValues(values);
Here dataCount is the number of rows with data just below the A50 cell. I assume that you do not have empty data for column A.
Ref: https://stackoverflow.com/a/17637159/2391195
This all works great. My question is how are you able to write the data in a column vertically instead of filling in just one row? or vise versa.
Great bit of reading and learning right there, thank you so much for that. I am working on something with both multiple rows and columns that i would like to be able to add to a running list for further analysis.
The plan is to accumulate data and add it in monthly, so a single row is not really going to cut it for me in the long run. And i would love not to have to move to the end of a long list and run the risk of editing it by mistake.
So my question is if there is any way to have the form submit how ever many tows there might be on any given month.
I’ve made a tiny example if having something tangible makes the question easier, just 3 rows, but i am looking to be able to add upwards of 30 rows in one go. Is it possible, and if so, can a kind soul direct me towards enlightenment ?
https://docs.google.com/spreadsheets/d/14aKPcnYQQy7LAjLZ1GMO0-viqTf26_7NkwEYuo4M6yI/edit?usp=sharing
Can I get the form to input into a range on the next tab? ignoring everything else outside that range?
I am struggling because I want the form to enter the details into the next sheet with some of the columns after the form entry columns to having code in them already, which then does its stuff on the data entered via the form. However it recognises the formula as data and jumps down to the next available empty rows.
Help please!
Hi,
is there a possibility that if a field gets populated by the user a second dependent field gets automatically populated by the app?
Hello,
Looking at a separate question now which I’m not sure is possible or not.
I have built a similar form to that of the example.
Is there a way to have a separate form to edit the data once it has been inputted?
for example, you have a separate form and load up current information for Bangladesh, however you now want to change the area to 145000.
is there a way to make it update the existing entry?
cheers
Used this code and it has worked perfectly,
i wanted to know if there is a way to make it skip columns and only populate some?
for example, populate the first 3 columns with data, skip 1, and then populate another 2 columns?
Cheers
Hi Charlie,
There are multiple ways to do that. It depends on your requirement.
Assuming that you are going to insert some formula for the skipped column, I suggest the following.
function submitData() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var formSS = ss.getSheetByName("Form"); //Form Sheet var datasheet = ss.getSheetByName("Data"); //Data Sheet //Input Values var values1 = [[formSS.getRange("D4").getValue(), formSS.getRange("D6").getValue(), formSS.getRange("D10").getValue()]]; var values2 = [[formSS.getRange("G10").getValue(), formSS.getRange("D12").getValue(), formSS.getRange("G12").getValue()]]; var lastRow = datasheet.getLastRow(); datasheet.getRange(lastRow+1,1,1,3).setValues(values1); datasheet.getRange(lastRow+1,5,1,3).setValues(values2); }Brilliant, Thanks so much.
Hello, ive just enabled this in my sheets and it works great at getting the data across. however the issue i am having is that it is still skipping the rows as i have formulas in the cells it is skipping.
is there a way to get over this?
kind regards
How can I change {
‘Form”) to any active sheet
How to clear the input bar after the data is submitted??
You can use clear() function for this.
See the modified code below.
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);
//Clear the fields after submitformSS.getRange("D4").clear();
formSS.getRange("D6").clear();
formSS.getRange("D10").clear();
formSS.getRange("G10").clear();
formSS.getRange("D12").clear();
formSS.getRange("G12").clear();
}
Thank you! this is very helpful! do you have script for Search Function? I tried to make a script for Search function but it’s not properly working. i searched by last name and first name. I cannot use last name only because i have entries with the same last name.
This script has worked like a charm but I was wondering if there was a way to instead have it submit a cell value, maybe submit a formula that will calculate on the data sheet. Example, I have:
formSS.getRange(“C3”).getValue(),
C3 has a formula in the cell and I would like to transfer that formula over rather than having it submit the cells value. Any help would be appreiciated.
Thank you for this post. Is it also possible to use this as a search data library from a google sheet. I have a reference Data library sheet with a lot of data and I’m looking for a easy way to find only the relevant data in the sheet with a kind of form (checklist)
can we create login form using g sheets?
thank you worked well. i want to use this form to update data already submitted in the datasheet. for eg updating delivery dates . can someone please help me
For all CRUD operations, please refer the post How to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets
Hi! this is very helpful, is there a way I can get it so that the data is added to next available row rather than the last row, as it is sending the data to row 500 etc at the bottom of my spreadsheet.
Great. Very helpful – simply and in point. Thanks for this article.
Here’s what i added to clear after clicking the submit button. I didn’t study coding and just did it by trial and error.
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(“B6”).getValue(),
formSS.getRange(“B7”).getValue(),
formSS.getRange(“B8”).getValue(),
formSS.getRange(“B9”).getValue(),
formSS.getRange(“B10”).getValue(),
formSS.getRange(“B11”).getValue(),
formSS.getRange(“B12”).getValue()]];
datasheet.getRange(datasheet.getLastRow()+1, 1, 1, 7).setValues(values);
formSS.getRange(“B7”).clear()
formSS.getRange(“B8”).clear()
formSS.getRange(“B9”).clear()
formSS.getRange(“B10”).clear()
formSS.getRange(“B11”).clear()
formSS.getRange(“B12”).clear()
}
Hey J M
You are an absolute legend, I have a question is there a way you can skip the first column or a selected column in the database for this function and anyway i could search different columns instead of the first one? Thank you again!