Tracking Email Opens with Gmail, Sheets, and Apps Script

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.

Edit: The original version of this article had created two script files to get the job done. I realized after the fact that you could do it all in one file. This revised version reflects that. In addition, I’ve added screen grabs from the new Google Apps Script editor interface

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:

Setup three tabs

For the Logs tab, set up the following columns:

Logs tab’s columns

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’s columns

For the Drafts tab, set up the following 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

Create a shape for Send:

Create send button

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

Create refresh button

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

The action 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:

Go to Script Editor

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 (new on the left, legacy on the right)

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.

Running getDrafts

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 is required to let the script run

Choose the appropriate Google Account

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.

Click Advanced to reveal the option to authorize the script to run

Click Go To Email Tracker Tool (unsafe) to finish up the authorization

Allow access to finish the authorization process and run the script

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

Drafts imported from my Drafts folder

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

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

Assign getDrafts

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

geDrafts script is running

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).

The below function will power the Web App functionality:

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.

Deploy in the Google Apps Script editor (new on the left, legacy on the right)

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 the web app to be run as you and accessible to anyone, even anonymous (new on the left, legacy on the right)

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:

Copy the current URL of the web app (new on the left, legacy on the right)

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.

Final Result

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.