Solved: Sync HubSpot Deals to a Google Sheet for Sales Visualization

#devops #programming #tutorial #cloud

🚀 Executive Summary

TL;DR: The article addresses the pain point of manually exporting stale HubSpot deal data to Google Sheets for sales visualization. It provides a Python script solution that automates fetching live deal data from the HubSpot API and pushing it directly into a Google Sheet, enabling real-time dashboards.

🎯 Key Takeaways

HubSpot Private Apps are the recommended secure method for API access, requiring the crm.objects.deals.read scope for fetching deal data.
Google Cloud Platform Service Accounts with JSON keys are used to authenticate and grant programmatic ‘Editor’ access to the Google Sheets API and Google Drive API.
The Python script utilizes requests for HubSpot API calls and google-api-python-client for Google Sheets API interactions, with python-dotenv for secure environment variable management.
Data synchronization involves clearing the existing Google Sheet data before writing new deal information to ensure data freshness and prevent stale rows.
Automated scheduling via cron jobs (e.g., 0 2 \* \* 1 python3 sync\_deals.py) ensures periodic, hands-off data updates for continuous sales visualization.

Sync HubSpot Deals to a Google Sheet for Sales Visualization

Hey folks, Darian Vance here. Let’s talk about a common pain point: getting timely sales data in front of the right people without the manual drudgery. For a while, our sales ops team was manually exporting HubSpot deal data to a CSV and then importing it into a Google Sheet every Monday morning. It was a tedious process, prone to errors, and the data was instantly stale. The leadership team wanted a live dashboard, but didn’t want another login to remember.

I built a simple Python script to automate this entire workflow. It pulls deal data from the HubSpot API and pushes it directly into a designated Google Sheet. That sheet then powers a Looker Studio dashboard that’s always up-to-date. This little automation saves us a few hours a week and has completely eliminated the manual reporting step. It’s a classic DevOps win: automate the boring stuff so your team can focus on what matters.

Today, I’m going to walk you through how to build it yourself.

Prerequisites

Before we dive in, make sure you have the following ready. We’re busy people, so getting this sorted out first will save you a headache later.

HubSpot Account: You’ll need Admin permissions to create a Private App.
Google Cloud Platform (GCP) Account: A project with the Google Sheets API and Google Drive API enabled is essential.
Python Environment: Python 3 installed on whatever machine will run the script.
Required Python Libraries: You’ll need to install a few packages. I usually run these commands in my terminal: pip install requests python-dotenv google-api-python-client google-auth-httplib2 google-auth-oauthlib.

The Step-by-Step Guide

Step 1: Create a HubSpot Private App

First, we need to get an API key from HubSpot. Their preferred method is using Private Apps, which is more secure than the old API keys.

Log into your HubSpot account and navigate to Settings (the gear icon) > Integrations > Private Apps.
Click “Create a private app” and give it a descriptive name like “GoogleSheets_Deal_Sync”.
Go to the Scopes tab. This is where you grant permissions. For this task, you only need one: crm.objects.deals.read. Always follow the principle of least privilege.
Click “Create app”. HubSpot will show you your app’s Access Token. Copy this token immediately. This is your API key, so treat it like a password. We’ll store it securely in a moment.

Step 2: Configure Your Google Cloud Service Account

Next, we need to give our script permission to talk to Google Sheets. We’ll do this using a Service Account, which is like a robot user for your GCP project.

In the Google Cloud Console, select your project.
Navigate to APIs & Services > Library. Search for and enable the “Google Sheets API” and “Google Drive API”.
Go to APIs & Services > Credentials. Click “Create Credentials” and select “Service Account”.
Give it a name like “hubspot-sheet-updater” and click “Create and Continue”, then “Done”.
Find your newly created service account in the list and click on it. Go to the Keys tab, click “Add Key”, and choose “Create new key”. Select JSON as the key type. A credentials.json file will download automatically.

Crucially: Keep this JSON file secure. It grants access to your Google account services. Don’t commit it to a public Git repository.

Step 3: Prepare the Google Sheet

This is the easy part. Create a new Google Sheet that will act as our data destination.

Create a new Google Sheet. I’ll call mine “Sales Deal Pipeline”.
Give the first worksheet a simple name, like “HubSpotDeals”.
Open your credentials.json file and find the client_email address (it looks something like your-service-account@your-project.iam.gserviceaccount.com).
Back in your Google Sheet, click the Share button and paste that service account email address. Grant it Editor permissions. This is a critical step that’s easy to forget.
Finally, copy the Spreadsheet ID from your browser’s URL bar. It’s the long string of characters between /d/ and /edit.

Step 4: The Python Script

Alright, let’s get to the code. I’ll skip the standard project setup steps like creating a directory and a virtual environment, since you probably have your own workflow for that. Let’s focus on the logic.

Create two files in your project directory: a script file named sync_deals.py and a configuration file named config.env to hold our secrets.

Your config.env file should look like this. Populate it with the credentials you gathered in the previous steps.

# config.env
HUBSPOT_API_KEY="your-private-app-token-here"
GOOGLE_SHEET_ID="your-spreadsheet-id-here"
GOOGLE_SHEET_NAME="HubSpotDeals"

Now for the main event, the sync_deals.py script. I’ve broken it down into functions to keep things clean.

