1 Answer

0 votes
by (487 points)

Today I am showing you How to submit a simple HTML form to a Google Sheet using only HTML and JavaScript.

This example shows how to set up a mailing list form that sends data to Google Sheets.

1. Set up a Google Sheet

Go to this link and ckick Make a copy link. 

Now you see the redymade  google sheet if you sign to your google account. 

Then Rename the Copy of HTML Form Reponses As you like.

Then delete the column E- color just look like this

* Set the following headers in the first row:

       A              B            C           D            

Timestamp  name  message  email  

After that delete the form data from the row 2 to row 6. 

You can increase the row numer by clicking Add. 

2. Create a Google App Script

Click on Extensions -> Apps Script. This will open new Google Script. Rename it to something like "HTML Form Script" or leave it default.

3. Run the initialSetup function

You should see a modal asking for permissions. Click Review permissions and continue to the next screen. Because this script has not been reviewed by Google, it will generate a warning before you can continue. You must click the "Go to Mailing List (Unsafe)" for the script to have the correct permissions to update your form.

After giving the script the correct permissions, you should see the following output in the script editor console:

4. Publish the project

Now your project is ready to publish. Select the Deploy button and New Deployment from the drop-down.

Click the "Select type" icon and select Web app.

In the form that appears, select the following options:

Description: Mailing List Form (This can be anything that you want. Just make it descriptive.)

Web app → Execute As: Me

Web app → Who has access: Anyone

Then click Deploy.

You should see a modal asking for permissions. Click Review permissions and continue to the next screen. Because this script has not been reviewed by Google, it will generate a warning before you can continue. You must click the "Go to Mailing List (Unsafe)" for the script to have the correct permissions to update your form.

Important: Copy and save the web app URL before moving on to the next step.

5. Configure your HTML form

Create a HTML form named "index.html" and paste the following code. 

And replacing [email protected] with your own gmail address. 

And action = "Your app url" with the URL you saved from the previous step then save the file. Last o all upload the index.html file to your control pannel and connect your webpage.

Now when you submit this form from any location, the data will be saved in the Google Sheet. Enjoy .......

Source here

by (487 points)

Here is the Code for index.html file 

<!doctype html>

<html lang="en">

<head>

    <meta charset="utf-8">

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

  <title>Contact Form</title>

  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">

<style>

a{ color:#2D3E52

} label {

display:block;

margin-top:20px;

}

textarea {

width:50%;

height:150px;

background:#efefef;

border:1px solid #dedede;

padding:10px;

margin-top:3px;

font-size:0.9em;

color:#3a3a3a;

-moz-border-radius:5px;

-webkit-border-radius:5px;

border-radius:5px;

}

input,select{

width:50%;

height:25px;

background:#efefef;

border:1px solid #dedede;

padding:10px;

margin-top:3px;

font-size:0.9em;

color:#3a3a3a;

-moz-border-radius:5px;

-webkit-border-radius:5px;

border-radius:5px;

}

input:focus, textarea:focus {

border:1px solid #97d6eb;

}

button {

width:50%;

height:38px;

border:none;

margin-top:20px;

cursor:pointer;

color:white;

background:#335599;

}

button:hover {

opacity:.9;

}

</style>

</head>

<body>

<center>

 <form class="gform pure-form pure-form-stacked" method="POST" data-email="[email protected]"

  action="Your app url">

    <!-- change the form action to your script url -->

    <div class="form-elements">

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

        <input id="name" name="name" required placeholder="" />

  

        <label for="email">Your Email Address:</label>

        <input id="email" name="email" type="email" value=""

        required placeholder=""/>

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

        <textarea id="message" name="message" required rows="5"

        placeholder=""></textarea>

      <button type='submit'>Send</button>

    </div>

    <!-- Customise the Thankyou Message People See when they submit the form: -->

    <div class="thankyou_message" style="display:none;">

      <span style='color:green'> Your message sent successfully !</span>

    </div>

  </form>

  <!-- Submit the Form to Google Using "AJAX" -->

  <script>

  (function() {

  // get all data in form and return object

  function getFormData(form) {

    var elements = form.elements;

    var honeypot;

    var fields = Object.keys(elements).filter(function(k) {

      if (elements[k].name === "honeypot") {

        honeypot = elements[k].value;

        return false;

      }

      return true;

    }).map(function(k) {

      if(elements[k].name !== undefined) {

        return elements[k].name;

      // special case for Edge's html collection

      }else if(elements[k].length > 0){

        return elements[k].item(0).name;

      }

    }).filter(function(item, pos, self) {

      return self.indexOf(item) == pos && item;

    });

    var formData = {};

    fields.forEach(function(name){

      var element = elements[name];

      

      // singular form elements just have one value

      formData[name] = element.value;

      // when our element has multiple items, get their values

      if (element.length) {

        var data = [];

        for (var i = 0; i < element.length; i++) {

          var item = element.item(i);

          if (item.checked || item.selected) {

            data.push(item.value);

          }

        }

        formData[name] = data.join(', ');

      }

    });

    // add form-specific values into the data

    formData.formDataNameOrder = JSON.stringify(fields);

    formData.formGoogleSheetName = form.dataset.sheet || "responses"; // default sheet name

    formData.formGoogleSendEmail

      = form.dataset.email || ""; // no email by default

    return {data: formData, honeypot: honeypot};

  }

  function handleFormSubmit(event) {  

    event.preventDefault();           

    var form = event.target;

    var formData = getFormData(form);

    var data = formData.data;

    // If a honeypot field is filled, assume it was done so by a spam bot.

    if (formData.honeypot) {

      return false;

    }

    disableAllButtons(form);

    var url = form.action;

    var xhr = new XMLHttpRequest();

    xhr.open('POST', url);

    // xhr.withCredentials = true;

    xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");

    xhr.onreadystatechange = function() {

        if (xhr.readyState === 4 && xhr.status === 200) {

          form.reset();

          var formElements = form.querySelector(".form-elements")

          if (formElements) {

            formElements.style.display = "none"; // hide form

          }

          var thankYouMessage = form.querySelector(".thankyou_message");

          if (thankYouMessage) {

            thankYouMessage.style.display = "block";

          }

        }

    };

    // url encode form data for sending as post data

    var encoded = Object.keys(data).map(function(k) {

        return encodeURIComponent(k) + "=" + encodeURIComponent(data[k]);

    }).join('&');

    xhr.send(encoded);

  }

  

  function loaded() {

    // bind to the submit event of our form

    var forms = document.querySelectorAll("form.gform");

    for (var i = 0; i < forms.length; i++) {

      forms[i].addEventListener("submit", handleFormSubmit, false);

    }

  };

  document.addEventListener("DOMContentLoaded", loaded, false);

  function disableAllButtons(form) {

    var buttons = form.querySelectorAll("button");

    for (var i = 0; i < buttons.length; i++) {

      buttons[i].disabled = true;

    }

  }

})();

  </script>

</center>

</body>

</html>

312 questions

91 answers

6 comments

5,034 users

3 Online Users
0 Member 3 Guest
Today Visits : 7025
Yesterday Visits : 2524
Total Visits : 6982815
...