Link your HTML form to a spreadsheet via Google Forms

Link your HTML form to a spreadsheet via Google Forms.

We often find contact forms on portfolio or company websites. They serve as a medium between the company and society. How cool would it be if we could link the form on our website to google forms and get the data in a dynamically updating spreadsheet? This blog takes you through a series of steps to achieve the same. I find this method really easy and foolproof so here we go.

Prerequisites —Keep your basic HTML form code ready before you start this tutorial.

I have created a sample HTML form to start with. I picked up the stylesheet and format from this amazing codepen. I’d like to thank the creator for this beautiful piece. It currently looks something like this —

Let’s get started. First, create a google form having the same fields as your HTML form. Even mark the fields you want to be tagged ‘required’. It would look something like this.

Now click on the eye icon at the top right of your form. This will generate a preview of your form. Make sure it has its URL ending with ‘…/viewform’

Now press ctrl+u on this page to see the source code. Then press ctrl+f so that you can search for a phrase in that page. Now the phrase we want to search is form action . The results should give you a tag with a URL ending with ‘…/formResponse’ . Copy this URL and save it somewhere for future use (maybe in a .txt file).

Scroll down the same page and you will see something like this. Copy down these numbers(in the same order) along with the above URL.

Come back to your index.html and modify your form component a bit. Now we are going to add the name property to each one of our input fields and give them the value corresponding to the numbers we saw above. Like so. Notice how we have written name = “entry.number” for each field. Make sure each of your input fields has an id too.

Now copy and paste the following code into the

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Document</title>
</head>


<!-- stylesheet: https://codepen.io/colorlib/pen/KVoZyv -->


<body>
  <div class="container">
    <form id="contact" onsubmit="return postToGoogle();">
      <h3>Contact Form</h3>
      <h4>Connect with us</h4>
      <fieldset>
        <input  placeholder="Your name"
                type="text" 
                name="entry.517626622" 
                data-name="Name" 
                id="Name" 
                required>
      </fieldset>
      <fieldset>
        <input 
                placeholder="Your Email Address" 
                type="email" 
                name="entry.935048337" 
                data-name="Email" 
                id="Email" 
                required>
      </fieldset>
      <fieldset>
        <input 
                placeholder="Your Phone Number" 
                type="tel" 
                name="entry.1737955681" 
                data-name="Phone" 
                id="Phone" 
                required>
      </fieldset>
      <fieldset>
        <textarea 
                placeholder="Type your message here...." 
                data-name="Message" 
                id="Message" 
                name="entry.354351359" 
                required>
        </textarea>
      </fieldset>
      <fieldset>
        <button type="submit" id="contact-submit">Submit</button>
      </fieldset>
    </form>
  </div>

  <script>
    function postToGoogle() {
      var field1 = $("#Name").val();
      var field2 = $("#Email").val();
      var field3 = $("#Phone").val();
      var field4 = $("#Message").val();

      $.ajax({
        url: "https://docs.google.com/forms/u/0/d/e/1FAIpQLSf2PfhlJGAZr7r6ia7HQRGVHXUmF3nxLMIpmRyg9qnRelMu_g/formResponse",
        data: {
          "entry.517626622": field1,
          "entry.935048337": field2,
          "entry.1737955681": field3,
          "entry.354351359": field4
        },
        type: "POST",
        dataType: "xml",
        success: function (d) {
          $('#contact').trigger('reset');
        },
        error: function (x, y, z) {
          $('#contact').trigger('reset');
        }
      });
      return false;
    }
  </script>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

</body>

</html>

11/07/2023 09:40 - Oleh Administrator - Dilihat 20155 kali

Komentar

Bagus

19/08/2023 08:10 - Maria Magdalena Tae

Perangkat ajat k Merdeka ini sangat baik

22/07/2023 18:02 - Nurdjannah Kabau

Komentari Tulisan Ini

Nama Lengkap *

Email *

URL

Komentar *

Fuad Nailulhuda, S.Pd.I

- Kepala Madrasah -

Assalamu'alaikum Warahmatullahi Wabarakatuh, Alhamdulillah, segala puji syukur kita panjatkan ke hadirat Allah SWT, yang telah melimpahkan rahmat dan karunia-Nya kepada…

Selengkapnya