Sync GoHighLevel to Google Sheets and a Data Warehouse

In short

• Pull contacts with GET /contacts/search, paginated by startAfter cursor.
• Pull opportunities with GET /opportunities/search, paginated the same way.
• Write rows to Google Sheets with the Sheets API v4 spreadsheets.values.append method.
• For a true data warehouse (BigQuery, Postgres, Snowflake), write to tables with an UPSERT on the GHL contact or opportunity ID.

Endpoints

Authentication

Use a private integration token for the GHL API calls. This is a long-lived token scoped to a single sub-account.

For Google Sheets, you need a Google service account with the Sheets API enabled and editor access to the spreadsheet. Download the service account JSON key and store it as an environment variable or a secrets manager secret.

Never commit the Google service account key to your repo. Add it to your .gitignore and load it from an environment variable or a secrets manager.

For a data warehouse (BigQuery, Postgres, Snowflake), use the appropriate client library and store the connection credentials in environment variables.

Recipes

Paginate through all GHL contacts

The contacts search endpoint returns up to 100 records at a time. Use the startAfter cursor from the response to get the next page. Keep fetching until you get fewer records than the limit.

This pattern works the same way for opportunities and appointments. Always handle the case where a page returns zero records to avoid infinite loops.

Node.js: paginate all contacts

async function* getAllContacts(token, locationId) {
  const base = 'https://services.leadconnectorhq.com';
  const headers = {
    Authorization: `Bearer ${token}`,
    Version: '2021-07-28',
  };
  const limit = 100;
  let startAfter = null;

  while (true) {
    const params = new URLSearchParams({ locationId, limit: String(limit) });
    if (startAfter) params.append('startAfter', startAfter);

    const res = await fetch(`${base}/contacts/search?${params}`, { headers });
    if (!res.ok) throw new Error(`GHL error: ${res.status}`);

    const data = await res.json();
    const contacts = data.contacts ?? [];

    for (const contact of contacts) {
      yield contact;
    }

    if (contacts.length < limit) break; // last page
    startAfter = data.meta?.startAfterRecord ?? contacts[contacts.length, 1].id;
  }
}

// Usage
for await (const contact of getAllContacts(process.env.GHL_TOKEN, process.env.GHL_LOCATION_ID)) {
  console.log(contact.id, contact.email);
}

Python: paginate all contacts

import time
import requests

def get_all_contacts(token, location_id):
    base = "https://services.leadconnectorhq.com"
    headers = {"Authorization": f"Bearer {token}", "Version": "2021-07-28"}
    limit = 100
    start_after = None

    while True:
        params = {"locationId": location_id, "limit": limit}
        if start_after:
            params["startAfter"] = start_after

        res = requests.get(f"{base}/contacts/search", headers=headers, params=params)
        res.raise_for_status()
        data = res.json()
        contacts = data.get("contacts", [])

        yield from contacts

        if len(contacts) < limit:
            break  # last page

        meta = data.get("meta", {})
        start_after = meta.get("startAfterRecord") or contacts[-1]["id"]
        time.sleep(0.2)  # be kind to the rate limit

Append contacts to a Google Sheet

Use the Google Sheets API v4 to append rows. The spreadsheets.values.append method adds rows at the end of existing data, which is ideal for an incremental sync.

For a full refresh (replace all rows), clear the sheet first with spreadsheets.values.clear, then write a header row, then append all contacts. For incremental sync, track the last sync timestamp and only pull contacts updated since then.

Node.js (googleapis)

import { google } from 'googleapis';

async function appendContactsToSheet(contacts, spreadsheetId, sheetName) {
  const auth = new google.auth.GoogleAuth({
    keyFile: process.env.GOOGLE_APPLICATION_CREDENTIALS,
    // or: credentials: JSON.parse(process.env.GOOGLE_SERVICE_ACCOUNT_JSON)
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
  });
  const sheets = google.sheets({ version: 'v4', auth });

  const rows = contacts.map((c) => [
    c.id,
    c.email ?? '',
    c.firstName ?? '',
    c.lastName ?? '',
    c.phone ?? '',
    (c.tags ?? []).join(', '),
    c.dateAdded ?? '',
    c.dateUpdated ?? '',
  ]);

  await sheets.spreadsheets.values.append({
    spreadsheetId,
    range: `${sheetName}!A1`,
    valueInputOption: 'RAW',
    insertDataOption: 'INSERT_ROWS',
    requestBody: { values: rows },
  });

  console.log(`Appended ${rows.length} contacts`);
}

