Create form using HTML,CSS
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>HTML form submit to Google Sheet</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container text-center">
<br>
<h1>HTML form submit to Google Sheet</h1>
<br>
<div class="card mx-auto" style="width: 18rem;">
<div class="card-body">
<form name="submit-to-google-sheet">
<div class="mb-3">
<input name="fullname" type="text" class="form-control" placeholder="Fullname" required>
</div>
<div class="mb-3">
<input name="email" type="email" class="form-control" placeholder="Email" required>
</div>
<button type="submit" class="btn btn-primary">Send</button>
</form>
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
Paste in sheet
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()
}
}
Choose which function to run: Select “doPost“. Since we are submitting our HTML form via the POST HTTP method.
Choose which deployment should run: Select “Head“.
Select event source: Select “From spreadsheet“.
Select event type: Select “On form submit“.
Failure notification settings: Select which one you prefer. We recommend the “Notify me Daily” option.
JS Code
<script>
const scriptURL = 'Your URL will be here'
const form = document.forms['submit-to-google-sheet']
form.addEventListener('submit', e => {
e.preventDefault()
fetch(scriptURL, { method: 'POST', body: new FormData(form) })
.then(response => console.log('Success!', response))
.catch(error => console.error('Error!', error.message))
})
</script>
1 Comments
Nice post
ReplyDelete