How to Refresh OKR Sheets in Google Sheets (2026)

There are 3 ways to refresh your OKR sheets automatically, each with different tradeoffs on setup time, reliability, and maintenance cost. This article covers all 3.

Why OKR Sheets Go Stale Between Refreshes

The problem isn't that Google Sheets can't recalculate. It's that recalculation and data refresh are different things.

Ctrl+Shift+F9 forces all formulas to recalculate from their current inputs. If those inputs haven't changed because the Actuals tab still has Friday's paste, you've just recalculated stale data faster.

A real refresh means the Actuals tab gets new rows from your CRM or ticketing system. After that, recalculation flows automatically through the formula layer.

The cost of skipping this is concrete. On a 91-day quarter, 43 days in, a $4.2M ARR objective sits at 47.3% expected pace. If your Actuals tab shows $1.82M closed instead of $1.94M because HubSpot data is 5 days old, you're calculating 43.3% attainment vs. 46.2% attainment. That's a -$120K variance that moves the board conversation from "slightly behind but fine" to "needs an explanation."

How to Refresh OKR Sheets With a Multi-Tab Architecture

The 4-tab model separates concerns cleanly:

The Scoring tab never takes a manual input. Every cell references Assumptions or Actuals. If a number looks wrong, you know exactly which tab broke.

A real Scoring formula reaching across tabs looks like this:

=IFERROR(
  SUMIFS(Actuals!$D:$D,
    Actuals!$B:$B, Assumptions!$B$3,
    Actuals!$C:$C, ">=" & Assumptions!$D$3,
    Actuals!$C:$C, "<=" & TODAY())
  / Assumptions!$E$3,
  0
)

This pulls closed revenue from the Actuals tab, filtered to the OKR owner in Assumptions!$B$3 and the current period ($D$3 through today), divided by the target in $E$3. Nothing hardcoded. Change the period dates in Assumptions and the Scoring tab adjusts automatically.

The pace-adjusted attainment formula sits one column over:

=IFERROR(
  Scoring!B4 /
  (DAYS(TODAY(), Assumptions!$D$3) / DAYS(Assumptions!$D$4, Assumptions!$D$3)),
  0
)

With 43 of 91 days elapsed and 43.3% attainment, this returns 91.5% - slightly behind pace, not a crisis. That distinction only exists if Actuals is current.

The Three Refresh Methods, Honestly Compared

IMPORTRANGE recalculates when Google decides to recalculate it, not when you need it. Google's Sheets documentation states that volatile functions recalculate "when the spreadsheet is opened or when a dependent cell changes," but cached results can persist for 30 minutes or more. For a quarterly board pack assembled the day before the meeting, acceptable. For a weekly business review with live pipeline data, not acceptable.

Apps Script triggers are reliable up to Google's execution quota: 6 minutes per day for consumer accounts, 30 minutes per day for Workspace accounts (per Google's Apps Script quotas documentation, as of June 2026). A script pulling 200 HubSpot deal rows via API takes about 8 seconds per run. At hourly intervals, that's under 4 minutes per day - fine on either account type.

The no-code connector path survives credential rotations better than Apps Script, which stores OAuth tokens in PropertiesService and silently fails when they expire.

Auto-Refresh OKR Sheets: Pulling HubSpot Data Without Code

The source system for most finance-owned OKRs is HubSpot (revenue), Jira (product velocity), or Zendesk (support SLA). Getting that data into the Actuals tab automatically is where most OKR sheets actually break down.

Manual workflow: export CSV, paste into Actuals, trigger recalc. This works once. By week 3 of a quarter, someone skips a week, the sheet is 26 of 90 days stale, and nobody notices until the board meeting.

A practical example: OKR objective is $18.7M annual ARR with three key results - new logo ARR ($8.2M target), expansion ARR ($6.8M target), and renewal rate (94% target). The Actuals tab needs HubSpot deal data split by type. ModelMonkey connects to HubSpot and writes that directly to Actuals!A:F on a scheduled refresh. The Scoring tab reads it with:

// KR1: New Logo ARR - attainment vs. target
=SUMIFS(Actuals!$E:$E, Actuals!$D:$D, "New Business",
  Actuals!$C:$C, ">=" & Assumptions!$B$2) / Assumptions!$C$5

// KR2: Expansion ARR - attainment vs. target
=SUMIFS(Actuals!$E:$E, Actuals!$D:$D, "Expansion",
  Actuals!$C:$C, ">=" & Assumptions!$B$2) / Assumptions!$C$6

When ModelMonkey refreshes Actuals at 9:00 AM on June 8, 2026, the Dashboard shows that morning's pipeline numbers by 9:05 AM. No export, no paste, no "who last updated the sheet?"

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.

The Apps Script Refresh That Actually Works

If you want to own the pipeline yourself, Apps Script can pull from any API with a time-based trigger. Here's a minimal pattern for HubSpot closed deals:

function refreshActuals() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const actuals = ss.getSheetByName('Actuals');
  
  // Store token in Project Settings > Script Properties, not hardcoded
  const token = PropertiesService.getScriptProperties()
    .getProperty('HUBSPOT_PRIVATE_APP_TOKEN');

  // Pull closed deals from quarter start (read date from Assumptions tab in prod)
  const url = 'https://api.hubapi.com/crm/v3/objects/deals/search';
  const payload = {
    filterGroups: [{
      filters: [
        { propertyName: 'closedate', operator: 'GTE', value: '2026-04-01' },
        { propertyName: 'dealstage', operator: 'EQ', value: 'closedwon' }
      ]
    }],
    properties: ['dealname', 'amount', 'closedate', 'dealtype', 'hubspot_owner_id'],
    limit: 200
  };

  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    headers: { 'Authorization': 'Bearer ' + token },
    payload: JSON.stringify(payload)
  });

  const deals = JSON.parse(response.getContentText()).results;

  // Write rows to Actuals starting at A2; clear below last row
  const rows = deals.map(d => [
    d.id,
    d.properties.hubspot_owner_id,
    d.properties.closedate,
    d.properties.dealtype,
    parseFloat(d.properties.amount) || 0,
    d.properties.dealname
  ]);

  if (rows.length > 0) {
    actuals.getRange(2, 1, rows.length, 6).setValues(rows);
    actuals.getRange(rows.length + 2, 1, 200, 6).clearContent();
  }

  // Stamp last refresh time on Dashboard
  ss.getSheetByName('Dashboard')
    .getRange('B1')
    .setValue('Refreshed: ' + new Date().toLocaleString());
}

Set the trigger: Extensions > Apps Script > Triggers > Add Trigger > refreshActuals > Time-driven > Every hour.

Two things to watch: the 200-row limit in the payload (paginate with after cursors if you have more deals than that), and token storage. HubSpot private app tokens don't expire, which makes them safer to store in PropertiesService than OAuth tokens. If you're using OAuth, add a token refresh wrapper or the script will break silently when the token expires.

What Good OKR Scoring Looks Like

With a current Actuals tab, the Scoring output for a $4.2M quarterly revenue OKR in Q2 2026 (day 43 of 91) reads like this:

A -$46,200 variance looks fine in context. With stale Actuals showing $1.82M instead of $1.94M, the variance reads -$530K and someone schedules a meeting that didn't need to happen. That's the operational cost of not refreshing.