Nightly HubSpot contact backfill from a Google Sheet

Every night at 2am, run a CRM backfill job that fills in missing contact data on HubSpot using a Google Sheet as the work queue. This is a deterministic pipeline with no judgement calls, so build it as a code workflow.

Trigger: cron, every day at 02:00 in the workspace timezone.

Step 1. Read the queue from Google Sheets. Use Get Values to read rows from the configured spreadsheet (default tab name 'CRM Backfill Queue', tab name configurable). Each row has columns: first_name, last_name, company_domain, status, email, credits_consumed, last_run_at. Select only rows where status is blank or equal to 'pending'. Take at most 100 rows per run to stay well under LeadMagic's 300 requests per minute limit.

Step 2. For each candidate row, call LeadMagic Email Finder with the first name, last name, and company domain. If Email Finder returns a verified deliverable work email, also call LeadMagic Mobile Finder using that email. If Email Finder returns not_found or the email is not verified, skip Mobile Finder for that row and mark the row 'not_found'.

Step 3. Track the X-Credits-Remaining response header on every LeadMagic call. If it drops below a configured floor (default 500), stop processing new rows for this run and leave the remaining rows as 'pending' so the next run picks them up. This guards against surprise zero balances for the ops team.

Step 4. Send the enriched contacts to HubSpot using Batch Upsert Contacts, matched on the email property, in batches of up to 100. Write first name, last name, phone (when Mobile Finder returned a number), and stamp a custom property called 'last_enriched_at' with today's date in YYYY-MM-DD. Upsert means missing contacts get created and partial contacts get filled in.

Step 5. Write each row's outcome back to the same Google Sheet using Update Values. Set the status column to 'enriched', 'not_found', or 'error'. Write the resolved email into the email column. Write credits_consumed as the sum of the credits_consumed values returned by Email Finder and Mobile Finder for that row. Write today's date into last_run_at. On error, set status to 'error' and include a short reason.

Configurable inputs: spreadsheet id, tab name, daily run time, max rows per run, LeadMagic credit floor, the name of the HubSpot custom property used for last enriched date, and which HubSpot phone property to write to.