In the last blog, we talked about how to setup Email Marketing using Google Apps Script. In this blog, you’ll learn to track whether your emails are opened by the recipients or not. This can be implemented using Google Apps Script and a tracking pixel. Tracking the email opening is important to measure the success of your email marketing campaign but you have to make sure that you maintain the privacy of the recipients in every way possible.

Contents

• Prerequisites
• Adding Status Column
• Writing Email Tracking Code
• Deploying as Web App
• Adding Tracking Pixel in Email
• Results

Prerequisites

Before getting started, follow all the steps discussed in setup Email Marketing using Google Apps Script.

Adding Status Column

Once you have set up the Google Sheet, add a status column to it. This column will be used to track the email openings.

Enter the user details in Google Sheet

Writing Email Tracking Code

Now is the time to add some code to the script from the previous blog.

You will add an <img> tag in your HTML file and specify its width="0" and height="0". You’ll add your script URL along with some query parameters in the src attribute. This kind of image is known as tracking pixel.

When the recipient will open the email, a GET request will be sent to the URL specified in the src attribute. You’ll handle this GET request in your Google Apps Script and update the Google Sheet based on the query parameters.

In the Main.gs, add template.email = email after template.name = name line in the sendEmails function:

function sendEmails(mail_template='content',
                    subject='Testing my Email Marketing') {
  
  // get the active spreadsheet and data in it
  const id = SpreadsheetApp.getActiveSpreadsheet().getId()
  const sheet = SpreadsheetApp.openById(id).getActiveSheet()
  const data = sheet.getDataRange().getValues()
  
  // iterate through the data, starting at index 1
  for (let i = 1; i < data.length; i++) {
    const row = data[i]
    const email = row[0]
    const name = row[1]
    
    // check if you can send an email
    if (MailApp.getRemainingDailyQuota() > 0) {
        
      // populate the template
      let template = HtmlService.createTemplateFromFile(mail_template)
      template.name = name
      template.email = email // add this line
      const message = template.evaluate().getContent()
      
      GmailApp.sendEmail(
        email, subject, '',
        {htmlBody: message, name: 'RavSam Team'}
      )
    }
  }
}

Then, add code for tracking the email opening:

// handles the get request to the server
function doGet(e) {
  const method = e.parameter['method']
  switch (method) {
    case 'track':
      const email = e.parameter['email']
      updateEmailStatus(email)
    default:
      break
  }
}

The above code handles the GET request. If the value of the query parameter method is track, then you get the value of the query parameter email, and pass it to the updateEmailStatus function.

Next, add the updateEmailStatus function to your script file:

function updateEmailStatus(emailToTrack) {
  
  // get the active spreadsheet and data in it
  const id = SpreadsheetApp.getActiveSpreadsheet().getId()
  const sheet = SpreadsheetApp.openById(id).getActiveSheet()
  const data = sheet.getDataRange().getValues()
  
  // get headers
  const headers = data[0]
  const emailOpened = headers.indexOf('status') + 1

  // iterate through the data, starting at index 1
  for (let i = 1; i < data.length; i++) {
    const row = data[i]
    const email = row[0]
    
    if (emailToTrack === email) {      
      // update the value in sheet
      sheet.getRange(i+1, emailOpened).setValue('opened')
      break
    }
  }
}

The comments in the code explain it well. You just loop over the data in the Google Sheet and compare the emails with the emailToTrack variable. Once you have found the match, the status column corresponding to the email is set to opened.

Deploying as Web App

To handle the GET request, you need to deploy your script as a Web app. To deploy as a Web app, go to Publish > Deploy as web app…. Set Who has access to the app: to Anyone, even anonymous and click Update.

Authorize the Google Apps Script to send an email on your behalf

Adding Tracking Pixel in Email

In the content.html, add a tracking pixel:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <!-- add this img tag -->
    <img src="https://script.google.com/macros/s/AKfycbxyhzk8JpzP1S-vXp6UVAOtQzN9qKqHLaKxiHr2cxxxxxxxxxxx/exec?method=track&email=<?= email ?>" width="0" height="0"> 
    
    Hi <?= name ?>. you are testing your beta features for email marketing.
  </body>
</html>

The <?= name ?> and <?= email ?> are called template variables and they will be populated by the sendEmails function.

Make sure to raplace the image src URL by your script URL.

Results

Alright, you have done all the necessary setup to start a successful email marketing campaign which can be tracked as well. Execute the sendEmails function and check your inbox on behalf of users.

Next, check whether you were successful in implementing tracking email openings or not. Let’s open the inbox and then check whether the Google Sheet was updated or not.

Woah! You can see that the Google Sheet was automatically updated when the recipient opened the email. This is the power of Google Apps Script. It is not widely used but many things can be implemented with them.