A zero-infrastructure inbound lead pipeline that turns a contact-form submission into:
- An enriched row in Google Sheets (Apollo.io fills in title, LinkedIn, phone, company).
- A HubSpot contact with an attached note containing the original form message.
- A Google Chat card for the sales team — with quick links to HubSpot and the Sheet, and a ready-to-paste ChatGPT prompt to draft a LinkedIn outreach reply.
The whole thing runs on a free Google Apps Script web app. No server, no Docker, no Vercel, no cold starts — just six .gs files behind a single POST endpoint.
An n8n version of the same pipeline is planned, for teams who want the visual canvas, queueing, and retry semantics that Apps Script doesn't give you.
┌────────────────┐ POST ┌──────────────────────┐
│ Website form │ ──────────▶ │ Apps Script Web App │
│ (contact / │ │ doPost(e) │
│ waitlist) │ └──────────┬───────────┘
└────────────────┘ │
┌───────────┼───────────┐
▼ ▼ ▼
Apollo.io Google Sheets HubSpot
(enrich) (log row) (contact + note)
│
▼
Google Chat card
(team notification)
The form-side change is a two-line fire-and-forget fetch. The form keeps doing whatever it was doing (sending an email, showing a success screen, etc.) — the lead automation runs in parallel and never blocks the user-facing UX. See examples/frontend-snippet.html.
src/
Config.gs Script-Properties getters (no secrets in source)
Webhook.gs doPost(e) handler + JSON response helper
Apollo.gs Apollo.io people-match enrichment
Sheets.gs Sheet logger (SheetLog) — append + backfill
HubSpot.gs Contact create + duplicate (409) handling + note
GoogleChat.gs Cards v2 builder + error notifier
Test.gs Per-module + end-to-end test harness
examples/
frontend-snippet.html Vanilla HTML form wired to the webhook
docs/
sheet-template.md Sheet column schema
-
Create a Google Sheet with one tab named
Inbound Web Form. Add the headers fromdocs/sheet-template.mdto row 1. -
Create a Google Chat space for inbound leads. In the space, Apps & integrations → Webhooks → Add webhook. Copy the URL.
-
Create an Apollo.io API key (free tier works for testing).
-
Create a HubSpot Private App with scopes
crm.objects.contacts.read,crm.objects.contacts.write,crm.objects.notes.write. Copy the token. -
In HubSpot, create a custom contact property with internal name
lead_source(Single-line text), or remove that line fromsrc/HubSpot.gs. -
Create a new Apps Script project (script.google.com). Paste each
.gsfile fromsrc/into a matching file in the editor. -
Set Script Properties (Project Settings → Script Properties):
Key Example SHEET_ID1AbC…XyZ(or the full Sheet URL — the getter extracts the ID)GOOGLE_CHAT_WEBHOOK_URLhttps://chat.googleapis.com/v1/spaces/…/messages?…APOLLO_API_KEY…HUBSPOT_TOKENpat-…HUBSPOT_PORTAL_ID12345678ICP_DESCRIPTIONFree-text ICP definition — gets pasted into the ChatGPT prompt -
Run the tests in order from the editor toolbar:
testConfig→testSheets→testGoogleChat→testApollo→testHubSpot→testFullFlow. -
Deploy → New deployment → Web app, Execute as: Me, Who has access: Anyone. Copy the
/execURL — that's your webhook. -
Wire your form to the webhook. See
docs/integrations.mdfor ready-to-paste snippets covering:- Vanilla HTML / JavaScript (live example)
- React / SPAs
- WordPress — Gravity Forms, WPForms, Elementor Forms, Contact Form 7 (with PHP hooks)
- Webflow, Tally, Typeform (via native webhooks or Zapier/Make)
- Server-side: curl, Node.js, Python
Apollo enrichment is wrapped in its own try/catch and returns {} on any failure (auth, rate limit, network). The lead still gets logged and notified — the team just sees fewer fields. Treating enrichment as a hard dependency would make the pipeline fragile against an external service that is, by definition, beyond your control.
Sheets → HubSpot → Chat each run in their own try/catch inside doPost. A HubSpot 400 (e.g., a missing custom property) won't swallow the Chat notification, and a Chat outage won't prevent the lead from being persisted. Whoever watches the channel sees an [ERROR] message either way.
Apps Script web apps don't reliably answer CORS preflight requests. The frontend snippet sends the body as Content-Type: text/plain;charset=utf-8 (a "simple" content type that doesn't trigger preflight) while keeping the body as a JSON string. doPost parses it with JSON.parse(e.postData.contents) regardless — content type is purely a CORS signal.
The form awaits its primary action (e.g., the existing email service) and doesn't await the webhook. The webhook either succeeds and the team sees a Chat card, or it fails silently into the browser console. The user-facing form never appears slow or broken because the CRM was having a bad afternoon.
HubSpot returns 409 Conflict when an email already exists. HubSpot.createContact() handles that by falling back to a search query and returning the existing contact ID — so duplicate submissions don't error out and the row in Sheets still gets a usable HubSpot_ID to link to.
Nothing sensitive lives in source. The Config module is the only thing that touches PropertiesService and gives every getter a clear error message if a property is missing, which is how 90% of "it doesn't work" turns out to be diagnosed at setup time.
- n8n version of the same pipeline. For teams that want a visual canvas, native retries, queue-based fan-out, and easier observability — at the cost of running n8n somewhere (self-hosted or n8n Cloud).
- Owner round-robin in HubSpot. Currently every contact is created unassigned; rotating ownership across a list of user IDs from Script Properties is a few lines.
- Lead scoring before notifying. Cheap pre-filter on Apollo's
organization.employee_count/industryagainst the ICP, so the Chat channel doesn't get pinged for obviously-irrelevant leads.
MIT — see LICENSE.