Submit data from HTML form to Google Sheets and database using PHP and ajax
in this article we will see, How to submit HTML form data to a Google Sheet and database too using PHP and ajax. This is a little bit tricky we have to send data in google Sheets using ajax and on the success function we will call another ajax function to send the request to our PHP page for inserting data into the database in deeper we can also send an email.
We will use below simple steps to complete this challenge.
- Step 01: setup Google Sheets
- Step 02: Design HTML form using bootstrap
- Step 03: create ajax function
- Step 04: create process.php page
Step 01: setup Google Sheets
Note: if you want to know more detail that how we can read our google sheets for inserting data from HTML forms you can go through our previous article Submit data from an HTML form to Google Sheets. or you can just follow this article.
Open your google sheet and you can insert the first row as same as the form field name then go to extension>app script and paste the below code. and save it run it to allow all the access and click on the debug button on the top right corner and then click on new deployment and setup it up for the web app finally you will get a URL just copy it and save it into a safe palace we will use it further.
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()
}
}
Step 02: Design HTML form using bootstrap
Now we have to create an HTML form we will use bootstrap you can also create your own custom HTML form. just copy the below HTML code and paste it into your HTML or PHP page.
<div class="container">
<div class="row justify-content-center">
<div class="col-md-6">
<h3 class="text-center mt-5"> google sheet form</h3>
<form id="frmSubmit" method="POST">
<div class="mb-3">
<label for="Name" class="form-label">Name</label>
<input type="text" class="form-control" id="" name="name" required="">
</div>
<div class="mb-3">
<label for="Email" class="form-label">Email </label>
<input type="email" class="form-control" name="email" required="">
</div>
<div class="mb-3">
<label for="Mobile" class="form-label">Mobile</label>
<input type="tel" class="form-control" id="" name="mobile" required="">
</div>
<div class="mb-3">
<label for="Message" class="form-label">Message</label>
<input type="text" name="message" class="form-control" style="width: 100%; height:200px;">
</div>
<button type="submit" class="btn btn-primary">Submit</button>
<div id="msg"></div>
</form>
</div>
</div>
</div>
Step 03: create ajax function
Now we will create two ajax functions one will use for sending data to google sheets and after a successful response, we will use another ajax for inserting data into the database or mail functionality.
<script>
jQuery('#frmSubmit').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/AKfycbz7Ngeaorh7FXluqbl57f7tuzjyQZmC3earP7P03-QefwTDS0fNrRbXcReVONoMMjJ9hw/exec',
type: 'post',
data: jQuery('#frmSubmit').serialize(),
success: function(result) {
resend();
}
});
});
function resend() {
jQuery.ajax({
url: 'process.php',
type: 'post',
data: jQuery('#frmSubmit').serialize(),
success: function(result) {
jQuery('#frmSubmit')[0].reset();
jQuery('#msg').html(result);
jQuery('#btnSubmit').attr('disabled', false);
//window.location.href='';
}
});
}
</script>
Step 04: create process.php page
finally, we will create a process.php file where we will connect our database and we will insert data just copy and paste the below code and change the database connection.
<?php
$mysqli = new mysqli("localhost", "database_name", "username", "dbpassword");
// Check connection
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli->connect_error;
exit();
} else {
if (isset($_POST['name']) && isset($_POST['email']) && isset($_POST['mobile']) && isset($_POST['message'])) {
$name = htmlspecialchars(trim($_POST['name']));
$email = htmlspecialchars(trim($_POST['email']));
$mobile = htmlspecialchars(trim($_POST['mobile']));
$msg = htmlspecialchars(trim($_POST['message']));
}
$sql = "INSERT INTO contact(name,email,mobile,message) VALUES ('" . addslashes($name) . "', '" . addslashes($email) . "','" . addslashes($mobile) . "', '" . addslashes($msg) . "')";
if (mysqli_query($mysqli, $sql)) {
echo '<div class="alert alert-success alert-dismissible fade show" role="alert">
<strong>Thank you!</strong> You you have sent message successfullly we will get back to you soon...
<button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
</div>';
} else {
echo "something went wrong!.." . $message;
}
// echo "data submit in database";
}
-
caligood
