Track Email Opens in Google Sheets with Apps Script

by

Want to know who opens the emails you send from Google Sheets? In this post, we’ll build a pixel-based email open tracker using Google Apps Script and log opens directly into your spreadsheet.

What you’ll learn:

  • How to send emails with an invisible tracking pixel
  • How to deploy your script as a web app to collect opens
  • How to log sends and opens in separate sheets
  • Best practices for reliability and privacy

1. Project Setup

  1. Create a new Google Sheets file and note its ID (the long string in the URL).
  2. In that sheet, add two tabs: Email and Logs.
    • Email columns: ID | Timestamp | Recipient | Subject | Preview | Open Count
    • Logs columns: Timestamp | ID | Recipient | Subject | Parameters
  3. Open Extensions ▶ Apps Script to start coding.

2. Server-side Code (Code.gs)

Paste the following into Code.gs. Comments explain each section.

// ─── Configuration ────────────────────────────────────────────────────────────
const SSID        = 'YOUR_SPREADSHEET_ID';    // replace with your Sheet ID
const SHEET_EMAIL = 'Email';                  // sheet for sent emails
const SHEET_LOGS  = 'Logs';                   // sheet for open logs

// ─── Menu & Dialog ─────────────────────────────────────────────────────────────
function onOpen() {
  // Adds "Emailer▶Send Email" to the spreadsheet menu
  SpreadsheetApp.getUi()
    .createMenu('Emailer')
    .addItem('Send Email', 'showDialog')
    .addToUi();
}

function showDialog() {
  // Opens a modal dialog using our HTML form
  const html = HtmlService
    .createHtmlOutputFromFile('index')
    .setWidth(800)
    .setHeight(600);
  SpreadsheetApp.getUi().showModalDialog(html, 'Send Tracked Email');
}

// ─── Sending Emails with Pixel ─────────────────────────────────────────────────
function sender(formData) {
  // Generate a unique ID for this send
  const id      = generateId();
  const email   = formData.email;
  const subject = formData.subject;

  // Build HTML body plus tracking pixel
  const body = formData.message + buildTrackerPixel(id, subject, email);

  // 1) Log the send in the "Email" sheet
  const sheet = SpreadsheetApp
    .openById(SSID)
    .getSheetByName(SHEET_EMAIL);
  sheet.appendRow([
    id,
    new Date(),
    email,
    subject,
    formData.message
      .replace(/<[^>]+>/g, '')
      .slice(0,100), // plain‑text preview
    0  // initial open count
  ]);

  // 2) Send the email
  MailApp.sendEmail({
    to: email,
    subject: subject,
    htmlBody: body
  });
}

// Generates a base‑36 timestamp ID
def generateId() {
  return (new Date().getTime()).toString(36);
}

// Returns an <img> tag that points at our web app, passing id/email/subject
function buildTrackerPixel(id, subject, email) {
  const WEBAPP_URL = 'YOUR_DEPLOYED_WEBAPP_URL';
  const params = [
    `id=${encodeURIComponent(id)}`,
    `em=${encodeURIComponent(email)}`,
    `sub=${encodeURIComponent(subject.replace(/'/g,''))}`
  ].join('&');
  return `<img src="${WEBAPP_URL}?${params}" width="1" height="1" style="display:none">`;
}

// ─── Tracking Endpoint ─────────────────────────────────────────────────────────
function doGet(e) {
  // Log parameters each time the pixel URL is fetched
  const ss   = SpreadsheetApp.openById(SSID);
  const logs = ss.getSheetByName(SHEET_LOGS);

  const id    = e.parameter.id;
  const email = e.parameter.em;
  const sub   = e.parameter.sub;

  // 1) Append a new log row
  logs.appendRow([
    new Date(),
    id,
    email,
    sub,
    JSON.stringify(e.parameter)
  ]);

  // 2) Find the matching send and increment its count
  const emailSheet = ss.getSheetByName(SHEET_EMAIL);
  const data       = emailSheet.getDataRange().getValues();
  data.forEach((row, i) => {
    if (row[0] === id) {
      const cell = emailSheet.getRange(i+1, 6); // 6th column: Open Count
      cell.setValue(cell.getValue() + 1);
    }
  });

  // 3) Return a 1×1 transparent GIF so the image renders properly
  const pixelData = Utilities.base64Decode(
    'R0lGODlhAQABAIABAP///wAAACwAAAAAAQABAAACAkQBADs='
  );
  return ContentService
    .createBinaryOutput(pixelData)
    .setMimeType(ContentService.MimeType.GIF);
}

