Most of the time you are designing static websites. But almost all of them have some components like forms, comments, where you want to collect the user responses. Setting up a dedicated server for backend and database is a good option, but there is a cost overhead as well. Thankfully, you can set up this entire system using a serverless architecture.
In this blog, you will learn to use amazing Google Apps Scripts as backend and Google Spreadsheets for data persistence to collect the form responses from your static website. This approach can help you set up forms on Github Pages, Netlify, or any other hosting provider. As a bonus, you will also add a webhook to notify your Leads team on Slack whenever a new form is filled.
Contents
- Creating a Google Spreadsheet
- Creating a Slack Bot
- Creating a Google Apps Script Project
- Deploying a Google Apps Script Project
- Setting up a HTML Form
- Setting up Javascript
- Results
Creating a Google Spreadsheet
- Create a new Google Spreadsheet and name the sheet as Sheet1.
- Add the following fields in the top row of your spreadsheet. Make sure you name them correctly because you will be using these names in your HTML form.
Creating a Slack Bot
To notify your Leads team on the Slack, you need to create a Slack bot.
- Go to https://api.slack.com/apps and click
Create New App. - Give your app a name and choose your Development Workspace from the dropdown.
- Once you have created an app, you need to turn on the Incoming Webhook feature and create a new webhook URL.
- Create a new webhook by clicking Add New Webhook to Workspace and choose the channel you want the notifications to be posted in. Your webhook URL should look like this
https://hooks.slack.com/services/T0160Uxxxxx/B0187Nxxxxx/4AZixxswHVxxxxxxxxxxxxxx. If you have access to a terminal, you can test the webhook as well by sending a POST request using cURL.
curl -X POST -H 'Content-type: application/json' --data '{'text':'Hello, World!'}' https://hooks.slack.com/services/T0160Uxxxxx/B0187Nxxxxx/4AZixxswHVxxxxxxxxxxxxxx
Creating a Google Apps Script Project
Now comes the most important and interesting part of the project. Google Apps Script is written in Javascript. So even if you have basic Javascript knowledge, setting up Google Apps will be a breeze for you.
- Create a new project at https://script.google.com/home.
- Create a new script file from File > New > Script, name it as Form.gs add the following code to it:
// new property service
const SCRIPT_PROP = PropertiesService.getScriptProperties()
function doGet(e) {
return handleResponse(e)
}
function handleResponse(e) {
// this prevents concurrent access overwritting data
// you want a public lock, one that locks for all invocations
const lock = LockService.getPublicLock()
lock.waitLock(30000) // wait 30 seconds before conceding defeat
try {
// next set where you write the data - you could write to multiple/alternate destinations
const doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty('key'))
const sheet = doc.getSheetByName(SHEET_NAME)
const headRow = 1
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
const nextRow = sheet.getLastRow() + 1 // get next row
const row = []
// loop through the header columns
for (i in headers) {
switch (headers[i]) {
case 'timestamp':
row.push(new Date())
break
default:
const str = e.parameter[headers[i]]
row.push(str.trim().substring(0, CHARACTER_LIMIT))
break
}
}
// add data to the spreadsheet
sheet.getRange(nextRow, 1, 1, row.length).setValues([row])
// send thanks email to customer
const emailStatus = notifyCustomer(row)
// send notification to slack
postToSlack(row, emailStatus)
// return json success results
return ContentService
.createTextOutput(JSON.stringify({'result': 'success'}))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
// if error then log it and return response
Logger.log(e)
return ContentService
.createTextOutput(JSON.stringify({'result': 'error'}))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
// release lock
lock.releaseLock()
}
}
function setup() {
const doc = SpreadsheetApp.getActiveSpreadsheet()
SCRIPT_PROP.setProperty('key', doc.getId())
}
Don’t forget to run the setup function. It is important to connect your project with the Google Spreadsheet and gain the right permissions.
- Create a new script file from File > New > Script and name it as Email.gs. In this file, you will write the code that sends an email back to the customer on your behalf.
- Add the following code to this script file:
function notifyCustomer(data) {
const name = data[1]
const message = 'Hi' + name + '. Your response has been received. you will get in touch with you shortly.'
// check if you can send an email
if (MailApp.getRemainingDailyQuota() > 0) {
const email = data[2]
// send the email on your behalf
MailApp.sendEmail({
to: email,
subject: 'Thanks for contacting RavSam Web Solutions.',
body: message
})
return true
}
}
- you will again create a new script file from File > New > Script and name it as Slack.gs
- In this file, you will write the code that notifies your Leads team on the form submission.
- Add the following code to this script file:
function postToSlack(data, emailSent) {
const name = data[1]
const email = data[2]
const phone = data[3]
const service = data[4]
const notes = data[5]
// check if email was sent
if (emailSent) const emailStatus = 'Email Sent'
else const emailStatus = 'Email Not Sent'
// create a message format
const payload = {
'attachments': [{
'text': 'Lead Details',
'fallback': 'New Customer Lead has been received',
'pretext': 'New Customer Lead has been received',
'fields': [
{
'title': 'Full Name',
'value': name,
'short': true
},
{
'title': 'Phone',
'value': '<tel:' + phone + '|' + phone + '>',
'short': true
},
{
'title': 'Service',
'value': service,
'short': true
}
{
'title': 'Email',
'value': emailStatus + ' to <mailto:' + email + '|' + email + '>',
'short': false
},
{
'title': 'Notes',
'value': notes,
'short': false
},
],
'mrkdwn_in': ['text', 'fields'],
'footer': 'Developed by <https://www.ravsam.in|RavSam Web Solutions>',
}]
}
// prepare the data to be sent with POST request
const options = {
'method' : 'post',
'contentType' : 'application/json',
'payload' : JSON.stringify(payload)
}
// send a post request to your webhook URL
return UrlFetchApp.fetch(webhookUrl, options)
}
- Finally, create a script file from File > New > Script and name it as Variables.gs to store your constant variables. In this file, you will store your constant variables that are referenced in the project.
- Add the following code to this script file:
// enter sheet name where data is to be written below
const SHEET_NAME = 'Sheet1'
// set a max character limit for each form field
const CHARACTER_LIMIT = 1000
// slack bot weebhook URL
const webhookUrl = 'https://hooks.slack.com/services/T0160Uxxxxx/B0187Nxxxxx/4AZixxswHVxxxxxxxxxxxxxx'
So your project is ready, but there is still one last thing to do. You need to deploy your project as a Web App so that you can access it over the Internet.
Deploying a Google Apps Script Project
At this point, you are done with code and now is the time to deploy your project as a Web App.
- Visit Publish > Deploy as Web App...
- Make sure you set the Who has access to the app: to Anyone, even anonymous. This is important so that you can make an unauthorized call to your Web App.
- Finally, deploy the web app and copy the web app’s URL. The URL should look like this
https://script.google.com/macros/s/AKfycbxSF9Y4V4qmZLxUbcaMB0Xhmjwqxxxxxxxxxxxxxxxxxxxxxxx/exec
Setting up a HTML Form
On your static website, add the following Bootstrap form:
<form id="contact-form" class="needs-validation" role="form" novalidate>
<div class="row">
<div class="col-md-6">
<div class="form-group">
<input type="text" name="name" class="form-control" placeholder="Full Name" required>
</div>
</div>
<div class="col-md-6">
<div class="form-group">
<input type="email" name="email" class="form-control" placeholder="Email" required>
</div>
</div>
<div class="col-md-6">
<div class="form-group">
<input type="tel" name="phone" class="form-control" placeholder="Mobile No." required>
</div>
</div>
<div class="col-md-6">
<div class="form-group">
<input type="text" name="service" class="form-control" placeholder="Service" required>
</div>
</div>
<div class="col-12">
<div class="form-group">
<textarea class="form-control rounded" rows="8" name="notes" placeholder="Any Notes" required></textarea>
</div>
</div>
<div class="col-12 mt-3">
<button class="btn btn-primary" type="submit" name="submit">Submit request -></button>
</div>
</div>
</form>
You need to make sure that the form fields’ names are same as headers in the Google Spreadsheet.
Setting up Javascript
Finally, you need to add some Javascript to make AJAX call to the Google Apps Script:
<script src="/assets/jquery/dist/jquery.min.js"></script>
<script src="/assets/popper.js/dist/umd/popper.min.js"></script>
<script src="/assets/bootstrap/dist/js/bootstrap.min.js"></script>
<script>
// for validating the forms
(function () {
'use strict';
window.addEventListener(
'load', function () {
const formObject = $('#contact-form');
const form = formObject[0];
if (form != undefined) {
form.addEventListener(
'submit',
function (event) {
const submitBtn = $('button[name="submit"]')[0];
submitBtn.disabled = true;
submitBtn.innerHTML = 'Submitting request...';
if (form.checkValidity() === false) {
submitBtn.disabled = false;
submitBtn.innerHTML = 'Submit request ->';
event.preventDefault();
event.stopPropagation();
}
else {
const url = 'https://script.google.com/macros/s/AKfycbxSF9Y4V4qmZLxUbcaMB0Xhmjwqxxxxxxxxxxxxxxxxxxxxxxx/exec';
const redirectSuccessUrl = '/thanks/';
const redirectFailedUrl = '/failed/';
const xhr = $.ajax({
url: url,
method: 'GET',
dataType: 'json',
data: formObject.serialize(),
success: function (data) {
submitBtn.disabled = false;
submitBtn.innerHTML = 'Submit request ->';
$(location).attr('href', redirectSuccessUrl);
},
error: function (data) {
submitBtn.disabled = false;
submitBtn.innerHTML = 'Submit request ->';
$(location).attr('href', redirectFailedUrl);
},
});
event.preventDefault();
event.stopPropagation();
}
form.classList.add('was-validated');
},
false
);
}
},
false
);
})();
</script>
If the form submission is successful, your customer will be redirected to the Thanks page. However, if anything goes wrong, your customer will be redirected to the Failed page.
Results
Reload your website to reflect the changes you made. Fill the form by adding all the required details and submit the form.
Hurray! You have received a notification sent by your Customer Leads bot.
Next, check your Google Spreadsheet as well and see whether the form response was recorded or not. You can see in the screenshot below that the form response has been successfully stored in the spreadsheet.
Using this workflow, you can get in touch with your customers as soon as possible and convert the leads into happy clients. Moreover, there is no need to set up servers and databases for collecting form responses on your website. You can use the same approach to collect comments on your blog posts as well.
