Saving Form Data into Google Sheet

Introduction

In today’s digital age, collecting and managing data efficiently is crucial for businesses and individuals alike. One popular way to gather data is through HTML forms, and what better way to store this data than in Google Sheets? In this guide, we will walk you through the process of creating a data entry form on your website and seamlessly transferring that data to Google Sheets. By the end of this tutorial, you’ll have a powerful tool to streamline your data collection process.

Understanding the Importance of HTML Form to Google Sheets Integration

Before we dive into the technical aspects, let’s briefly discuss why integrating HTML forms with Google Sheets is so valuable. This approach offers several benefits:

1. Efficient Data Collection: HTML forms are user-friendly and widely accessible, making data collection a breeze.
2. Real-time Updates: Data entered into the form can be instantly reflected in the Google Sheet, ensuring that you have up-to-the-minute information at your fingertips.
3. Centralized Data Storage: Google Sheets provides a centralized location for storing and managing your data, eliminating the need for scattered files and manual data entry.

Now, let’s move on to the practical steps involved in setting up your own HTML form for Google Sheets integration.

Setting Up the HTML Form

Before we dive into the integration with Google Sheets, let’s start with the basics – creating an HTML form to collect data. Here’s a simple HTML form template to get you started:

<form id="data-entry-form">
  <label for="name">Name:</label>
  <input type="text" id="name" name="name" required><br>

  <label for="email">Email:</label>
  <input type="email" id="email" name="email" required><br>

  <label for="age">Age:</label>
  <input type="number" id="age" name="age" required><br>

  <button type="submit">Submit</button>
</form>

In this HTML form, we have fields for collecting a person’s name, email, and age. The form has a unique ID (“data-entry-form”), and each input field has a name attribute that we will use when transferring the data to Google Sheets.

Using JavaScript to Save Form Data

Now, let’s delve into the JavaScript code that will allow us to save the data submitted through the HTML form into a Google Sheet. We will use the Google Sheets API for this purpose. To follow along, make sure you have the necessary API credentials and libraries set up.

document.addEventListener("DOMContentLoaded", function () {
    const form = document.getElementById("data-entry-form");
    form.addEventListener("submit", function (e) {
        e.preventDefault();
        const formData = new FormData(form);
        const data = {};
        formData.forEach((value, key) => {
            data[key] = value;
        });

        // Convert the data to JSON (optional)
        const jsonData = JSON.stringify(data);

        // Send data to Google Sheets using Google Apps Script URL
        const scriptURL = 'YOUR_GOOGLE_APPS_SCRIPT_URL';
        const requestOptions = {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json'
            },
            body: jsonData
        };

        fetch(scriptURL, requestOptions)
            .then(response => {
                // Handle the response as needed
                console.log('Data sent successfully');
                form.reset();
            })
            .catch(error => {
                console.error('Error sending data:', error);
            });
    });
});

In this JavaScript code, we first prevent the default form submission behavior. We then extract form data, convert it to a JSON object, and send it to Google Sheets using a Google Apps Script URL. Be sure to replace YOUR_GOOGLE_APPS_SCRIPT_URL with the actual URL of your Google Apps Script.

Google Sheets Integration

Now that you have your HTML form in place, let’s integrate it with Google Sheets to automatically populate a spreadsheet with the submitted data. Follow these steps:

1. Create a Google Sheets Spreadsheet

• Log in to your Google account and go to Google Sheets.
• Create a new spreadsheet or use an existing one to store your form data.

2. Install Google Apps Script

• In your Google Sheets document, click on “Extensions” and select “Apps Script.”
• Delete any code that appears and replace it with the following script:

const sheetName = 'Sheet1' //  Enter sheet name where data is to be written below
const scriptProp = PropertiesService.getScriptProperties()

function initialSetup () {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
  
  const lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    const body = e.postData.contents
    const bodyJSON = JSON.parse(body)
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    const sheet = doc.getSheetByName(sheetName)
    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    const nextRow = sheet.getLastRow() + 1

    const newRow = headers.map(function(header) {
      return header === 'Date' ? new Date() : bodyJSON[header];
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  catch (e) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
      .setMimeType(ContentService.MimeType.JSON)
  }

  finally {
    lock.releaseLock()
  }
}

3. Deploy the Script

• Save your Google Apps Script and click the disk icon to save your project.
• Click on the deploy button (rocket icon), choose a new deployment, and set the access to “Anyone, even anonymous.”

4. Get the Form Submission URL

• After deploying, you will receive a URL for your script.
• Assign the value of the URL to the const scriptURL variable in the JavaScript code provided above.

5. Testing

• Test your form by submitting data.
• Check your Google Sheet to see the data automatically appear in your designated columns.

Conclusion

Congratulations! You have successfully created an HTML form that transfers data seamlessly to Google Sheets. This method can be a powerful tool for various applications, from simple contact forms to detailed surveys. By following this guide and customizing it to your needs, you can optimize your data collection process and keep your records organized. Start collecting and managing your data efficiently today!