# Apps Script Dashboard

> Build Google Apps Script web app dashboards with filters, KPIs, tables, and charts. Use when creating interactive dashboards backed by Google Sheets data.

- Skill: `janagk2/apps-script-dashboard` (Agent Skill)
- Install (CLI): `npx skillmds@latest add janagk2/apps-script-dashboard`
- Raw SKILL.md: https://api.skillmd.com/api/skills/janagk2/apps-script-dashboard/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: JanaGK2 (https://skillmd.com/u/janagk2)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/janagk2/apps-script-dashboard

---


# Build Apps Script Dashboard

## When to Use

- User asks to create a dashboard from Google Sheets data
- Need interactive filters, KPIs, paginated tables
- Building web app with `HtmlService`

## Architecture Pattern

```
Google Sheet (data source)
    ↓
Apps Script (Code.gs)
    ├── doGet() → serves HTML
    ├── getData() → returns filtered data
    ├── getFilterOptions() → returns filter values
    └── calculateKPIs() → returns aggregated metrics
    ↓
HTML/CSS/JS (DashboardUI.html)
    ├── Filter bar with multi-select dropdowns
    ├── KPI cards
    ├── Data table with pagination
    └── Charts (via external libraries)
```

## Step-by-Step Build Process

### 1. Prepare Data Sheet

Ensure source sheet has:
- Header row with column names
- Clean data (no merged cells)
- Filter columns prefixed with `_` for clarity (e.g., `_GEO`, `_SEGMENT`)

### 2. Create Apps Script Project

In Google Sheet: Extensions → Apps Script

### 3. Implement Code.gs

Use template from `templates/Code.gs`:

```javascript
// Configuration
const CONFIG = {
  DATA_SHEET: 'Data',
  ROWS_PER_PAGE: 100,
};

// Web app entry point
function doGet() {
  return HtmlService.createHtmlOutputFromFile('DashboardUI')
    .setTitle('Dashboard')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

// Load data with optional caching
function loadData() {
  const cache = CacheService.getScriptCache();
  const cached = cache.get('data');
  if (cached) return JSON.parse(cached);
  
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.DATA_SHEET);
  const data = sheet.getDataRange().getValues();
  
  // Convert to objects
  const headers = data[0];
  const rows = data.slice(1).map(row => {
    const obj = {};
    headers.forEach((h, i) => obj[h] = row[i]);
    return obj;
  });
  
  try {
    cache.put('data', JSON.stringify(rows), 21600); // 6 hours
  } catch (e) {
    console.log('Data too large for cache');
  }
  
  return rows;
}
```

### 4. Implement DashboardUI.html

Use template from `templates/DashboardUI.html`:

Key components:
- CSS variables for consistent styling
- Filter bar with multi-select dropdowns
- `google.script.run` for server calls
- Client-side filtering for responsiveness

### 5. Add Charts (Optional)

See `apps-script-visualizations` skill for chart integration.

### 6. Configure appsscript.json Manifest (CRITICAL for Web Apps)

**When deploying as a web app that accesses spreadsheets by ID**, you MUST configure OAuth scopes in the manifest. Without this, you'll get:

```
Error: You do not have permission to call SpreadsheetApp.openById
```

**Steps:**
1. In Apps Script editor, click **gear icon** (Project Settings)
2. Check: "Show 'appsscript.json' manifest file in editor"
3. Click `appsscript.json` in file list and add scopes:

```json
{
  "timeZone": "America/New_York",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.container.ui",
    "https://www.googleapis.com/auth/userinfo.email"
  ],
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "DOMAIN"
  }
}
```

> **Note:** `"ANYONE"` may be blocked by org Google Workspace admin policy. For internal org dashboards, prefer `"DOMAIN"` plus app-level email Whitelist / `ALLOWED_EMAILS` (see §7 and Email Whitelisting below).

**When is this required?**
- Spreadsheet created programmatically (MCP, API, Python script)
- Script added to sheet after creation (not via Extensions > Apps Script in that sheet)
- Using `SpreadsheetApp.openById()` instead of `getActiveSpreadsheet()`
- Deploying as standalone web app (not container-bound)

**When is this NOT required?**
- Script created via Extensions > Apps Script from within the target sheet
- Container-bound scripts accessing only their parent spreadsheet

### 7. Deploy workflow — clasp by default for real dashboards

#### Default execution path (mandatory unless user refuses)

- Do **not** lead with copy-paste browser deploy instructions for real dashboards.
- Assume **clasp** for real/stakeholder/iterated `/exec` dashboards that live in a git repo.
- Ask clasp vs browser-only **only** for throwaway experiments or when intent is ambiguous.
- If clasp setup fails, fall back to browser deploy — do not block the user on clasp.
- **NEVER** force copy-paste browser deploy as the primary path when clasp is available and the dashboard is non-throwaway.

Give an **opinionated recommendation** based on deployment type:

