Build a read-only dashboard for non-editors

Northwind’s stakeholders want to see the revenue numbers, but they do not need to edit the Sheet — and sharing the Sheet itself is a blunt instrument. View access still exposes every other tab, every formula, and every stray note, and one accidental edit from a “viewer” who was actually given edit rights causes a morning of cleanup.

This automation serves the data as a web app instead. A single page reads one range from the Sheet, draws it as a chart, and shows nothing else. Stakeholders get a clean dashboard URL; they never touch the spreadsheet. Because the script runs as you, viewers do not even need access to the underlying Sheet.

What you’ll need

• A revenue Sheet with two columns and a header row: a label (month, quarter) in column A and a number in column B. Copy the Sheet ID from its URL.
• An Apps Script project containing two files: a Dashboard.html file for the page and a .gs file for the server code.
• The project deployed as a web app, set to execute as you so viewers do not need access to the Sheet (see Deploy it).

The HTML

This is the Dashboard.html file. The <?!= ?> tag injects the data the server prepared, and Chart.js (loaded from a CDN) draws it.

<!-- Dashboard.html — the read-only page stakeholders see. -->
<h1>Northwind dashboard</h1>
<canvas id="chart"></canvas>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
  // The server fills `series` in before sending the page.
  const data = <?!= JSON.stringify(series) ?>;

  new Chart(document.getElementById('chart'), {
    type: 'line',
    data: {
      labels: data.labels,
      datasets: [{ label: 'Revenue', data: data.values }],
    },
  });
</script>

The script

This is the server-side .gs file. doGet builds the page; revenueSeries reads the Sheet into the shape the chart expects.

// The Sheet that holds the revenue figures. Copy the ID from its URL.
const REVENUE_SHEET_ID = '1abcRevenueId';

/**
 * Serves the dashboard page. Reads the revenue data and injects it into
 * the HTML template before sending it to the browser.
 */
function doGet() {
  const template = HtmlService.createTemplateFromFile('Dashboard');

  // Hand the data to the template; the <?!= ?> tag picks it up.
  template.series = revenueSeries();

  return template.evaluate()
    .setTitle('Northwind dashboard');
}

/**
 * Reads the revenue Sheet into the {labels, values} shape Chart.js wants.
 *
 * @returns {{labels: string[], values: number[]}} Data for the chart.
 */
function revenueSeries() {
  const rows = SpreadsheetApp.openById(REVENUE_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  // Drop the header row, then split into labels (col A) and values (col B).
  const [, ...dataRows] = rows;
  return {
    labels: dataRows.map((r) => r[0]),
    values: dataRows.map((r) => r[1]),
  };
}

How it works

1. doGet runs when a stakeholder opens the web app URL. It loads Dashboard.html as a template — a template, not a plain file, so the <?!= ?> tag is evaluated.
2. revenueSeries opens the revenue Sheet and reads the whole used range with getDataRange.
3. It drops the header row, then maps the rest into two parallel arrays: labels from column A and values from column B — the exact shape Chart.js expects.
4. doGet assigns that object to template.series. When evaluate() runs, the <?!= JSON.stringify(series) ?> tag inside the HTML is replaced with the real data.
5. The browser receives a finished page. Chart.js loads from the CDN and draws a line chart from the injected data.
6. Because the script executes as you, the viewer’s browser never touches the Sheet — they see only the chart.

Example run

The revenue Sheet holds:

Month	Revenue
Jan	42000
Feb	47500
Mar	51000
Apr	49000

A stakeholder opening the web app URL sees a single page titled Northwind dashboard with a line chart rising from 42,000 in January to 49,000 in April. They cannot see the Sheet, other tabs, or any formula — just the chart. When the Sheet is updated, the next page load reflects the new numbers automatically.

Deploy it

The dashboard is only reachable once the project is deployed as a web app:

1. In the Apps Script editor, click Deploy then New deployment.
2. Choose Web app as the type.
3. Set Execute as to yourself — this is what lets viewers see the data without Sheet access.
4. Set Who has access to Anyone for a public link, or Anyone within your organisation to keep it internal.
5. Click Deploy, approve the authorisation prompt, and share the web app URL.

After any change to the code or HTML, deploy a new version of the same deployment, or viewers keep seeing the old build.

Watch out for

• Re-deploy after edits. Saving is not enough — publish a new version of the deployment, or the live URL serves the previous build.
• “Execute as me” means viewers see whatever you can. The script reads the Sheet with your permissions. Only surface ranges you are happy for every viewer to see, and never inject a whole sensitive tab into the page.
• The data is in the page source. JSON.stringify(series) writes the numbers straight into the HTML. Anyone who views source sees the raw values — fine for figures you would share anyway, not for anything confidential.
• createTemplateFromFile, not createHtmlOutputFromFile. Only a template evaluates the <?!= ?> tag. The plain version would print the tag literally.
• The page is read-only by design. It draws data and nothing else — there is no write path. For an editable interface, build a form with google.script.run instead.
• CDN dependency. Chart.js loads from jsDelivr. If that CDN is blocked on a viewer’s network the chart will not render. Pin a specific version in the URL, or host the library yourself, for a critical dashboard.