How to Build an Email Open Tracker with Google Apps Script and Tailwind CSS

by

Tracking whether your emails are opened can be incredibly useful, whether you’re sending newsletters, important notifications, or just want to know if your message reached your audience. In this post, I’ll walk you through how to create a simple Email Open Tracker using Google Apps Script and a sleek front-end interface styled with Tailwind CSS.

You’ll learn how to:

  • Create a clean, user-friendly interface for sending emails
  • Embed an invisible tracking pixel into your emails
  • Log email sending and open events into a Google Sheet for easy tracking
  • Use Google Apps Script web app as both the email sender and the tracking pixel server

What’s Included in This Project?

  1. A responsive HTML front-end UI for entering recipient email, subject, and message.
  2. Google Apps Script backend that:
    • Sends emails with a unique tracking pixel embedded
    • Serves the tracking pixel (a 1×1 transparent GIF)
    • Records email open events triggered when the pixel is loaded
    • Logs all data to a Google Sheet for real-time tracking

The User Interface — Simple, Clean, and Mobile-Friendly

The HTML UI is designed with Tailwind CSS and includes:

  • Input fields for Recipient Email, Subject, and an optional Email Body
  • A blue button to Send Tracked Email
  • A message area that shows status updates (errors, sending, success)

The UI provides immediate feedback, validates inputs, and resets fields after sending an email. It looks professional with rounded corners, subtle shadows, and uses the “Inter” font for clarity and elegance.

<!-- Excerpt from the UI -->
<input type="text" id="recipient" placeholder="e.g., recipient@example.com" />
<button onclick="sendEmail()">Send Tracked Email</button>
<p id="message"></p>

How the Tracking Works — The Invisible Pixel Magic

What’s a Tracking Pixel?

A tracking pixel is a tiny (1×1 pixel) transparent image embedded in an email. When the recipient opens the email, their email client loads this pixel from your server, triggering a request that signals the email has been opened.

How We Use It Here

  • When you send an email, the backend generates a unique ID.
  • The email body includes an invisible <img> tag with a URL pointing back to our Google Apps Script web app, including this unique ID.
  • When the recipient opens the email, the image loads, calling the web app with the unique ID.
  • The script records the open event in the Google Sheet — timestamp, open count, and marks the email status as “Opened.”

Behind the Scenes: Google Apps Script Code

1. Sending the Tracked Email

  • Generates a UUID for the email.
  • Creates a tracking pixel URL with this unique ID.
  • Appends a new row to your Google Sheet with the email details and initial status.
  • Sends the email via GmailApp.sendEmail() with the embedded tracking pixel.

2. Tracking Email Opens

  • When the pixel URL is requested, the script extracts the unique ID.
  • Searches the Google Sheet for that ID.
  • Updates the status to “Opened,” records the open timestamp, and increments the open count.

3. Serving the HTML Front-End

  • The doGet function serves the email sending form when accessed normally.
  • When the pixel request arrives (identified by action=trackOpen), it triggers the tracking logic.

Setting Up Your Spreadsheet

Make sure to create a Google Sheet with a tab named Email Log (or whatever you configure in the script). The columns are expected to be:

TimestampUnique IDStatusOpen TimestampOpen CountRecipient EmailSubject
2025-06-11 12:00abc123Sent0recipient@example.comImportant Update

This sheet will store all the email activity, providing you with a clear record of who received and opened your emails.

How to Deploy

  1. Create a new Google Apps Script project and paste the server-side code.
  2. Create the HTML file (index.html) with the UI code.
  3. Set your spreadsheet URL and sheet name in the script.
  4. Deploy the script as a Web App with access to Anyone, even anonymous (so email clients can load the pixel).
  5. Open the web app URL in your browser to send tracked emails.

Why Use This Tracker?

  • Free and customizable — runs entirely on Google Apps Script with your own Google Sheet.
  • Simple UI — easy to send tracked emails without manual URL manipulation.
  • Privacy friendly — you control the data and no third-party services are involved.
  • Perfect for newsletters, campaigns, or personal use to verify email engagement.

1. HTML Front-End Code (index.html)