| Situation | Recommend | Why |
|-----------|-----------|-----|
| Shared web app (`/exec`), will iterate, lives in a git repo | **clasp** | Local edit in Cursor, `clasp push`, then `clasp deploy -i <id>` for versioned updates |
| Production / team dashboard with versioned deployments | **clasp** | Redeploys are repeatable from the terminal; code stays reviewable |
| One-off throwaway, tiny experiment, or user refuses local setup | **Browser-only** | Faster; no Node/clasp setup |
| Container-bound script only (Extensions → Apps Script on a sheet), no standalone web app | **Browser-only** (or clasp only if they want git) | Often never leaves the sheet editor |

#### Head (`/dev`) vs versioned (`/exec`)

| | Test (Head) | Production |
|--|-------------|------------|
| URL | ends in `/dev` | ends in `/exec` |
| Updates | latest saved code | needs new version / `clasp deploy` |
| Who can open | script **editors** only | per deployment access settings |

Never share `/dev` as the team link. Shared dashboards use versioned `/exec` + redeploy after code changes.

#### Clasp path (default for real dashboards)

Prerequisite: Node.js + `@google/clasp` (`npm i -g @google/clasp`), then `clasp login` once.

1. Create a local folder in the repo (e.g. `webapp/` or `dashboards/<name>/`).
2. Add `appsscript.json` (manifest with required OAuth scopes — see §6).
3. Add `.gs` / `.html` sources in that folder.
4. Link to Google:
   - New project: `clasp create --type webapp --title "…" --rootDir .`
   - Existing project: `clasp clone <scriptId>` or write `.clasp.json` with `scriptId` + `rootDir`.
5. Sync: `clasp push` (upload) / `clasp pull` (download).
6. First shareable URL: Deploy → New deployment → Web app (or `clasp deploy`), copy the `/exec` URL.
7. Later updates to the **same** URL: `clasp push` then `clasp deploy -i <DEPLOYMENT_ID> -d "note"`.

#### Workspace domain constraint (webapp.access)

If deploy fails because **Anyone** access is disabled by org Google Workspace admin:

1. Set `"webapp"."access"` to `"DOMAIN"` in `appsscript.json` (see §6 example).
2. Keep **app-level** access control — Whitelist sheet, `ALLOWED_EMAILS`, and/or `ALLOWED_DOMAIN` (see Email Whitelisting below).
3. **Both URL forms** serve the same deployment:
   - Standard: `https://script.google.com/macros/s/<deploymentId>/exec`
   - Org-domain path: `https://script.google.com/a/macros/<org-workspace-domain>/s/<deploymentId>/exec`
   Use whichever your org shares; they are not separate deployments.
4. Ensure `https://www.googleapis.com/auth/userinfo.email` is in `oauthScopes` — without it, whitelist email detection fails silently (see **CRITICAL: userinfo.email Scope Required** in Email Whitelisting).

`DOMAIN` + in-code whitelist is the recommended default for internal org dashboards when org policy blocks `"ANYONE"`.

#### If user chooses browser-only

1. Deploy → New deployment → Web app
2. Execute as: Me (user deploying)
3. Who has access: **Anyone within org domain** (`DOMAIN`) or **Anyone** — per org policy; pair with in-code email whitelisting when using broad deploy access
4. Copy the `/exec` URL
5. **Re-authorize when prompted** — Google will ask for spreadsheet access
6. Later code changes still need Manage deployments → Edit → New version (Head `/dev` is not a shared production link)

## Email Whitelisting (Access Control)

Instead of managing access through Google's deployment settings (which requires redeployment every time you add/remove users), implement email whitelisting in the script. This allows:
- Deploy once with broad access (`DOMAIN` or `Anyone`, depending on org policy — see §7 Workspace domain constraint)
- Control who can access via code or a Whitelist sheet
- No redeployment needed when access changes

### CRITICAL: userinfo.email Scope Required

**If you see `email: unknown` or empty email in access denied messages**, the script is missing the `userinfo.email` OAuth scope.

When using whitelisting with "Execute as: Me" deployment:
- The script runs with the deployer's credentials (can access spreadsheet)
- `Session.getActiveUser().getEmail()` returns the **visitor's** email (not deployer's)
- But ONLY if `https://www.googleapis.com/auth/userinfo.email` is declared in `appsscript.json`

Without this scope, email detection fails silently and returns empty/unknown. This scope is already shown in the Step 6 `appsscript.json` example above — do not omit it.

### CRITICAL: Web App Context

**In standalone web apps, `getActiveSpreadsheet()` returns NULL!**

Always use `openById()` with an explicit spreadsheet ID:

```javascript
// ============================================================
// SPREADSHEET ACCESS - CRITICAL FOR WEB APPS
// ============================================================
const SPREADSHEET_ID = 'your-spreadsheet-id-here';  // <-- REQUIRED

function getSpreadsheet() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (ss) return ss;
  } catch (e) {}
  return SpreadsheetApp.openById(SPREADSHEET_ID);
}
```

### Implementation Pattern

Add to Code.gs:

