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.
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:
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!
Once you have a Google sheets list of freshly scraped leads, here's what you should do before their import to HubSpot or lemlist:
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:
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
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. 🚀