Serve multiple tenants from one web app

Northwind hosts a handful of small client portals — each one a status page showing that customer’s projects, invoices and contacts. The data is in one sheet (with a tenant column), but giving every customer its own deployment means a dozen script projects to maintain, a dozen URLs to rotate, and a dozen sets of permissions to keep straight.

A multi-tenant web app keeps one deployment and partitions the view by query string. https://script.google.com/.../exec?t=holt-and-sons shows Holt & Sons’ data; ?t=watkins-ltd shows Watkins’s. The same code, the same sheet, the same URL — only the slice the page renders changes. It is the lightest practical pattern for serving many customers from one Apps Script project.

What you’ll need

• A Google Sheet listing known tenants. The first column holds the tenant slug (holt-and-sons, watkins-ltd); the script rejects any visitor whose ?t= is not on the list.
• A second Google Sheet holding the data, with a tenant column matching those slugs. Rows are filtered by that column.
• An Apps Script project containing the script below and one HTML template, Tenant.html, that reads the per-tenant data.
• Editor access to both sheets from the account that deploys the web app.

The script

// The sheet that lists every valid tenant. Column A: slug.
const TENANTS_SHEET_ID = '1abcTenantsId';

// The sheet that holds per-tenant data. First column must be the tenant
// slug so the script can filter on it.
const DATA_SHEET_ID = '1abcDataId';

// Cache key prefix for the known-tenants list. We re-read the list at
// most once every CACHE_TTL_SECONDS to avoid hammering the sheet.
const TENANTS_CACHE_KEY = 'known-tenants';
const CACHE_TTL_SECONDS = 300;

/**
 * Routes incoming requests by ?t= (the tenant slug). Unknown or missing
 * tenants get a plain refusal page — no data is loaded for them.
 *
 * @param {GoogleAppsScript.Events.DoGet} e The request event.
 */
function doGet(e) {
  const tenant = String((e && e.parameter && e.parameter.t) || '').trim();

  if (!tenant || !isKnownTenant(tenant)) {
    return HtmlService.createHtmlOutput('Unknown tenant.')
      .setTitle('Northwind');
  }

  const t = HtmlService.createTemplateFromFile('Tenant');
  t.tenant = tenant;
  t.data = loadFor(tenant);

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

/**
 * Checks the slug against the tenant list. The list is cached for five
 * minutes so a flood of requests does not read the sheet each time.
 *
 * @param {string} tenant The slug to validate.
 * @returns {boolean} True if the slug appears in the tenant sheet.
 */
function isKnownTenant(tenant) {
  const cache = CacheService.getScriptCache();
  let known = cache.get(TENANTS_CACHE_KEY);

  if (!known) {
    const slugs = SpreadsheetApp.openById(TENANTS_SHEET_ID).getSheets()[0]
      .getRange('A2:A')
      .getValues()
      .flat()
      .filter(Boolean)
      .map(String);
    known = JSON.stringify(slugs);
    cache.put(TENANTS_CACHE_KEY, known, CACHE_TTL_SECONDS);
  }

  return JSON.parse(known).includes(tenant);
}

/**
 * Loads the rows belonging to one tenant. The first column of the data
 * sheet must hold the tenant slug.
 *
 * @param {string} tenant The slug to filter on.
 * @returns {Array[]} The data rows (header excluded) for that tenant.
 */
function loadFor(tenant) {
  return SpreadsheetApp.openById(DATA_SHEET_ID).getSheets()[0]
    .getDataRange()
    .getValues()
    .slice(1) // drop the header
    .filter((r) => String(r[0]) === tenant);
}

How it works

1. doGet pulls the tenant slug from ?t=, trims it, and refuses anything missing or unknown. Refusal is plain text — there is no point rendering a styled page for someone we will not serve.
2. isKnownTenant reads the tenants sheet once every five minutes, caches the list of slugs in CacheService, and answers from the cache after that. This is the hot path on every page load, so the cache matters.
3. For a known tenant, loadFor reads the data sheet, drops the header, and filters rows where the first column matches the slug.
4. The script passes tenant and data to Tenant.html as template variables. The page renders the slice in whatever shape you choose — tables, cards, summary counts.
5. Because the URL is the only thing that differs between customers, you can rotate, suspend or revoke a tenant by editing one row of the tenants sheet — the cache picks up the change within five minutes.

Example run

The tenants sheet:

slug
holt-and-sons
watkins-ltd
albright-inc

The data sheet:

tenant	project	status
holt-and-sons	Roof rebuild	In progress
holt-and-sons	Skylight fit	Done
watkins-ltd	Bathroom retile	In progress
albright-inc	Garage extension	Quoted

A visit to ?t=holt-and-sons renders two rows: the roof rebuild and the skylight fit. ?t=watkins-ltd renders one. ?t=unknown-co shows “Unknown tenant.” and never touches the data sheet.

Deploy it

1. Deploy as a web app with Execute as set to your account and Who has access to Anyone with the link. Slugs act as unguessable tokens, so make them random strings rather than the customer’s name if you need extra obscurity.
2. Send each customer their personalised URL — the slug plays the role of a shared secret.
3. To add a new tenant: add a row to the tenants sheet and start writing data with that slug in the data sheet. Wait up to five minutes for the tenant cache to refresh, or bump CACHE_TTL_SECONDS lower while you are testing.

Watch out for

• Slugs are not real authentication. Anyone who learns one can read that tenant’s data. For sensitive information, put a Google sign-in in front by setting Execute as: User accessing and Who has access: Anyone in your domain, then check the user’s email against an allowed list.
• All tenants share one deployment’s quotas and rate limits. A noisy tenant can slow everyone — for high scale, split into per-tenant deployments or move the hot data into a CacheService layer.
• The data filter is a linear scan. For a few thousand rows it is fine; at tens of thousands, split per tenant into separate tabs or sheets and point at the right one based on the slug.
• HTML templating in Apps Script does not auto-escape <?!= ?> output. If tenant data can contain anything user-supplied, encode it on the server before passing it to the template.
• A 5-minute cache means tenant removals take up to five minutes to take effect. If a tenant must be cut off immediately, also delete their rows from the data sheet — refusing the slug does not erase the underlying data.