```javascript
// ============================================================
// SPREADSHEET ACCESS - REQUIRED FOR WEB APPS
// ============================================================
const SPREADSHEET_ID = 'your-spreadsheet-id-here';

function getSpreadsheet() {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    if (ss) return ss;
  } catch (e) {}
  return SpreadsheetApp.openById(SPREADSHEET_ID);
}

// ============================================================
// EMAIL WHITELISTING - Access Control
// ============================================================
const ACCESS_CONFIG = {
  ENABLED: true,
  ALLOWED_DOMAIN: '',             // e.g., '@company.com' - leave empty to disable domain check
  ALLOWED_EMAILS: [               // Hardcoded fallback emails
    'admin@example.com',
  ],
  USE_WHITELIST_SHEET: true       // Recommended: manage via 'Whitelist' sheet tab
};

function checkAccess() {
  if (!ACCESS_CONFIG.ENABLED) {
    return { authorized: true, email: '', reason: 'Access control disabled' };
  }
  
  const email = Session.getActiveUser().getEmail();
  
  if (!email) {
    return { authorized: false, email: '', reason: 'Could not determine user email' };
  }
  
  // Check domain (if configured)
  if (ACCESS_CONFIG.ALLOWED_DOMAIN && email.endsWith(ACCESS_CONFIG.ALLOWED_DOMAIN)) {
    return { authorized: true, email: email, reason: 'Domain authorized' };
  }
  
  // Check hardcoded emails (fallback)
  if (ACCESS_CONFIG.ALLOWED_EMAILS.map(e => e.toLowerCase()).includes(email.toLowerCase())) {
    return { authorized: true, email: email, reason: 'Email in allowed list' };
  }
  
  // Check whitelist sheet (recommended for easy management)
  if (ACCESS_CONFIG.USE_WHITELIST_SHEET) {
    const whitelistEmails = getWhitelistFromSheet();
    if (whitelistEmails.includes(email.toLowerCase())) {
      return { authorized: true, email: email, reason: 'Email in Whitelist sheet' };
    }
  }
  
  return { authorized: false, email: email, reason: 'Email not authorized' };
}

function getWhitelistFromSheet() {
  try {
    // CRITICAL: Use getSpreadsheet(), NOT getActiveSpreadsheet()
    const ss = getSpreadsheet();
    const sheet = ss.getSheetByName('Whitelist');
    if (!sheet) return [];
    const data = sheet.getDataRange().getValues();
    // Skip header row (row 0), extract emails from column A
    return data.slice(1)
      .map(row => String(row[0] || '').trim().toLowerCase())
      .filter(e => e.includes('@'));
  } catch (e) {
    console.log('Whitelist error:', e);
    return [];
  }
}
```

### Update doGet() to Check Access

```javascript
function doGet() {
  const access = checkAccess();
  
  if (!access.authorized) {
    // Include debug info to help troubleshoot access issues
    return HtmlService.createHtmlOutput(
      `<html><body style="font-family:sans-serif;text-align:center;padding:50px;">
        <h1 style="color:#EE0000;">Access Denied</h1>
        <p><strong>Reason:</strong> ${access.reason}</p>
        <p><strong>Your email:</strong> ${access.email || 'Could not detect'}</p>
        <hr style="margin:30px 0;">
        <p style="color:#666;">Contact the dashboard administrator to request access.</p>
        <p style="color:#999;font-size:12px;">
          Debug: Domain=${ACCESS_CONFIG.ALLOWED_DOMAIN || 'none'}, 
          Sheet=${ACCESS_CONFIG.USE_WHITELIST_SHEET ? 'enabled' : 'disabled'}
        </p>
      </body></html>`
    ).setTitle('Access Denied');
  }
  
  return HtmlService.createHtmlOutputFromFile('DashboardUI')
    .setTitle('Dashboard');
}
```

### Whitelist Sheet Setup

If using `USE_WHITELIST_SHEET: true`:

1. Create a sheet tab called **"Whitelist"**
2. **Row 1 = Header** (required - code skips row 1)
3. Column A: Email addresses
4. Column B: Notes (optional)

Example:
| Email | Notes |
|-------|-------|
| user1@example.com | Added 2026-05-25 |
| user2@example.com | External partner |

### Helper: Create/Manage Whitelist Sheet

Add this function to create the Whitelist sheet with proper formatting:

```javascript
function manageWhitelist() {
  const ss = getSpreadsheet();
  let sheet = ss.getSheetByName('Whitelist');
  
  if (!sheet) {
    sheet = ss.insertSheet('Whitelist');
    sheet.getRange('A1:B1').setValues([['Email', 'Notes']]);
    sheet.getRange('A1:B1')
      .setBackground('#EE0000')
      .setFontColor('#FFFFFF')
      .setFontWeight('bold');
    sheet.setColumnWidth(1, 250);
    sheet.setColumnWidth(2, 200);
    SpreadsheetApp.getUi().alert('Whitelist sheet created. Add emails to column A (skip header row).');
  } else {
    SpreadsheetApp.getUi().alert('Whitelist sheet already exists.');
  }
}

// Add menu item
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Dashboard')
    .addItem('Manage Whitelist', 'manageWhitelist')
    .addToUi();
}
```

### When to Use Each Option

| Scenario | Configuration |
|----------|---------------|
| Internal only (single domain) | `ALLOWED_DOMAIN: '@company.com'` |
| Internal + few external | Domain + `ALLOWED_EMAILS` list |
| Many external users | `USE_WHITELIST_SHEET: true` |
| Testing (no restriction) | `ENABLED: false` |

