Pull HubSpot deals into a forecast sheet

Northwind’s sales team lives in HubSpot, but the monthly forecast meeting happens in a spreadsheet. So someone exports the deal pipeline, pastes it in, and then hand-applies a probability to each stage to get a weighted number. By the time the sheet is ready it is already a day stale, and the probabilities drift every time a different person does the export.

This script closes that gap. It pulls every open deal straight from the HubSpot CRM API, multiplies each amount by the win probability for its stage, and writes both the raw and weighted figures to a forecast sheet. Run it on a schedule and the forecast is always current — and the probabilities are the same every time, because they live in one config block instead of in someone’s head.

What you’ll need

• A HubSpot account with deals in the pipeline, and a private app token with the crm.objects.deals.read scope.
• That token saved as HUBSPOT_KEY in Script Properties — see Store API keys and secrets securely.
• A Google Sheet to hold the forecast. The script writes to the first tab and clears it on every run, so use a dedicated sheet.
• The internal stage IDs from your HubSpot pipeline. They are not the friendly names you see in the UI — see “Watch out for” for how to find them.

The script

// The HubSpot private app token, kept out of the code.
const HUBSPOT_KEY = PropertiesService.getScriptProperties()
  .getProperty('HUBSPOT_KEY');

// The spreadsheet that holds the forecast. The first tab is rebuilt each run.
const FORECAST_SHEET_ID = '1abcForecastId';

// Win probability for each HubSpot deal stage, keyed by internal stage ID.
// These are the numbers the forecast is weighted by — edit them here, once.
const STAGE_PROBABILITY = {
  appointmentscheduled: 0.2,
  qualifiedtobuy: 0.4,
  presentationscheduled: 0.6,
  decisionmakerboughtin: 0.8,
  contractsent: 0.9,
  closedwon: 1.0,
  closedlost: 0.0,
};

/**
 * Pulls open deals from HubSpot, weights each amount by its stage
 * probability, and writes a forecast to the spreadsheet.
 */
function pullDeals() {
  // 1. Ask HubSpot for deals, requesting only the four properties we use.
  const url = 'https://api.hubapi.com/crm/v3/objects/deals' +
    '?limit=100&properties=dealname,amount,dealstage,closedate';
  const res = JSON.parse(UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + HUBSPOT_KEY },
    muteHttpExceptions: true,
  }).getContentText());

  // 2. Bail out early if HubSpot returned nothing usable.
  if (!res.results || !res.results.length) {
    Logger.log('No deals returned from HubSpot — nothing to do.');
    return;
  }

  // 3. Turn each deal into a row, adding a weighted amount column.
  const rows = res.results.map((d) => {
    const stage = d.properties.dealstage;
    const amount = parseFloat(d.properties.amount || 0);
    // An unknown stage gets probability 0 so it never inflates the forecast.
    const probability = STAGE_PROBABILITY[stage] ?? 0;
    return [
      d.properties.dealname,
      stage,
      amount,
      Math.round(amount * probability),
      d.properties.closedate,
    ];
  });

  // 4. Rebuild the first tab from scratch so stale deals never linger.
  const sheet = SpreadsheetApp.openById(FORECAST_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 5).setValues([
    ['deal', 'stage', 'amount', 'weighted', 'closeDate'],
  ]);
  sheet.getRange(2, 1, rows.length, 5).setValues(rows);

  // 5. Add a total row so the forecast number is right there in the sheet.
  const totalRow = rows.length + 2;
  const weightedTotal = rows.reduce((sum, r) => sum + r[3], 0);
  sheet.getRange(totalRow, 1).setValue('Weighted forecast');
  sheet.getRange(totalRow, 4).setValue(weightedTotal);

  Logger.log('Wrote ' + rows.length + ' deals, weighted total ' + weightedTotal);
}

How it works

1. pullDeals calls the HubSpot CRM deals endpoint, asking for only the four properties the forecast needs. Requesting a narrow property list keeps the response small and fast.
2. If HubSpot returns no results, it logs a message and stops — no point clearing the sheet just to write an empty header.
3. For each deal it reads the stage, parses the amount, and looks up the win probability in STAGE_PROBABILITY. The ?? 0 fallback means a stage you have not mapped contributes nothing rather than crashing the run.
4. It clears the first tab and writes a fresh header plus one row per deal, with a new weighted column alongside the raw amount.
5. It sums the weighted column and writes a single total row, so the forecast figure is in the sheet itself — no formula to maintain.

Example run

Say HubSpot returns three open deals:

deal	stage	amount
Acme retainer	qualifiedtobuy	12000
Globex rollout	contractsent	30000
Initech pilot	appointmentscheduled	5000

After a run, the forecast sheet holds:

deal	stage	amount	weighted	closeDate
Acme retainer	qualifiedtobuy	12000	4800	2025-09-30
Globex rollout	contractsent	30000	27000	2025-09-15
Initech pilot	appointmentscheduled	5000	1000	2025-10-20
Weighted forecast			32800

The raw pipeline is 47,000, but the weighted forecast — the number the meeting actually wants — is 32,800, and it lands the same way every run.

Trigger it

A forecast is only useful if it is current, so run this on a schedule:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose pullDeals, a Time-driven source, and a Day timer at an early hour — say 6am to 7am — so the sheet is fresh before anyone opens it.

For a busier pipeline, a twice-daily trigger keeps the forecast closer to real time without much extra cost.

Watch out for

• Stage IDs are not stage names. dealstage returns an internal ID like qualifiedtobuy, not the label you see in HubSpot. Find the real IDs under Settings → Objects → Deals → Pipelines, or log one deal’s properties and read them off. Any stage missing from STAGE_PROBABILITY is weighted at 0.
• The script reads one page of 100 deals. HubSpot paginates results, so a larger pipeline needs a loop that follows res.paging.next.after until it is absent — the same pattern as a cursor-based API.
• It pulls every deal, including closedlost. Those are weighted at 0 so they do not skew the forecast, but they still take up rows. Filter them out in the map step if you want a cleaner sheet.
• The API token carries CRM read access. Keep it in Script Properties, never in the code, and scope the private app to deals only.
• The sheet is cleared and rebuilt every run. Do not add manual notes or extra columns to the first tab — they will be wiped. Keep working notes on a second tab instead.