Tracking Email Opens with Gmail, Sheets, and Apps Script

Lee-on Pedahzur
May 13 · 12 min read

This detailed walkthrough will let you build a tracker for email opens using Gmail, Google Sheet, and Google Apps Script. Functionality is kept to a minimum, but I’m happy to expand for other use cases if you leave a comment.

By the end of this tutorial, you will be able to do the following:

  1. Select & send email drafts that you want to be tracked from Google Sheets
  2. See the date and time your tracked emails have been opened

But first, a brief explanation of how tracking email-opens works and why you may see possible false-positive or false-negative results.

To track whether an email was opened or not, most services use a web beacon or pixel.

Essentially, a small snippet of code is embedded within an email. That code tells the email client that there is an image stored at a certain URL and that it needs to download it as a part of the email. When the email client downloads the email instead of getting an image, it triggers a recording of information. That trigger serves as the basis of knowing who opened what email and when.

This can lead to a false-negative when an email client is set up to not download images by default, and thus an email-open event doesn’t get recorded unless the user enables images for the email. More commonly, it leads to a lot of false-positives when an email is downloaded and scanned for viruses by the email client or anti-virus software. A false-positive can also be triggered if the email is forwarded or opened by the sender from their Sent folder.

Before diving in, hat/tip to Amit Agarwal’s post that served as a basis for this tutorial. Since it is best practice to avoid sending Personally Identifiable Information (PII) to Google Analytics and setting the reporting back into Google Sheets makes the code more cumbersome, I decided to replicate the functionality using a simple Web App. That way, everything is contained within Google Sheets with no need to get familiar with (or run afoul of) Google Analytics.

Step One: Set Up the Google Sheet Spreadsheet

We’ll use Google Sheets to send email drafts and keep track of email opens, so begin by creating a new Google Sheet and set up three tabs: (1) Drafts, (2) Summary, (3) Logs, to look like this:

The three tabs of the spreadsheet
The three tabs of the spreadsheet

For the Logs tab, set up the following columns:

Column headers
Column headers

For the Summary tab, we will use formulas to retrieve and summarize information from the Logs sheet, which will record every single ping of the tracking pixel.

In cell A1, we will use the UNIQUE() formula to get the unique combination of Subject and To lines:=UNIQUE(Logs!B1:C).

Then in cell C2, we will use a MAXIFS function to find the latest date and time that the corresponding Subject/To tracking pixel was pinged: =MAXIFS(Logs!$A$2:$A,Logs!$B$2:$B,A2,Logs!$C$2:$C,B2). You may need to format the cell to Date/Time format when the first email opens.

Lastly, in cell D2 we will use a COUNTIF function to count all the times the corresponding Subject/To tracking pixel was pinged: =COUNTIFS(Logs!$B$2:$B,A2,Logs!$C$2:$C,B2) .

These two functions will need to be dragged down as new emails get recorded in the Logs tab.

Summary tab setup
Summary tab setup

For the Drafts tab, set up the following columns:

Drafts Tab’s Columns
Drafts Tab’s Columns

In the Drafts tab, we will also create two buttons that will help us retrieve our Gmail Drafts and that will help us send those drafts as emails. To do that, we need to insert a Drawing:

Insert Drawing
Insert Drawing

Create a shape for Send:

Send Button
Send Button

Repeat the same steps to create another drawing that will serve as the Refresh button:

Refresh Button
Refresh Button

Make sure that they are two separate drawings and place them near the last column on the Drafts tab:

Send & Refresh Buttons
Send & Refresh Buttons

To give these two buttons the functionality of retrieving drafts and sending emails, we will use Google Apps Script, which is a JavaScript-based coding platform that is built to power up the Google Suite in a similar way that Visual Basic for Application powers up Microsoft Office.

To access the Google Apps Script editor, we’ll open the Script Editor from within the Google Sheets top menu:

Script editor in the Tools menu
Script editor in the Tools menu

A new tab will open up with an empty code editor where we will write all of the functions we need to make the spreadsheet act as our control center.

Google Apps Script Editor
Google Apps Script Editor

The first function we’ll create is the one that will retrieve your drafts from Gmail (feel free to delete the default myFunction code):

function getDrafts(){

// Store a reference to the current spreadsheet that the script is tied to
var spreadSheet = SpreadsheetApp.getActive()
// Store a reference to the Drafts tab
var draftSheet = spreadSheet.getSheetByName("Drafts")
//Reset the Send? column by clearing it all, including the data validation (checkbox)
draftSheet.getDataRange().clear().clearDataValidations()
//Retrieve from Gmail the full list of drafts in the Drafts folder
var drafts = GmailApp.getDrafts()
//Set up the array that will output the information back to the sheet and include the headers as the first row
var draftsOutput = [["Subject","To","ID","Send ?"]]

//Loop throuhg all the drafts return from Gmail
for (var i = 0; i<drafts.length; i++){
//Store the current draft for ease and clarity
var draft = drafts[i]
//Add the Subject, To line, unique ID, and a false value to uncheck the checkbox to the output array
draftsOutput.push([draft.getMessage().getSubject(),draft.getMessage().getTo(), draft.getId(),false])
}

//Using the array’s length and breadth, pass the array of drafts to the Drafts sheet
draftSheet.getRange(1, 1,draftsOutput.length,draftsOutput[0].length).setValues(draftsOutput)
//Create a new data validation in the form of checkboxes in the last coulumn of the sheet (the "Send ?" column)
draftSheet.getRange(2,draftsOutput[0].length,draftsOutput.length).setDataValidation(SpreadsheetApp.newDataValidation()
.setAllowInvalid(true)
.requireCheckbox()
.build());

}

