Build a newsletter sender with open tracking

Northwind sends a monthly studio newsletter, and for a long time the only question after each send was “did anyone actually read it?” A paid email platform answers that, but it also means exporting subscribers, paying per contact, and managing yet another login for a list of a few hundred people.

This automation keeps the whole thing inside Google. One script reads a Subscribers sheet and sends the newsletter through Gmail, embedding a tiny 1x1 tracking pixel in each message. A second script, deployed as a web app, serves that pixel — and every time a recipient’s email client fetches it, the open is logged to an Opens sheet. You get a rough but honest read on engagement without leaving the spreadsheet.

What you’ll need

• A Subscribers sheet with a header row and these columns: email, firstName, joinedAt, unsubscribed. Leave unsubscribed blank for active subscribers; put any truthy value (like TRUE) to skip someone.
• An Opens sheet with a header row and these columns: email, subject, openedAt. The tracker appends to it — you do not fill it in by hand.
• An Apps Script web app deployed with Execute as: Me and Who has access: Anyone, even anonymous — email clients fetch the pixel without any login, so the deployment must accept anonymous requests.
• The web app’s /exec URL, pasted into PIXEL_URL in the send script.

The send script

// The spreadsheet that holds your subscriber list.
const SUBS_SHEET = '1abcSubsSheetId';

// The /exec URL of the deployed tracker web app (see "The tracker" below).
// Every email embeds a pixel pointing here.
const PIXEL_URL = 'https://script.google.com/macros/s/YOUR_DEPLOYMENT/exec';

// The name recipients see in the "From" field.
const SENDER_NAME = 'Northwind Studios';

/**
 * Sends one newsletter to every active subscriber, embedding a unique
 * tracking pixel in each message so opens can be logged later.
 *
 * @param {string} subject  The email subject line.
 * @param {string} html     The newsletter body as an HTML string.
 */
function sendNewsletter(subject, html) {
  const sheet = SpreadsheetApp.openById(SUBS_SHEET).getSheets()[0];

  // 1. Read the whole sheet and split off the header row.
  const [header, ...rows] = sheet.getDataRange().getValues();
  if (!rows.length) {
    Logger.log('No subscribers — nothing to send.');
    return;
  }

  // 2. Map header names to column indexes so the code does not depend
  //    on column order.
  const col = Object.fromEntries(header.map((h, i) => [h, i]));

  let sent = 0;
  for (const row of rows) {
    // 3. Skip anyone who has unsubscribed.
    if (row[col.unsubscribed]) continue;

    const email = row[col.email];
    if (!email) continue;

    // 4. Build a pixel URL carrying this recipient's email and the
    //    subject, so each open can be attributed to a person and a send.
    const pixel = `${PIXEL_URL}?e=${encodeURIComponent(email)}` +
      `&s=${encodeURIComponent(subject)}`;

    // 5. Append the 1x1 pixel to the body. It is invisible, but fetching
    //    it is what records the open.
    const body = `${html}<img src="${pixel}" width="1" height="1" alt="" />`;

    GmailApp.sendEmail(email, subject, '', {
      htmlBody: body,
      name: SENDER_NAME,
    });
    sent++;
  }
  Logger.log('Sent newsletter to ' + sent + ' subscribers.');
}

The tracker (deploy as web app)

// The spreadsheet that collects open events.
const OPENS_SHEET = '1abcOpensSheetId';

// A 1x1 transparent GIF, base64-encoded. Returned for every request so
// the recipient's email client gets a valid (if invisible) image.
const TRANSPARENT_GIF =
  'R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7';

/**
 * Web app entry point. Logs an open event (if the request carries an
 * email and subject) and always returns a 1x1 transparent GIF.
 *
 * @param {Object} e  The event object Apps Script passes to doGet.
 */
function doGet(e) {
  const email = e.parameter.e;
  const subject = e.parameter.s;

  // 1. Only log when both parameters are present — a bare request to the
  //    URL should still return a pixel, just without a logged open.
  if (email && subject) {
    SpreadsheetApp.openById(OPENS_SHEET).getSheets()[0]
      .appendRow([email, subject, new Date()]);
  }

  // 2. Decode the transparent GIF into an image blob.
  const pixel = Utilities.newBlob(
    Utilities.base64Decode(TRANSPARENT_GIF),
    'image/gif');

  // 3. Return it as binary so the email client renders a real image.
  return ContentService.createBinaryOutput(pixel)
    .setMimeType(ContentService.MimeType.GIF);
}

How it works

1. sendNewsletter opens the Subscribers sheet, reads every row, and maps header names to column indexes so the code does not break if columns move.
2. For each subscriber it skips anyone with a truthy unsubscribed value, then builds a pixel URL that carries that person’s email address and the newsletter subject as query parameters.
3. It appends a 1x1 <img> tag pointing at that URL to the bottom of the HTML body and sends the message through Gmail.
4. When the recipient opens the email, their mail client fetches the embedded image — which is a request to the tracker web app.
5. doGet reads the e and s parameters, appends a row to the Opens sheet recording who opened which subject and when, and returns a 1x1 transparent GIF so the client has a valid image to display.

Example run

Suppose the Subscribers sheet looks like this:

email	firstName	joinedAt	unsubscribed
[email protected]	Ava	2025-01-12
[email protected]	Ben	2025-02-03	TRUE
[email protected]	Cara	2025-03-20

Calling sendNewsletter('Studio news — July', '<h1>Hello!</h1>...') sends to Ava and Cara and skips Ben. As recipients open the email over the following days, the Opens sheet fills in:

email	subject	openedAt
[email protected]	Studio news — July	2025-07-15 09:41
[email protected]	Studio news — July	2025-07-15 14:08
[email protected]	Studio news — July	2025-07-16 08:02

Note Ava appears twice — the pixel fires on every open, so the same person can generate several rows. Count distinct emails per subject for an open rate.

Run it

This is an on-demand job — you run it when a newsletter is ready.

1. Deploy the tracker first: Deploy > New deployment > Web app, with Execute as: Me and Who has access: Anyone, even anonymous. Copy the /exec URL into PIXEL_URL in the send script.
2. In the editor, call sendNewsletter with your subject and HTML body — the simplest way is a small wrapper function you edit each month.
3. Approve the authorisation prompt the first time.

If you redeploy the web app and the URL changes, update PIXEL_URL or new sends will track to the wrong place.

Watch out for

• Many email clients block external images by default, and privacy features like Apple Mail Privacy Protection pre-fetch images on the user’s behalf. Opens are always undercounted in one direction and inflated in the other — treat them as a trend, not a precise count.
• The pixel fires on every open, so one engaged reader produces multiple rows. Deduplicate by email per subject before reporting an open rate.
• Comply with opt-out law. Include a visible unsubscribe link in the body that flips the unsubscribed column, and honour it on the next send.
• Gmail caps how many recipients you can email per day (around 100 on a consumer account, more on Workspace). A large list will hit the quota — send in batches across days, or use a Workspace account.
• The pixel URL exposes subscriber emails to anyone who inspects the email source, and the Opens sheet is written under your account — keep it private.