Sometimes, you need to record Html form’s data into Google sheet so that you can use this data later. I am going to explain how can we do this by using HTML form and google sheet.
HTML Form Code
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, minimum-scale=1.0">
<title>Contact Us</title>
<link rel="shortcut icon" href="images/index.ico" type="image/x-icon">
<!-- CSS: Fontawesome -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<!-- CSS: Bootstrap v-5.3 -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.4.1/dist/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<body>
<!-- space -->
<div class="py-lg-5 py-md-4 py-sm-3 py-2"></div>
<div class="row justify-content-center pt-5">
<div class="col-lg-7 col-md-10">
<h3 class="fw-normal text-center mb-5 border-bottom pb-2">Contact with Our Help Team</h3>
<form class="form_overlay" method="POST" accept-charset="UTF-8" autocomplete="off" name="contact">
<div class="row g-4">
<div class="col-lg-6">
<div class="input_overlay">
<label for="fname" class="lable">Enter your first name</label>
<input type="text" name="first_name" class="form-control" required autocomplete="off">
</div>
</div>
<div class="col-lg-6">
<div class="input_overlay">
<label for="lname" class="lable">Enter your last name</label>
<input type="text" name="last_name" class="form-control" required autocomplete="off">
</div>
</div>
<div class="col-lg-6">
<div class="input_overlay">
<label for="email" class="lable">Enter email address</label>
<input type="text" name="email" class="form-control" required autocomplete="off">
</div>
</div>
<div class="col-lg-6">
<div class="input_overlay">
<label for="phone" class="lable">Phone number</label>
<input type="text" name="phone" class="form-control" required autocomplete="off">
</div>
</div>
<div class="col-12">
<div class="input_overlay">
<label for="subject" class="lable">Subject name</label>
<input type="text" name="subject" class="form-control" required autocomplete="off">
</div>
</div>
<div class="col-12">
<div class="input_overlay textarea">
<label for="desc" class="lable">Description</label>
<textarea name="description" cols="30" rows="4" class="form-control" required autocomplete="off"></textarea>
</div>
</div>
<div class="col-12 text-end">
<button type="submit" class="btn btn-primary mt-3">Submit</button>
</div>
<p id="demo"></p>
</div>
</form>
</div>
</div>
</main>
<script>
const scriptURL = 'https://script.google.com/macros/s/AKfycbyMVQlyqjWagy6MS0C4ICRW6Be5T647IAR1iClnTLaih6qZce3Hclv4_NvN8uhW2EJZzw/exec'
const form = document.forms['contact']
form.addEventListener('submit', e => {
e.preventDefault()
fetch(scriptURL, { method: 'POST', body: new FormData(form)})
.then(response => document.getElementById("demo").innerHTML = "<div class='alert alert-primary' role='alert'><b>Thank You for providing the details, We shall get back to you shortly !</b></div>",contact.reset())
.catch(error => console.error('Error!', error.message))
})
</script>
</body>
</html>
Script for HTML Form
<script>
const scriptURL = 'https://script.google.com/macros/s/AKfycbyMVQlyqjWagy6MS0C4ICRW6Be5T647IAR1iClnTLaih6qZce3Hclv4_NvN8uhW2EJZzw/exec'
const form = document.forms['contact']
form.addEventListener('submit', e => {
e.preventDefault()
fetch(scriptURL, { method: 'POST', body: new FormData(form)})
.then(response => document.getElementById("demo").innerHTML = "<div class='alert alert-primary' role='alert'><b>Thank You for providing the details, We shall get back to you shortly !</b></div>",contact.reset())
.catch(error => console.error('Error!', error.message))
})
</script>
Script for Google Sheet
var sheetName = 'contact'
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()
}
}