This is the user interface where you enter the recipient’s email, subject, and optional message. It’s styled with Tailwind CSS and vanilla CSS for a clean, responsive look.

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <title>Email Open Tracker</title>
  <script src="https://cdn.tailwindcss.com"></script>
  <style>
    body {
      font-family: 'Inter', sans-serif;
      display: flex;
      justify-content: center;
      align-items: center;
      min-height: 100vh;
      background-color: #f0f4f8;
      padding: 1rem;
      box-sizing: border-box;
    }
    .container {
      background-color: #ffffff;
      padding: 2.5rem;
      border-radius: 1.5rem;
      box-shadow: 0 10px 25px rgba(0, 0, 0, 0.1);
      width: 100%;
      max-width: 600px;
      display: flex;
      flex-direction: column;
      gap: 1.5rem;
    }
    input[type="text"],
    textarea {
      width: 100%;
      padding: 0.75rem;
      border: 1px solid #cbd5e1;
      border-radius: 0.75rem;
      font-size: 1rem;
      transition: border-color 0.2s ease-in-out;
    }
    input[type="text"]:focus,
    textarea:focus {
      outline: none;
      border-color: #3b82f6;
      box-shadow: 0 0 0 3px rgba(59, 130, 246, 0.2);
    }
    button {
      background-color: #3b82f6;
      color: white;
      padding: 0.85rem 1.5rem;
      border: none;
      border-radius: 0.75rem;
      font-size: 1.1rem;
      cursor: pointer;
      transition: background-color 0.2s ease-in-out, transform 0.1s ease-in-out;
      box-shadow: 0 4px 10px rgba(59, 130, 246, 0.2);
    }
    button:hover {
      background-color: #2563eb;
      transform: translateY(-2px);
    }
    button:active {
      transform: translateY(0);
    }
    #message {
      text-align: center;
      font-size: 1rem;
      color: #333;
      margin-top: 1rem;
    }
  </style>
</head>
<body>
  <div class="container">
    <h1 class="text-2xl font-bold text-center text-gray-800">Email Open Tracker</h1>

    <div class="input-group">
      <label for="recipient" class="block text-gray-700 text-sm font-medium mb-1">Recipient Email:</label>
      <input type="text" id="recipient" placeholder="e.g., recipient@example.com" class="w-full">
    </div>

    <div class="input-group">
      <label for="subject" class="block text-gray-700 text-sm font-medium mb-1">Subject:</label>
      <input type="text" id="subject" placeholder="e.g., Important Update" class="w-full">
    </div>

    <div class="input-group">
      <label for="body" class="block text-gray-700 text-sm font-medium mb-1">Email Body (optional):</label>
      <textarea id="body" rows="5" placeholder="Your email content here..." class="w-full"></textarea>
    </div>

    <button onclick="sendEmail()">Send Tracked Email</button>
    <p id="message"></p>
  </div>

  <script>
    function sendEmail() {
      const recipient = document.getElementById('recipient').value;
      const subject = document.getElementById('subject').value;
      const body = document.getElementById('body').value;
      const messageDiv = document.getElementById('message');

      if (!recipient || !subject) {
        messageDiv.style.color = '#dc2626'; // red error color
        messageDiv.textContent = 'Please enter recipient email and subject.';
        return;
      }

      messageDiv.style.color = '#1d4ed8'; // blue for processing
      messageDiv.textContent = 'Sending email...';

      // Call Google Apps Script backend function
      google.script.run
        .withSuccessHandler(function(response) {
          if (response.success) {
            messageDiv.style.color = '#16a34a'; // green for success
            messageDiv.textContent = `Email sent successfully! Check your Google Sheet for tracking data.`;
            // Reset input fields
            document.getElementById('recipient').value = '';
            document.getElementById('subject').value = '';
            document.getElementById('body').value = '';
          } else {
            messageDiv.style.color = '#dc2626';
            messageDiv.textContent = `Error: ${response.message}`;
          }
        })
        .withFailureHandler(function(error) {
          messageDiv.style.color = '#dc2626';
          messageDiv.textContent = `An error occurred: ${error.message || error}`;
        })
        .sendTrackedEmail(recipient, subject, body);
    }
  </script>
</body>
</html>

What this does:

  • Creates a centered form with inputs for recipient email, subject, and body.
  • Validates required fields (recipient and subject).
  • On clicking Send Tracked Email, it calls the Google Apps Script function sendTrackedEmail() with the input values.
  • Displays messages for errors, processing, and success.
  • Resets the form after a successful send.

2. Google Apps Script Backend Code (Code.gs)

