How to Automate Leads from Apartments.com Into Google Sheets

Being a landlord is quite a stressful part of my life – especially during leasing season.

There are lots of moving parts when looking for new tenants – from keeping track of prospects, emailing them, scheduling showings, and then taking notes, it can really add up.

So that’s where this automated rental leads sheet came into the picture. 

Why I built this tool

I’ve been automating mundane tasks and data pipelines at my job for many years (I work in digital marketing). I started using Tiller a few years ago and it got me excited about automating more things in my personal life.

Right now I use the main Tiller sheet with the Rental Manager Sheet to track rental expenses, but I needed something to also keep track of potential new tenants. 

For our rental listings, my husband and I use apartments.com which sends an email to us every time someone fills out the interest form.

This rental season has been very hot and it’s been hard to keep up with all the emails that come in. Beyond that, I wanted a simpler way to track all the leads we received and those we had contacted. 

Why I used a spreadsheet

Spreadsheets are easy! And because Google Sheets are shareable, I can collaborate with my husband and we can track everything together. 

Using Google Sheets also means I can use Google Apps Script which is an awesome (and very easy) way to connect Google services.

Apps Script allows me to pull information from emails and send it directly to my spreadsheet. 

How I automated it

As mentioned, I used Google Apps Script to automate this process. If you’re new to the wonderful world of Apps Script, I highly recommend checking out Ben Collins’s resources.

Before we get into that, let’s talk about the framework for this automation. Starting with a framework helps me decide how I’ll write the code and usually makes the process easier, and the code … prettier. 

To make this happen, I need the following: 

1. A way to find the emails from apartments.com
2. A way to pull only the information I care about (like name, email, move in date, etc) 
3. A way to paste that data into a sheet
4. A way to trigger this automation to start

Set Up Auto-Tagging in Gmail

There are lots of ways to write code that will “find” certain emails in a Gmail inbox. But let’s opt for the simplest and codeless option – auto-tagging directly in Gmail with labels. Since each lead email has the same Subject Line, this will work perfectly. To set up an auto label in Gmail you have to:

1. Type in the subject line in the search bar at the top and press enter.
2. At the bottom of the search window, click Create filter.
3. Scroll to Apply the label: and choose a label.
4. Click Create filter.

If you want to use the sheets template provided here, make sure you use the label name “leadlist“. 

Find All the Labeled Emails

Once you have a label set up, we can start to code. We’ll need to write a script that looks for that label, parses the email body text, and then looks for key information we want. Luckily, I came across this piece of code by Moayad Hani that does just that, so let’s customize it a bit. 

We’ll create a function called getAptEmails(), define a couple of variables for the label name, and then go fetch all the email threads that have the “leadlist” label on them. After we’ve found all the emails that match, we need to get all the details and then remove the label. 

function getAptEmails(){
var label = GmailApp.getUserLabelByName("leadlist"); //get labeled emails
var threads = label.getThreads(); // find all the threads
//loop through emails that match the label.
for(var i = threads.length - 1; i >=0; i--){ // for every message in every thread that matches the label
var messages = threads[i].getMessages();
for (var j = 0; j <messages.length; j++){
var message = messages[j];
extractDetails(message); // extract the message details
GmailApp.markMessageRead(message); // mark the message as read
}
threads[i].removeLabel(label); // remove the label
}
}

Parse Emails & Look for Key Information

To extract all the relevant information, we need a new function aptly named extractDetails(). This function will allow us to only pull in the most important parts of the email.

In this case, I’m looking to pull in the potential tenants Name, email, phone number, date they reached out, potential move in date, and any comments they leave. To do this we’ll first define those variables and then add the “search terms” or as we’re calling them in this script, emailKeywords

