Build an internal admin dashboard

Northwind’s operations data lives in half a dozen spreadsheets — clients in one, invoices in another, projects somewhere on a shared drive. Each tab is fine on its own, but moving between them means hopping tabs, remembering which sheet has which view, and copy-pasting cell references nobody else understands. New starters never quite learn the system; old hands work around it.

This is a single web app that pulls the lot together. One doGet routes by query string to a Home, Clients or Invoices page, each backed by its own Sheet. The pages share a header and a layout so the experience feels like one app, not three. It is the smallest scaffold you can grow into a real internal tool — add sections by adding HTML files, not by rebuilding the shell.

What you’ll need

• A handful of Google Sheets — one for each section the dashboard reads. The example below uses a clients sheet and an invoices sheet; add more as you grow.
• An Apps Script project with one .gs file and one HTML file per section (Home.html, Clients.html, Invoices.html).
• Editor access to every sheet from the account that deploys the web app — the script reads them all as that user.

The script

// One row per route: which template to render and which sheet (if any)
// to load data from. Adding a new section is a one-line change here plus
// an HTML file with the matching name.
const ROUTES = {
  home:     { template: 'Home',     sheetId: null },
  clients:  { template: 'Clients',  sheetId: '1abcClientsId' },
  invoices: { template: 'Invoices', sheetId: '1abcInvoicesId' },
};

// Default route when ?r= is missing or unknown.
const DEFAULT_ROUTE = 'home';

/**
 * Routes incoming requests by ?r= and renders the matching template with
 * its data pre-loaded into `data`. Templates can read `data` via
 * <?!= JSON.stringify(data) ?> at the top of the HTML.
 *
 * @param {GoogleAppsScript.Events.DoGet} e The request event.
 */
function doGet(e) {
  const requested = (e && e.parameter && e.parameter.r) || DEFAULT_ROUTE;
  const route = ROUTES[requested] ? requested : DEFAULT_ROUTE;

  const t = HtmlService.createTemplateFromFile(ROUTES[route].template);
  t.route = route;            // so the template can highlight the active tab
  t.data = loadDataFor(route);

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

/**
 * Loads the data each route needs. Home shows summary counts; clients
 * and invoices read their own sheet.
 *
 * @param {string} route The route key from ROUTES.
 * @returns {Object} A serialisable object the template can read.
 */
function loadDataFor(route) {
  if (route === 'clients')  return { rows: readSheet(ROUTES.clients.sheetId) };
  if (route === 'invoices') return { rows: readSheet(ROUTES.invoices.sheetId) };

  // Home: just the counts. Cheap, and avoids loading every row on every
  // visit to the landing page.
  return {
    stats: {
      clients: countRows(ROUTES.clients.sheetId),
      invoices: countRows(ROUTES.invoices.sheetId),
    },
  };
}

/**
 * Reads a whole sheet into an array of objects keyed by header.
 *
 * @param {string} id Spreadsheet ID.
 * @returns {Object[]} Rows as {header: value} maps.
 */
function readSheet(id) {
  const [headers, ...rows] = SpreadsheetApp.openById(id).getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(headers.map((k, i) => [k, r[i]])));
}

/**
 * Cheap row count — does not pull every row, just the data range size.
 */
function countRows(id) {
  const sheet = SpreadsheetApp.openById(id).getSheets()[0];
  return Math.max(0, sheet.getLastRow() - 1); // minus the header
}

How it works

1. doGet reads ?r= from the request and looks the route up in the ROUTES table. Unknown values fall back to home so a typo never breaks the link.
2. The matched route names an HTML template and (optionally) a sheet ID. loadDataFor returns the data that template needs — summary counts for home, a full row list for clients and invoices.
3. readSheet reads the whole sheet once and turns it into an array of objects keyed by header. The template renders rows by header name, so adding a column to the sheet shows up without code changes.
4. countRows is the cheap path for the home page: it uses getLastRow instead of pulling every row, so the dashboard stays snappy even when the underlying sheets grow.
5. The template gets route and data set as instance properties, so the HTML can highlight the active tab and render rows with one line of JSON.stringify.

Example run

A user visits https://script.google.com/macros/s/AKfycb.../exec?r=clients. The clients sheet looks like this:

name	tier	owner
Holt & Sons	Gold	Priya
Watkins Ltd	Silver	Marco
Albright Inc	Gold	Priya

The Clients page renders the three rows — name, tier and owner — under a header bar that highlights the active Clients tab. Visiting ?r=home instead shows a small summary: 3 clients · 47 invoices. An unknown ?r=nonsense quietly redirects to the home view.

Deploy it

1. In the Apps Script editor click Deploy → New deployment, choose Web app, set Execute as to your account and Who has access to Anyone in your domain so the dashboard stays staff-only.
2. Open the /exec URL. The home page loads with the summary counts; add ?r=clients or ?r=invoices to switch sections.
3. When you change the script or any HTML file, redeploy a new version under Manage deployments. The URL is stable across versions.

To add a new section — say, projects — add projects: { template: 'Projects', sheetId: '1abcProjectsId' } to ROUTES, add Projects.html, and redeploy. No other code changes are needed.

Watch out for

• The script runs as the deployer, so it sees every sheet they can see. Treat the web-app URL as a privileged endpoint — set access to Anyone in your domain and audit who can hit it.
• Reading whole sheets on every request is fine for hundreds of rows but not thousands. Cache results with CacheService for read-heavy sections, or paginate the sheet read.
• Errors in loadDataFor propagate to the user as a stack trace. Wrap each branch in a try/catch if you would rather show a friendly “couldn’t load this section” message.
• All write operations should go through google.script.run, not query strings. Putting a deletion behind ?action=delete looks tempting and invites the next person to click it from a browser history bar.
• HTML templating in Apps Script does not auto-escape values inside <?!= ?>. If sheet data can contain user input, encode it before output, or it will render as live HTML.