# sync_deals.py
import os
import requests
from dotenv import load_dotenv
from google.oauth2 import service_account
from googleapiclient.discovery import build

# Load environment variables from config.env
load_dotenv('config.env')

# --- CONFIGURATION ---
HUBSPOT_TOKEN = os.getenv('HUBSPOT_API_KEY')
GOOGLE_SHEET_ID = os.getenv('GOOGLE_SHEET_ID')
GOOGLE_SHEET_NAME = os.getenv('GOOGLE_SHEET_NAME')
SERVICE_ACCOUNT_FILE = 'credentials.json' # Assumes the JSON key is in the same directory
GOOGLE_SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

def get_hubspot_deals():
    """Fetches a list of deals from the HubSpot API."""
    print("Fetching deals from HubSpot...")

    headers = {
        'Authorization': f'Bearer {HUBSPOT_TOKEN}',
        'Content-Type': 'application/json'
    }

    # Define the properties you want to pull from each deal
    properties = "dealname,amount,dealstage,closedate,hubspot_owner_id"
    url = f"https://api.hubapi.com/crm/v3/objects/deals?properties={properties}&limit=100"

    try:
        response = requests.get(url, headers=headers)
        response.raise_for_status()  # This will raise an HTTPError for bad responses (4xx or 5xx)

        deals_json = response.json().get('results', [])
        if not deals_json:
            print("No deals found.")
            return None

        # Prepare data for Google Sheets: Header row + data rows
        headers = ["Deal Name", "Amount", "Deal Stage", "Close Date", "Owner ID"]
        processed_deals = [headers]

        for deal in deals_json:
            props = deal.get('properties', {})
            row = [
                props.get('dealname', 'N/A'),
                props.get('amount', '0'),
                props.get('dealstage', 'N/A'),
                props.get('closedate', 'N/A'),
                props.get('hubspot_owner_id', 'N/A')
            ]
            processed_deals.append(row)

        print(f"Successfully fetched {len(processed_deals) - 1} deals.")
        return processed_deals

    except requests.exceptions.RequestException as e:
        print(f"Error fetching from HubSpot: {e}")
        return None

def update_google_sheet(data):
    """Clears and updates a Google Sheet with new data."""
    print("Connecting to Google Sheets API...")

    creds = service_account.Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=GOOGLE_SCOPES)

    try:
        service = build('sheets', 'v4', credentials=creds)
        sheet = service.spreadsheets()

        # 1. Clear the existing data to prevent stale rows
        print(f"Clearing sheet: '{GOOGLE_SHEET_NAME}'...")
        sheet.values().clear(
            spreadsheetId=GOOGLE_SHEET_ID,
            range=GOOGLE_SHEET_NAME
        ).execute()

        # 2. Write the new data
        print(f"Writing {len(data)} rows to the sheet...")
        body = {'values': data}
        sheet.values().update(
            spreadsheetId=GOOGLE_SHEET_ID,
            range=f"{GOOGLE_SHEET_NAME}!A1",
            valueInputOption="USER_ENTERED",
            body=body
        ).execute()

        print("Google Sheet updated successfully.")

    except Exception as e:
        print(f"An error occurred with Google Sheets API: {e}")

if __name__ == "__main__":
    print("--- Starting HubSpot to Google Sheets Sync ---")
    deal_data = get_hubspot_deals()

    if deal_data:
        update_google_sheet(deal_data)
    else:
        print("No data fetched from HubSpot. Skipping sheet update.")

    print("--- Sync complete ---")

Pro Tip: In my production setups, I abstract the API clients into their own classes for better organization and testing. I also implement more robust pagination for HubSpot, as this basic script only fetches the first 100 deals. You can do this by checking the paging object in the HubSpot API response and making subsequent requests until it’s empty.

Step 5: Schedule the Sync with Cron

A script is only useful if it runs automatically. On a Linux server, a cron job is the standard way to do this. You can schedule the script to run at any interval you like.

For example, to run the script every Monday at 2 AM, you would set up a cron job like this:

0 2 * * 1 python3 sync_deals.py

This simple command ensures your data is refreshed weekly without any manual intervention.

Where I Usually Mess Up (Common Pitfalls)

Even simple integrations have their “gotchas.” Here are a few I’ve run into:

Forgetting to Share the Google Sheet: This is the number one cause of failure. If the service account doesn’t have “Editor” access to the sheet, the API will throw a permissions error. Always check this first.
Incorrect HubSpot Scopes: If you get a 403 Forbidden error from HubSpot, it’s almost certainly because your Private App doesn’t have the right permissions. Double-check that crm.objects.deals.read is enabled.
Environment Variables Not Loading: Make sure your config.env file is in the same directory as your script, or provide the correct path to load_dotenv(). And please, don’t commit your config.env or credentials.json files to version control. Use a .gitignore file.

Conclusion

And that’s it. You now have a resilient, automated pipeline for getting key sales data out of HubSpot and into a format perfect for visualization and wider business reporting. This is a foundational piece of automation that you can expand upon—syncing contacts, tickets, or custom objects is just a matter of changing the API endpoint and properties.

By investing a little time upfront, you free up your team to analyze data instead of just moving it around. Hope this helps you out.

Cheers,

Darian Vance