This script handles sending the email, embedding the tracking pixel, recording data to Google Sheets, and tracking email opens.

// ========== Configuration ==========
const SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1X7GugLa****HFhJo/edit'; // Your Google Sheet URL
const SHEET_NAME = 'Email Log'; // Sheet tab name where email data is stored

// Base64 encoded 1x1 transparent GIF (tracking pixel)
const GIF_BASE64 = 'iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mNkYAAAAAYAAjCB0C8AAAAASUVORK5CYII=';

// ========== Web App Functions ==========

/**
 * doGet handler for web app.
 * If called with action=trackOpen, it records email open event.
 * Otherwise, serves the HTML UI.
 */
function doGet(e) {
  if (e.parameter.action === 'trackOpen') {
    return trackOpen(e);
  } else {
    return HtmlService.createTemplateFromFile('index')
      .evaluate()
      .setTitle('Email Open Tracker')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  }
}

/**
 * Function called when tracking pixel URL is accessed.
 * Logs the email open event in the spreadsheet and returns transparent GIF.
 */
function trackOpen(e) {
  const uniqueId = e.parameter.id;
  const timestamp = new Date();

  Logger.log(`Received trackOpen request for ID: ${uniqueId}`);

  if (uniqueId) {
    const sheet = getSheet();
    if (sheet) {
      const data = sheet.getDataRange().getValues();
      let found = false;

      // Find the row with the matching unique ID
      for (let i = 1; i < data.length; i++) {
        if (data[i][1] === uniqueId) { // Column B = unique ID
          let currentOpenCount = data[i][4] || 0; // Column E = Open Count
          currentOpenCount++;

          // Update the sheet with open info
          sheet.getRange(i + 1, 3).setValue('Opened');               // Status (Column C)
          sheet.getRange(i + 1, 4).setValue(timestamp.toLocaleString()); // Open Timestamp (Column D)
          sheet.getRange(i + 1, 5).setValue(currentOpenCount);       // Open Count (Column E)

          Logger.log(`Email ID ${uniqueId} marked as 'Opened', Count: ${currentOpenCount}`);
          found = true;
          break;
        }
      }
      if (!found) {
        Logger.log(`Unique ID ${uniqueId} not found.`);
      }
    } else {
      Logger.log('Sheet not found. Check SPREADSHEET_URL and SHEET_NAME.');
    }
  } else {
    Logger.log('No unique ID provided in tracking request.');
  }

  // Return the transparent GIF as the pixel
  const bytes = Utilities.base64Decode(GIF_BASE64);
  return ContentService.createTextOutput(bytes)
    .setMimeType(ContentService.MimeType.GIF);
}

/**
 * Sends the email with embedded tracking pixel.
 * Records email details in the Google Sheet.
 * Called from the front-end JS.
 */
function sendTrackedEmail(recipientEmail, subject, body) {
  try {
    const uniqueId = Utilities.getUuid(); // Generate unique ID for this email
    const scriptUrl = ScriptApp.getService().getUrl(); // Get web app URL

    // Construct pixel URL with unique ID and action parameter
    const trackingPixelUrl = `${scriptUrl}?action=trackOpen&id=${uniqueId}`;
    Logger.log(`Tracking pixel URL: ${trackingPixelUrl}`);

    // Create HTML img tag with tracking pixel (hidden)
    const trackingPixelHtml = `<img src="${trackingPixelUrl}" width="1" height="1" style="display:none !important; margin:0 !important; padding:0 !important; font-size:0 !important; line-height:0 !important;" alt="" />`;

    // Combine user message body with tracking pixel
    const emailBody = body ? `${body}<br>${trackingPixelHtml}` : trackingPixelHtml;

    // Append email data to Google Sheet BEFORE sending
    const sheet = getSheet();
    if (sheet) {
      sheet.appendRow([
        new Date().toLocaleString(), // Timestamp
        uniqueId,                    // Unique ID
        'Sent',                      // Status
        '',                          // Open Timestamp (empty at send time)
        0,                           // Open Count
        recipientEmail,              // Recipient Email
        subject                     // Subject
      ]);
      Logger.log(`Logged email details for ID: ${uniqueId}`);
    } else {
      throw new Error('Sheet not found.');
    }

    // Send the email via Gmail with HTML body containing the pixel
    GmailApp.sendEmail(recipientEmail, subject, '', { htmlBody: emailBody });
    Logger.log(`Email sent to ${recipientEmail} with ID: ${uniqueId}`);

    return { success: true, message: 'Email sent and tracking initiated.' };

  } catch (error) {
    Logger.log(`Error sending tracked email: ${error.message}`);
    return { success: false, message: error.message || 'Failed to send email.' };
  }
}

