Use Google Apps Script to collect form responses on a static website and get notified on Slack, all without setting up a server.

Collect form responses using Google Apps Script

Ravgeet Dhillon

Ravgeet Dhillon

Written on Aug 02, 2020 in Development

⏱ 17 min read

Blog banner for Collect form responses using Google Apps Script

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

  1. Create a new Google Spreadsheet and name the sheet as Sheet1.
  2. 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.
Format for Google Spreadsheet for collecting form responses
Google Spreadsheet to collect form responses

Creating a Slack Bot

To notify your Leads team on the Slack, you need to create a Slack bot.

  1. Go to https://api.slack.com/apps and click Create New App.
  2. Give your app a name and choose your Development Workspace from the dropdown.
  3. Once you have created an app, you need to turn on the Incoming Webhook feature and create a new webhook URL.
  4. 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
Setup name of your Slack app and development workspace
Setup name of your Slack app and development workspace

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.

  1. Create a new project at https://script.google.com/home.
  2. 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.

  1. 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.
  2. 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)
}
  1. 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.
  2. 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.

  1. Visit Publish > Deploy as Web App...
  2. 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.
  3. 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
Deploy the Google Apps Script project as a web app
Deploy the Google Apps Script project as a web app

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 -&gt;</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 -&gt;';
                                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 -&gt;';
                                        $(location).attr('href', redirectSuccessUrl);
                                    },
                                    error: function (data) {
                                        submitBtn.disabled = false;
                                        submitBtn.innerHTML = 'Submit request -&gt;';
                                        $(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.

Fill out the sample website form
Fill out the website form

Hurray! You have received a notification sent by your Customer Leads bot.

Notification received in the Slack channel
Notification received in the Slack channel

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.

Form response recorded in Google Spreadsheet
Form response recorded in Google 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.

📫

Loved this post? Join our Newsletter.

We write about React, Vue, Flutter, Strapi, Python and Automation. We don't spam.

Please add a valid email.
By clicking submit button, you agree to our privacy policy.
Thanks for subscribing to our newsletter.
There was some problem while registering your newsletter subscription. Please try again after some time or notify the owners at info@ravsam.in

ABOUT AUTHOR

Ravgeet Dhillon

Ravgeet is a Co-Founder and Developer at RavSam. He helps startups, businesses, open-source organizations with Digital Product Development and Technical Content Writing. He is a fan of Jamstack and likes to work with React, Vue, Flutter, Strapi, Node, Laravel and Python. He loves to play outdoor sports and cycles every day.

Got a project or partnership in mind?

Let's Talk

Contact Us ->