---

## Advanced Access Control (PropertiesService + Google Groups)

For production dashboards with sensitive data, use this more robust approach:

- **PropertiesService storage** - No external spreadsheet to manage permissions for
- **Google Groups support** - Access auto-updates when users join/leave groups
- **Permanent admins** - Hardcoded admins can't accidentally lock themselves out
- **Admin UI** - Manage access from within the dashboard (no code editing)
- **Server-side security** - Access check runs before any content is sent to browser

### Architecture

```
User visits web app URL
        |
        v
doGet() -> Session.getActiveUser().getEmail()
        |
        v
checkUserAccess(email) checks:
  1. Hardcoded ADMIN_EMAILS array
  2. Stored EMAIL entries (PropertiesService)
  3. Stored GROUP entries (GroupsApp membership)
        |
    +-- GRANTED --> Render dashboard (Index.html)
    |               + inject isAdmin flag
    |               + show Settings button if admin
    |
    +-- DENIED --> Render AccessDenied.html
                   + show user's email
                   + show contact for requesting access
```

### Storage

- **Engine**: `PropertiesService.getScriptProperties()` (built-in key-value store)
- **Key**: `access_entries`
- **Value**: JSON array of `{ type: "EMAIL"|"GROUP", value: "user@domain.com", description: "optional" }`
- **Limits**: 9KB per property (hundreds of entries)
- **Persistence**: Survives redeployments and code pushes

### Step 1: Create AccessControl.js

```javascript
/**
 * AccessControl.js - Access Control using PropertiesService
 * 
 * Supports: individual emails, Google Groups, permanent admins
 * Storage: PropertiesService (no external sheets required)
 */

// ============================================================
// CONFIGURATION - UPDATE THESE FOR YOUR DEPLOYMENT
// ============================================================

// Permanent admins - always have access, cannot be deleted from UI
var ADMIN_EMAILS = [
  'your-email@domain.com'  // <-- REPLACE with actual admin email
];

// Contact shown on Access Denied page
var ACCESS_CONTACT = {
  name: 'Your Name',           // <-- REPLACE
  email: 'your-email@domain.com'  // <-- REPLACE
};

// PropertiesService key for stored entries
var ACCESS_PROP_KEY = 'access_entries';

// ============================================================
// ACCESS CHECK
// ============================================================

/**
 * Check whether the given email is allowed to access the dashboard.
 * Priority: hardcoded admins > stored emails > stored groups.
 */
function checkUserAccess(userEmail) {
  if (!userEmail) return false;
  userEmail = userEmail.toLowerCase().trim();
  
  // 1. Hardcoded admins
  for (var i = 0; i < ADMIN_EMAILS.length; i++) {
    if (ADMIN_EMAILS[i].toLowerCase() === userEmail) {
      console.log('Access granted (admin): ' + userEmail);
      return true;
    }
  }
  
  // 2. Stored entries (emails and groups)
  var entries = getStoredEntries();
  for (var j = 0; j < entries.length; j++) {
    var entry = entries[j];
    if (entry.type === 'EMAIL' && entry.value.toLowerCase() === userEmail) {
      console.log('Access granted (email): ' + userEmail);
      return true;
    }
    if (entry.type === 'GROUP' && isUserInGroup(userEmail, entry.value)) {
      console.log('Access granted (group ' + entry.value + '): ' + userEmail);
      return true;
    }
  }
  
  console.log('Access denied: ' + userEmail);
  return false;
}

/**
 * Check if a user is a member of a Google Group.
 */
function isUserInGroup(userEmail, groupEmail) {
  try {
    var group = GroupsApp.getGroupByEmail(groupEmail);
    return group.hasUser(userEmail);
  } catch (e) {
    console.error('Group check failed for ' + groupEmail + ': ' + e.message);
    return false;
  }
}

/**
 * Check if the current user is a hardcoded admin.
 */
function isCurrentUserAdmin() {
  var email = Session.getActiveUser().getEmail().toLowerCase().trim();
  for (var i = 0; i < ADMIN_EMAILS.length; i++) {
    if (ADMIN_EMAILS[i].toLowerCase() === email) return true;
  }
  return false;
}

// ============================================================
// PROPERTIES STORAGE
// ============================================================

function getStoredEntries() {
  try {
    var props = PropertiesService.getScriptProperties();
    var json = props.getProperty(ACCESS_PROP_KEY);
    if (!json) return [];
    return JSON.parse(json);
  } catch (e) {
    console.error('Failed to read access entries:', e);
    return [];
  }
}

function saveStoredEntries(entries) {
  var props = PropertiesService.getScriptProperties();
  props.setProperty(ACCESS_PROP_KEY, JSON.stringify(entries));
}

// ============================================================
// CLIENT-CALLABLE FUNCTIONS (Settings UI)
// ============================================================

/**
 * Get all access entries for display in the Settings modal.
 */
function getAccessEntries() {
  if (!isCurrentUserAdmin()) {
    return { status: 'error', message: 'Only admins can view access settings.' };
  }
  
  var stored = getStoredEntries();
  var entries = [];
  
  // Add hardcoded admins first (non-deletable)
  for (var i = 0; i < ADMIN_EMAILS.length; i++) {
    entries.push({
      id: 'admin_' + i,
      type: 'ADMIN',
      value: ADMIN_EMAILS[i],
      description: 'Permanent admin (hardcoded)',
      deletable: false
    });
  }
  
  // Add stored entries (deletable)
  for (var j = 0; j < stored.length; j++) {
    entries.push({
      id: 'stored_' + j,
      type: stored[j].type,
      value: stored[j].value,
      description: stored[j].description || '',
      deletable: true
    });
  }
  
  return {
    status: 'success',
    entries: entries,
    currentUser: Session.getActiveUser().getEmail().toLowerCase()
  };
}

/**
 * Add a new access entry.
 */
function addAccessEntry(type, value, description) {
  if (!isCurrentUserAdmin()) {
    return { status: 'error', message: 'Only admins can manage access.' };
  }
  
  type = (type || '').toString().toUpperCase().trim();
  value = (value || '').toString().toLowerCase().trim();
  description = (description || '').toString().trim();
  
  if (!type || !value) {
    return { status: 'error', message: 'Type and email/group are required.' };
  }
  if (['EMAIL', 'GROUP'].indexOf(type) === -1) {
    return { status: 'error', message: 'Type must be EMAIL or GROUP.' };
  }
  if (value.indexOf('@') === -1) {
    return { status: 'error', message: 'Invalid email format.' };
  }
  
  // Check for duplicates
  var entries = getStoredEntries();
  for (var j = 0; j < entries.length; j++) {
    if (entries[j].value.toLowerCase() === value) {
      return { status: 'error', message: 'This email/group is already in the access list.' };
    }
  }
  
  entries.push({ type: type, value: value, description: description });
  saveStoredEntries(entries);
  return { status: 'success', message: 'Entry added successfully.' };
}

/**
 * Delete a stored access entry by index.
 */
function deleteAccessEntry(index) {
  if (!isCurrentUserAdmin()) {
    return { status: 'error', message: 'Only admins can manage access.' };
  }
  
  var entries = getStoredEntries();
  if (index < 0 || index >= entries.length) {
    return { status: 'error', message: 'Invalid entry index.' };
  }
  
  entries.splice(index, 1);
  saveStoredEntries(entries);
  return { status: 'success', message: 'Entry deleted successfully.' };
}

/**
 * Returns contact info for the Access Denied page.
 */
function getAccessDeniedInfo() {
  return {
    contactName: ACCESS_CONTACT.name,
    contactEmail: ACCESS_CONTACT.email,
    userEmail: Session.getActiveUser().getEmail() || 'Unknown'
  };
}
```

