Quote Stage Sync → Google Sheet

Audience: developers & AI agents · Scope: append a row to a Google Sheet every time a quote's Pipedrive deal stage (or status) changes, from the site or the Pipedrive webhook · Last reviewed: 2026-07-31

TL;DR

Every time a quote's Pipedrive deal stage moves (or its status changes, e.g. Open → Lost), one row is recorded in a local table and later mirrored — append-only — into a Google Sheet (columns A–AA — the original mock quote_follow_up_syncs A–V plus W quote_title / X quote_status / Y rsm_name / Z last_feedback_on / AA change_reason). The change is captured synchronously in the DB at the moment it happens, so the acting agent is known (updated_by_user_id). When the change comes in from the Pipedrive webhook there is no acting user, so updated_by_user_id is left empty — that is the feature's headline rule. A cron job drains the not-yet-mirrored rows to the sheet.

The "stage" is the quote's native Pipedrive deal stage (quotes.pipedrive_stage_id → the imported #__configbox_external_pipedrive_stages), so the stage name and win probability come straight from that table — the same stages the follow-up landing page lets an agent pick and that the webhook updates as a deal moves along its pipeline.

Recording is always-on: every real stage/status change is written to the local table regardless of configuration (the table is the source of truth, like the sibling BcPipedriveChangeLog audit table). The quote_stage_sync_sheet setting only controls the downstream mirror — leave it blank to keep the rows local (nothing is pushed to a Google Sheet); set it to turn the sheet export on.

Not to be confused with quotes.pipedrive_stage_changed_on — a single "deal stage last moved at" (UTC) stamp kept on the quote row itself, updated on every stage change from any source. This feature is the append-only log (a row per movement, with actor/source/previous stage); that column is just the latest-change timestamp. See pipedrive-deal-updates.md → Deal-stage change timestamp.

Also distinct from the follow-up nudge sheet (nudge-cadence.md): that is a reconciled (rewritten each run) sheet of quotes currently due for a nudge, driven by the pipedrive_stage_changed_on clock + a per-stage cadence — not an append-only history like this one.

