How to keep HubSpot leads list updated & without duplicates

One of the key factors to a successful campaign is reaching out to the right people.

But how do you ensure you don't import and contact the same prospects multiple times and ruin your chances of getting replies?

In this article, you'll find a hack to quickly check the status of your leads in HubSpot before import, so you don't lose time over prospecting.

Step #1 Ensure your leads' status is up to date

The first step to keeping your leads list clean is ensuring that the "lead status" property in HubSpot reflects your prospects' lifecycle.

E.g., when a deal is won, your lead status should be "Customer", or when your lead has been contacted, their status should be "Contacted".

Here's how you can automate lead status movements:

1. Go to HubSpot's workflow automation
2. Create a new workflow for each status
   → for "Customer", use this workflow:

If you want to save hours manually migrating leads data between your CRM and sales engagement tool, you can integrate your HubSpot CRM into lemlist.

This integration will allow lemlist to enrich your HubSpot database with accurate leads info, so you'll have their 100% synced overview.

Depending on your setup, you can add and update leads in HubSpot due to their activity in the lemlist campaign (such as Replied, Clicked, Marked as interested, etc.)

Following this guide, you can set up your lemlist x HubSpot integration to always know your leads' status and how to approach them for the best results!

Step #2 Check HubSpot status via Google Sheets

Once you have a Google sheets list of freshly scraped leads, here's what you should do before their import to HubSpot or lemlist:

1. Go to Extensions → Apps Script

2. Remove the code and copy-paste the following instead
 → copy and paste this code:

HUBSPOT_API_KEY = "YOUR_API_KEY"

/**
* Check if the contact is in Hubspot.
*
* @param {string} email.
* @return HS_lead_Status
* @customfunction
*/

function findHubspotContact(email){  
‍
result = hubspotLookUp(email)  
‍
while (result.includes("RATE_LIMITS")){
‍
    var randomNumber = Math.random();
‍
  // convert 5 seconds to milliseconds
  var maxSleep = 1000;

  // multiply the random number by the max sleep time
 var sleepTime = randomNumber * maxSleep;

 // use the Utilities.sleep() method to make the script wait
  Utilities.sleep(sleepTime);

   result = hubspotLookUp(email)  }    return result    }function hubspotLookUp(email) {
 var apiKey = HUBSPOT_API_KEY;
 var url = "<https://api.hubapi.com/crm/v3/objects/contacts/search>";

 var options = {
   "muteHttpExceptions": true,
   "method": "post",
   "headers": {
     "Authorization": "Bearer " +apiKey,
     "Content-Type": "application/json",
     "Accept": "application/json"
   },
   "payload": JSON.stringify({
   "properties": [ "email", "hs_lead_status","notes_last_contacted" ],
   "filterGroups":[
     {
       "filters":[
         {
           "propertyName": "email",
           "operator": "EQ",
           "value": email
         }
       ]
     }
   ]
   })
 };

 var response = UrlFetchApp.fetch(url,options);
 var responseCode = response.getResponseCode();
 var responseBody = response.getContentText();

// Logger.log(responseBody)
if (responseCode === 200) {
if (JSON.parse(responseBody).results[0]){

  // display the results in 2 columns
var results = new Array(1);let info = new Array(3);
info[0]=JSON.parse(responseBody).results[0].properties.hs_lead_status
if (JSON.parse(responseBody).results[0].properties.notes_last_contacted){
let lastContactedDate= Date.parse(JSON.parse(responseBody).results[0].properties.notes_last_contacted)
info[1]= new Date(lastContactedDate)}
results[0]=info

return results} else {return "Not in Hubspot"}
 } else {
   Logger.log(Utilities.formatString("Request failed forgot %d: %s", responseCode, responseBody));
   return Utilities.formatString("Request failed forgot %d: %s", responseCode, responseBody);
 }
}

‍

3. Get your HubSpot API Key:

• Go to HubSpot's Settings → Integrations → Private Apps
• Click on Create a Private App

• Give a name and go to Scopes
• Authorize read access to "crm.objects.contacts "

• Click on Create app
• Choose Show token and copy it

4. Paste your HubSpot API key into the Google Apps Script project
 → your setup should look like this:

5. Save your project and go back to your sheet

6. Use formula =findHubspotContact({email}) to see your new leads' exact status in HubSpot CRM

Conclusion

Keeping your leads list clean saves you hours creating campaigns for existing customers or those not ready to buy from you.

Use this Google Sheet hack and lemlist to keep in the loop with your prospects' lifecycle so you approach them at the right time and boost your sales chances!

If you want to grow your business with more advice like this, join the lemlist family, where 11k+ successful people share free outreach tips daily. 🚀