Skip to main content

​
Demo video

This guide walks you through constructing comprehensive live dashboards in Google Sheets using Superjoin to sync real-time data from tools like HubSpot. Whether you’re analyzing your sales pipeline or tracking ongoing opportunities, you’ll learn how to set up a powerful, dynamic report that stays up-to-date automatically.

​
Introduction

With Superjoin, you can pull live sales pipeline data from HubSpot directly into Google Sheets. This enables you to build dashboards that always reflect your latest business reality. Ideal for anyone responsible for pipeline analysis, these live dashboards remove manual updates and keep reports accurate.

​
Prerequisites

  • Google Sheets access with edit permissions on the target spreadsheet
  • Superjoin for Google Sheets add-on installed (see Quickstart)
  • Connected HubSpot account with access to Deals and related objects

​
Step 1: Import data with Superjoin

Superjoin connects Google Sheets to your business systems to keep data fresh without manual imports. Follow these steps to import live HubSpot pipeline data:
1

Open Google Sheets

Install/add the Superjoin add-on if you haven’t already
2

Open the Superjoin sidebar

Click the Superjoin menu and select Open Sidebar
3

Add a new data connection

  1. In the sidebar, click Add Data Source
  2. Select HubSpot from the list of integrations
  3. Authenticate your HubSpot account as prompted
4

Configure data import

  1. Choose which HubSpot object to import (e.g., Deals for pipeline data)
  2. Define the fields/columns you want (such as Deal Name, Amount, Stage, Owner, Close Date, etc.)
  3. Set the import to Auto-Sync (recommended) for live updates (see Formula Filldown)
5

Run the import

The Superjoin data will appear as a new sheet in your spreadsheet
With live pipeline data, your reports reflect the most up-to-date information for decision-making and forecasting.

​
Step 2: Explore and build the dashboard

The dashboard you build on your Superjoin-connected sheet is fully dynamic. Here’s how to bring that to life:
1

Review the imported Superjoin data sheet

Ensure all pipeline fields are present
2

Create a new summary sheet

Create a dashboard tab where you will build the report UI
3

Reference Superjoin Data

  1. Use formulas to pull numbers from your live data (explained in the next section)
  2. If you change underlying data (like opportunity stages in CRM), your dashboard reflects those changes immediately
Filters drive everything. Changing the opportunity type updates metrics and visuals instantly.

​
Step 3: Set up the live data sheet (best practices)

A key difference with Superjoin-powered dashboards: Your sheet is always syncing with the source, so:
  1. Do NOT directly edit the imported Superjoin sheet. This ensures uninterrupted sync and future-proof updates.
  2. Leave all raw data columns untouched.
  3. Add calculated/formula columns to the right:
    • Open a blank column adjacent to your imported data
    • Use row-level formulas (e.g., =IF([Status] = "Closed Won", 1, 0)) to enrich your raw data
    • Example calculations:
      • Closed date conversions
      • Stage buckets (Won/Open/Lost)
      • Owner or team name extraction
  4. Superjoin auto-fills formulas for every new row that syncs in future updates.
  5. Use the space beside the imported data to transform and analyze without breaking the sync.
Do not overwrite or delete columns in the imported Superjoin sheet. Make your transformations in new columns to the right to preserve syncing.

​
Step 4: Build the metrics layer

This is the foundation of your dashboard – a “metrics block” where you summarize pipeline KPIs for display
1

Create the Metrics Layer

Create a block (section) above/beside your imported/processed data
2

Build summary metrics

Use these example formulas:
# Total Pipeline Value
=SUMIF([Stage], "Open", [Amount])

# Won Revenue This Month
=SUMIFS([Amount], [Stage], "Closed Won", [Close Date], ">="&FirstOfMonth, [Close Date], "<="&Today)

# Deal Count
=COUNTIF([Stage], "Open")
3

Use named ranges

Use structured references from your formula columns to simplify formulas
4

