Stop paying for BI seats: build a self-updating dashboard in Google Sheets

You need a dashboard, not a BI project

Every small team reaches the same point: someone asks "how is the website doing?" and the answer lives in three places. Google Analytics has the raw events, a warehouse table has last quarter, and the number everyone quotes comes from a screenshot in Slack.

The usual fix is a BI tool, and most of them charge per seat, every month. The bill grows with every person who wants to look, not with how much work the charts do. A five-person team ends up paying a monthly license for each reader of the same seven charts. On top of that come a connector to configure, a data model to learn, and someone who owns it all.

The charts are not the expensive part. Google Sheets draws them for free, and everyone on the team already has it open. What a small team actually needs is the data behind the charts, refreshed on a schedule. That costs us a few cents a week.

So we built exactly that: charts that update on their own, with SQL doing the work behind them. This post shows how, with Thunderduck and Google Sheets, on our own website analytics: 11 weeks of traffic, 506 sessions and 10 top pages.

The idea: SQL writes the tabs, Sheets draws the charts

Thunderduck is a query engine that runs DuckDB next to your data. It can read a Google Sheets tab like a table, and it can write a query result into a tab. That second part is what turns a spreadsheet into a small BI tool. The split of work is simple:

• Thunderduck does the heavy part: it queries the Google Analytics data, cleans it, groups it and writes one small tab per report.
• Google Sheets does the visual part: each chart points at a tab, so a new result means a new chart.
• A schedule reruns the queries every Monday, so the dashboard keeps itself current.

Only small summary tables reach the sheet, under 100 rows each, while the raw events stay in BigQuery. Nobody learns a new interface, and the people reading the dashboard never leave the spreadsheet.

Setup: one Drive folder, one service account

A Google Sheets catalog in Thunderduck is a Drive folder. Every spreadsheet in the folder becomes a schema, and every tab becomes a table. Setup happens once:

• 1. In a Google Cloud project, enable the Drive API and the Sheets API.
• 2. Create a service account and download a JSON key for it. It needs no project role.
• 3. Share the Drive folder with the service account's email as Editor, so it can write tabs. In a Shared Drive, add it as a member of the drive instead; if your Google Workspace blocks sharing outside the company, an admin has to allow this one address.
• 4. In Thunderduck, add a Google Sheets catalog: paste the folder link and the JSON key. The key goes to a secrets manager, never into plain text.
• 5. Create an empty spreadsheet in that folder. We called ours GA_Reports. Thunderduck writes tabs into existing spreadsheets; it does not create the file. Then click refresh on the catalog in Tables, so Thunderduck sees the new spreadsheet.

Our catalog is called td_sheets_test. From here on, td_sheets_test/GA_Reports/<tab> is the address of any tab we want to write.

The analytics data starts in Google Analytics 4, which exports raw events to BigQuery every day. A dbt pipeline in Thunderduck turns those events into two small Iceberg tables each morning at 06:00 UTC: ga4_traffic_overview (daily users and sessions by source, device and country) and ga4_top_pages (daily views and engagement per page). Every report in this post reads those two tables.

The SQL: one COPY per tab

Every report is one statement: a query, then TO the tab it should land in. The tab is created if it is missing and replaced if it exists. The pattern never changes:

COPY (<any query>) TO '<catalog>/<spreadsheet>/<tab>' (FORMAT gsheet);

COPY (
  SELECT day,
         sum(sessions) AS sessions,
         sum(users)    AS users,
         -- 7-day moving average of daily sessions
         round(avg(sum(sessions)) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) AS sessions_7d_avg
  FROM thunder_duck_demo.default.ga4_traffic_overview
  GROUP BY day ORDER BY day
) TO 'td_sheets_test/GA_Reports/Daily traffic' (FORMAT gsheet);

The query can do the cleanup a spreadsheet formula would struggle with. Here GA's source and medium become readable channel names:

COPY (
  SELECT CASE WHEN source = '(direct)'   THEN 'Direct'
              WHEN medium = 'cpc'        THEN 'Paid search'
              WHEN medium = 'organic'    THEN 'Organic search'
              WHEN medium = 'paid-social'
                OR regexp_matches(source, 'linkedin|facebook|instagram|^t\.co$') THEN 'Social'
              WHEN medium = 'ai-assistant' THEN 'AI assistant'
              WHEN medium = 'referral'   THEN 'Referral'
              ELSE 'Unknown' END AS channel,
         sum(sessions) AS sessions,
         sum(users)    AS users
  FROM thunder_duck_demo.default.ga4_traffic_overview
  GROUP BY 1 ORDER BY sessions DESC
) TO 'td_sheets_test/GA_Reports/Channels' (FORMAT gsheet);

A histogram is easiest when SQL does the bucketing, so the sheet only has to draw columns. This one shows how long a page holds a visitor:

