How to send HTML form data to Google Sheets?

Sending your form data and manage it can be a very tricky process. In this tutorial, we will send our website's form data to Google sheets without any server-side coding.

4 min read

Sending your form data and manage it can be a very tricky process. Generally, we use databases to store form data and server-side scripting to collect it from the website. When it comes to collect data from a static website then it seems totally impossible.  

Some of you might have thought can we collect form data from a static website? Yes, you can do it! The website's form of data can be easily collected, stored, and managed on Google sheets.

In this tutorial, we will send our website's form data to Google sheets.


DEMO

HTML

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Serverless Form</title>
</head>
<body>

    <h2>HTML Forms</h2>

    <form action="" method="POST">
      <label for="fname">First name:</label>
      <input type="text" id="fname" name="fname" value="John">
      <label for="lname">Last name:</label>
      <input type="text" id="lname" name="lname" value="Doe">
      <input type="submit" value="Submit">
    </form> 
    
</body>
</html>

Remember following points to make your form work

Include jQuery in your HTML page.

<script src="https://unpkg.com/jquery@3.3.1/dist/jquery.min.js"></script>

Create a HTML form and and give name to the form.

<form id="contactForm" name="serverless-form">

Insert the input fields with with name value.

<input type="text" id="fname" name="fname" value="John">

Google Sheet Config.

  1. Make a Google sheet in your Google Drive.
  2. Write the fields in your Google sheets same as you write the name values in input fields.
Google Sheet
Fill the name field in Google Sheet

3.  Click on tools in menu bar and go to to script editor.

4.  Paste the following script in your editor and change your sheet name and mail id.

//This script should be pasted in your google sheet script editor.

function doPost(e) {
    return handleResponse(e);
}

//  Enter sheet name where data is to be written below
var SHEET_NAME = "Contact-form-demo";

var SCRIPT_PROP = PropertiesService.getScriptProperties();

function handleResponse(e) {

    var lock = LockService.getPublicLock();
    lock.waitLock(30000);

    try {

        // select the sheet name where we write the data
        var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
        var sheet = doc.getSheetByName(SHEET_NAME);

        // Take row_1 as the header
        var headRow = e.parameter.header_row || 1;
        var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
        var nextRow = sheet.getLastRow() + 1; // get next row
        var row = [];

        // loop through the header columns
        for (i in headers) {
            if (headers[i] == "Timestamp") {
                row.push(new Date());
            } else if (headers[i] == "sn") {
                row.push(sheet.getLastRow());
            } else {
                row.push(e.parameter[headers[i]]);
            }
        }
        // more efficient to set values as [][] array than individually
        sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);

        //send email
        sendEmail(e.parameter);

        // return json success results
        return ContentService
            .createTextOutput(JSON.stringify({
                "result": "success"
            }))
            .setMimeType(ContentService.MimeType.JSON);
    } catch (e) {
        // if error return this
        return ContentService
            .createTextOutput(JSON.stringify({
                "result": "error",
                "error": e
            }))
            .setMimeType(ContentService.MimeType.JSON);
    } finally { //release lock
        lock.releaseLock();
    }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

function sendEmail(data) {

    var body = '';
    for (let key in data) {
        if (data.hasOwnProperty(key)) {
            body = body + `<b>${key}</b> : ${data[key]}<br/>`;
        }
    }

    GmailApp.sendEmail('your-email-address', 'Contact form message', '', {
        'name': 'Contact Form',
        'htmlBody': 'You have received a new contact message. <br/><br/>' + body
    });
}

HTML Form config

Include loader animation style in your page

<style>
        #loader {
            display: none;
            position: fixed;
            top: 0;
            left: 0;
            right: 0;
            bottom: 0;
            width: 100%;
            background: rgba(0, 0, 0, .75) url(img/spinner.svg) no-repeat center center; //include your spinner url
            z-index: 10000
        }
 </style>
  1. Save the script.
  2. Click on Publish and Deploy as web app.
  3. Copy the web app link and paste in the script given below in the const scriptURLC field.

Copy the following script after Jquery script in your HTML page.

<script>
        var spinner = $('#loader');
        const scriptURLC ='your google sheet script'
        const serverlessForm = document.forms['serverless-form'];

        serverlessForm.addEventListener('submit', e => {
            e.preventDefault();
            spinner.show();


            fetch(scriptURLC, {
                    method: 'POST',
                    body: new FormData(serverlessForm)
                })
                .then(res => {

                    console.log(res);
                    spinner.hide();

                    if (res['status'] == 200) {
                        swal("Your form has been submitted!",
                            "We will get back to you soon. Have a great day!", "success");
                        return true;

                    } else {
                        swal("Something went wrong!", "Please try after some time", "error");

                    }
                    document.getElementById('submitForm').classList.remove('loading');
                })
                .catch(error => {

                    swal("Something went wrong!", "Please try after some time", "error");
                    // todo enable submit button

                })
        });
    </script>

Dronacharya Developer Circle / Serverless Form
Sending your form data and manage it can be a very tricky process. In this tutorial, we will send our website’s form data to Google sheets.
Code Repository

Featured Templates

14 Free HTML CSS website templates

Here is the collection of some free and personal use HTML CSS website templates which may help you to get started on the web.

3 min read

How to make a URL shortener

In this tutorial, we are gonna make a perfect URL shortener for your project using Nodejs, Express, and MongoDB.

3 min read