Google Sheets Email Open Tracking: What Works After Apple MPP
On this page, 9 sections
Google Sheets email open tracking usually means a tracking pixel: a tiny image in the email whose address points at a server that records each time it is loaded. In a sheet-driven mail merge, that server is the hard part, and the numbers it gives back are weaker than they look.
Apple says its Mail Privacy Protection stops senders from using invisible pixels to collect information about the reader, so a sender cannot know when an email is opened, and it masks the reader’s IP address. That one sentence changes what an open count means.
This post explains the mechanics, what a Sheets-based setup can and cannot log, and a script that logs link clicks per recipient instead. If you are still working out how many emails you can send, start with Gmail sending limits for mail merge.
How a tracking pixel works
The email contains an image tag. The image source is a URL that includes an ID for the recipient. When the mail app loads the image, a request reaches the tracking server, which writes a row: recipient ID, time, and whatever the request carried. The row becomes an open.
Three things have to be true for that to count as a person reading the email: the app loads remote images, it loads them when the person opens the message, and it loads them from the person’s own device.
Apple’s newsroom text says it stops senders from using invisible pixels to collect information and masks the IP address so it cannot be used to work out location.
What Gmail and Apple documents tell you
Apple’s announcement describes the feature for the Mail app, and its Mail user guide has a page on using Mail Privacy Protection. Gmail’s help page on turning images on or off says Google scans images for signs of suspicious content before you receive them.
What that means in practice: a pixel hit can come from a person or from software, and Apple’s feature is built to stop senders using these pixels at all. A count of hits is a count of loads, and I cannot tell you from these sources what share of them are people.
The Apps Script limit I could not get around
The obvious idea is to host the pixel in Apps Script. The web apps guide says a web app needs doGet(e) or doPost(e) returning an HtmlOutput or TextOutput object. Neither is an image.
I could not establish from the guide that a web app can return image bytes, so I do not build a pixel on it. That is a gap in what is documented, not proof it cannot be done.
A page that returns text still works for something else: a link. If the email contains a link to your web app with the recipient ID in the query string, a click reaches doGet, and the script can log it. A click is a person acting, which a pixel load is not.
function doGet(e) {
const id = (e && e.parameter && e.parameter.id) || "unknown";
SpreadsheetApp.getActive().getSheetByName("Log")
.appendRow([new Date(), id, "click"]);
return ContentService.createTextOutput("ok");
}I ran node –check on this, then executed it with mocked SpreadsheetApp and ContentService. A call with parameter id=r42 appended a row to the Log sheet with the id and the word click, and a call with no parameters logged unknown. Both returned the text ok. I did not deploy it, so the first real click through your own deployment is the test.
Putting a tracked link in a mail merge
The MailApp reference documents sendEmail for sending from a script. In a loop over sheet rows, you build the link for each row from the web app URL and the row’s ID, put it in the message body, and send.
The earlier post on this site, how to send mass email in bulk with Gmail using Sheets, has the sending loop. Only the link line is new:
const link = WEB_APP_URL + "?id=" + encodeURIComponent(row.id);
const body = "Hello " + row.name + ",\n\nThe details are here: " + link;WEB_APP_URL is the address Apps Script gives you after deployment. Because the ID goes in the query string, use an ID you made up (a row number or a short code), never the email address. Anyone can edit a URL, so treat the log as indicative, not as proof.
What to track instead of opens
| Signal | What it needs | How reliable, from the sources |
|---|---|---|
| Pixel load | Image URL on a server you control | Apple says its feature stops senders using invisible pixels for this |
| Link click | A web app or redirect that logs the ID | A deliberate action, but only if the reader clicks |
| Reply | Nothing from you | Strongest; you count it yourself |
Honest limits of this setup
- A click log shows who clicked, not who read. Many readers never click.
- The log sheet grows by a row per click, so archive it before it gets large.
- If a recipient forwards the email, the forwarded link carries the original ID.
- Link scanners can load a link before a person sees it. I did not test whether any scanner does this to your mail.
For a small, personal send, the strongest signal is still a reply. Count replies in a column and treat everything else as background.
Test on yourself before you send to anyone
Send the merge to two addresses you own, on different mail apps, click the tracked link in each, and look at the Log sheet. You should see two rows with different IDs.
If a row appears before you click, something loaded the link on its own, and that is useful to know before you read a real list. I did not run this test on a live deployment, so the first one is yours.
The sending side has its own rules, covered in sending the same email to multiple recipients separately in Gmail: one message per person, and a daily ceiling that depends on the route. A tracked link adds nothing to the recipient count, but a bounce still shows up in your inbox, not in the Log sheet.
Keep the Log sheet’s columns simple: time, id, event. Add a status column in your main sheet and fill it from the log with a lookup, so each row shows clicked or not yet.
Resist adding more fields until you know which question you are answering. A log you can read at a glance gets used. One that needs a pivot table to interpret tends to be abandoned.
If a number looks too good, assume a load happened without a person. A click seconds after sending may not be a reader at all. Look at the gap between send time and click time before you celebrate.
Do this today
- Decide what action proves interest: a reply, a booking, a click.
- Add an id column to the sheet and a Log sheet with timestamp, id and event.
- Deploy the Apps Script as a web app and open the URL once to see a row appear.
- Add the tracked link to the merge and send to yourself first.
Resources
- Apple newsroom: iOS 15 privacy features, including Mail Privacy Protection (read on 11 October 2026)
- Apple Mail user guide: use Mail Privacy Protection (read on 11 October 2026)
- Gmail Help: turn images on or off (read on 11 October 2026)
- Apps Script web apps guide (read on 11 October 2026)
- Apps Script MailApp reference (read on 11 October 2026)