How to Use HTML Forms for Free with Google Sheets

Deepanshu Bhalla Add Comment

This tutorial provides a step-by-step guide on using HTML forms for free with Google Sheets. Perfect for any website, it can be easily integrated into static site generators (SSGs) like Astro, Hugo, Eleventy etc as well as popular CMS such as Ghost, Blogger and Drupal.

How to Use HTML Forms for Free

Features

  • 100% Free: No monthly or daily limits.
  • Email Notifications: Get alerts when forms are submitted.
  • Store Data: Save all form submissions in Google Sheets.
  • Fully Customized: Make it fit your needs.

Steps to Connect HTML Form to Google Sheets

Please follow the steps below how to collect HTML form responses in Google Sheets for free.

1. Create a Google Sheet

Start by creating a new Google Sheet where the form submissions will be stored. Enter these columns in first row - Date, Name, Email and Message.

Google Sheet for HTML Form Submissions

2. Set Up Google Apps Script

How to Use App Script in Sheets
  1. Open your Google Sheet, click on Extensions > Apps Script to access the Apps Script editor.
  2. In the Apps Script editor, remove the existing myFunction() function and then paste the following script to handle the form submissions. This script will process the incoming data from your HTML form.
  3. In the script, enter your email in emailRecipient variable. You can also change subject of email by editing this emailSubject variable.
  4. Rename project to anything you like.
const targetSheet = 'Sheet1';
const properties = PropertiesService.getScriptProperties();

function setupInitial() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  properties.setProperty('spreadsheetId', activeSpreadsheet.getId());
}

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

  try {
    const document = SpreadsheetApp.openById(properties.getProperty('spreadsheetId'));
    const sheet = document.getSheetByName(targetSheet);

    const columnHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0].map(header => header.toLowerCase());
    const nextAvailableRow = sheet.getLastRow() + 1;

    const params = {};
    for (let key in e.parameter) {
      params[key.toLowerCase()] = e.parameter[key];
    }

    const rowData = columnHeaders.map(header => (header === 'date' ? new Date() : params[header] || ''));
    sheet.getRange(nextAvailableRow, 1, 1, rowData.length).setValues([rowData]);

    // Improved email formatting
    const emailRecipient = 'youremail@email.com';  // Replace with your email
    const emailSubject = 'New Inquiry Recorded';
    
    // Construct a nicely formatted message
    let emailBody = `A new record was added to ${targetSheet} on row ${nextAvailableRow}.\n\nDetails:\n`;
    columnHeaders.forEach((header, index) => {
      emailBody += `${header.charAt(0).toUpperCase() + header.slice(1)}: ${rowData[index]}\n`;
    });

    MailApp.sendEmail(emailRecipient, emailSubject, emailBody);

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextAvailableRow }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': error.message }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

3. Run the Script

Run App Script
  1. Please make sure to save the project. Once saved, Run button will be visible and then click on it.
  2. You will see a popup asking for permissions. Click Review permissions to go to the next screen.
  3. A warning will show up as google has not tested this new script before. Click Go to Project (unsafe) to give the script the permissions it needs.
  4. Execution will be started and shown in the execution log window.

4. Add Trigger

Choose the "Triggers" project from the sidebar and click the Add Trigger button.

Triggers in Google Sheets

In the popup window, select these options:

  • Choose which function to run: doPost
  • Choose which deployment should run: Head
  • Select event source: From spreadsheet
  • Select event type: On form submit
  • Failure Notification Settings: Notify me immediately

Click Save button to save the above settings.

5. Deployment

Deployment

Click on the Deploy button and select New Deployment from the dropdown menu.

Next, click the Select type icon and choose Web app.

In the form that appears, select the following options:

  • Description: Any name you want.
  • Execute As: Me
  • Who has access: Anyone

After that, click Deploy button and then copy the Web app URL.

6. Create HTML Form

Refer to the code below to create an online form and replace action="WEBAPP_URL" with your URL you got in the previous step.

  • HTML
  • CSS
  • Javascript

HTML

<div class="main-container">
  <div class="form-container">
    <form action="https://script.google.com/macros/s/xxxxxxxxx/exec" method="POST" id="formId">
      <div class="mb-5">
        <label for="name" class="form-label">Full Name</label>
        <input type="text" name="name" id="name" placeholder="Full Name" class="form-input" required />
      </div>

      <div class="mb-5">
        <label for="email" class="form-label">Email Address</label>
        <input type="email" name="email" id="email" placeholder="Enter your email" class="form-input" required />
      </div>
      <div class="mb-5">
        <label for="message" class="form-label">Message</label>
        <textarea rows="6" name="message" id="message" placeholder="Enter your message" class="form-input" required></textarea>
      </div>

      <div>
        <button type="submit" class="btn">Submit</button>
      </div>
    </form>
  </div>
</div>
Important Note : Make sure column names of google sheets and input names of form are same.

Once user submits form, you will get email notification as shown below.

Email Notification

Also users' data will be appended to your google sheets.

Form Records
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 0 Response to "How to Use HTML Forms for Free with Google Sheets"
Next → ← Prev