Snapshot your HubSpot pipeline weekly in Google Sheets using Zapier

Prerequisites

Overview

Zapier's built-in HubSpot integration doesn't have a "get all deals" action — it works on individual records. For a weekly pipeline snapshot, you'll use Schedule by Zapier to trigger weekly, Webhooks by Zapier to call the HubSpot Search API directly, Code by Zapier to aggregate metrics, and Google Sheets to append the snapshot row.

Step 1: Prepare the Google Sheet

Create a new Google Sheet with headers in the first row:

Date | Total Value | Deal Count | Appointment Scheduled (Count) | Appointment Scheduled (Value) | Qualified (Count) | Qualified (Value) | Proposal (Count) | Proposal (Value) | Closed Won (Count) | Closed Won (Value)

Adjust the stage columns to match your HubSpot pipeline stages. Each weekly run will add a new row below the headers.

Step 2: Add a Schedule trigger

Create a new Zap. Choose Schedule by Zapier as the trigger:

• Trigger event: Every Week
• Day of the week: Monday
• Time of day: 8:00am
• Timezone: Select your team's timezone

Step 3: Fetch pipeline stages via Webhooks

Add a Webhooks by Zapier action step to get stage names:

• Action event: Custom Request
• Method: GET
• URL: https://api.hubapi.com/crm/v3/pipelines/deals
• Headers:
  • Authorization: Bearer YOUR_HUBSPOT_PRIVATE_APP_TOKEN
  • Content-Type: application/json

This returns stage IDs and their human-readable labels. You'll pass this to the Code step.

Step 4: Fetch deals via Webhooks

Add another Webhooks by Zapier action step:

• Action event: Custom Request
• Method: POST
• URL: https://api.hubapi.com/crm/v3/objects/deals/search
• Headers:
  • Authorization: Bearer YOUR_HUBSPOT_PRIVATE_APP_TOKEN
  • Content-Type: application/json
• Data (raw JSON):

{
  "filterGroups": [
    {
      "filters": [
        {
          "propertyName": "pipeline",
          "operator": "EQ",
          "value": "default"
        }
      ]
    }
  ],
  "properties": [
    "dealname", "amount", "dealstage",
    "closedate", "createdate"
  ],
  "limit": 100
}

Step 5: Summarize with Code by Zapier

Add a Code by Zapier step (JavaScript) to aggregate the deal data into a single snapshot row:

• Input Data:
  • rawDeals: Map the response body from the deals Webhook step
  • rawStages: Map the response body from the stages Webhook step

const deals = JSON.parse(inputData.rawDeals);
const stagesData = JSON.parse(inputData.rawStages);
const results = deals.results || [];
 
// Build stage map and ordered list
const stageMap = {};
const stageOrder = [];
for (const pipeline of stagesData.results) {
  for (const stage of pipeline.stages) {
    stageMap[stage.id] = stage.label;
    stageOrder.push(stage.label);
  }
}
 
// Calculate metrics
let totalValue = 0;
const byStage = {};
 
for (const deal of results) {
  const amount = parseFloat(deal.properties.amount || '0');
  totalValue += amount;
 
  const stageId = deal.properties.dealstage || 'unknown';
  const stageName = stageMap[stageId] || stageId;
 
  if (!byStage[stageName]) {
    byStage[stageName] = { count: 0, value: 0 };
  }
  byStage[stageName].count += 1;
  byStage[stageName].value += amount;
}
 
// Build output — Zapier Code returns a flat object
const today = new Date().toISOString().split('T')[0];
const output = {
  date: today,
  totalValue: totalValue.toFixed(0),
  dealCount: results.length.toString(),
};
 
// Add per-stage metrics
for (let i = 0; i < stageOrder.length; i++) {
  const name = stageOrder[i];
  const data = byStage[name] || { count: 0, value: 0 };
  output[`stage_${i}_count`] = data.count.toString();
  output[`stage_${i}_value`] = data.value.toFixed(0);
}
 
return output;

Step 6: Append row to Google Sheets

Add a Google Sheets action step:

• Action event: Create Spreadsheet Row
• Spreadsheet: Select your snapshot sheet
• Worksheet: Select the sheet tab
• Map each column from the Code step output:
  • Date: {{date}}
  • Total Value: {{totalValue}}
  • Deal Count: {{dealCount}}
  • Appointment Scheduled (Count): {{stage_0_count}}
  • Appointment Scheduled (Value): {{stage_0_value}}
  • Continue for each stage...

Step 7: Test and publish

1. Click Test on each step to verify the data flows correctly
2. Check the Google Sheet — confirm a new row appeared with today's date and the correct metrics
3. Turn the Zap On

Cost and task usage

• Professional plan: $29.99/mo (billed annually) with 750 tasks/month
• This Zap uses ~4 tasks/month (one per Monday): 1 schedule trigger + 1 webhook (stages) + 1 webhook (deals) + 1 code + 1 Google Sheets = 5 steps per run. Zapier counts successful actions toward task usage.
• If you need the Team plan for more tasks, that's $103.50/mo.

Limitations

• No native pagination: Zapier can't loop through HubSpot's paginated API results without Code or additional Webhook steps. For 100+ deals, this gets cumbersome.
• Stage name indexing: The Code step outputs stage_0_count, stage_1_count, etc. If you reorder stages in HubSpot, the column mapping breaks. Document the index-to-stage mapping.
• Code step sandboxing: Code by Zapier can't make HTTP requests — all data must be passed in. Complex transformations may hit the 30-second timeout.

Next steps

• Add week-over-week deltas — use a Zapier Lookup Table or read the previous row from Google Sheets to calculate changes
• Slack notification — add a Slack step at the end to confirm the snapshot was recorded
• Conditional alerts — add a Filter step to notify you only when pipeline value drops below a threshold