Weekly Pipedrive pipeline snapshot to Google Sheets and Slack

Build me a code workflow that runs every Monday at 8:00am America/New_York and produces a deterministic weekly snapshot of my Pipedrive pipeline. It should be fully reproducible, no LLM judgement needed.

Step 1: Use Pipedrive List Pipelines to enumerate every active pipeline. Capture each pipeline's id and name. Skip archived or deleted pipelines.

Step 2: For each pipeline, use Pipedrive List Deals filtered by that pipeline_id and status=open to pull every open deal. Paginate through all pages. For each deal, keep the deal id, stage_id, stage name, value, currency, add_time, and update_time.

Step 3: In code, aggregate the deals by (pipeline, stage). For each (pipeline, stage) bucket compute: deal_count, total_value (sum of value, converted to a single reporting currency if needed), average_days_in_stage (average of now minus stage_change_time, or update_time if stage_change_time is unavailable), and stalled_count (count of deals whose update_time is more than 14 days before the run time). Make the 14 day stalled threshold a configurable constant.

Step 4: Use Google Sheets Append Values to append one row per (pipeline, stage) bucket to a designated tracking tab. Columns in order: snapshot_date (the run date in America/New_York, YYYY-MM-DD), pipeline_name, stage_name, deal_count, total_value, average_days_in_stage, stalled_count. Use USER_ENTERED so numbers and dates are parsed correctly. Do not overwrite existing rows, only append.

Step 5: Use Slack Bot Send a Message to post one summary message to the configured leadership channel. The message should include: total open deal count and total open value across all pipelines, the count of stalled deals, the pipeline with the largest open value, and a link to the Google Sheets tab. Format it tightly with a bold headline and a short bullet list.

Make these configurable inputs: spreadsheet_id, tracking tab name, Slack channel, reporting timezone, stalled threshold in days, and an optional currency for value conversion. Log a final summary line with total pipelines covered, total deals processed, and total value so a human can sanity check the run.