Make sure to save your script, and name it appropriately, and then use the dropdown to select the getDrafts function (if not already selected) and the play button to run the function.

Image for post
Image for post

You will need to go over an authorization process so the script can access your Gmail since we are going to be using it to retrieve and send emails.

Authorization alert
Authorization alert
Image for post
Image for post

Here you may encounter a scary window that will tell you that “This app hasn’t been verified by Google yet. Only proceed if you know and trust the developer.” Go ahead and choose the Advanced option to move on with the authorization. The reason for the warning is that Google doesn’t know what the app is trying to do and if you are not a registered developer, it won’t even trust you with your own data.

App not verified warning
App not verified warning
Advanced option
Advanced option
Authorization screen
Authorization screen

Once the function is done running, you will see the following in the Drafts tab (based on whatever is in your Drafts folder):

getDrafts Function Result
getDrafts Function Result

Next, we’ll link the function to the Refresh button by clicking on it and then on the three dots on the upper right-hand side of it in order to open a menu that will allow us to assign a script to it.

Assign script menu
Assign script menu

In the pop-up box that opens, we’ll put getDrafts as the script to execute whenever we click on Refresh.

Assigning getDrafts
Assigning getDrafts

Go ahead and click on Refresh to see that it is running.

Script running alert
Script running alert

