Case Study

Two CRMs. One Master View. Zero Middleware.

A Google Apps Script pipeline that unified Housecall Pro and GoHighLevel into a single Looker Studio dashboard — 15,149 records synced on first run, 0 SaaS fees added.

A fast-growing NC home services contractor ran field ops in Housecall Pro and lead tracking in GoHighLevel. Every weekly review required a manual export from both platforms and 30–60 minutes of reconciliation. We automated the entire pipeline inside Google Workspace — no middleware, no new monthly fees.

Discuss a Similar ProjectAll Case Studies
Home Services · North Carolina, USA·Google Apps ScriptLooker StudioHousecall Pro APIGoHighLevel APIGoogle Sheets
Home Services Analytics|Last 30 days ▾1 May 2026→31 May 2026
HCPGHLAuto-refresh daily

New Leads

186

▲ 12% vs last mo

GoHighLevel

Book Rate

42%

▲ 4 pts vs last mo

GHL + HCP

Jobs In Progress

34

Housecall Pro

Estimates Written

$128K

▲ 9% vs last mo

Housecall Pro

Completed Revenue

$94K

▲ 7% vs last mo

Housecall Pro

AR Outstanding

$21K

▼ 2% vs last mo

Housecall Pro

Lead Pipeline

GHL
New Lead186
Contacted142
Estimate Sent98
Booked78
Won52

WIP by Job Type

HCP
Water Mitig.
14
Mould Remed.
12
Crawl Space
8
05101520

34 active jobs total

Book Rate Trend

GHL
Week 1Week 2Week 3Week 4

▲ +4 pts month-over-month

Leads by Source

GHL
Web / SEO
72
Referral
58
Biz Dev
34
Google Ads
22
050100150186

Completed Revenue (30d)

HCP
actual forecast

AR Ageing

HCP
Current (0–30 days)$12,400
Overdue (31–60 days)$5,800
Late (61–90 days)$2,100
90+ days$700

Total: $21,000

Estimates Written vs Closed (30d)

HCP

Written

Closed

Active WIP Trend

HCP
May 1May 8May 15May 22May 31
At a glance

Results That Speak for Themselves

15,149+

Records unified on first run

2

Disconnected CRMs connected

0

Middleware / SaaS fees added

60 min

Weekly manual work eliminated

From challenge to outcome

The Problem & Our Approach

Two Systems. No Unified View.

Housecall Pro held every dollar of operational data — estimates, jobs in progress, invoices, AR ageing. GoHighLevel held the full lead funnel — sources, ad spend, pipeline stages, and booking outcomes. The two systems had no direct connection.

  • Weekly ops review required manual exports from both platforms + 30–60 min of reconciliation
  • No book-rate visibility — lead-to-job conversion lived across two platforms with no bridge
  • No real-time WIP view — Water Mitigation / Mould / Crawl Space jobs only visible inside HCP
  • AR ageing (30/60/90 days) was a manual Monday pull from Housecall Pro
  • Hard constraint: no third-party middleware — everything had to run inside Google Workspace

A Google Apps Script Pipeline That Owns Both APIs

Lets Viz built a Google Apps Script pipeline that connects directly to both APIs, upserts every record into a Master Google Sheet on a daily time-driven trigger, and feeds a live Looker Studio dashboard — no server, no cron job, no SaaS subscription.

  • HousecallPro.gs — fetches estimates, jobs, and invoices via HCP REST API; incremental mode (updated_at filter) + full backfill; 429/5xx backoff + cursor pagination
  • GoHighLevel.gs — fetches opportunities and contacts via the GoHighLevel Marketplace API; pipeline-stage and user caches for O(1) lookups
  • SheetWriter.gs — upserts rows by ID (no blind overwrites); enforces headers; prefixes formula-injection characters
  • Main.gs — runDaily() for incremental upsert; runAll() for full backfill; per-source entry points for targeted debugging
  • Looker Studio connected to the Master Sheet once — every dashboard update is automatic

What We Achieved

15,149+ records unified from Housecall Pro and GoHighLevel on first run
30–60 minute weekly export-and-reconcile task fully eliminated
Book rate measurable by lead source for the first time
AR ageing (30/60/90 days) updates every morning without finance team export
WIP by job type visible in a shareable Looker Studio dashboard
Zero new recurring costs — entire pipeline runs inside Google Apps Script
Daily incremental runs complete in under 2 minutes

Pipeline Architecture

Data SourcesPipelineTruthDashboard

Housecall Pro

REST API

Estimates
Jobs
Invoices

GoHighLevel

Marketplace API

Contacts
Opportunities
API calls

Google Apps Script

No server · No middleware

HousecallPro.gs
GoHighLevel.gs
SheetWriter.gs
Main.gs (daily)
upsert rowsby ID · daily

Google Sheets

Master Data Store

Estimates tab
Jobs tab
Invoices tab
Opportunities tab
connected onceauto-refreshes

Looker Studio

Live Dashboard

Leads by source
Book rate & WIP
Revenue & estimates
AR ageing (30/60/90d)
Daily time-driven trigger · Incremental upsert · Zero middleware · Runs entirely inside Google Workspace

What Was Unified on First Run

SourceRecord TypeCount
Housecall ProEstimates6,939
Housecall ProJobs8,908
GoHighLevelCRM Opportunities4,302
Total—15,149+

What Leadership Can Now See — Daily

Every metric below refreshes automatically each morning without any manual action.

  • Leads by source (web, bizdev, referral) — GoHighLevel
  • Ad spend per lead source — GoHighLevel custom field
  • Book rate — leads to booked jobs — GoHighLevel + HCP linked
  • Estimates written (7d / 30d) — Housecall Pro
  • Estimates closed — value of closed business — Housecall Pro
  • WIP by job type (Water Mitigation / Mould / Crawl Space) — Housecall Pro
  • Completed revenue (7d / 30d) — Housecall Pro
  • AR ageing — 30 / 60 / 90 days — Housecall Pro invoices

Engineering Decisions That Mattered

  • Incremental sync with updated_at filter — daily runs complete in under 2 minutes and never re-process unchanged records
  • Upsert-by-ID pattern — safe to re-run; no duplicate rows, no data loss on retry
  • Pipeline-stage cache built once per run — O(1) lookups replace repeated API calls for every opportunity row
  • Formula-injection guard in SheetWriter — user-controlled data cannot execute as Sheets formulas
  • First full backfill (15,149 records) completed in under 3 minutes with zero manual intervention

The Pattern — Not the Industry

The problem this client had is not unique to home services. Any business running two or more operational systems — an ERP for production, a CRM for sales, an accounting platform for finance — faces the same disconnect. The data exists. The APIs exist. What is missing is a reliable, automated bridge built with Google Apps Script that pulls everything into one place without adding middleware fees, fragile Zapier chains, or a full data-engineering stack.

  • Google Apps Script runs inside Google Workspace — no new infrastructure required
  • The upsert + incremental-sync pattern scales to any API that exposes an updated_at filter
  • Looker Studio connects to the Sheet once; every dashboard update is automatic
  • No recurring SaaS cost, no third-party dependency, no data leaving your Google environment

Discuss a similar Home Services project

Lets Viz runs a paid discovery audit ($500–1,000, credited toward the project) to scope your requirements, data model, and architecture before writing a line of code.

From Lets Viz

Helping companies make data-driven decisions to achieve their Goals

Experience superior data analysis with our custom dashboards. Specialising in all tools and technologies, we turn data into visually compelling, actionable insights.

NDA-safe · No obligation · Clear next steps

Get in TouchLive Dashboard Experience