Real-Time Business Dashboard Without a Data Team: Google Sheets + brooked.io

SMBs use an average of seven different business applications, and 46% of business leaders report feeling overwhelmed by the volume of data those tools produce. You do not need BigQuery, Tableau, or a data engineer to turn that data into a dashboard you can actually use. This guide shows you exactly how to build a real-time business dashboard in Google Sheets, from choosing your KPIs to scheduling automatic refreshes, with a concrete example connecting HubSpot, QuickBooks, and GA4.

Why You Don't Need a Data Team for This

The Common Misconception

When most people hear "real-time business dashboard," they imagine a data warehouse, SQL queries, and a BI analyst. That setup exists because it scales to millions of rows and dozens of sources. Most SMBs do not have millions of rows. They have a few thousand contacts in their CRM, a few hundred invoices in their accounting software, and a few thousand monthly website sessions, all of which fit comfortably in a Google Sheet.

The modern stack for SMB dashboards is:

• Google Sheets as the data layer and reporting layer
• A data connector (like brooked.io) to move data from your apps into Sheets automatically
• Looker Studio or native Sheets charts for visualization

No SQL. No cloud infrastructure. No dedicated analyst.

What "Real-Time" Actually Means for Most Businesses

For most business decisions, "real-time" means data that is current as of this morning, not data updated to the millisecond. A dashboard that refreshes every hour gives your team accurate pipeline value, revenue figures, and traffic numbers for every decision made during a workday. That is sufficient for virtually all SMB use cases and far better than a spreadsheet updated manually once a week.

Step 1: Choose Your 5–7 KPIs

Why Fewer Is Better

The most common dashboard mistake is trying to track everything. A dashboard with 30 metrics is as useful as a dashboard with zero, it creates noise that prevents any signal from landing. Force yourself to choose five to seven KPIs that directly answer the question: "Is the business healthy right now?"

A Framework for Choosing KPIs

For each potential metric, ask three questions:

1. Will this number change a decision we make today? If no one would act differently based on seeing this number, it does not belong on the main dashboard.
2. Does it have a clear owner? A metric with no one responsible for it is a decorative number.
3. Is it a leading or lagging indicator? You want a mix, lagging indicators (revenue, expenses) tell you what happened; leading indicators (new leads, trial signups) tell you what is about to happen.

A Solid Starting KPI Set for Most SMBs

Adjust based on your business model. A SaaS company might replace "active customers" with "MRR" and add "churn rate." A service business might add "utilization rate."

Step 2: Connect Your Data Sources to One Sheet

The Architecture

Your Google Sheet should have a dedicated tab for each data source, plus one "Dashboard" tab that pulls from all of them. This structure keeps source data clean and separates it from display logic.

Recommended Sheet structure:

Tabs:
- HubSpot_raw     ← pipeline data from CRM
- QuickBooks_raw  ← revenue and expense data
- GA4_raw         ← traffic and conversion data
- Dashboard       ← charts and KPI cards built from the above

Option A: Import Manually (Free, Not Scalable)

You can export CSV files from each tool and paste them into the corresponding tab. This works for a first prototype but breaks down quickly, it requires someone to remember to do it, introduces human error, and does not support "real-time" in any meaningful sense.

Option B: Use IMPORTDATA / IMPORTRANGE (Free, Limited)

Google Sheets has native import functions:

• =IMPORTDATA(url) can pull from publicly accessible CSV endpoints.
• =IMPORTRANGE(sheet_url, range) can pull from another Google Sheet.

These are useful for connecting Sheets to each other but cannot authenticate with third-party APIs like HubSpot or QuickBooks.

Option C: Use brooked.io (Recommended)

brooked.io connects directly to your business tools and writes structured data into designated Sheet tabs on a schedule you set. Setup involves:

1. Connect brooked.io to your data sources (OAuth flow, takes 2–3 minutes per source).
2. Specify which Sheet and which tab each source should write to.
3. Set the refresh schedule (hourly is the default; custom schedules are available).

After setup, your HubSpot_raw, QuickBooks_raw, and GA4_raw tabs update automatically without any manual intervention.

Step 3: Build the Dashboard Tab

KPI Summary Cards

At the top of your Dashboard tab, create a row of KPI summary cells. Each cell pulls a single number from the corresponding raw tab using a formula.

Example formulas (adjust column references to match your actual Sheet structure):

Open Pipeline Value:
=SUMIF(HubSpot_raw!B:B,"Open",HubSpot_raw!C:C)

