Sync Google Sheets leads into Bigin contacts and deals

Every 30 minutes, pull new lead rows from a Google Sheets intake tab and push them into Bigin as contacts, optionally creating deals for rows flagged as opportunities.

Trigger: cron, every 30 minutes.

Configurable inputs the workflow should expose: the Google Sheets spreadsheet id, the intake tab name, the A1 range to scan (default like Intake!A2:I), the column layout (first name, last name, email, phone, company, source, create-deal flag, processed marker), the Bigin Sub_Pipeline name, and the Bigin Stage name for new deals.

Step 1 — Read the sheet. Use Google Sheets Get Values on the configured range to load all rows from the intake tab. Skip the header row. For each data row, treat the processed marker column as the skip signal: if that cell already contains a value (for example "Synced" or a timestamp), skip the row. Also skip rows with an empty email, since Bigin upserts key on email.

Step 2 — Normalize each row into a Bigin contact payload. Map first name to First_Name, last name to Last_Name, email to Email, phone to Phone, company to Account_Name, and the source column to Lead_Source. Trim whitespace and drop empty fields.

Step 3 — Upsert contacts. Call Bigin Upsert Records against the Contacts module, wrapping records in the data array (max 100 per call, so batch if there are more), with duplicate_check_fields set to Email so re-runs update the existing contact instead of duplicating. After the call, iterate the per-record results and check each entry's code and status — a 200 HTTP response can still contain per-record failures like DUPLICATE_DATA, MANDATORY_NOT_FOUND, or INVALID_DATA. Capture the returned record id (details.id) for each successful upsert so we can link deals to it.

Step 4 — Create deals for flagged rows. For every row whose create-deal column is truthy ("yes", "true", checkbox true, non-empty), build a Pipelines record with Deal_Name (default to a template like "[Company] — [First name] [Last name]", falling back to the email if company is missing), Sub_Pipeline set to the configured sub-pipeline name, Stage set to the configured stage name, and Contact_Name set to the Bigin contact id from step 3. Call Bigin Insert Records against the Pipelines module. Again check per-record code/status in the response — Bigin requires Deal_Name, Sub_Pipeline, and Stage on Pipelines inserts, so surface any MANDATORY_NOT_FOUND clearly.

Step 5 — Mark processed rows in the sheet. For every row that upserted successfully (and, if applicable, whose deal insert also succeeded), write a value into the processed marker column — either a static string like "Synced" or an ISO timestamp. Use Google Sheets Update Values with valueInputOption USER_ENTERED, targeting the specific A1 cells for those rows. If any rows partially failed (contact ok, deal failed), leave the processed marker empty so the next run retries — but log the failure so the operator can see it.

Constraints and nuance: never write the processed marker for a row that had a Bigin per-record failure; the response's code field is the source of truth, not the HTTP status. Bigin request bodies always wrap records in a data array. Use the apiDomain from the Bigin credential as the base URL — do not hardcode the US domain. Respect Bigin's max of 100 records per insert/upsert call. If Bigin returns 429 (credit or concurrency exhaustion), back off and stop for this run; the next 30-minute tick will retry the unmarked rows.

Output: a run summary the operator can inspect — count of rows scanned, contacts upserted, deals created, rows skipped (already processed, missing email), and any per-record failures with their Bigin error codes.