Creating a contact form with Google Sheets and App script

HOW TO CREATE CONTACT FORM IN APP SCRIPT

— Chelsea ♥




To create a contact HTML page with timestamp, name, email, and message using Google Sheets inside of App Script and style the form with Material Design CSS, follow the steps below:

Step 1: Create a new Google Sheet


Open your Google Drive account and create a new Google Sheet. Rename the sheet to "Contact Form Responses." You'll also add your data headers here. Add the Timestamp, Name, Email, Message in the first row of the columns.

Step 2: Set up the Apps Script


Open the Apps Script by clicking on Extensions > Apps Script from the Google Sheet menu. Once the editor is open, delete any existing code. 

Setting up the functions

doGet(): This function is a Google Apps Script doGet() function that serves an HTML page to the client when they visit the web app's URL. It creates an HTML template from a file called Contact.html, sets the data property of the template to an empty object, and returns the evaluated template.

submitFormData(formData): This function is called by the client-side JavaScript when the user submits the contact form. It appends a new row to the active sheet in the spreadsheet with the current date and the form data, and then sends an email with the form data to a specified email address.

This line gets the active sheet in the spreadsheet and assigns it to the sheet variable.

const sheet = SpreadsheetApp.getActiveSheet();


This line appends a new row to the sheet variable with an array of values that includes the current date and the form data. The appendRow() method is called on the sheet object, and the array of values is passed as an argument.

sheet.appendRow([new Date(), formData.Name, formData.Email, formData.Message]);

These lines define the email, subject, and message variables, and then use the MailApp.sendEmail() method to send an email with the form data to the specified email address. The sendEmail() method takes three arguments: the recipient email address (email), the email subject (subject), and the email message (message). The email message is constructed using string concatenation to combine the form data values into a single string.

const email = "shedevsthings@gmail.com";
const subject = "Contact Form Submission";
const message = "Name: " + formData.Name + "\n" +
                "Email: " + formData.Email + "\n" +
                "Message: " + formData.Message;
MailApp.sendEmail(email, subject, message);


Together these functions perform the task needed to send data from our HTML document to our google sheet.

function doGet() {
  var template = HtmlService.createTemplateFromFile('contact');
  template.data = {};
  return template.evaluate();
}
function submitFormData(formData) {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([new Date(), formData.Name, formData.Email, formData.Message]);
 
  const email = "YOUR-EMAIL-HERE";
  const subject = "Contact Form Submission";
  const message = "Name: " + formData.Name + "\n" +
                  "Email: " + formData.Email + "\n" +
                  "Message: " + formData.Message;
  MailApp.sendEmail(email, subject, message);}

Once you have added the functions to the spreadsheet - create a new html file in app script and title it Contact. Here you will create the HTML web app we need to display the form on our website.

Step 3: Create the HTML form

<html>

  <head>

    <meta charset="utf-8">

    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <meta name="description" content="contact form example">

  <title> SDT - Contact Form Example</title>

    <!-- Add Material Design CSS -->

    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/css/materialize.min.css">


  </head>

  <body>

    <form id="contact-form" class="container">

      <h4 class="center-align">Contact Form</h4>

      <div class="input-field">

        <input type="text" id="name" name="Name" required>

        <label for="name">Name</label>

      </div>

      <div class="input-field">

        <input type="email" id="email" name="Email" required>

        <label for="email">Email</label>

      </div>

      <div class="input-field">

        <textarea id="message" class="materialize-textarea" name="Message" required></textarea>

        <label for="message">Message</label>

      </div>

      <div class="center-align">

        <input type="submit" class="btn waves-effect waves-light" value="Submit">

      </div>

    </form>

 <script>

      const form = document.querySelector("#contact-form");

      form.addEventListener("submit", function(event) {

        event.preventDefault();

       

        const name = form.elements.Name.value;

        const email = form.elements.Email.value;

        let message = form.elements.Message.value;


        message = message.replace(/</g, "&lt;").replace(/>/g, "&gt;"); // sanitize message


        google.script.run.submitFormData({ Name: name, Email: email, Message: message });

       

        document.querySelector("#contact-form").innerHTML = "Thank you for contacting us. We will be in touch.";

      });

    </script>


    </script>

    <!-- Add Material Design JS -->

    <script src="https://cdnjs.cloudflare.com/ajax/libs/materialize/1.0.0/js/materialize.min.js"></script>

  </body>

</html>

Once the html page is completed, run the script to grant permissions by selecting the run option under the code.gs file. This will bring up the permissions box which you will need to allow in order to run the contact form. Deploy the app as a web app and test. If you run into an error remember to debug and check that you have written the html and code correctly. Copy the url and you can embed it as a full page embed or iframe the link to add it to your google site. For your google site you do not need the iframe. You can paste the link directly inside your google site. Here is an example iframe tag you can use.

<iframe src="YOUR_URL_HERE" style="width: 100%; height: 500px;"></iframe>

3 comments

  1. It doesnt connect to the spreadsheet

    ReplyDelete
  2. Thank you so much! but can you help me with adding it to my html site... i want to also style it according to my website.

    ReplyDelete
  3. Hi there i want to add a checkbox area as well, so that my clients can click on multiple services they require. Could you guide me thru that? By the way thank you for this form :)

    ReplyDelete