Data entry is one of the important steps in the data analysis process. It is important that the data collected is correctly entered into the electronic format. A good data entry form can reduce the number of human errors added during the data entry. A correctly designed data entry form can speed up data entry and boost productivity. By reducing the errors in data entry, you can get more accurate results. This tutorial will show you how to submit an HTML form to Google Sheets for easy data entry.
Table of Contents
Benefits of having an online data entry form
The data entry forms are even helpful when they are available online. With online forms, you can allow your data entry operators to work from home. You can implement tracking methods to monitor their performance. So, you can do the data entry work more easily and efficiently with online forms.
How to Submit an HTML form to Google Sheets using Google Apps Script’s HTML Service
Google Forms is the most popular online survey tool, which is available for free. However, when it comes to mass data entry works, it is not that supportive. Instead of using Google Forms, you can build your own data entry form with Google HTML Service. With HTML service, you can have your own design, input fields, and validations and save your data to Google Sheet. And also you can embed your form in Google Sites and build your own web app.
This post will show you how you can create the following simple data entry form with Google Apps Script and submit the data to Google Sheets.
Step 01. Create a new Google Sheet on your Google Drive
Create a new Google Sheet and add column labels as shown in the below image (we do not use these column names in the program, it is only to identify the data stored underneath)
Step 02. Creating the HTML form
Open the script editor from Tools → Script editor in your Google Sheet.
Then, replace the code in the Code.gs file with the following code.
function doGet(request) {
return HtmlService.createTemplateFromFile('Index').evaluate();
}Next, create a new HTML file in the script editor from, File → New → HTML file. name it as “Index“. Then replace the auto-generated code with the following.
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-6">
<form id="myForm" onsubmit="handleFormSubmit(this)">
<p class="h4 mb-4 text-center">Contact Details</p>
<div class="form-row">
<div class="form-group col-md-6">
<label for="first_name">First Name</label>
<input type="text" class="form-control" id="first_name" name="first_name" placeholder="First Name">
</div>
<div class="form-group col-md-6">
<label for="last_name">Last Name</label>
<input type="text" class="form-control" id="last_name" name="last_name" placeholder="Last Name">
</div>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<p>Gender</p>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="gender" id="male" value="male">
<label class="form-check-label" for="male">Male</label>
</div>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="gender" id="female" value="female">
<label class="form-check-label" for="female">Female</label>
</div>
</div>
<div class="form-group col-md-6">
<label for="dateOfBirth">Date of Birth</label>
<input type="date" class="form-control" id="dateOfBirth" name="dateOfBirth">
</div>
</div>
<div class="form-group">
<label for="email">Email</label>
<input type="email" class="form-control" id="email" name="email" placeholder="Email">
</div>
<div class="form-group">
<label for="phone">Phone Number</label>
<input type="tel" class="form-control" id="phone" name="phone" placeholder="Phone Number">
</div>
<button type="submit" class="btn btn-primary btn-block">Submit</button>
</form>
<div id="output"></div>
</div>
</div>
</div>
</body>
</html>Now the HTML form is ready, and you can deploy it as a web app by going to Publish → Deploy as a web app… Click the deploy button on the dialog box and copy the link in the next dialog box. Paste the link in the browser, then you can see the form we created.
To add styles to the form, I have loaded Bootstrap CSS via CDN (You can see it in line 5 of the above code).
Step 03. Sending Form Data to Google Sheet
Using google.script.run asynchronous client-side JavaScript API, we can call server-side Apps Script functions from HTML-service pages.
The following JavaScript calls the server-side function processForm once you click the submit button. To add this JavaScript, create a new HTML file and name it ass JavaScript. Then replace the auto-generated code with the following.
<script>
// Prevent forms from submitting.
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener('load', preventFormSubmit);
function handleFormSubmit(formObject) {
google.script.run.processForm(formObject);
document.getElementById("myForm").reset();
}
</script>Then add the following code just before the </head> tag of the Index.html file. This code calls the server-side function include() and includes the above JavaScript in the head section of the Index.html file.
<?!= include('JavaScript'); ?>The function preventFormSubmit() in the above JavaScript code change the default form behavior and it prevents the form from redirecting to an inaccurate URL. Once you click the submit button, the above JavaScript calls the server-side function handleFormSubmit() with the form data.
Then add the following two server-side functions to the Code.gs file we created at the beginning and replace the URL with your Google Sheet URL.
/* @Include JavaScript and CSS Files */
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
/* @Process Form */
function processForm(formObject) {
var url = "https://docs.google.com/spreadsheets/d/1esdBN1frPQoo-rKi3-cg7-Zk4H9ZwWkonU7glN5p-so/edit#gid=0";
var ss = SpreadsheetApp.openByUrl(url);
var ws = ss.getSheetByName("Data");
ws.appendRow([formObject.first_name,
formObject.last_name,
formObject.gender,
formObject.dateOfBirth,
formObject.email,
formObject.phone]);
}** Important! Replace the URL (line 9) with the URL of your spreadsheet.
Your final code arrangement should look like the one below.
You can see the live form and the datasheet from the following links. Datasheet itself has the code. You can see the code by going to Tools > Script editor.
The Final Code
Step 04. Deploying a script as a web app
Now you can deploy the above code as a web app by going to Publish → Deploy as a web app… Then select New for the project version and click update. Copy the Current web app URL in the next dialog box and click OK. Paste the copied URL in the browser to get the form. You can read more about Deploying a script as a web app from this Google Apps Script Guide.
The following video explains how to copy the above Google Sheets to your drive and to deploy the Apps Script as a web app.
What Next?
In the above code, I have not added form validations. In order to improve the data quality, you need to add data validation for your critical fields. You can add form validation either from the server-side or client-side.
You can also embed this form on a Google Site. Read more about Embedding your web app in Google Sites from this Google Apps Script guide.
If you are looking for a way to create a form for Google Sheets itself, you may read Creating Forms in Google Sheets – Sidebar & Modal Dialog forms.
If you are looking for a more advanced form with all the CRUD operations you may read How to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets.
Great resource! Thanks. How can I display a confirmation message after the form is submitted?
Thanks.. It;s very helpful for me
Great tutorial. if you can do this with login form then that would be really cool…
Your data entry form with google html works on Desktop/laptop but when trying to run using a mobile device (android) submit button does not work. Any suggestion on how to solve this issue?
+ 1
Hi, the tutorial worked out for the given code. thanks a lot for that.
I customized the form to add some fields. I put up a google autocomplete api in the field and it works when I run the code in Index.html in jsfiddle but when I try to run it through the script’s url, it always errors out.
Can you give any pointers for resolving that?
Now that is where the wizardry begins. That gives me a very good start, thank you, really helpful.
Hey thanks for the great tutorial. It all worked out for me except Google places API is not working in the demo run, so one of my fields I am not able to input. But when I run the same contents of the Index.html on jsfiddle I am getting the google places API working perfectly.
Any thoughts?
I would also love to learn how to add some sort of action when the form is submitted. Something that indicates it was successful and they don’t need to submit it again, which fills the sheet with unnecessary extra data. Any tips on how to accomplish this? Thank you!
This is a great tool. I have been experimenting with it during the summer and hope to make use of it once school resumes.
One question: is there a way to add an action when the form is submitted? Like open a new page? I don’t see the usual “action” coding in this format. Thanks!
THATS WORK PERFECTLY IN MINE! THANK YOU! 🙂
Wonder if I could upload image from Google Drive and the sheets show only the link.
Hi do you have a file which it will send Email to the person submitting the form? and a copy to the data owner??
Absolutely brilliant ! Thnak you so much
Cristal clear to understand even for a foreigner
Hi Sir I am a teacher and I want to make an application using google scripts so my students Result data spreadsheet with Stud Roll , Class , Birth date , stud name , and subject – wise Marks . Can i Use the Above to Create a Search For Exam Marks Based on Stud Roll , Class , & Birth date ?
if yes further can i merge this search with an html page?
Yes, you can do both.
You may also see the post “How to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets” In this method, I am using a search function
getRecordById(id)to get record for populating the form when you click the update button. You can modify this function to match your requirement.In addition to that, you can embed your web app in Google Sites and make a complete web app.
You are the BEST!
Hi,
Great tutorial. thank you.
How do I embed the form in to a HTML website
Dhanu
Hello! Sir,
Is there a way to call back data from that Google Sheets then we can edit existing data and save/update a new information?
Thank you very much.
Hi Anuar,
This code is written only to create new record in Google Sheets. If you want to create a web app with all Create, Read, Update, and Delete (CRUD) operations go to the following post.
How to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets
Thanks
I am trying to embed this on my google site and I am getting this error “can’t embed due to provider site permissions” ,changing the permissions when I publish does not help
excellent code very well explained and very simple. Congratulations
Hi Admin,
I’m not a programmer but I look like one of the best scripts I’ve found on the web. It has a clean code.
Of course, a base on which to develop, each according to their needs.
In this regard, I wanted to ask you if you can see an example of checking an input data and sending an error message.
Thank you