Revenue MTD:
=SUMIFS(QuickBooks_raw!D:D, QuickBooks_raw!A:A, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Weekly Sessions:
=SUMIFS(GA4_raw!C:C, GA4_raw!A:A, ">="&TODAY()-7)

Conditional Formatting for At-a-Glance Status

Apply conditional formatting to each KPI cell to give it a traffic-light color based on performance:

1. Select the KPI cell.
2. Go to Format > Conditional formatting.
3. Set rules: red if below target threshold, yellow if near threshold, green if above.

This lets any stakeholder read the dashboard in five seconds without interpreting numbers.

Charts

Add two or three trend charts below the KPI summary row:

• A line chart showing revenue vs. expenses over the last 90 days (from QuickBooks_raw).
• A bar chart showing weekly sessions and lead volume over the last 8 weeks (from GA4_raw and HubSpot_raw combined).
• A funnel or column chart showing pipeline stages (from HubSpot_raw).

Keep charts simple. The goal is pattern recognition, not detailed analysis.

Step 4: Schedule Auto-Refresh

Why Manual Refresh Defeats the Purpose

A dashboard that requires someone to click "refresh" is a dashboard that will be stale whenever it matters most. The entire value of this setup comes from data arriving automatically.

Using brooked.io's Scheduler

In brooked.io, set each data source connection to refresh on your desired cadence:

• Hourly: Best for sales and marketing dashboards where daily decisions are made throughout the workday.
• Daily at 6 AM: Best for finance dashboards reviewed at the start of each business day.
• Weekly: Appropriate for strategic KPIs that do not change day-to-day.

brooked.io sends a notification if a sync fails, so you are not left looking at stale data without knowing it.

Refresh Timestamps

Add a "Last updated" cell to your Dashboard tab that shows when data was last written:

=TEXT(MAX(HubSpot_raw!Z:Z),"MMM D, YYYY h:MM AM/PM")

(Assuming brooked.io writes a timestamp in column Z of each raw tab.) This simple addition builds immediate trust with viewers who want to know if the numbers are current.

Step 5: Share with Stakeholders

Access Control

Google Sheets has granular sharing settings. For a business dashboard:

• Give executives View access to the Dashboard tab only, using a restricted share link.
• Give the person maintaining the Sheet (probably you) Editor access.
• Do not share edit access with everyone, accidental formula changes are the most common way dashboards break.

Sharing a Specific Tab Only

Google Sheets does not natively hide individual tabs from view-only users, but you can publish a single tab to the web as a read-only view: File > Share > Publish to web > select the Dashboard tab > publish. This creates a URL that shows only the Dashboard tab and updates automatically as the underlying data changes.

Embedding in Slack or a Company Portal

Looker Studio connects to Google Sheets natively. Once your Dashboard tab is populated, you can build a Looker Studio report on top of it in 20–30 minutes, then share the Looker Studio URL with stakeholders. The Looker Studio report loads instantly (because it reads from Sheets, not from slow APIs) and can be embedded in Notion, Confluence, or a Slack channel via the /unfurl link preview.

Concrete Example: HubSpot + QuickBooks + GA4

Here is the full flow for a concrete SMB scenario: a B2B software company with a sales team, a finance function, and a marketing team.

Data sources:

• HubSpot CRM: tracks open deals, pipeline value, new leads
• QuickBooks Online: tracks monthly revenue, cost of goods, operating expenses
• Google Analytics 4: tracks website sessions, goal completions (demo requests)

brooked.io configuration:

• HubSpot → HubSpot_raw tab, refreshed hourly
• QuickBooks → QuickBooks_raw tab, refreshed daily at 6 AM
• GA4 → GA4_raw tab, refreshed daily at 6 AM

Dashboard tab KPIs:

1. Open pipeline value (HubSpot)
2. Deals closed this month (HubSpot)
3. Revenue MTD (QuickBooks)
4. Gross margin % (QuickBooks)
5. Weekly sessions (GA4)
6. Demo requests this week (GA4)

Result: The CEO opens the Sheet every morning and sees a current, accurate snapshot of business health in under 30 seconds, with no data team, no BI tool license, and no SQL required.

Tool Comparison Table

Troubleshooting

My Dashboard formulas return zero or blank after brooked.io writes data Check that the column headers in the raw tab exactly match what your formulas reference. brooked.io writes column headers on the first row; if your SUMIF refers to column B but the data landed in column C, the formula returns zero. Open the raw tab and verify the column layout.

Data is updating but the Dashboard charts are not refreshing Charts in Google Sheets sometimes need a manual trigger to repaint after a programmatic data update. Try selecting a cell in the chart's source range and pressing Delete, then Ctrl+Z (undo)-this forces a recalculation event. For a permanent fix, see the related article on Google Sheets formula refresh issues.

Some deals are missing from the HubSpot pipeline value brooked.io (and any CRM connector) typically pulls deals in a specific pipeline stage filter. Verify the filter configuration in brooked.io matches the deal stages you want to include. Closed-lost deals are often excluded by default.

QuickBooks revenue figures do not match my QuickBooks reports This is usually a cash vs. accrual accounting mismatch. Confirm whether brooked.io is set to pull on a cash or accrual basis, and ensure it matches how your QuickBooks reports are generated.

Bottom Line

A real-time business dashboard is not a data engineering project. It is a half-day Google Sheets project if you use the right tools. The pattern is simple: get your data into Sheets automatically, build a Dashboard tab on top of it, and share a view-only link. brooked.io handles the hardest part, keeping the data current, so you can focus on what the numbers actually mean.

Build Your Dashboard with brooked.io

brooked.io connects HubSpot, QuickBooks, GA4, and dozens of other sources directly to your Google Sheet, automatically, on a schedule, without code. Start your first connection in under five minutes and have a working dashboard before the end of the day.

Internal link suggestions:

• "Looker Studio Taking Forever to Load? The Real Cause (and 3 Ways to Fix It)" → /looker-studio-slow-loading-fix
• "Why Google Sheets Formulas Don't Update Automatically" → /google-sheets-formulas-not-updating
• "GA4 (other) Bucket Taking Over Your Google Sheets Report? Here's the Fix" → /ga4-other-bucket-fix

Troubleshooting quick reference

Frequently asked questions