← All Apps Scripts templates
Get the Lead Status of each lead in Hubspot

Get the Lead Status of each lead in Hubspot

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

The new formula in Google Sheets:


The code to paste in Apps Script inside Google Sheets:


* 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

    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" ],
            "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);

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)

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

Reach us if the process is unclear or if the script is outdated
Apps Scripts template

Get the Lead Status of each lead in Hubspot

Last updated
Built by
Avatar Placeholder

Similar Apps Script for


in Google Sheets

No similar items yet.