What’s happening here?

  • onOpen & showDialog: Add custom menu and display an HTML form.
  • sender(): Logs each send (with unique ID) and appends a tracking pixel to your email.
  • generateId(): Ensures each email has a unique identifier.
  • buildTrackerPixel(): Crafts an <img> tag whose URL triggers the doGet handler each time it’s loaded.
  • doGet(e): Runs on each pixel request, appends a log entry, and updates the open count in your spreadsheet.
  • Transparent GIF: Responds with a 1×1 transparent image so email clients load it invisibly.

3. Client-side HTML (index.html)

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    body { font-family: Arial,sans-serif; padding: 20px }
    label { display: block; margin-top: 10px }
    input, textarea { width: 100%; box-sizing: border-box; }
    button { margin-top: 12px; padding: 8px 16px }
  </style>
</head>
<body>
  <h2>Send a Tracked Email</h2>

  <label>To:
    <input type="email" name="email" required>
  </label>

  <label>Subject:
    <input type="text" name="subject" required>
  </label>

  <label>Message (HTML):
    <textarea name="message" rows="6" required></textarea>
  </label>

  <button id="sendBtn">Send</button>

  <script>
    document.getElementById('sendBtn').addEventListener('click', () => {
      const form = document.forms[0];
      google.script.run
        .withSuccessHandler(() => google.script.host.close())
        .sender({
          email:   form.email.value,
          subject: form.subject.value,
          message: form.message.value
        });
    });
  </script>
</body>
</html>

Explanation:

  • Collects recipient, subject, and HTML message.
  • Calls the server-side sender() function via google.script.run.
  • Closes the dialog on success.

4. Deploying Your Tracker

  1. Publish ▶ Deploy as web app.
  2. Choose “Anyone, even anonymous” for access so pixel loads work in all email clients.
  3. Copy the Web App URL and replace YOUR_DEPLOYED_WEBAPP_URL in buildTrackerPixel().
  4. Reload your sheet to see the Emailer menu.
  5. Send emails and watch the Email and Logs sheets fill up as recipients open them.

5. Tips & Considerations

  • Caching: Consider CacheService to throttle repeated opens from the same user/IP.
  • Privacy: Inform recipients about tracking to comply with privacy regulations.
  • Reliability: Many email clients block images by default—track opens are approximate.
  • Error Handling: Wrap URL parsing and sheet writes in try/catch to avoid silent failures.

  1. Send emails with a 1×1 tracking pixel
  2. Log each send (ID, timestamp, recipient, subject, body, open‐count) in an “Email” sheet
  3. Record every open (timestamp, ID, email, subject) in a “Logs” sheet and increment the open‐count in “Email”

1. Server-side Code (Code.gs)

// ───────────────────────────────────────────────────────────────────────────────
// Configuration
// ───────────────────────────────────────────────────────────────────────────────
const SSID       = '1X7XIFi****9fHxBX8'; // ← your Spreadsheet ID
const SHEET_EMAIL = 'Email';  // Sheet where sends are recorded
const SHEET_LOGS  = 'Logs';   // Sheet where opens are recorded

// ───────────────────────────────────────────────────────────────────────────────
// 1) Add a custom menu to launch the send dialog
// ───────────────────────────────────────────────────────────────────────────────
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Emailer')
    .addItem('Send Email', 'showDialog')
    .addToUi();
}

// ───────────────────────────────────────────────────────────────────────────────
// 2) Show a modal dialog for composing your email
// ───────────────────────────────────────────────────────────────────────────────
function showDialog() {
  const html = HtmlService
    .createHtmlOutputFromFile('index')
    .setWidth(800)
    .setHeight(600);
  SpreadsheetApp.getUi().showModalDialog(html, 'Send Tracked Email');
}

// ───────────────────────────────────────────────────────────────────────────────
// 3) Sender: called from client HTML; appends row & sends email with pixel
// ───────────────────────────────────────────────────────────────────────────────
function sender(formData) {
  const id      = generateId();
  const email   = formData.email;
  const subject = formData.subject;
  const body    = formData.message +
                  buildTrackerPixel(id, subject, email);

  // 3a) Log the send
  const sheet = SpreadsheetApp.openById(SSID).getSheetByName(SHEET_EMAIL);
  sheet.appendRow([
    id,
    new Date(),
    email,
    subject,
    formData.message.replace(/<[^>]+>/g, '').slice(0,100), // plain preview
    0  // initial open count
  ]);

  // 3b) Send
  MailApp.sendEmail({
    to: email,
    subject: subject,
    htmlBody: body
  });
}