/**
 * Helper: Open spreadsheet and get the configured sheet.
 */
function getSheet() {
  try {
    const spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
    if (!spreadsheet) {
      Logger.log(`Spreadsheet not found at URL: ${SPREADSHEET_URL}`);
      return null;
    }
    const sheet = spreadsheet.getSheetByName(SHEET_NAME);
    if (!sheet) {
      Logger.log(`Sheet "${SHEET_NAME}" not found.`);
      return null;
    }
    return sheet;
  } catch (e) {
    Logger.log(`Error accessing spreadsheet or sheet: ${e.message}`);
    return null;
  }
}

What this does:

  • doGet(e):
    • If called with action=trackOpen&id=..., it handles the pixel request and logs the email open.
    • Otherwise, serves the email sending form (index.html).
  • trackOpen(e):
    • Reads the unique ID from URL parameters.
    • Searches the Google Sheet for this ID.
    • Updates the status to Opened, logs timestamp and increments open count.
    • Returns a 1×1 transparent GIF image (the tracking pixel).
  • sendTrackedEmail(recipientEmail, subject, body):
    • Generates a unique ID.
    • Builds the tracking pixel URL with that ID.
    • Adds the pixel as a hidden <img> tag inside the email body.
    • Logs the email details in the Google Sheet with status “Sent.”
    • Sends the email with GmailApp.sendEmail().
    • Returns success or error status back to the front-end.
  • getSheet():
    • Opens the Google Sheet by URL and fetches the specified sheet tab.
    • Returns the Sheet object or null if not found.

3. How It All Works Together: Step-by-Step Flow

  1. User opens the web app URL → The form (index.html) loads.
  2. User fills out recipient, subject, and optionally the body, then clicks Send Tracked Email.
  3. Front-end JS calls sendTrackedEmail Apps Script function with these values.
  4. Apps Script generates a unique ID, builds a tracking pixel URL, and appends a new row to the spreadsheet with email info.
  5. Apps Script sends the email with the embedded invisible tracking pixel.
  6. When recipient opens the email, their email client loads the tracking pixel image by requesting the pixel URL from the Apps Script web app.
  7. Apps Script receives the pixel request via doGet with action=trackOpen&id=....
  8. The script finds the email record by unique ID in the spreadsheet, marks the status as “Opened,” logs timestamp, and increments open count.
  9. You can open the Google Sheet to view all emails sent, their statuses, when opened, and how many times opened.

4. What You Need to Do to Get This Running

  1. Create a new Google Spreadsheet — Name the tab Email Log (or change SHEET_NAME in the script).
  2. Create a new Google Apps Script project linked to your Google account.
  3. Add two files:
    • index.html with the HTML front-end code.
    • Code.gs with the backend Apps Script code.
  4. Update the constant SPREADSHEET_URL in the script with your actual Google Sheet URL.
  5. Deploy the Apps Script project as a Web App:
    • In Apps Script editor:
      Publish → Deploy as web app
      • Execute as: Me (your account)
      • Who has access: Anyone, even anonymous
  6. Open the web app URL from the deployment. You’ll see the email form.
  7. Send a test email to yourself or someone else.
  8. Open the email in the recipient inbox and then check your Google Sheet to see the “Opened” status and timestamp.

5. Important Notes & Considerations

  • Tracking pixel relies on images loading in the recipient’s email client. Some clients block images by default, so open tracking may not always be 100% reliable.
  • Ensure the spreadsheet and Apps Script project have proper permissions so the script can read/write to the spreadsheet.
  • Keep the deployed web app URL private; anyone who accesses the pixel URL with the unique ID can trigger an open event.
  • You can customize or extend the sheet with additional columns (e.g., email body preview, link clicks, etc.).

Summary

This Email Open Tracker project:

  • Uses a modern front-end for easy email sending.
  • Embeds a hidden pixel to detect when emails are opened.
  • Logs data in a Google Sheet for transparent tracking.
  • Runs entirely on Google Apps Script and your Google Drive, no external services.