PROBATE LEAD AUTOMATION · MICHAEL BARDY LTD
The ingest connector
RapidUpload v1.1 — the Google Apps Script that turns a raw dataset into a recoverable HubSpot load, one row at a time.
LAYER 1 OF 2
File Drop → master ledger → HubSpot upsert
HANDS OFF
15-minute silent poll · nothing discarded
WHAT IT DOES
A workbook in, a CRM record out
Apps Script has no “file added” event, so a time-driven poll picks up the weekly drop and carries it all the way to HubSpot — with the master sheet as the recoverable ledger in the middle.
01 · DETECT
.xlsx in File Drop
→
02 · CONVERT
Data kept in Sorted_RAW
→
03 · CHECK
custom contract to match your sheet
↓
04 · VALIDATE
Dedupe & mint lead id
→
05 · APPEND
Master Dataset Ledger
→
06 · UPSERT
HubSpot universal lead ID
HubSpot is always loaded from the sheet, never from the file directly
02 / 15
WHERE FILES LAND
One Drive tree, one trigger
The connector watches a single Drive folder set. A workbook dropped into File Drop is the only thing that starts a run — every other folder has its own job.
Google Drive: the Rapid Upload v1.1 folder containing Program Management, Probate Mastery Documents, File Drop and Campaign_Data subfolders
File Drop
The weekly workbook and vendor returns land here — the one folder the poll acts on
Campaign_Data
Config, Sorted_RAW (archive & converted data) and DNC Suppression
Program Management & Probate Mastery Documents
Program assets, control and reference documents — outside the ingest path
File Drop is the single trigger — the 15-minute poll only ever acts on what lands there
03 / 15
THE MASTER SHEET
A ledger, not a staging area
Every column has one owner. A geometry assertion runs before any hash write, append or pending read — so a drifted column fails loudly instead of writing silently wrong data.
A
Lead id — this script
Header reads “Record ID”
B:AU
Vendor — 46 inbound columns
Verbatim and in file order
AV:BA
Control block — this script
hash · timestamp · source_file · contact_id · load_status · load_error
BC →
Other processes
Case Title — never touched here
assertMasterGeometry_() — recoverable because the file never overwrites the ledger
04 / 15
VALIDATION
The column contract
A dropped file whose header row does not match the known contract is refused outright. Every row that clears the header is then checked against the required-field rules.
REQUIRED PER ROW
  • Case Number
  • Property Address
  • At least one of PR Last Name / Last Name
The dataset columns cover interested parties, property, value and associated contacts — verbatim
Nothing is silently discarded
A row that fails validation goes to Quarantine — with the rejection reason  — never to HubSpot, never discarded.
tabs: RunLog · Quarantine
Multiple datasets from various origins established as unique contact entries or existing row appends
05 / 15
DEDUPE
One lead, hashed once
A SHA-256 fingerprint of five columns decides whether a row is the same lead — so a vendor punctuation change never re-appends the dataset as new.
HASH_COLUMNS
  • County Name
  • Case Number
  • Last Name
  • First Name (Deceased)
  • Property Address
HASH_NORMALIZE · alnum
Strips every non-alphanumeric before hashing:
123 Main St. == 123 Main St
Real differences still separate. Change the columns or the rule and every stored hash is invalidated — rebuild once before the next drop.
Setup → Rebuild ALL hashes is gated behind a typed confirmation — it cannot be usefully undone
06 / 15
IDENTITY
Mint the universal ID, keep it forever
New rows get a Universal ID in column A; an existing value there is never overwritten. The id is dual-written as both lead_id and lead_uid, and HubSpot upserts on the UID.
LEAD_ID_MODE · ulid
ULID for new rows
Existing column A never overwritten in either mode
DUAL-WRITE
lead_id + lead_uid
The UID is the unique upsert key; lead_id is preserved as history
FIRST LOAD ONLY
enrichment_stage = PENDING
Withheld once a contact id exists, so a retry can’t rewind the stages
LockService wraps every run — concurrent polls can never mint overlapping ids
07 / 15
THE LOAD
Batch upsert that heals and appends itself
100 records a call, keyed on the UID, idempotent — and built to isolate a single bad value instead of failing all 100 forever.
Blank values are never sent
A sparse weekly file cannot wipe enrichment already on the record
A 4xx splits the batch
Halves recursively to a single record, isolating the offender with its own error
Matched on the echoed id
Results join on the returned lead_id, never on array position
Write-back per batch
One AY:BA block write records CREATED / UPDATED / ERROR
HubSpot batches validate all-or-nothing — the recursive split is what keeps one bad row from blocking the rest
08 / 15
VENDOR RETURNS
Not everything is a new lead
A contact append (cell phone, email, ip address) obtained from external providers return shares the File Drop but is not a new record ingest — it is routed to append, matched on the contact id, and can never create a record.
HOW IT’S TOLD APART
  • Identified by Record ID 
  • Matched on Record ID = HubSpot contact id — no UID needed
  • Sparse write: empty cells are never sent
