Get the Lead Status of each lead in Hubspot

Create a new Google Sheets file
Go to Extensions → Apps Script
Remove all the code and copy-paste the following instead
Get your Hubspot API Key
Go to Private Apps: Settings → Integrations → Private Apps
Click on “Create a Private App”
Name it and go to "Scopes"
Authorize read access to “crm.objects.contacts“
Click on “Create app”
Click on “show token” and copy it
Paste it into your Google Apps Script project
If everything is ok, it should look like this: "pat-eu1sdvdvx"
Save your project and go back to your sheet
Use =findHubspotContact({email}), like classic Google Sheets Formula

‍

The new formula in Google Sheets:


=findHubspotContact({email})

‍

The code to paste in Apps Script inside Google Sheets:


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);
  }
  
}

‍