Build a live dashboard of form responses

A Google Form fills its linked sheet one row at a time, and that raw sheet is fine for the record but useless at a glance. When Northwind runs a sign-up form or a feedback survey, someone keeps opening the responses tab to count rows and tally categories by eye — a task that gets less reliable the busier the form gets.

This script reads the responses sheet and rebuilds a small dashboard tab: the total number of submissions and a breakdown by category. Pointed at a form- submit trigger, it refreshes itself every time a new response lands, so the dashboard is always current without anyone touching it.

What you’ll need

• A Google Form linked to a responses spreadsheet. The first sheet of that spreadsheet must include a Category column — the script splits the counts on it.
• A second Google Sheet (or a second tab) for the dashboard. The script clears and rewrites its first sheet, so keep nothing else there.
• The two file IDs, pasted into the constants at the top of the script.

The script

// The spreadsheet the form writes responses into.
const RESPONSES_SHEET_ID = '1abcResponsesId';

// The spreadsheet the dashboard is rebuilt in.
const DASHBOARD_SHEET_ID = '1abcDashboardId';

/**
 * Reads the form responses and rebuilds the dashboard sheet with a
 * total submission count and a breakdown by category.
 */
function rebuildDashboard() {
  const responses = SpreadsheetApp.openById(RESPONSES_SHEET_ID)
    .getSheets()[0].getDataRange().getValues();

  // Bail out early if there are no responses yet (header row only).
  if (responses.length < 2) {
    Logger.log('No responses yet — nothing to build.');
    return;
  }

  // 1. Split off the header and map header names to column indexes.
  const [header, ...rows] = responses;
  const col = Object.fromEntries(header.map((key, i) => [key, i]));

  // 2. The headline number: how many responses in total.
  const total = rows.length;

  // 3. Tally responses by their Category value.
  const byCategory = {};
  for (const r of rows) {
    const category = r[col.Category] || 'unknown';
    byCategory[category] = (byCategory[category] || 0) + 1;
  }

  // 4. Clear the dashboard and write the total and the breakdown.
  const dash = SpreadsheetApp.openById(DASHBOARD_SHEET_ID).getSheets()[0];
  dash.clear();
  dash.getRange(1, 1, 1, 2).setValues([['Metric', 'Value']]);
  dash.getRange(2, 1, 1, 2).setValues([['Total', total]]);

  const categoryRows = Object.entries(byCategory);
  dash.getRange(4, 1, 1, 2).setValues([['Category', 'Count']]);
  dash.getRange(5, 1, categoryRows.length, 2).setValues(categoryRows);
  Logger.log('Dashboard rebuilt from ' + total + ' responses.');
}

How it works

1. rebuildDashboard opens the responses spreadsheet and reads its first sheet with getDataRange. If there is nothing but a header row it logs and stops.
2. It splits off the header and builds a col lookup from header name to index, so a Category column can sit anywhere without breaking the script.
3. The total is simply the number of data rows — the headline figure for the top of the dashboard.
4. It walks every response and tallies the Category column into a byCategory object. A blank category falls back to 'unknown' so no response is silently lost from the count.
5. It clears the dashboard sheet and writes two small blocks: a total row near the top, then a Category/Count table starting at row 5 with one row per distinct category.
6. Because the whole dashboard is rebuilt each run, it always reflects the current state of the responses — categories that disappear drop off, new ones appear automatically.

Example run

Say the responses sheet has collected eight submissions:

Timestamp	Name	Category
…	Priya	Bug
…	Sam	Feature
…	Alex	Bug
…	(5 more)	…

After a run, the dashboard sheet reads:

Metric	Value
Total	8
Category	Count
Bug	4
Feature	3
unknown	1

One response had no category and is counted under unknown rather than dropped.

Trigger it

Wire it to a form-submit trigger so the dashboard refreshes itself:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add Trigger → choose rebuildDashboard, event source From spreadsheet, event type On form submit.
3. Save and approve the authorisation prompt.

If you would rather not rebuild on every single submission, use a Time-driven trigger every 5 minutes instead — a small lag in exchange for fewer runs.

Watch out for

• The script keys on a column literally named Category. If the form question is worded differently, the header will not match — rename the column or update the col.Category lookup to the real header text.
• dash.clear() wipes the dashboard’s first tab completely, including any chart or formatting. Build charts on a separate tab that reads from this one.
• A form-submit trigger fires once per response. A burst of submissions means a burst of full rebuilds — fine for a normal form, but switch to a timed trigger if the form is high-volume.
• Category counts are exact string matches. Bug and bug with a trailing space count as two categories — trim or normalise the value if responses are free-typed rather than chosen from a list.
• The dashboard is only as fresh as the last trigger run. If you disable the trigger, the numbers freeze — run rebuildDashboard by hand to catch up.