Snapshot your HubSpot pipeline weekly in Google Sheets using n8n

Prerequisites

Step 1: Prepare the Google Sheet

Create a new Google Sheet and set up 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 actual HubSpot pipeline stages. The automation will append a new row each week with the snapshot data.

Step 2: Create the workflow and schedule trigger

Open n8n and create a new workflow. Add a Schedule Trigger node:

• Trigger interval: Weeks
• Day of week: Monday
• Hour: 8
• Minute: 0
• Timezone: Set to your team's timezone (n8n cloud defaults to UTC)

Step 3: Fetch deal stages from HubSpot

Add an HTTP Request node to get stage ID-to-label mappings:

• Method: GET
• URL: https://api.hubapi.com/crm/v3/pipelines/deals
• Authentication: Predefined Credential Type → HubSpot API

This returns all pipelines and their stages. You'll use the id and label from results[].stages[] to translate stage IDs in the deal data.

Step 4: Search for active deals

Add a second HTTP Request node to pull all deals using the HubSpot Search API:

• Method: POST
• URL: https://api.hubapi.com/crm/v3/objects/deals/search
• Body content type: JSON
• Body:

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

Step 5: Aggregate metrics with a Code node

Add a Code node (set to "Run Once for All Items") to compute the snapshot row:

const deals = $input.all().map(item => item.json);
const results = deals[0]?.results || deals;
const pipelineData = $('Fetch Stages').first().json.results;
 
// Build stage name lookup
const stageMap = {};
const stageOrder = [];
for (const pipeline of pipelineData) {
  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;
  const stageName = stageMap[stageId] || stageId;
 
  if (!byStage[stageName]) {
    byStage[stageName] = { count: 0, value: 0 };
  }
  byStage[stageName].count += 1;
  byStage[stageName].value += amount;
}
 
// Build the row in stage order
const today = new Date().toISOString().split('T')[0];
const row = [today, totalValue, results.length];
 
for (const stageName of stageOrder) {
  const data = byStage[stageName] || { count: 0, value: 0 };
  row.push(data.count, data.value);
}
 
return [{ json: { row, totalValue, dealCount: results.length, byStage } }];

Step 6: Append the row to Google Sheets

Add a Google Sheets node:

• Resource: Sheet Within Document
• Operation: Append Row
• Document: Select your snapshot spreadsheet
• Sheet: Select the sheet tab
• Mapping: Map each column from the row array output of the Code node

Alternatively, if you want to pass the full row array at once, use an HTTP Request node to call the Sheets API directly:

• Method: POST
• URL: https://sheets.googleapis.com/v4/spreadsheets/YOUR_SPREADSHEET_ID/values/Sheet1!A1:append?valueInputOption=USER_ENTERED
• Authentication: Google OAuth2
• Body:

{
  "values": [{{ $json.row }}]
}

Step 7: Add error handling

1. In each HTTP Request node, enable Settings → Retry On Fail with 2 retries and 5 second wait
2. Create a separate Error Workflow with an Error Trigger node that sends you a notification when the main workflow fails
3. In the main workflow, go to Settings → Error Workflow and select your error workflow

Step 8: Test and activate

1. Click Execute Workflow to run the full workflow manually
2. Check the Code node output — verify the row array has the right number of columns
3. Open Google Sheets and confirm a new row was appended with today's date and metrics
4. Toggle the workflow to Active so the Schedule Trigger fires every Monday

Cost and maintenance

• n8n cloud: starts at $24/mo for the Starter plan (2,500 executions/month). A weekly snapshot uses ~4 executions/month. Self-hosted n8n is free.
• Google Sheets API: free within Google's standard quotas (500 requests per 100 seconds per project).
• Maintenance: minimal once running. Update the Code node stage mapping if you rename pipeline stages in HubSpot. Monitor the error workflow for failures.

Next steps

Once the basic snapshot is running, consider adding:

• Week-over-week deltas — read the previous row from Google Sheets in the Code node and calculate percentage changes
• Weighted pipeline — multiply each stage's value by its historical close rate for a weighted total
• Chart automation — use Google Sheets' built-in charting on the snapshot data to track trends visually
• Slack notification — add a Slack node at the end to confirm the snapshot was recorded, with a link to the Sheet