Nightly Clay to Smartlead lead sync with a Google Sheets audit log

Build a code workflow that runs every night at 11pm on a cron trigger. The job is a deterministic pipeline that syncs approved leads from a Clay table into a Smartlead campaign and writes an audit row per lead to Google Sheets. No natural-language reasoning is needed. Every step is a known API call with structured inputs.

Build-time config the workflow should accept as inputs (set once, not per run): Clay table_id for the Ready to Send table, the name of the Clay status column and the approved value (default column Status, value Approved), the Smartlead campaign_id to push into, and the Google Sheets spreadsheet_id and tab name for the audit log.

Step 1. Call the Clay Query Tables operation against the configured table_id, filtering for the approval column equals the approved value. Iterate the returned cursor until every page is drained and collect all matching rows in memory. Preserve the Clay row id on each row so we can log it later.

Step 2. Read the audit log tab in Google Sheets and build a set of emails that have already been synced in prior runs. Use this to filter the Clay rows down to only leads whose email is not already in the sheet. This is the dedupe step and it is required so we never re-add a lead.

Step 3. Map each remaining Clay row to Smartlead's lead shape: email, first_name, last_name, company_name, and one personalization custom field carried through from Clay (make the source column name configurable, default personalization). Skip rows with a missing or invalid email.

Step 4. Chunk the mapped leads into batches of up to 400 (Smartlead's bulk import limit) and call the Smartlead Add Leads to Campaign operation once per batch, targeting the configured campaign_id.

Step 5. After each Smartlead batch completes, append one row per lead to the Google Sheets audit tab via the Append Values operation. Each row should contain: run timestamp (ISO 8601, UTC), email, smartlead_status (accepted, duplicate, or the error message Smartlead returned for that lead), and the source Clay row_id. Use one Append Values call per batch, not one per lead.

At the end of the run, emit a short structured summary: total approved rows found in Clay, total skipped because of the sheet dedupe, total sent to Smartlead, and total accepted vs rejected. Log this to the workflow run output. If any step throws a hard error (auth failure, table not found, campaign not found, sheet not writable) fail the whole run loudly so the operator sees it in the next morning's inbox and can fix the config.