// ───────────────────────────────────────────────────────────────────────────────
// 4) Build the HTML <img> tag pointing at your deployed doGet URL
// ───────────────────────────────────────────────────────────────────────────────
function buildTrackerPixel(id, subject, email) {
  // Replace this URL with your published Web App URL (see step 5)
  const WEBAPP_URL = 'https://script.google.com/macros/s/AKfycbwqUtdrRjDhcDSml012sKNMe-28OTmusmUczGFNiXE2M_PSTYKl/exec';
  const params = [
    'id='  + encodeURIComponent(id),
    'em='  + encodeURIComponent(email),
    'sub=' + encodeURIComponent(subject.replace(/'/g,'')),
  ].join('&');
  return `<img src="${WEBAPP_URL}?${params}" width="1" height="1" style="display:none">`;
}

// ───────────────────────────────────────────────────────────────────────────────
// 5) Tracking endpoint: doGet logs the open and increments count
// ───────────────────────────────────────────────────────────────────────────────
function doGet(e) {
  const ss    = SpreadsheetApp.openById(SSID);
  const logs  = ss.getSheetByName(SHEET_LOGS);
  const email = e.parameter.em;
  const sub   = e.parameter.sub;
  const id    = e.parameter.id;

  // 5a) Append to Logs
  logs.appendRow([
    new Date(),
    id,
    email,
    sub,
    JSON.stringify(e.parameter)
  ]);

  // 5b) Increment open count in Email sheet
  const sheet = ss.getSheetByName(SHEET_EMAIL);
  const data  = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === id) {
      const cell = sheet.getRange(i+1, /* open‐count col */ 6);
      cell.setValue(cell.getValue() + 1);
      break;
    }
  }

  // 5c) Return a 1×1 transparent GIF so the browser actually loads an image
  const pixel = Utilities.newBlob(
    Utilities.base64Decode(
      'R0lGODlhAQABAIABAP///wAAACwAAAAAAQABAAACAkQBADs='
    ), 'image/gif'
  );
  return ContentService
    .createBinaryOutput(pixel)
    .setMimeType(ContentService.MimeType.GIF);
}

// ───────────────────────────────────────────────────────────────────────────────
// Helper: generate a unique ID for each send
// ───────────────────────────────────────────────────────────────────────────────
function generateId() {
  return (new Date().getTime()).toString(36);
}

2. Client-side HTML (index.html)

t<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    body { font-family: Arial, sans-serif; padding: 20px; }
    label { display:block; margin-top:10px; }
    input, textarea { width:100%; box-sizing:border-box; margin-top:4px; }
    button { margin-top: 12px; padding:8px 16px; }
  </style>
</head>
<body>
  <h2>Send a Tracked Email</h2>

  <label>Recipient Email
    <input type="email" name="email" required>
  </label>

  <label>Subject
    <input type="text" name="subject" required>
  </label>

  <label>Message (HTML)
    <textarea name="message" rows="6" required></textarea>
  </label>

  <button id="sendBtn">Send Email</button>

  <script>
    document.getElementById('sendBtn').addEventListener('click', e => {
      e.preventDefault();
      const form = document.querySelector('body');
      const data = {
        email:   form.email.value,
        subject: form.subject.value,
        message: form.message.value
      };
      google.script.run
        .withSuccessHandler(() => google.script.host.close())
        .sender(data);
    });
  </script>
</body>
</html>

3. Deployment Steps

  1. Copy both files into your Apps Script project (Code.gs and index.html).
  2. Deploy your project as a Web App (Publish ▶ Deploy as web app…), selecting “Anyone, even anonymous” if you want pixel GETs to work without login.
  3. Copy the Web App URL and replace WEBAPP_URL in buildTrackerPixel().
  4. Reload your spreadsheet; use Emailer ▶ Send Email to compose and send.
  5. After your recipients open the email (images enabled), their client will fetch the pixel URL, triggering doGet(e), which logs the open and bumps the counter.