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:

// 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:

{
  "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:

Head (/dev) vs versioned (/exec)

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:

// ============================================================
// 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:

// ============================================================
// 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

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:

Helper: Create/Manage Whitelist Sheet

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

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

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

/**
 * 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

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):

<!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

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

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:

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:

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:

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:

// 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:

// 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: '' };
}

// 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:

// 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:

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

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:

// 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:

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:

// 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;
  });
}

// 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:

// 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
}

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

<scr

…(truncated)