Format your metrics area

  1. Use bold headers and light cell fills to clearly distinguish metrics from raw data
  2. Display relevant KPIs important to your team (e.g., pipeline by segment, sales rep, deal type)

​
Step 5: Set up the drop-down filter

Dynamic filtering lets your dashboard viewers slice the data by attributes like opportunity type or business segment.
  1. Insert a drop-down cell at the top of your dashboard area.
  2. Populate the drop-down with filter values:
    • Click the drop-down cell
    • Go to Insert > Drop-down (Google Sheets) and select Drop-down from a range
    • Choose a reference range — ideally, a list of unique values via UNIQUE() from your main data (e.g., all distinct Pipeline Types)
  3. Label the drop-down (e.g., Select Opportunity Type).
  4. Point your summary and chart formulas to the drop-down selection:
    • Example: =SUMIF([OpportunityType], $A$1, [Amount]) where $A$1 is your drop-down cell

​
Step 6: Use dynamic formulas

Tie your dashboard formulas to the filter selection so everything updates in real-time.
  1. Build formulas that reference the drop-down cell. For example: =SUMIFS([Amount], [OpportunityType], $A$1)
  2. Apply similar formulas to all relevant summary metrics and charts.
  3. Test by changing the drop-down — all dashboard values should update instantly for the chosen filter.
  4. Handle edge cases:
    • Add formulas to show “All” or an empty state if nothing matches the selected filter
    • Use IFERROR or ISBLANK to prevent errors when filter values are missing

​
Step 7: Automate historic data collection

Automating the collection of historical metrics is crucial for tracking trends and performance over time in your Superjoin-powered dashboards. Here’s how to implement this process step-by-step: 1. Gather all summary metrics and KPIs from your metrics block. These should be calculated on your middle layer using live, formula-based results connected to your Superjoin data. These metrics might include total pipeline value, closed revenue, deal count, or any custom KPIs you’ve built. 2. Prepare a separate tracking sheet dedicated to historic data storage. Set up columns for the date (e.g., daily, weekly, or monthly), alongside each metric you intend to monitor over time.
  1. Automate snapshotting metrics at regular intervals:
    • Use Google Apps Script or manual methods to copy the latest values from your metrics block into the corresponding row of your tracking sheet
    • Schedule this process to run daily, weekly, or on another cadence suited to your reporting needs
    • Ensure that only values (not formulas) are pasted to preserve the historical record even as live data changes
  2. Track how your metrics change over time. The tracking sheet gradually accumulates snapshots, allowing you to calculate trends, run comparisons, and visualize time series directly on your dashboard.
  1. Reference historic data in your dashboards:
    • Use dynamic charts and summary sections that read from your tracking sheet to showcase trends like pipeline growth, win rates, or period-over-period changes
    • This historical context adds deeper analytical value than point-in-time numbers alone, enabling true analysis and forecasting
  2. Integrate your automated tracking into the overall dashboard flow:
    • Your live dashboard should pull both current and historic values, with the same filter logic from Step 5 applied as needed to surface targeted insights
    • By layering real-time and historical data, your dashboard delivers ongoing visibility into both recent activity and long-term performance
  3. Summary and best practices:
    • Maintain a live Superjoin sheet for real-time sync
    • Build a robust middle layer for calculated results
    • Store regular metric snapshots in a tracking/history sheet
    • Reference both live and trend data in your dashboard visualizations, with interactive filtering for drill-down analysis

​
Conclusion: Dashboard structure overview

The full Superjoin dashboard workflow follows these layers:
  • Live data sheet synced via Superjoin
  • Middle layer of calculated formulas that reference the live data
  • Historical tracking sheet for time-based analysis
  • Final dashboard that combines metrics and trendlines for actionable reporting
This setup empowers you to create robust, interactive sales dashboards that require minimal maintenance and always stay current with your business data.
  • Quickstart — Install the add-on and connect your first source in minutes.
  • Formula Filldown — Auto-apply formulas to new rows as data refreshes.
  • Scheduled refresh — Keep dashboards current with automated refreshes.
⌘I