Effortless Form Handling with Google Apps Script

When running a website, having an way to manage user inquiries and feedback is essential. One common feature many websites implement is a “Contact Us” or “Request Demo” form. This form enables visitors to reach out to you directly. But how do you effectively manage these submissions without needing expensive third-party services or complicated backend systems? The answer: Google Sheets and Google Apps Script!

In this blog post, we’ll walk you through how to use Google Sheets combined with Google Apps Script to handle a “Request Demo” form on your website. The process will allow us to:

• Collect form submissions directly into a Google Sheet.
• Send a confirmation email to the user.
• Notify the website admin about the new submission.

Let’s dive in!

Why Use Google Sheets for Form Submissions?

Google Sheets is an excellent tool for tracking and managing form submissions due to following reasons:

1. Free and Easy to Use: You don’t need to pay for third-party form handling services or complex backend systems.
2. Real-Time Collaboration: Multiple people can view and edit the data in real-time, making collaboration easier.
3. Customisable: You can modify and extend the script as needed to add more functionality.

By combining Google Sheets with Google Apps Script, you can automate many of the tedious tasks associated with handling form submissions.

Steps to Set Up the Contact Us Form Handler

We’ll break down the process into clear steps, starting from setting up Google Sheets, creating a Google Apps Script to handle form submissions, deploying the script as a web app, and integrating it with your website’s form.

Setting Up Google Sheets

First, create a Google Sheet that will store the form submissions:

1. Open Google Sheets and create a new document.
2. Change name of sheet to from sheet1 to form-response
3. In the first row, add headers for the data you want to collect:

• Name
• Email
• Message
• Date

This sheet will automatically store all form submissions in the rows below.

Creating the Google Apps Script

Now, we’ll create the script that processes the form submissions. Google Apps Script is a cloud-based JavaScript platform that allows you to integrate and automate tasks within Google Workspace apps.

1. In your Google Sheet, go to Extensions > Apps Script.
2. Delete any code that is present by default and paste the following code:

const SHEET_NAME = "form-response"
const EMAIL_TO_NOTIFY = "[email protected]"

function doPost(e) {
  try {
  var name = e.parameter.name;
  var email = e.parameter.email;
  var message = e.parameter.message;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(SHEET_NAME);
    sheet.appendRow(["Name", "Email", "Message", "Date"]);
  }

  var timestamp = new Date();
  sheet.appendRow([name, email, message, timestamp]);

  // send confirmation email to user
  var userSubject = "Thank you for contacting us";
  var userBody = "Hey " + name + ",\n\nThanks for reaching out! We've received your demo request, and don't worry—our team of highly trained, coffee-powered humans will get back to you soon (or maybe a bit later, depending on how strong the coffee is today).\n\nIn the meantime, feel free to browse our site, or just pretend to stare at the screen like you're working. \n\nCheers";
  MailApp.sendEmail(email, userSubject, userBody);

  // send email to admin
  var adminSubject = "New Contact Us Form Submission";
  var adminBody = "You have received a new submission from the Contact Us form:\n\n" +
    "Name: " + name + "\n" +
    "Email: " + email + "\n" +
    "Message: " + message + "\n\n" +
    "Check the responses in the Google Sheet.";
  MailApp.sendEmail(EMAIL_TO_NOTIFY, adminSubject, adminBody);

  // Return a success response
  return ContentService.createTextOutput(JSON.stringify({
    result: "success",
    message: "Form submitted successfully"
  })).setMimeType(ContentService.MimeType.JSON);

  } catch (error) {
  return ContentService.createTextOutput(JSON.stringify({
    result: "error",
    message: error.message
  })).setMimeType(ContentService.MimeType.JSON);
  }
}

Deploying the Script as a Web App

Now that the script is ready, we need to deploy it as a web app to make it accessible from your website.

1. Click on Deploy (Top right corner) > New deployments > Select type > Web app.
2. Configure the deployment:

• Project version: Select “New version.”
• Execute the app as: Choose “Me” (the script owner).
• Who has access: Choose “Anyone, even anonymous.”

1. Click Deploy and copy the web app URL— this is the URL you’ll use to point your form submissions to. (APP_SCRIPT_URL)

   you may need to grant permission for the first deployment, don’t worry its a one time process.

Integration with website form

With your Google Apps Script deployed as a web app, it’s time to integrate it with your website.

Here’s an example HTML form that submits data to the script:

<form id="contact-form" method="POST">
  <label for="name">Name:</label>
  <input type="text" id="name" name="name" required><br><br>
  
  <label for="email">Email:</label>
  <input type="email" id="email" name="email" required><br><br>
  <label for="message">Message:</label>
  <textarea id="message" name="message" required></textarea><br><br>
  <button type="submit">Submit</button>
</form>

<script>
  document.getElementById('contact-form')
  .addEventListener('submit', function(e) {
  e.preventDefault();
  const formData = new FormData(this);
  fetch('APP_SCRIPT_URL', {
    method: 'POST',
    body: formData
  })
  .then(response => response.json())
  .then(data => {
    if (data.result === 'success') {
    alert('We will get back to you shortly.');
    } else {
    alert('Something went wrong');
    }
  })
  .catch(error => {
    alert('An error occurred: ' + error);
  });
  });
</script>

Make sure to replace APP_SCRIPT_URL with the URL of your deployed web app.

Testing and Debugging

Once the form is integrated into your website, test the process by filling out the form. we can see:

• The data saved in the Google Sheet.
• A confirmation email sent to the user.
• A notification email sent to the admin.

---

Congrats! You’re ready for all the “Can you fix my printer?” messages. 😈 💻