Build a web app with interactive charts

Northwind tracks revenue and churn in two Sheets, and every Monday someone screenshots a chart from each into a Slack thread. The screenshots go stale within an hour, the formatting drifts, and the link to the underlying sheet gets lost in scroll. What the team actually wants is one URL they can open to see the current numbers — rendered as charts, not paragraphs.

This pattern serves a small dashboard page with two Chart.js graphs, one bar and one line, fed by data the script reads from your Sheets at render time. The charts are interactive — hover, zoom, legend toggles — without you maintaining a charting library yourself. It is the smallest live dashboard that does not lie.

What you’ll need

• Two Google Sheets (or two tabs on one sheet — adjust the script accordingly): one with revenue, one with churn. Each in the shape label, value with headers in row 1.
• An Apps Script project with one .gs file and one HTML file called Charts.html.
• Editor access to the source sheets from the account that deploys the web app.

The HTML (Charts.html)

<!-- The page loads Chart.js from a CDN and renders two canvases. The
     data is inlined at render time so the page never needs to call back
     to the server for the figures. -->
<canvas id="rev"></canvas>
<canvas id="churn"></canvas>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
  // Server-side data, baked into the page on every render. Reload to
  // pick up the latest sheet values — there is no client polling.
  const rev = <?!= JSON.stringify(revenue) ?>;
  const churn = <?!= JSON.stringify(churn) ?>;

  // Revenue: a bar chart with one bar per period. Chart.js handles
  // hover tooltips and the legend on its own.
  new Chart(document.getElementById('rev'), {
    type: 'bar',
    data: {
      labels: rev.labels,
      datasets: [{ label: 'Revenue', data: rev.values }],
    },
    options: { responsive: true },
  });

  // Churn: a line chart with one point per period.
  new Chart(document.getElementById('churn'), {
    type: 'line',
    data: {
      labels: churn.labels,
      datasets: [{ label: 'Churn %', data: churn.values }],
    },
    options: { responsive: true },
  });
</script>

The script

// Sheets that feed the dashboard. First column: label. Second column:
// numeric value. Headers in row 1.
const REVENUE_SHEET_ID = '1abcRevenueId';
const CHURN_SHEET_ID = '1abcChurnId';

/**
 * Serves the dashboard page with both series pre-loaded. We pull from
 * each sheet at render time — once per page load, not per chart.
 */
function doGet() {
  const t = HtmlService.createTemplateFromFile('Charts');
  t.revenue = series(REVENUE_SHEET_ID);
  t.churn = series(CHURN_SHEET_ID);

  return t.evaluate()
    .setTitle('Northwind dashboard')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

/**
 * Reads a {label, value} sheet into a Chart.js-friendly shape. Drops
 * blank rows so a trailing empty cell does not produce a phantom bar.
 *
 * @param {string} id Spreadsheet ID.
 * @returns {{labels: string[], values: number[]}} The series.
 */
function series(id) {
  const [, ...rows] = SpreadsheetApp.openById(id).getSheets()[0]
    .getDataRange()
    .getValues();

  const filtered = rows.filter((r) => r[0] !== '' && r[0] !== null);

  return {
    labels: filtered.map((r) => String(r[0])),
    values: filtered.map((r) => Number(r[1]) || 0),
  };
}

How it works

1. doGet loads Charts.html as a template and assigns both series to template variables. Both reads happen server-side once per page load.
2. series reads a sheet, drops the header and any blank rows, and returns {labels, values}. Non-numeric cells are coerced to 0 so a stray text value does not blow up the chart.
3. The HTML template inlines both objects as JSON via <?!= ?>. The page never makes a follow-up call — everything Chart.js needs is in the initial response.
4. Chart.js takes each {labels, values} and renders a responsive chart. The library gives you hover tooltips, legend toggles and a resize listener for free.
5. To refresh the figures, the user reloads the page. There is no cache, no polling, and no second deployment to keep in sync.

Example run

The revenue sheet:

month	revenue
Jan	12000
Feb	14500
Mar	13800
Apr	16200

The churn sheet:

month	churn
Jan	3.1
Feb	2.8
Mar	3.4
Apr	2.5

The dashboard URL renders a four-bar revenue chart (rising) above a four-point churn line (wobbling around 3%). Hover any bar or point to see the exact value. Edit a cell in the sheet, reload the page, and the chart updates.

Deploy it

1. Click Deploy → New deployment, choose Web app, set Execute as to your account and Who has access to Anyone in your domain so only staff can open the dashboard.
2. Share the /exec URL — pin it in the Slack channel that used to host the screenshots.
3. When you change the script or HTML, redeploy a new version under Manage deployments. The URL is stable across versions.

Watch out for

• Reading two sheets on every page load is fine for low traffic, but a dashboard linked from a busy channel can get hit hard. Cache results with CacheService for a few minutes if you see read latency creep up — see Cache API responses to stay under quotas.
• Chart.js is loaded from a public CDN. If the page must work offline or inside a strict CSP, vendor the library — paste the minified source into a second HTML file and include it server-side.
• <?!= ?> does not escape its output. The JSON serialiser handles most cases, but if you ever inline raw user text into the HTML, escape it first or you have an XSS hole.
• Non-numeric values in the value column become 0 because of the Number(...) || 0 guard. That keeps the chart drawing but silently hides bad data — log a warning in series if you would rather know.
• The page is a snapshot of the sheet at request time. If two viewers open it five minutes apart they will see different bars; that is the point of a live dashboard, but be ready for “why doesn’t it match the screenshot” questions during reporting cycles.