COPY (
  WITH p AS (
    SELECT page_views, avg_engagement_time_msec / 1000.0 AS eng_s
    FROM thunder_duck_demo.default.ga4_top_pages
    WHERE avg_engagement_time_msec IS NOT NULL
      AND page_location NOT LIKE '%/home-1%')   -- skip an internal test page
  SELECT CASE WHEN eng_s < 5   THEN '0–5s'
              WHEN eng_s < 15  THEN '5–15s'
              WHEN eng_s < 30  THEN '15–30s'
              WHEN eng_s < 60  THEN '30–60s'
              WHEN eng_s < 120 THEN '1–2 min'
              ELSE '2+ min' END AS engagement,
         count(*)        AS page_days,
         sum(page_views) AS page_views
  FROM p GROUP BY 1 ORDER BY min(eng_s)
) TO 'td_sheets_test/GA_Reports/Engagement histogram' (FORMAT gsheet);

The charts: built once, updated forever

You can add each chart by hand with Insert → Chart. We used a short Apps Script instead (Extensions → Apps Script), so the whole dashboard is rebuilt with one click. Each chart points at whole columns of a tab:

function buildDashboard() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const old = ss.getSheetByName('Dashboard');
  if (old) ss.deleteSheet(old);   // rebuild from scratch on every run
  const dash = ss.insertSheet('Dashboard', 0);
  const tab = name => ss.getSheetByName(name);

  dash.insertChart(dash.newChart().asLineChart()
    .addRange(tab('Daily traffic').getRange('A1:A'))   // day
    .addRange(tab('Daily traffic').getRange('B1:B'))   // sessions
    .addRange(tab('Daily traffic').getRange('D1:D'))   // 7-day average
    .setOption('title', 'Daily sessions and 7-day average')
    .setOption('colors', ['#d1d5db', '#fb8c1a'])
    .setNumHeaders(1)   // row 1 holds the column names
    .setPosition(4, 1, 0, 0)
    .build());

  dash.insertChart(dash.newChart().asColumnChart()
    .addRange(tab('Engagement histogram').getRange('A1:B'))
    .setOption('title', 'How long a page holds a visitor')
    .setOption('colors', ['#fb8c1a'])
    .setNumHeaders(1)
    .setPosition(4, 8, 0, 0)
    .build());

  // … the same pattern for Weekly traffic, Channels, Top pages, Devices, Countries
}

Because the ranges are whole columns, the script runs once. When Thunderduck rewrites a tab, its chart redraws by itself.

The dashboard answered a few questions right away:

• Direct traffic is 88% of sessions (446 of 506). Search and social are barely started, which tells us where the next effort goes.
• The home page has 1,623 of the views. The next pages are /ai (186), /product (150) and /pricing (120).
• Most page visits are short. 207 of 311 page-days held visitors for under 15 seconds.
• Product and pricing pages hold attention. Visitors spend about 25 seconds on /product and 22 on /pricing, roughly twice the 12.5 seconds on the home page. People who get past the home page are reading.

Make it live: a schedule every Monday

A dashboard that someone has to refresh is a dashboard that goes stale. In Thunderduck, each COPY becomes a scheduled job: a name, the SQL, a cron expression and a time zone.

A job runs one statement, so the seven tabs are seven jobs. You can create them in the console, or ask Claude to do it through Thunderduck's MCP server: "schedule these seven exports for Monday 7am".

On Monday morning, Thunderduck rewrites the tabs and every chart on the Dashboard redraws. Nobody opens a BI tool, and nobody touches the spreadsheet.

The GA4 tables are rebuilt by dbt every morning (see Setup), and the Monday jobs run after that, so the dashboard always shows Google Analytics data up to a few days ago.

All seven refreshes cost about two cents a week, under ten cents a month. That price covers the whole team, however many people open the sheet.

Bonus: the sheet talks back

The connection runs both ways. Add a tab called Targets where the team types a weekly sessions goal, and Thunderduck can read it like any table and join it with the analytics:

SELECT w.week_start, w.sessions, t.target,
       round(100.0 * w.sessions / t.target) AS pct_of_target
FROM (SELECT date_trunc('week', day)::date AS week_start, sum(sessions) AS sessions
      FROM thunder_duck_demo.default.ga4_traffic_overview
      GROUP BY 1) w
JOIN td_sheets_test.GA_Reports.Targets t
  ON CAST(t.week_start AS DATE) = w.week_start   -- sheet columns arrive as text or numbers, never dates
ORDER BY w.week_start;

One table comes from Iceberg, the other from a spreadsheet tab someone typed into, joined in a single query. Sheets returns numbers as numbers and everything else as text, so cast a date column before joining on it.

Edits in the sheet show up on the next query, with no import step. The spreadsheet becomes both the dashboard and the place where people give the data its context.

Wrap-up

A mini BI stack in three parts: SQL that reads data where it lives, a spreadsheet that draws it, and a schedule that keeps it fresh. No new tool to learn, and no per-seat bill: under ten cents a month, whether five people look at it or fifty.

Thunderduck connects to PostgreSQL, MySQL, BigQuery, Iceberg, S3 and more, so the same pattern works for sales, support or finance data: https://thunderduck.ai/connectivity/

Every Google Sheets option, from reading tabs to appending into a range, is in the docs: https://docs.thunderduck.ai/catalogs/google-sheets/