WHAT IT STAMPS
  • Enrichment_stage = APPEND_COMPLETE
  • Real Estate Enrichment Automation Enabled
  • phone_validated / email_validated
  • contactable_channel confirmed
Vendor Returns are processed in the same FILE DROP folder and append the dataset to complete enrichment
09 / 15
DESIGN FOR FAILURE
Every failure is recoverable
The connector assumes files will be malformed, calls will 4xx, and polls will overlap — and makes every one of those a logged, resumable event.
Ledger first
Rows land on the sheet before HubSpot — the backlog is always resumable
Quarantine, not delete
A bad row is held with its reason and all 46 columns
LockService
Wraps every run — overlapping polls cannot collide
Retry failed loads
Re-sends only AZ = ERROR rows; never touches Drive
FAILED_ prefix + email
Failures email the owner; fix the cause, strip the prefix, next poll retries
Token in Script Properties
Never in a cell, log or file; .xlsm rejected on sight
Nothing is ever silently discarded
10 / 15
Editor recovery: retryFailedLoads · loadBacklogToHubSpot · dryRun · processVendorReturns
11 / 15
OPERATIONS
Unattended, but never opaque
RUNLOG · EVERY RUN REPORTS ITS COUNTS AND STATUS
Situation
Action
Weekly, unattended
Start watching File Drop — 15-min poll; failures email the owner
Error in Upload
Retry failed loads — re-sends only those rows
Rows never sent
Load backlog to HubSpot until nothing is pending
Failed Row Records are logged in the Master Ledger
Read the emailed error, fix, strip the prefix
Weekly review
RunLog for counts · Quarantine for held-back rows
The RunLog tab: one row per run with rows read, quarantined, duplicates, appended, HubSpot created, updated and errors, and a status of RECOVERY, OK, PARTIAL, NO_FILE or NO_XLSX with its message
THE HANDOFF
Where it meets the framework
The connector and the enrichment pipeline are two deployments that agree on three things — and deliberately nothing else.
connector loads PENDING
→ enrichment fires on the weekly batch
THREE CONTRACTS
Two layers, one seam
Change one of these and both layers move together. Everything else on each side is free to change independently.
01
The File Drop folder
Every inbound file lands here. Only the weekly batch starts enrichment; returns update existing records.
02
The identifier
Connector mints the UID; S0 adopts it. Every upsert joins on lead_uid, lead_id is history.
03
The HubSpot contact
Connector owns the 46 inbound columns; the pipeline owns enrichment. Retries never rewind advanced records.
Each layer stands alone — the connector loads HubSpot with no enrichment present, and vice versa
13 / 15
DEPLOY
Bound to the sheet, run in order
The script binds to the master spreadsheet and installs from one menu — authorised as the account that owns the Drive folders.
The master sheet with the Probate Automation menu open: Setup (Store HubSpot token, Preflight check, Store enrichment trigger token), Process File Drop now, Process DNC / enrichment returns now, Start and Stop watching File Drop, over the control-block columns
SETUP MENU, IN ORDER
  1. Store HubSpot token
  2. Preflight check
  3. Portal schema setup
  4. Verify upsert readiness
  5. Process File Drop now
  6. Start watching File Drop
PREFLIGHT PROVES
Folders, write test, a real conversion, the column contract and the token.
Authorize as the folder owner — that identity owns the trigger and receives failure email
14 / 15
THE INGEST LOOP
Drop a file, trust the load
weekly .xlsx
→
validate · dedupe · mint
→
master ledger
→
HubSpot PENDING
→
enrichment framework
RapidUpload v1.1 does the unglamorous work well — a recoverable, auditable load that never loses a row — so the enrichment framework and CoWork can build on accurate data you can trust.
15 / 15