function extractDetails(message){
    // these are the elements that we will extract that are specific to the way that apartments.com sends leads emails to your inbox
  var emailDeets = {
    body: "Null",
    fullName: "Null", //
    emailAddr: "Null",
    phoneNum: "Null", //
    leadDate: "Null",
    moveDate: "Null",
    comments: "Null"
  }

  // these are the keywords that we will look for in the email in order to extract only the text we want in our spreadsheet 

  var emailKeywords = {
    fullName: "Name: ",
    emailAddr: "Email: ",
    phoneNum: "Phone: ",
    leadDate: "Lead Submitted: ",
    moveDate: "Move Date: ",
    comments: "Comments: "
  } 

  emailDeets.body = message.getPlainBody();// get all the info from the plain version of the email, line by line

We’ll be using something called regex to look for the “search terms” within the email body text and then save the information that follows it. Since each tenant might not be providing each of the elements (i.e. not all tenants will leave a comment), I had to add a try and catch element to the script.

Once we find the info, we save it as a “string” and trim any whitespace. 

 var regExp;
  
  regExp = new RegExp("(?<=" + emailKeywords.fullName + ").*");
      try {
        emailDeets.fullName = emailDeets.body.match(regExp).toString().trim(); // get the info that directly follows "Name: " in the email and then trim the info and convert it to a string so we can save it to our array
      }
      catch(err) {
        Logger.log(err); // catch any errors in case the email is missing stuff
      } 
  regExp = new RegExp("(?<=" + emailKeywords.phoneNum + ").*");
      try {
        emailDeets.phoneNum = emailDeets.body.match(regExp).toString().trim();
        }
      catch(err) {
        Logger.log(err);
        }    
  regExp = new RegExp("(?<=" + emailKeywords.emailAddr + ").*");
      try {
        emailDeets.emailAddr = emailDeets.body.match(regExp).toString().trim();
        }
      catch(err) {
        Logger.log(err);
       }  
  regExp = new RegExp("(?<=" + emailKeywords.leadDate + ").*");
      try {
        emailDeets.leadDate = emailDeets.body.match(regExp).toString().trim();
        }
      catch(err) {
        Logger.log(err);
      }  
  regExp = new RegExp("(?<=" + emailKeywords.moveDate + ").*");
      try {
            emailDeets.moveDate = emailDeets.body.match(regExp).toString().trim();
         }
      catch(err) {
        Logger.log(err);
      }    
  regExp = new RegExp("(?<=" + emailKeywords.comments + ").*");
      try {
        emailDeets.comments = emailDeets.body.match(regExp).toString().trim();
      }
      catch(err) {
        Logger.log(err);
      } 

Paste Information into the Sheet

Once we’ve looped through every email with that tag and extracted the information we want, we can paste it into our spreadsheet. To do that, we have to “activate” the spreadsheet. In the template, that sheet is called “All Leads” so we are activating it by name here. 

Now we’re going to define an array called emailDeetsArr that contains each of the elements we just extracted. The order of the variables is the order we will paste the info into our sheet which is the very last step!

Using appendRow we can “add” all the recently extracted info to the first empty row in our “All Leads” spreadsheet. That way, we can keep pasting new info every couple of days or even hours.

var leadSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("All Leads"); // activate the lead sheet that we want to paste all this data into
  
  var emailDeetsArr = [ emailDeets.fullName,emailDeets.emailAddr, emailDeets.phoneNum,emailDeets.leadDate, emailDeets.moveDate, emailDeets.comments]; // Structure the array so that each part will go into a different column when we paste the data
  
  leadSheet.appendRow(emailDeetsArr); //now we paste the data from the array to the end of the spreadsheet that we've activated
}

Creating A Trigger For My Sheets Automation

Now that we’ve set up the main code, we have to create a way to “trigger” it so we can use it whenever we want. My favorite way to do this is to add a “button” or “drop-down” to Google Sheets UI.  To do this I created a function called onOpen() that creates a custom menu for the template spreadsheet.

That way, all I have to do is click on the “Automate” drop-down and then click 💰 Get Leads for my sheet to be populated with new data! 

function onOpen() { // creates the menu in the sheet so that you can run the email script whenever       var ui = SpreadsheetApp.getUi();       ui.createMenu(‘Automate’)           .addItem(‘💰 Get Leads’, ‘getAptEmails’)           .addToUi();     }

Running The Automation For the First Time

To use the template, make a copy! Once you’ve made a copy, you’ll need to give the script access. To do this you need to click Automate >> Get Leads. The first time you do this, a dialogue box will pop up from Google asking you to allow permissions. This will allow the script to connect your gmail with this sheet and run the above automation. Because this isn’t a verified app, you’ll need to click “Advanced” >> “Go To Get Leads from Apartments.com (unsafe)”. 

About the Author

Automator of the mundane, Mary Albright builds tools with google apps scripts and google sheets to make life easier. She’s currently the lead SEO at GOAT Group and in her spare time works on various side projects at The Flourish Co. Follow Mary on Twitter and connect on LinkedIn.