Skip to content
Home » Blog » Digital Marketing » Start blogging » Email Marketing » Lead Capture Form Template: Store Data in Google Sheets

Lead Capture Form Template: Store Data in Google Sheets

A visually appealing lead capture form concept with digital marketing elements, illustrating user engagement and data collection.

Capturing leads effectively is crucial for growing your business. In this guide, you’ll learn how to create a simple lead capture form using PHP and HTML and store the collected data in a Google Sheet automatically.

Table of Contents

Step 1: Create the Lead Capture Form

First, create an index.php file to design a simple form for collecting user details like name, email, and phone number.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Lead Generation</title>
    <style>
        body { font-family: Arial, sans-serif; background: #f4f4f4; text-align: center; padding: 50px; }
        .container { background: #fff; padding: 20px; max-width: 400px; margin: auto; box-shadow: 0 4px 8px rgba(0, 0, 0, 0.1); }
        input, button { width: 90%; padding: 10px; margin: 10px 0; }
        button { background: #28a745; color: #fff; border: none; cursor: pointer; }
        button:hover { background: #218838; }
    </style>
</head>
<body>
    <div class="container">
        <h2>Get Your Free Guide!</h2>
        <form action="process.php" method="post">
            <input type="text" name="name" placeholder="Your Name" required><br>
            <input type="email" name="email" placeholder="Your Email" required><br>
            <input type="tel" name="phone" placeholder="Your Phone" required><br>
            <button type="submit">Claim Your Free Guide</button>
        </form>
    </div>
</body>
</html>

Step 2: Process Form Data

Create a process.php file to handle form submissions, send a welcome email, and store data in Google Sheets.

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = htmlspecialchars($_POST["name"]);
    $email = htmlspecialchars($_POST["email"]);
    $phone = htmlspecialchars($_POST["phone"]);

    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        die("<script>alert('Invalid email format'); window.location='index.php';</script>");
    }

    $to = "your-email@example.com";
    $subject = "New Lead Submission";
    $message = "Name: $name\nEmail: $email\nPhone: $phone";
    $headers = "From: noreply@yourdomain.com";

    if (mail($to, $subject, $message, $headers)) {
        include 'welcome.php';
        sendWelcomeEmail($email, $name);

        include 'store_google_sheet.php';
        storeInGoogleSheet($name, $email, $phone);

        echo "<script>alert('Thank you! Your submission has been received.'); window.location='index.php';</script>";
    } else {
        echo "<script>alert('Error sending email. Please try again.'); window.location='index.php';</script>";
    }
}
?>

Step 3: Send a Welcome Email

Create a welcome.php file to send an automated welcome email to users.

<?php
function sendWelcomeEmail($email, $name) {
    $subject = "Welcome to Our Community!";
    $message = "Hi $name,\n\nThank you for signing up! We're excited to have you on board. Stay tuned for amazing updates.\n\nBest Regards,\nYour Team";
    $headers = "From: noreply@yourdomain.com";
    
    mail($email, $subject, $message, $headers);
}
?>

Step 4: Store Data in Google Sheets

To store form submissions in Google Sheets, you’ll need a Google Apps Script.

Create a Google Apps Script (code.gs)

  1. Open Google Sheets
  2. Click Extensions > Apps Script
  3. Delete any code and replace it with:
function doPost(e) {
  try {
    var sheet = SpreadsheetApp.openById("YOUR_SHEET_ID").getActiveSheet();
    var params = e.parameter;
    sheet.appendRow([params.name, params.email, params.phone, new Date()]);
    return ContentService.createTextOutput("Success");
  } catch (error) {
    return ContentService.createTextOutput("Error: " + error.message);
  }
}
  1. Click Deploy > New Deployment
  2. Set Execution Type to Web App
  3. Set Access to Anyone
  4. Copy the generated URL

Modify store_google_sheet.php to Send Data

Create a store_google_sheet.php file to send collected data to Google Sheets via the Apps Script Web App.

<?php
function storeInGoogleSheet($name, $email, $phone) {
    $scriptUrl = "YOUR_GOOGLE_APPS_SCRIPT_URL";
    
    $data = array(
        "name" => $name,
        "email" => $email,
        "phone" => $phone
    );
    
    $options = array(
        "http" => array(
            "header"  => "Content-type: application/x-www-form-urlencoded",
            "method"  => "POST",
            "content" => http_build_query($data)
        )
    );
    
    $context  = stream_context_create($options);
    file_get_contents($scriptUrl, false, $context);
}
?>

Step 5: Testing

  1. Fill out the form on index.php.
  2. Check if you receive the welcome email.
  3. Open your Google Sheet and verify if data is recorded correctly.

Conclusion

By following these steps, you now have a functional lead capture form that collects user details, sends an email confirmation, and stores the data in Google Sheets. This setup is an efficient way to manage leads and improve conversions!

Email is need not only for marketing

Author Profile

Anup Ain

Hey, my name is Anup Ain. I am a blogger and a digital marketing intern. I enjoy sharing my knowledge and experiences with others.