### Step 2: Modify doGet() Entry Point

```javascript
function doGet(e) {
  var userEmail = Session.getActiveUser().getEmail();
  
  // Access gate - check BEFORE rendering anything
  if (!checkUserAccess(userEmail)) {
    return HtmlService.createTemplateFromFile('AccessDenied')
      .evaluate()
      .setTitle('Access Restricted')
      .addMetaTag('viewport', 'width=device-width, initial-scale=1');
  }
  
  // User is authorized - render dashboard
  var template = HtmlService.createTemplateFromFile('Index');
  template.userEmail = userEmail;
  template.isAdmin = isCurrentUserAdmin();
  
  return template
    .evaluate()
    .setTitle('Your Dashboard Name')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
```

### Step 3: Create AccessDenied.html

A standalone page shown to unauthorized users (no dashboard code exposed):

```html
<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
    body {
      font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', sans-serif;
      background: #151515;
      min-height: 100vh;
      display: flex;
      align-items: center;
      justify-content: center;
      color: #e0e0e0;
    }
    .container {
      text-align: center;
      padding: 48px 40px;
      max-width: 520px;
      background: #1f1f1f;
      border: 1px solid #383838;
      border-radius: 12px;
    }
    .lock-icon {
      width: 64px; height: 64px;
      margin: 0 auto 24px;
      background: rgba(238, 0, 0, 0.1);
      border: 1px solid rgba(238, 0, 0, 0.25);
      border-radius: 50%;
      display: flex;
      align-items: center;
      justify-content: center;
      font-size: 28px;
    }
    h1 { font-size: 24px; font-weight: 600; color: #ffffff; margin-bottom: 12px; }
    .subtitle { font-size: 15px; color: #707070; line-height: 1.6; margin-bottom: 28px; }
    .user-email-box {
      background: rgba(238, 0, 0, 0.08);
      border: 1px solid rgba(238, 0, 0, 0.2);
      border-radius: 8px;
      padding: 10px 20px;
      display: inline-block;
      font-family: monospace;
      font-size: 13px;
      color: #ee0000;
      margin-bottom: 28px;
    }
    .contact-card {
      display: inline-flex;
      align-items: center;
      gap: 12px;
      background: #292929;
      border: 1px solid #383838;
      border-radius: 8px;
      padding: 12px 20px;
    }
    .contact-avatar {
      width: 36px; height: 36px;
      background: #ee0000;
      border-radius: 50%;
      display: flex;
      align-items: center;
      justify-content: center;
      font-weight: 600;
      font-size: 14px;
      color: #ffffff;
    }
    .contact-info { text-align: left; }
    .contact-name { font-size: 14px; font-weight: 600; color: #ffffff; }
    .contact-email a { font-size: 13px; color: #707070; text-decoration: none; }
    .contact-email a:hover { color: #ee0000; }
  </style>
</head>
<body>
  <div class="container">
    <div class="lock-icon">&#x1F512;</div>
    <h1>Access Restricted</h1>
    <p class="subtitle">
      You don't have permission to view this dashboard.<br>
      Your current account is:
    </p>
    <div class="user-email-box" id="user-email">Loading...</div>
    <p style="font-size: 13px; color: #707070; margin-bottom: 8px;">
      To request access, contact:
    </p>
    <div class="contact-card">
      <div class="contact-avatar" id="contact-initials">--</div>
      <div class="contact-info">
        <div class="contact-name" id="contact-name">Loading...</div>
        <div class="contact-email">
          <a href="#" id="contact-email-link">Loading...</a>
        </div>
      </div>
    </div>
  </div>
  <script>
    google.script.run
      .withSuccessHandler(function(info) {
        document.getElementById('user-email').textContent = info.userEmail || 'Unknown';
        var name = info.contactName || 'Admin';
        var email = info.contactEmail || '';
        var parts = name.split(' ');
        var initials = parts.length >= 2 
          ? parts[0].charAt(0) + parts[1].charAt(0) 
          : name.substring(0, 2);
        document.getElementById('contact-initials').textContent = initials.toUpperCase();
        document.getElementById('contact-name').textContent = name;
        document.getElementById('contact-email-link').textContent = email;
        document.getElementById('contact-email-link').href = 'mailto:' + email;
      })
      .getAccessDeniedInfo();
  </script>
</body>
</html>
```