Next, we’ll go back to the Script Editor window where we had the getDrafts function, and below it, we’ll create another function that will generate a unique URL for each email which will trigger the webhook web app we’ll create in Step Two. The description of what each line is doing is commented out (//) similar to the first function we built.

function getTrackingGIF(email, subject) {
// Create a url based on the Email Tracker Webhook web app's URL and attaching two URL paramaters
// that will pass the Subject and the To line of the email to the web app. Replace [WEBAPP URL] below with the URL of your web app
var imgURL = "[WEBAPP URL]"
// encode the Subject to assure that it will be passed properly as a part of a URL
+ "?esubject=" + encodeURIComponent(subject.replace(/'/g, ""))
// encode the To line to assure that it will be passed properly as a part of a URL
+ "&eto=" + encodeURIComponent(email);

//Return an HTML tag for a 1x1 pixel image with the image source as the web app's URL
return "<img src='" + imgURL + "' width='1' height='1'/>";
}

Later, we’ll replace the URL in the square brackets [] (including the brackets themselves) with the URL of the web app we’ll build. With the getTrackingGIF function, we’re creating an HTML tag that will be appended to an email we’ve already drafted in Gmail.

Since the comments are straight forward, we’ll move on to the send function which will go after getTrackingGIF. The sendMail function will retrieve the specific draft we are interested in sending, attach the HTML tracker tag, and send the draft.

function sendMail(draftId) { 
// Retrieve a reference to the specific draft by the unique ID, draftId
var draft = GmailApp.getDraft(draftId)
// Store the Message object of the draft
var message = draft.getMessage()
// Store the actual contents of the drafts, or the body of the message
var body = message.getBody();

// Add the HTML code ofr the tracking image by calling the getTrackingGIF function
// and passing the draft's To line and Subject line
body += getTrackingGIF(message.getTo(), message.getSubject());

// Update the draft with the new body while making sure that all other info remains the same
draft.update(message.getTo(), message.getSubject(), body, {
htmlBody: body,
cc: message.getCc(),
bcc: message.getBcc(),
attachments: message.getAttachments()})

// Send the draft
draft.send()
// Refresh the updated Drafts folder to show the draft was sent
getDrafts()
}

The function does the following:

  1. Retrieves the draft using the unique ID we will pass to it (more detail in the next function);
  2. Sends the To line and Subject line to the getTrackingGIF function to get the customized HTML tag;
  3. Adds the HTML tag to the body of the email;
  4. Updates the draft with the new body while making sure all other aspects of the draft remain the same; and
  5. Lastly, it sends the draft and refreshes the list of drafts on the Drafts sheet by calling the getDrafts function.

The last function we need to create is the one that will be executed once we click on the Send button so we can send multiple drafts at the same time.

function sendButton() {
// Store a reference to the current spreadsheet that the script is tied to
var spreadSheet = SpreadsheetApp.getActive()
// Store a reference to the Drafts tab
var draftSheet = spreadSheet.getSheetByName("Drafts")
// Store all the information in the Range of cells that has data within the Drafts tab
var draftRange = draftSheet.getDataRange().getValues()

// Loop through all the rows in the Drafts tab
for (var i=0; i<draftRange.length; i++) {
// check if any row has a check mark (is true) in the Send? column,
// and pass the unique id of the draft to the sendMail function to send it if true
if (draftRange[i][3]==true) sendMail(draftRange[i][2])
}

//Reset the Send? column by clearing its content
draftSheet.getRange(2,4,draftRange.length-1).clearContent()

}

The sendButton function:

  1. Retrieves the table of drafts from the Drafts sheet;
  2. Loops through each row to check if we checked the box for that draft to be sent;
  3. If it is checked, then the ID of the draft is sent to the sendMail function; and finally,
  4. Clears the checkbox column

Finally, we assign the Send button the sendButton function - in the same way we assigned getDrafts to the Refresh button - so it will be triggered when the Send button is clicked.

Before moving on, remember to save (Ctrl/Cmd + S) the file.

Step Two: Create a Webhook to Handle Email Opens

The next thing we need to do is to create a service (web app) that will record the information when an email opens. The web app would act very similarly to how webhooks work, but simpler as we would be using a GET rather than a POST HTTP request. No need to worry if you don’t know/care what it means, it bears no difference in building this tracker.

Suffice it to say that the web app will have a URL we can reference and that would trigger a function to record an email open.

We’re going to leverage Google Apps Script again, but this time build a standalone, versus Google Sheet-linked function, that will be the basis of our web app. The function that we’ll write is a requirement for making this Google Apps Script project into a standalone web app and would be the function that executes when the URL is triggered by the web beacon (the tracking GIF).

Start by logging on to https://script.google.com/home which looks similar to Google Drive, and click on New Project:

Google Apps Script Home
Google Apps Script Home

Name the project however you like, I choose to call it “Email Tracker Webhook” and replace the boilerplate code (the empty myFunction) with the following:

function doGet(e) {
// Store a reference for the tracking spreadsheet
var trackingSpSheet = SpreadsheetApp.openById("[SPREADSHEET_ID]")
// Store a reference for the Logs sheet
var outputSheet = trackingSpSheet.getSheetByName("Logs")
// Get the number of the last row in the Logs sheet with no content
var lastRow = outputSheet.getLastRow()+1

// Set the value of the last row to the current date and the email info
outputSheet.getRange(lastRow, 1,1,3).setValues([[new Date(),e.parameters.esubject,e.parameters.eto]])
// Return a string version of e to comply with the web app requirement
return ContentService.createTextOutput(JSON.stringify(e))
}

Replace the [SPREADSHEET_ID] (including the brackets) placeholder in the first line the function (not the comment line) with the ID of the spreadsheet you created in Step One. The ID is in the URL of the spreadsheet, between the /d/ and /edit parts: https://docs.google.com/spreadsheets/d/[SPREADSHEET_ID]/edit

Now, let’s walk through what the code above is doing:

  1. The function doGet accepts an object e which will have the information we want about which email was opened
  2. We will store a reference to the Google Spreadsheet document we want to have the email opens recorded in using the variable trackingSpSheet. This is where you fill in the appropriate SPREADSHEET_ID.
  3. We will store a reference to the individual sheet called “Logs” in that spreadsheet using the variable outputSheet
  4. We will store the number of the row after the last row that has content on that individual sheet so we can continue to add more information without deleting past entries
  5. We will write the current date and time, the email’s subject, and the email’s recipients (the ones in the To: line) to the sheet
  6. We will return a response that is the content of the variable e in order to comply with Google Apps Script requirements for a Standalone Web App. It will also let us see what information the variable e stores as we test it.

Next, we will set up this Apps Script project to be a standalone app by clicking on Publish and then Deploy as web app from the menu.

Publish menu
Publish menu

Then we will set the options of the web app so it won’t require authentication and can be triggered and allow anyone to have access such that the email clients can reach the web app using a URL.

Deploy as web app dialog box
Deploy as web app dialog box

You’ll now go through a similar authentication process as you did in Step One. By the end of it you’ll see the URL we’ll need to paste in the getTrackingGIF function we built in Step One:

Image for post
Image for post

Step Three: Link the Spreadsheet to the Web App

In this last step, we’ll take the URL of the web app we just created, and we’ll paste it instead of [WEBAPP URL]in the getTrackingGIF function from Step One.

Once this is done, go ahead and create a test draft in Gmail to send to yourself or someone else, hit the Refresh button in the spreadsheet, check the box next to that draft, and click the Send button to send it.

Once the email is opened, the spreadsheet’s Logs and Summary tabs will be updated automatically.

Image for post
Image for post

Now you’re all set to send out tracked emails

If you like this tutorial and want more, let me know in the comments. Questions, issues, concerns? Let me know in the comments.

The Startup

Medium's largest active publication, followed by +720K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store