Python (gspread)

import json
import os
import gspread
from google.oauth2.service_account import Credentials

SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
creds = Credentials.from_service_account_info(
    json.loads(os.environ["GOOGLE_SERVICE_ACCOUNT_JSON"]),
    scopes=SCOPES,
)
gc = gspread.authorize(creds)

def append_contacts_to_sheet(contacts, spreadsheet_id, sheet_name):
    sh = gc.open_by_key(spreadsheet_id)
    ws = sh.worksheet(sheet_name)

    rows = [
        [
            c.get("id", ""),
            c.get("email", ""),
            c.get("firstName", ""),
            c.get("lastName", ""),
            c.get("phone", ""),
            ", ".join(c.get("tags", [])),
            c.get("dateAdded", ""),
            c.get("dateUpdated", ""),
        ]
        for c in contacts
    ]
    if rows:
        ws.append_rows(rows, value_input_option="RAW")
    print(f"Appended {len(rows)} contacts")

Upsert contacts into PostgreSQL for a real data warehouse

For a proper data warehouse, use a PostgreSQL INSERT ... ON CONFLICT DO UPDATE (upsert) keyed on the GHL contact ID. This is safe to run repeatedly without creating duplicates.

Add a synced_at column to track when each row was last written. Index the dateUpdated column in GHL to support incremental queries (pull only contacts updated since your last sync run).

Node.js (postgres)

import postgres from 'postgres';

const sql = postgres(process.env.DATABASE_URL);

async function upsertContacts(contacts) {
  const rows = contacts.map((c) => ({
    id: c.id,
    location_id: c.locationId,
    email: c.email ?? null,
    first_name: c.firstName ?? null,
    last_name: c.lastName ?? null,
    phone: c.phone ?? null,
    tags: c.tags ?? [],
    date_added: c.dateAdded ? new Date(c.dateAdded) : null,
    date_updated: c.dateUpdated ? new Date(c.dateUpdated) : null,
    synced_at: new Date(),
  }));

  await sql`
    INSERT INTO ghl_contacts ${sql(rows)}
    ON CONFLICT (id) DO UPDATE SET
      email = EXCLUDED.email,
      first_name = EXCLUDED.first_name,
      last_name = EXCLUDED.last_name,
      phone = EXCLUDED.phone,
      tags = EXCLUDED.tags,
      date_updated = EXCLUDED.date_updated,
      synced_at = EXCLUDED.synced_at
  `;
  console.log(`Upserted ${rows.length} contacts`);
}

SQL: create the table

CREATE TABLE IF NOT EXISTS ghl_contacts (
  id            TEXT PRIMARY KEY,
  location_id   TEXT NOT NULL,
  email         TEXT,
  first_name    TEXT,
  last_name     TEXT,
  phone         TEXT,
  tags          TEXT[] DEFAULT '{}',
  date_added    TIMESTAMPTZ,
  date_updated  TIMESTAMPTZ,
  synced_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ghl_contacts_location
  ON ghl_contacts (location_id);

CREATE INDEX IF NOT EXISTS idx_ghl_contacts_email
  ON ghl_contacts (email);

Common errors

• Infinite loop on pagination: the startAfter cursor did not advance. Make sure you are reading the cursor from the API response, not reusing the same value. Log the cursor on each page during testing.
• Google Sheets 429: you are appending too many rows too quickly. Add a delay between batch appends or use a single large append instead of one append per contact.
• Duplicate rows in Google Sheets: you appended the same contacts twice without clearing the sheet first. Either use a clear-then-write pattern or track the last sync timestamp and only pull new contacts.
• Rate limit from GHL: the pagination loop is running too fast. Add a small delay (100-200ms) between page requests to stay within rate limits.

MCP tool definition

MCP tool definition (JSON)

{
  "name": "ghl_export_contacts",
  "description": "Export all GoHighLevel contacts for a location to a JSON array, paginating automatically.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "locationId": { "type": "string" },
      "updatedSince": {
        "type": "string",
        "description": "ISO 8601 datetime. Only return contacts updated after this time."
      },
      "limit": {
        "type": "number",
        "description": "Max contacts to return (default 1000, max 10000)"
      }
    },
    "required": ["locationId"]
  }
}