### Comparison: Simple vs Advanced Access Control

| Feature | Simple (Whitelist Sheet) | Advanced (PropertiesService) |
|---------|--------------------------|------------------------------|
| Storage | Google Sheet tab | Built-in key-value store |
| Google Groups | No | Yes (auto-sync) |
| Admin UI | No (edit sheet) | Yes (in-dashboard) |
| Permanent admins | No | Yes (can't lock out) |
| External dependency | Requires sheet | Self-contained |
| Best for | Simple internal tools | Production dashboards |

### Security Notes

- Access check runs **server-side** in `doGet()` - cannot be bypassed by browser tools
- All management functions verify `isCurrentUserAdmin()` before executing
- PropertiesService data is encrypted at rest by Google
- Group membership is verified in real-time via GroupsApp API

---

## Specification Compliance

### CRITICAL: Never Remove Specified Features

When implementing a dashboard from user specifications:

1. **List all specified filters** → Verify each exists in source data
2. **If data missing** → Add to data pipeline, DON'T remove filter
3. **Trace back**: Source CSV → Build script → Dashboard sheets → UI → Backend handler
4. **Verify end-to-end** before claiming complete

**Common mistake:** Removing a UI filter because the data isn't in the dashboard sheets.
**Correct approach:** Add the column to `build_dashboard_sheets.py`, regenerate, re-upload.

See rule: `follow-specifications-exactly.mdc`

## Performance Optimization

### For Large Datasets (5000+ rows)

1. **Pre-aggregate data** into summary sheets
2. **Load all data once** to client, filter locally
3. **Use pagination** for detail tables
4. **Cache aggressively** with `CacheService`
5. **Use compact data encoding** (indices instead of strings)
6. **Defer text loading** until user expands a row

### Basic Caching Pattern

```javascript
function getCachedData(key, fetchFn, ttl = 21600) {
  const cache = CacheService.getScriptCache();
  let data = cache.get(key);
  
  if (!data) {
    data = JSON.stringify(fetchFn());
    try {
      cache.put(key, data, ttl);
    } catch (e) {
      // Data too large, skip cache
    }
  }
  
  return JSON.parse(data);
}
```

### Chunked Cache Pattern (For Large Data >100KB)

CacheService has a 100KB limit per key. For large datasets, split into chunks:

```javascript
const CACHE_VERSION = 'v1';  // Bump after data changes to invalidate

function _cacheGet(key) {
  const cache = CacheService.getScriptCache();
  const nStr = cache.get(key + '_n');
  if (!nStr) return null;
  
  const n = parseInt(nStr);
  let json = '';
  for (let i = 0; i < n; i++) {
    const chunk = cache.get(key + '_' + i);
    if (!chunk) return null;  // Partial cache = invalid
    json += chunk;
  }
  
  try { return JSON.parse(json); } 
  catch(e) { return null; }
}

function _cachePut(key, data, ttl) {
  const cache = CacheService.getScriptCache();
  try {
    const json = JSON.stringify(data);
    const CHUNK = 90000;  // Stay under 100KB limit
    const chunks = [];
    for (let i = 0; i < json.length; i += CHUNK) {
      chunks.push(json.slice(i, i + CHUNK));
    }
    cache.put(key + '_n', String(chunks.length), ttl);
    chunks.forEach((c, i) => cache.put(key + '_' + i, c, ttl));
  } catch(e) {
    console.log('Cache put failed:', e);
  }
}

// Usage in main data loader
function getInitialData() {
  const cacheKey = 'dashboard_' + CACHE_VERSION;
  const cached = _cacheGet(cacheKey);
  if (cached) return cached;
  
  const data = _buildAllData();
  _cachePut(cacheKey, data, 480);  // 8 minutes
  return data;
}
```

### Cache Warming (Prevent Slow First Load)

Add a function to pre-warm cache before users access the dashboard:

```javascript
function warmCache() {
  const key = 'dashboard_' + CACHE_VERSION;
  const data = _buildAllData();
  _cachePut(key, data, 480);
  Logger.log('Cache warmed successfully');
}
```

Run `warmCache()` manually after data uploads or set up a time-driven trigger.

### Compact Data Encoding

Reduce JSON payload size by using integer indices instead of repeated strings:

```javascript
function _buildAllData() {
  const rawData = loadRawData();
  
  // Build dimension arrays (unique values)
  const geos = [...new Set(rawData.map(r => r.Geo))].sort();
  const segments = [...new Set(rawData.map(r => r.Segment))].sort();
  
  // Create lookup maps
  const geoIdx = {};
  geos.forEach((v, i) => geoIdx[v] = i);
  const segIdx = {};
  segments.forEach((v, i) => segIdx[v] = i);
  
  // Encode rows as compact arrays
  const accounts = rawData.map(r => [
    r.Name,                    // 0: name (string, keep as-is)
    r.URL,                     // 1: url
    geoIdx[r.Geo] ?? -1,       // 2: geo index (integer!)
    segIdx[r.Segment] ?? -1,   // 3: segment index
    r.Value || 0,              // 4: numeric value
    r.IsActive ? 1 : 0         // 5: boolean as 0/1
  ]);
  
  return {
    dims: { geos, segments },  // Dimension arrays for lookup
    accounts                    // Compact integer-encoded data
  };
}
```

Client-side decoding:

```javascript
// In frontend JavaScript
function decodeRow(row, dims) {
  return {
    name: row[0],
    url: row[1],
    geo: dims.geos[row[2]] || 'Unknown',
    segment: dims.segments[row[3]] || 'Unknown',
    value: row[4],
    isActive: row[5] === 1
  };
}
```

### On-Demand Text Loading

For rows with large text fields, load text only when user expands:

```javascript
// Server-side: lightweight initial load
function getInitialData() {
  // Returns compact data WITHOUT large text fields
  return { accounts: [...], dims: {...} };
}

// Server-side: load text on demand
function getAccountText(recordId) {
  const sheet = getSpreadsheet().getSheetByName('Accounts');
  const data = sheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    if (String(data[i][0]).trim() === String(recordId).trim()) {
      return {
        description: String(data[i][10] || ''),
        notes: String(data[i][11] || ''),
        analysis: String(data[i][12] || '')
      };
    }
  }
  return { description: '', notes: '', analysis: '' };
}
```

```javascript
// Client-side: load on expand
function expandRow(recordId, containerEl) {
  containerEl.innerHTML = 'Loading...';
  google.script.run
    .withSuccessHandler(text => {
      containerEl.innerHTML = `
        <div class="text-section">
          <div class="lbl">Description</div>
          <p>${text.description || 'N/A'}</p>
        </div>
      `;
    })
    .getAccountText(recordId);
}
```

## Common Patterns

### Column Index Constants (Maintainability)

Define column indices as constants for readable, maintainable code:

```javascript
// Define column indices (0-based) for each sheet
const ACCOUNTS = {
  ID: 0, NAME: 1, URL: 2, GEO: 3, REGION: 4,
  SEGMENT: 5, VALUE: 6, STATUS: 7, NOTES: 8
};

const INITIATIVES = {
  ACCOUNT_ID: 0, NAME: 1, CATEGORY: 2, VALUE: 3, TIMING: 4
};

// Usage - much clearer than magic numbers
const name = row[ACCOUNTS.NAME];
const value = parseFloat(row[ACCOUNTS.VALUE]) || 0;
```

### Multi-Sheet Data Loading

For dashboards that join data from multiple sheets:

```javascript
function _buildAllData() {
  const ss = getSpreadsheet();
  
  // Load primary sheet
  const accountsRaw = ss.getSheetByName('Accounts').getDataRange().getValues();
  
  // Build lookup maps from primary data
  const accountById = {};
  for (let i = 1; i < accountsRaw.length; i++) {
    const id = String(accountsRaw[i][ACCOUNTS.ID]);
    accountById[id] = {
      name: accountsRaw[i][ACCOUNTS.NAME],
      geo: accountsRaw[i][ACCOUNTS.GEO],
      value: parseFloat(accountsRaw[i][ACCOUNTS.VALUE]) || 0
    };
  }
  
  // Load related sheet and join
  const initRaw = ss.getSheetByName('Initiatives').getDataRange().getValues();
  const initiatives = [];
  for (let i = 1; i < initRaw.length; i++) {
    const accountId = String(initRaw[i][INITIATIVES.ACCOUNT_ID]);
    const account = accountById[accountId] || {};
    initiatives.push({
      accountName: account.name || 'Unknown',
      accountGeo: account.geo || '',
      initName: initRaw[i][INITIATIVES.NAME],
      category: initRaw[i][INITIATIVES.CATEGORY],
      value: parseFloat(initRaw[i][INITIATIVES.VALUE]) || 0
    });
  }
  
  return { accounts: Object.values(accountById), initiatives };
}
```

### Multi-Select Filter

```javascript
function getFilterOptions(column, currentSelections) {
  const data = loadData();
  
  // Filter by upstream selections
  let filtered = data;
  // ... apply other filters ...
  
  // Count values
  const counts = {};
  filtered.forEach(row => {
    const val = row[column];
    counts[val] = (counts[val] || 0) + 1;
  });
  
  return Object.entries(counts)
    .map(([value, count]) => ({ value, count }))
    .sort((a, b) => a.value.localeCompare(b.value));
}
```

### Cascading Filter Configuration

Define filters as a configuration array for maintainability:

```javascript
// Server-side configuration — use string IDs that match HTML element suffixes
const FILTERS = [
  { id: 'geo',     name: 'Geo',     column: '_GEO' },
  { id: 'segment', name: 'Segment', column: '_SEGMENT' },
  { id: 'region',  name: 'Region',  column: '_REGION' },
  { id: 'status',  name: 'Status',  column: '_STATUS' },
];

function getDashboardConfig() {
  return {
    filters: FILTERS,
    displayColumns: ['Name', 'Geo', 'Segment', 'Value', 'Status'],
    rowsPerPage: 100
  };
}
```

### Bidirectional Filter Cascading

When selecting a value in any filter, update options in ALL other filters:

```javascript
function getFilterOptions(filterIndex, currentSelections) {
  const { data } = loadData();
  const filter = FILTERS[filterIndex];
  
  // Filter data by ALL OTHER selections (not this filter)
  let filteredData = data;
  FILTERS.forEach((f, i) => {
    if (i === filterIndex) return;  // Skip current filter
    const selected = currentSelections[f.column] || [];
    if (selected.length > 0 && !selected.includes('All')) {
      filteredData = filteredData.filter(row => {
        const rowValues = String(row[f.column] || '').split(';');
        return selected.some(sv => rowValues.includes(sv));
      });
    }
  });
  
  // Count values for THIS filter from filtered data
  const counts = {};
  filteredData.forEach(row => {
    const values = String(row[filter.column] || '').split(';');
    values.forEach(v => {
      if (v.trim()) counts[v.trim()] = (counts[v.trim()] || 0) + 1;
    });
  });
  
  return Object.entries(counts)
    .map(([value, count]) => ({ value, count }))
    .sort((a, b) => a.value.localeCompare(b.value));
}
```

### Client-Side Filtering (Responsive UX)

Load all data once, filter on client for instant responsiveness:

```javascript
// Server-side: load ALL data
function loadAllData() {
  const { data } = loadData();
  const filterColumns = FILTERS.map(f => f.column);
  const displayColumns = ['Name', 'Geo', 'Segment', 'Value'];
  const allColumns = [...new Set([...displayColumns, ...filterColumns])];
  
  return data.map(row => {
    const result = {};
    allColumns.forEach(col => result[col] = row[col]);
    return result;
  });
}
```

```javascript
// Client-side filtering
let allData = [];  // Loaded once

function initDashboard() {
  google.script.run
    .withSuccessHandler(data => {
      allData = data;
      renderFilters();
      applyFiltersAndRender();
    })
    .loadAllData();
}

function applyFiltersAndRender() {
  let filtered = allData;
  
  // Apply each filter
  FILTERS.forEach(filter => {
    const selected = getSelectedValues(filter.column);
    if (selected.length > 0 && !selected.includes('All')) {
      filtered = filtered.filter(row => {
        const rowValues = String(row[filter.column] || '').split(';');
        return selected.some(sv => rowValues.includes(sv));
      });
    }
  });
  
  updateFilterCounts(filtered);  // Update counts in dropdowns
  renderTable(filtered);
  renderKPIs(filtered);
}
```

### Typeahead Search

Add name search with suggestions:

```javascript
// Server-side
function searchNames(query, currentSelections) {
  if (!query || query.length < 2) return [];
  
  let data = loadData().data;
  const queryLower = query.toLowerCase();
  
  // Apply current filter selections
  FILTERS.forEach(filter => {
    const selected = currentSelections[filter.column] || [];
    if (selected.length > 0 && !selected.includes('All')) {
      data = data.filter(row => {
        const rowValues = String(row[filter.column] || '').split(';');
        return selected.some(sv => rowValues.includes(sv));
      });
    }
  });
  
  // Search names
  return data
    .filter(row => String(row['Name'] || '').toLowerCase().includes(queryLower))
    .map(row => row['Name'])
    .slice(0, 10);  // Limit suggestions
}
```

```html
<!-- Client-side typeahead -->
<input type="text" id="name-search" placeholder="Search names..." 
       oninput="debounce(handleNameSearch, 300)(this.value)">
<div id="suggestions"></div>

<scr

…(truncated)