Dependency: this builds directly on the Quote Follow-Up work (the imported Pipedrive pipeline stages: the #__configbox_external_pipedrive_stages table, quotes.pipedrive_stage_id, and ConfigboxModelAdminbcpipedrivestages).

How it works

        deal stage / status change
   ┌───────────┬───────────────┬──────────────────────────┐
   │ (a) follow-up landing page │ (b) My Quotes portal      │ (c) Pipedrive webhook (inbound)
   │  recordFromWebsite()       │  recordFromWebsite()      │  recordFromWebhook()  (user = NULL)
   └───────────┴───────────────┴──────────────────────────┘
               ▼
   #__configbox_external_quote_stage_syncs   ← source of truth (one row per movement)
               │  cron:  cb_quote_stage_sync_export_sheet.php  (every few minutes)
               ▼
   Google Sheet tab (append-only, cols A–AA)  ← downstream mirror
                                               (except col Z, refreshed on the latest row)

Why a local table instead of appending to the sheet directly:

1. The acting agent is only known in the web request. The outbound "quote → Pipedrive deal" push runs later in cron with no session, so "who did it" must be captured synchronously — that is what BcQuoteStageSyncLog does.
2. sync_id, the per-quote counter, is_latest_sync and previous_stage are trivially correct when a local table is authoritative, and don't depend on the (rate-limited) Sheets API.
3. Rate limits. A single append is ~2 Sheets API calls, quota 60/min/user. A burst of webhook-driven changes must not append inline, so changes are written cheaply to the DB and drained at a controlled pace.

The recorder is idempotent (no new row unless tracked data actually moved) and never throws (an audit trail must not break the flow it observes). Tracked movement = stage, status, or the expected completion ("close") date — each gets its own row, with change_reason (AA) naming the move. While the latest row is still un-mirrored, a remaining "soft" change (lost reason / comment / bid date) amends that pending row in place rather than adding a no-movement row — this is how the My Quotes portal's two-step status-then-lost-reason flow keeps the reason on the same row.

Column mapping (sheet cols A–AA)

Col	Sheet field	Source
A	sync_id	SY- + zero-padded row id (SY-0001). Just a unique identifier; gaps are fine.
B	quote_id	numeric quote id
C	quote_serial	quote serial (e.g. AAA1003)
D	pipeline_id	the quote's Pipedrive pipeline id (quotes.pipedrive_pipeline_id)
E	pipeline_name	that pipeline's name (pipedrive_pipelines.name)
F	project_id	quote opportunity_id (empty when the quote isn't part of an opportunity)
G	sync_number_per_quote	running count per quote (1, 2, 3, …)
H	sync_date_time	UTC datetime of the change
I	is_latest_sync	Y on the newest row per quote, N on older rows
J	sync_source	the interface the change came through: Follow-up Landing Page (the no-login landing page) · Web Portal (the logged-in My Quotes portal) · Pipedrive Webhook. This is not how the agent authenticated and not the reason — that is sync_trigger.
K	sync_trigger	the reason: Follow-up email (landing page) · Inside Sales Change (webhook) · Pro-active update (My Quotes portal) · Initial creation (reserved)
L	updated_by_user_id	acting ConfigBox numeric user id — empty for webhook-sourced rows
M	branch_id	quote owner's branch (configbox_users.custom_2)
N	status	the agent-feedback status pipedrive_agent_feedback_status → Open / Won / Lost (the agent's own read, not necessarily the native deal status)
O	lost_reason	pipedrive_agent_feedback_lost_reason (the fixed-list category). When the category is "Other", the recorder substitutes the free-form elaboration from the native pipedrive_lost_reason so the sheet shows the actual reason, not the bare "Other".
P	stage	Pipedrive deal stage name (pipedrive_stages.name via quotes.pipedrive_stage_id)
Q	previous_stage	the prior row's stage name (empty for the first row)
R	probability_derived	the stage's deal_probability (from pipedrive_stages)
S	probability_model_version	the pipedrive_probability_model_version setting (free text)
T	expected_bid_date	pipedrive_agent_feedback_bid_date — the optional "Expected bid date" datepicker on the landing page (when the project goes out to bid)
U	expected_release_date	pipedrive_agent_feedback_completion_date — the "Expected completion" datepicker (when the order is released)
V	comments	pipedrive_agent_feedback_comments
W	quote_title	the quote name (BcQuote->name; the opportunity name when the quote is part of one)
X	quote_status	the quote's expiry status (BcQuote->status_name, the literal CURRENT / EXPIRED set from the quote's expiry date) — distinct from the agent-feedback status in col N (Open/Won/Lost)
Y	rsm_name	the quote owner's branch RSM name (ConfigboxModelAdminbcbranches rsm_name, keyed by configbox_users.custom_2 — same branch as branch_id, same source the nudge sheet uses for rsm_email)
Z	last_feedback_on	When the agent last responded on the follow-up landing page (UTC) — a real submission or a one-tap "Nothing has changed" confirmation. Straight from the quote's pipedrive_agent_feedback_updated_on, which touchFeedbackTimestamp() stamps on both paths. The one cell that is refreshed, not just appended — see below.
AA	change_reason	The movement that created this row, human-readable (stage: Design/ Budget → Quoted, status: Open → Lost, first record — …). Derived in record() from the same stage/status comparison that decides whether a row is recorded at all (migration 0.5.85).

Behaviour baked in:

• Lost quotes carry the stage (and pipeline) forward. The follow-up flow clears pipedrive_stage_id / pipedrive_pipeline_id on a "lost" update, so the sync row keeps the last known stage + pipeline.
• previous_stage is empty on the first recorded row for a quote (the log begins fresh — no backfill).
• Change-detection uses the numeric pipedrive_stage_id, so any real stage move is caught. A deal that moves to another pipeline is captured too — the quote tracks pipedrive_pipeline_id (kept in sync both ways, see pipeline-stages.md), so pipeline_id/pipeline_name follow the stage.
• Two independent optional dates. expected_bid_date (T) comes from the landing page's Expected bid date; expected_release_date (U) from its Expected completion date. Either can be blank. A change to the completion date fires its own row (it is tracked movement — a forecast moving is what the sheet exists to show); a bid-date-only edit stays soft and amends the still-pending row in place.

last_feedback_on (Z) — the one refreshed cell

Every other column is a point-in-time snapshot, so appending a row is enough. last_feedback_on is not, because of one deliberate gap: a "Nothing has changed" confirmation bumps the quote's pipedrive_agent_feedback_updated_on but records no sync row at all — nothing moved in the pipeline (see landing-page.md). Left alone, the most common "the agent did respond" signal would never reach the sheet, which defeats the point of having the column.

So the value is kept current at three points:

1. At record time — a new row is inserted with the quote's stamp (BcQuoteStageSyncLog::record()).
2. On a still-un-mirrored row — confirmNoChange() calls BcQuoteStageSyncLog::touchLastFeedback(), which updates that row's value in place. It never inserts a row and never touches a mirrored one. (Soft-field edits go through the usual amendPendingRow(), which now covers this column too.)
3. On an already-mirrored row — the export job's ConfigboxModelBcquotestagesync::refreshLastFeedbackCells() rewrites the column-Z cell of each quote's latest mirrored row when the quote's stamp has moved past it, then stores the new value so the DB row keeps mirroring the sheet. This runs even when there is nothing to append (a quiet run with no stale rows costs zero Sheets calls). Only the is_latest_sync row is refreshed — older rows keep the value they were written with, so history stays intact.

The net effect: the row a reader looks at for "where is this quote now" also answers "when did the agent last respond", including a no-change confirmation.

Files

Path	What
…/customization/updates/0.5.66.php	migration: creates the sync table (with pipeline_id/pipeline_name) + the two settings columns in the Pipedrive extension table
…/customization/updates/0.5.77.php	migration: adds the trailing quote_title / quote_status / rsm_name columns (sheet cols W/X/Y) to the sync table
…/customization/updates/0.5.81.php	migration: adds the trailing last_feedback_on column (sheet col Z) to the sync table and backfills it from each row's quote pipedrive_agent_feedback_updated_on
…/customization/updates/0.5.85.php	migration: adds the trailing change_reason column (sheet col AA) to the sync table
…/customization/updates/0.5.68.php	migration: adds pipedrive_agent_feedback_bid_date to the quotes table (source for expected_bid_date, col T)
…/customization/system_overrides/BcQuoteStageSyncLog.php	the recorder (synchronous, idempotent, never throws); sync_source/sync_trigger both passed in by each caller
…/customization/models/bcquotestagesync.php	exportPendingRowsToSheet() — the cron mirror
docroot/cli/cb_quote_stage_sync_export_sheet.php	cron entry point for the mirror
…/customization/updates/0.5.69.php	migration: creates the _external_settings_quote_follow_up extension table and relocates the follow-up settings into it
…/customization/models/adminbcsettings.php	quote_stage_sync_sheet + pipedrive_probability_model_version live in the Quote Follow-Up Landing Page group, stored in the #__configbox_external_settings_quote_follow_up extension table (storeExternally)
…/customization/models/bcquotelandingpage.php + views/bcquotelandingpage/tmpl/form.php + assets/javascript/quoteLandingPage.js	the optional "Expected bid date" datepicker (capture + storage); hook: recordFromWebsite(SOURCE_LANDING_PAGE, …) after saveAgentUpdate()
…/customization/models/bcpipedrive.php	hook: recordFromWebhook() after the inbound quote writes — gated on the webhook having touched a sheet-tracked field (stage/pipeline, status, lost reason, close date); any-field churn (value, person, owner …) no longer reaches the recorder, so a quote's first-ever row can only be caused by a tracked change
…/customization/controllers/bcmyquotes.php	hooks: recordFromWebsite(SOURCE_WEB_PORTAL, …) on the portal status / lost-reason handlers

Table #__configbox_external_quote_stage_syncs; reads #__configbox_external_pipedrive_stages and #__configbox_users.custom_2.

Setup / operations

Recording needs no configuration — it starts the moment the migrations run. Steps 3–5 only concern the optional Google-Sheet mirror.

1. Migrate. 0.5.66.php / 0.5.69.php run on the next page load (create the sync table and the _external_settings_quote_follow_up settings table).

2. Import the pipeline stages. The stage name + probability come from #__configbox_external_pipedrive_stages; make sure the Pipedrive pipeline is configured and the stages imported (admin → ConfigBox settings → Quote Follow-ups → Imported Pipedrive Stages → "Import stages from Pipedrive").

3. Share the sheet (only if you want the mirror). The Google service account (JSON key under Google Cloud Integration) must have write access to the target sheet. The target must be a native Google Sheet — an uploaded .xlsx/Office file is rejected by the Sheets API (FAILED_PRECONDITION: "The document must not be an Office file"), so convert it first (File → Save as Google Sheets). This applies to every sheet export in the codebase, not just this feature.

4. Configure (admin → ConfigBox settings → Quote Follow-Up Landing Page):

   • Quote stage-change log sheet — the target as spreadsheetId#gid (spreadsheet id + numeric tab gid). Blank = keep rows local only (no sheet mirror); recording still happens.
   • Win-probability model version — a free-text label written into column S (e.g. v1.0).

   The target tab must start empty (or already carry the exact current header). The exporter checks A1 on every run (sheetHasHeader(), even a quiet run with nothing to append): when A1 is empty it writes its HEADERS row and clears every stored sheet_row reference — a missing header means the tab was wiped or recreated, so the stored row numbers point at cells that no longer hold what they did, and left in place the is_latest flip and last_feedback_on refresh would write into those ghost cells (re-expanding the emptied grid and pushing the next append far below the header). Clearing them re-anchors the mirror at the top; both cell-writers skip NULL-sheet_row rows. So a wiped tab self-heals on the next run. What does not self-heal: a tab that already has a different/older header row (e.g. an earlier mock layout missing quote_serial / pipeline_id / pipeline_name) — A1 is non-empty, so the exporter just appends below it and every row lands under the wrong labels. Point at a fresh empty tab and let the exporter write the header. The canonical column order is the A–AA table above / HEADERS in ConfigboxModelBcquotestagesync. (Columns are only ever appended — quote_title/quote_status/ rsm_name were added as W/X/Y, last_feedback_on as Z, change_reason as AA — so an existing tab keeps its earlier data; add the new headers to its header row, or start a fresh tab, so the new cells land under the right labels.)

5. Add the cron (every few minutes) to drain rows to the sheet:

   */5 * * * * php /var/www/betacalco.com/cli/cb_quote_stage_sync_export_sheet.php

   The runner is mutually exclusive via a MySQL named lock; rows are marked mirrored only after a successful append, so a Sheets outage just leaves them pending for the next run.

Turning the sheet mirror off: blank the sheet setting. Recording to the local table continues; only the export to Google Sheets stops (rows just accumulate un-mirrored). To stop recording entirely you would have to remove the hooks — by design there is no "off" switch for the audit trail.

Fixed — sheet_row drift after DB-row deletions. The exporter used to place each appended row from the DB's COUNT(sheet_synced = 1) (nextRow = alreadySynced + 2). The sheet is append-only, but its DB rows can be deleted (e.g. clearStageSync in the tests), so once any synced row was deleted the DB count fell below the sheet's real length and the stored sheet_row drifted low — and because the cross-run is_latest flip (flipSupersededLatestCells) writes N by that stored sheet_row, the next multi-sync export would flip the wrong cell. Fixed by having ConfigboxModelGooglesheets::appendSheetValues() return the real first row from Google's updatedRange, which the exporter now stores; self-correcting from here on.

One-time repair for environments that ran the old exporter. The fix prevents future drift but does not retro-fix already-stored sheet_row values. To repair: read the sheet's column A, map each SY-NNNN sync id to its actual row number, and run UPDATE #__configbox_external_quote_stage_syncs SET sheet_row = <actual> WHERE id = <n> where they differ (e.g. SY-0184 → row 44). Rows for quotes that later re-sync or get cleaned up correct themselves anyway. (The dev sheet had 2 such rows — repaired 2026-07-10.)

Testing notes

Verified end-to-end against a local quote and a real (throwaway, native) Google Sheet:

• Always-on recording: a real quote's change records a row even with no sheet configured (the local table is the source of truth; the sheet only gates the mirror) — confirmed by calling the recorder against a live quote with a blank sheet setting and seeing the row appear.
• Record half: sync numbering, is_latest_sync flipping, previous_stage chaining, lost-stage carry-forward, updated_by_user_id = user on the web path and empty on the webhook path, sync_source = the interface (Follow-up Landing Page / Web Portal / Pipedrive Webhook) independent of sync_trigger, deal-stage name + probability resolution (pipedrive_stages), pipeline id + name resolution (pipedrive_pipelines), branch lookup (custom_2), the configured model-version label, the two independent dates (expected_bid_date from the bid-date field, expected_release_date from the completion date), idempotency, and the two-step lost-reason amend.
• Recorder semantics (Playwright, capture-safe): tests/specs/agent/quote-stage-sync-behaviour.spec.ts drives the real follow-up form through a sequence and reads the recorded rows back over the test-support endpoint (support.stageSyncRows, no sheet, no Pipedrive), locking in: a first submit records exactly one row (numbering, is_latest, empty previous_stage, source/trigger, the acting agent); idempotency (a same status+stage re-submit adds no row); the soft-field amend-in-place (a comment-only edit updates the still-un-mirrored row rather than appending); an actual stage move appends row 2 (sync number, is_latest flip, previous_stage chaining); and Lost appends a row that carries the stage + pipeline forward and surfaces the reason. This is the everyday counterpart to the opt-in live smoke below.
• Landing-page dates (Playwright): tests/specs/agent/quote-follow-up-variations.spec.ts fills both date pickers and asserts each lands in its own quote column, and that a "lost" update clears both.
• Sheet mirror: header + append (cols A–AA, incl. pipeline_id/pipeline_name, quote_title/quote_status/rsm_name, last_feedback_on and change_reason), and the cross-run is_latest cell flip (an earlier row's column-I cell is set to N when a newer row is appended) — confirmed by reading the sheet back. With no sheet configured the export CLI reports "No target sheet configured" and no-ops (recording is unaffected).
• Live end-to-end (Playwright, opt-in): tests/specs/agent/quote-stage-e2e-live.spec.ts (run with E2E_LIVE_SMOKE=1) drives a real stage change on the follow-up page, runs the real export, and reads the row back from the actual Google Sheet — asserting the stage name, probability_derived, status and is_latest_sync, column-aligned. Same run also verifies the real Pipedrive deal. This is the automated counterpart to the manual "reading the sheet back" check above. See pipedrive-testing.md → Live end-to-end smoke.

Decisions (resolved with the team)

• Ids (B, C, L): store the numeric quote id, add a separate quote_serial column, numeric user id — no Q-/U- prefixes.
• project_id (F): the quote's opportunity_id; empty when the quote isn't part of an opportunity.
• branch_id (M): the quote owner's branch = configbox_users.custom_2 (the "Branch" field, joined to ConfigboxModelAdminbcbranches). ("agency_id" in the mock = branch.)
• status (N): Open / Won / Lost from the agent-feedback status (pipedrive_agent_feedback_status) — the agent's own read, not necessarily the native Pipedrive deal status. "On Hold" (in the mock) is not a real status and is dropped.
• lost_reason (O): the fixed list, plus free text when the agent picks "Other".
• stage / probability (P, R): the Pipedrive deal stage — its name and deal_probability from pipedrive_stages (the mock's collapsed labels/numbers were misleading).
• pipeline_id / pipeline_name (D, E): the quote's Pipedrive pipeline, added once quotes track a pipeline (kept in sync with the deal both ways — see pipeline-stages.md).
• probability_model_version (S): a configurable free-text setting (Quote Follow-Up group), not code.
• Recording is always-on (was opt-in): the local table is written on every real change regardless of whether a sheet is configured — the table is the source of truth, matching the always-on BcPipedriveChangeLog audit table. Only the sheet mirror is gated on quote_stage_sync_sheet.
• Settings storage: quote_stage_sync_sheet and pipedrive_probability_model_version live in the Quote Follow-Up Landing Page settings group, in the #__configbox_external_settings_quote_follow_up extension table (storeExternally) alongside quote_followup_token_lifetime_days — see landing-page.md "Settings".
• sync_source (J) — the interface (resolved): the interface the change came through, distinct from both the reason (sync_trigger) and how the agent authenticated: Follow-up Landing Page (the no-login landing page), Web Portal (the logged-in My Quotes portal), Pipedrive Webhook. Source tells us the most-used interface; trigger tells us the main reasons. The two are independent and can differ — e.g. an agent updating a quote in the Web Portal without ever opening the emailed link is Web Portal (source) + Pro-active update (trigger). Passed in by each caller.
• sync_trigger (K): Initial creation (deal/quote creation — reserved), Follow-up email (landing-page usage), Pro-active update (My Quotes portal), Inside Sales Change (webhook / internal team moving the deal). The trigger is passed in by each hook, so new event sources are easy to add.
• expected_bid_date (T) vs expected_release_date (U) — the two dates (resolved): the landing page now captures two independent optional dates. The dedicated "Expected bid date" (pipedrive_agent_feedback_bid_date) → expected_bid_date (T); the existing "Expected completion" date (pipedrive_agent_feedback_completion_date, "when you expect the order to be released") → expected_release_date (U). Both are optional; either may be blank. (The completion date also still feeds Pipedrive's stock expected_close_date on the outbound deal push — that mapping is unchanged.)
• What fires a row: a stage change, a status change, or an expected-completion-date change (remaining no-movement edits — comment, bid date — amend the pending row instead). The close date was promoted to tracked movement on 2026-07-31: as a soft field its change was silently LOST once the pending row had been mirrored, because the amend only touches un-mirrored rows.
• My Quotes portal (was undecided): its status/lost-reason changes are recorded (Pro-active update). Because that UI sets status then lost-reason in two AJAX calls, the recorder amends the still-pending row's lost reason on the second call rather than emitting a bare row.
• is_latest_sync (I): maintained on the sheet (the previously-latest row's cell is flipped to N).
• sync_date_time (H): UTC.

Still open

Nothing outstanding — the previously-open items (the two dates, and the sync_source labels) are resolved above under Decisions.