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.
In this post, I will show you how you can create the following simple data entry form with Google Apps Script and submit the data into Google Sheets.
Step 1: Creating the HTML form
The full code for the Contact Form and how to handle the submission is below.
Open up a blank file in the text editor you use for programming, copy and paste the code below and save the file with a [.html] extension. We saved ours as index.html (or any name you love) and also with ajax integration script.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Google Sheet Submit</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="//maxcdn.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" rel="stylesheet">
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="//maxcdn.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js"></script>
<style>
body{
background: #f1f1f1;
}
form {
border: solid #d8d8d8 3px;
border-radius: 4px;
width: 60%;
margin: 60px auto;
background: #ffffff;
padding: 60px 120px 80px 120px;
text-align: center;
}
label {
display: block;
position: relative;
margin: 40px 0px;
}
.label-txt {
position: absolute;
top: -1.6em;
padding: 10px;
font-family: sans-serif;
font-size: .8em;
letter-spacing: 1px;
color: rgb(120,120,120);
transition: ease .3s;
}
.input {
width: 100%;
padding: 10px;
background: transparent;
border: none;
outline: none;
}
.line-box {
position: relative;
width: 100%;
height: 3px;
background: #efefef;
}
.line {
position: absolute;
width: 0%;
height: 2px;
top: 0px;
left: 50%;
transform: translateX(-50%);
background: #00b9b1;
transition: ease .6s;
}
.input:focus + .line-box .line {
width: 100%;
}
.label-active {
top: -3em;
}
button {
display: inline-block;
padding: 12px 24px;
background: rgb(220,220,220);
font-weight: bold;
color: rgb(120,120,120);
border: none;
outline: none;
border-radius: 3px;
cursor: pointer;
transition: ease .3s;
}
button:hover {
background: #00b9b1;
color: #ffffff;
}
#msg{
color:red;
}
</style>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="col-md-10 offset-1">
<form method="post" id="formName">
<label>
<p class="label-txt">ENTER YOUR NAME</p>
<input type="text" class="input" name="name" required>
<div class="line-box">
<div class="line"></div>
</div>
</label>
<label>
<p class="label-txt">ENTER YOUR EMAIL</p>
<input type="text" class="input" name="email" required>
<div class="line-box">
<div class="line"></div>
</div>
</label>
<button type="submit" id="btnSubmit">Submit</button>
<div id="msg"></div>
</form>
</div>
</div>
</div>
</div>
<script>
jQuery('#formName').on('submit',function(e){
e.preventDefault();
jQuery('#msg').html('Please wait...');
jQuery('#btnSubmit').attr('disabled',true);
jQuery.ajax({
url:'https://script.google.com/macros/s/AKfycbzL5c0j3kpnEqKlL3OsIsIlU3uSuLx3Nb1yr6rvThPQH9EukBd9LFfNcq_MA1utbBbcjw/exec',
type:'post',
data:jQuery('#formName').serialize(),
success:function(result){
jQuery('#formName')[0].reset();
jQuery('#msg').html('Thank You');
jQuery('#btnSubmit').attr('disabled',false);
setTimeout(function(){
window.location.href='index.html';
}, 1000);
}
});
});
</script>
</body>
</html>Step 2: Create a new Google Sheet
Create a new Google Sheet and add column labels as shown in the below image, name it new Google Sheet to any you want.
Remember one think Write column headers equal to the name(s) of the <input> tags
Step 3: Create Sheets Apps Script
To do this we need to connect our script to the form by opening the Google Apps Script editor up in the Google Form.
You can do this by clicking the vertical ellipsis in the top-right of the Google Form editor and selecting Script editor find: Extension → Apps Script
Step 4: Crete Code.gs
You can copy and paste this now into your project without change any thing or edit it.
Usually you can change project name (Untitled project) to any you want create name my project the same as my Form or Sheet if it is directly related.
Remove all codes inside Code.gs and paste it into bellow codes
var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
function intialSetup () {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
var sheet = doc.getSheetByName(sheetName)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
var nextRow = sheet.getLastRow() + 1
var newRow = headers.map(function(header) {
return header === 'timestamp' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}Next, go to Run → Run Function → initialSetup to run this function.
- In the Authorization Required dialog.
- click on Review Permissions.
- Not it can Sign in or pick the Google account associated with this projects.
- Click Allow
Step 5: Publish the project as a web app
- Create any Description and Set Project Version to New and put initial version in the input field below.
- Leave Execute the app as: set to Me(your@address.com).
- For Who has access to the app: select Anyone, even anonymous.
- Click Deploy
- In the popup, copy the Current web app URL from the dialog and click ok.
Step 6: Replace web app URL into HTML form (in step 1)
Open the file named index.html. On line find “url” replace <SCRIPT URL> with your script url: you create now
In the above code, I have not added form validations or extra function, create error free code with us, you need to add data validation for your critical fields.
Conclusion
In this tutorial, we looked at creating a custom email responder that is triggered when a Google Form is sent. We looked at how to add triggers to run our code when the form is submitted. Then we made sure all of our permissions and scopes were added so that our code would run. We also had to add the Gmail API advanced service to get our signature block. this above code will work on localhost also, i create with this on my local computer Localhost.
If you’ve liked this tutorial and want to get regular updates on what I am working on next, you can subscribe down below this post.