Submit data from an HTML form to Google Sheets
in this article we will see, How to submit simple HTML form data to a Google Sheet directly using only HTML and JavaScript. for this we have to set up our google sheet first. To set up google Sheets we have to open our google Sheets then go to the extension tab and click add script tab, paste the below macro script this script is google own, you can also find it on the google developer site.
let's start, open your google sheet and you can insert the first row as mentioned below screen.
But make sure the first-row column name should be same as our html form input name and there is no bound that how many column we can take multiple column according to our html form input field. in this example, we will take four input, name, email, mobile and message so that we have taken four clumn as mentioned above image.
Now go to the extension tab and click on app script tab like below.
Now paste the below code inside that and save it then click on the run button.
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()
}
}
Make sure we are using bootstrap for design and jquery for the ajax function to be run.
Now just copy and paste the below ajax function and replace their URL with what you have copied from google sheet
<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/AKfycbz9Pc6hWgexMQQ2sBI29JE_m17beoVf0Ox6nFugg7mDMWo_qShFBdWWgcMmpiejAfaq/exec',
type: 'post',
data: jQuery('#frmSubmit').serialize(),
success: function(result) {
jQuery('#frmSubmit')[0].reset();
jQuery('#msg').html("Thank You");
jQuery('#btnSubmit').attr('disabled', false);
}
});
});
</script>
After running the above script you will get a permission modal like the below just allow it.
Now click on the run button, and you will get the permission modal like below just allow it. After giving the script the correct permissions, you should see the following output in the script editor console:
If you are getting the above screen that means your script has the correct permissions to continue to the next step.
Now click on the Deploy tab in the top right corner and click new deployment
You will get the model that must be set for the web app and fill in the detail then click on the next button then you will get the below screen. just copy the URL and save it in a safe place we will use it further.
After copying the URL click on the done button now you have setup successfully your google sheet for receiving data from the HTML form.
Now we will create an HTML form with four inputs, just copy and paste the below code inside 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%;">
</div>
<button type="submit" class="btn btn-primary">Submit</button>
<div id="msg"></div>
</form>
</div>
</div>
</div>
Note: In the above example I have submitted data to google Sheets using ajax. If you have any doubts or getting error just comment below, I will reply to you as soon as possible.
