AccessControl.js

function setupCoachesListSheet() {
  try {
    var spreadsheet = COACHING_STUDENT_LOOKUP_SPREADSHEET_ID
      ? SpreadsheetApp.openById(COACHING_STUDENT_LOOKUP_SPREADSHEET_ID)
      : SpreadsheetApp.getActiveSpreadsheet();
    if (!spreadsheet) throw new Error("Could not open the coaching spreadsheet.");

    var sheet = spreadsheet.getSheetByName(APP_ACCESS_SHEET_NAME);
    if (!sheet) sheet = spreadsheet.insertSheet(APP_ACCESS_SHEET_NAME);

    ensureCoachesListHeaders_(sheet);
    clearAppAccessCache_();

    return {
      success: true,
      sheetName: APP_ACCESS_SHEET_NAME,
      spreadsheetUrl: spreadsheet.getUrl(),
      message: "Advisors list is ready. Add allowed advisor emails under the Email header and set Watch List to TRUE for advisors who can view it."
    };
  } catch (e) {
    return { error: e.message };
  }
}

function checkAppAccess_() {
  var email = getSignedInUserEmail_();
  if (!email) {
    return {
      allowed: false,
      email: "",
      message: "Access denied because the signed-in email could not be identified."
    };
  }

  try {
    var coachAccess = getCoachAccessPolicyMap_();
    if (coachAccess[email] && coachAccess[email].app) {
      return { allowed: true, email: email };
    }

    return {
      allowed: false,
      email: email,
      message: "Access denied for " + email + ". Ask an administrator to add this email to the Advisors list sheet."
    };
  } catch (e) {
    return {
      allowed: false,
      email: email,
      message: "Access denied because the Advisors list could not be checked: " + e.message
    };
  }
}

function assertAppAccess_() {
  var access = checkAppAccess_();
  if (!access.allowed) throw new Error(access.message);
  return access;
}

function checkWatchListAccess_() {
  var access = assertAppAccess_();
  var email = normalizeEmailForAccess_(access.email);
  if (isWatchListAllowedEmail_(email)) return { allowed: true, email: email };

  return {
    allowed: false,
    email: email,
    message: "Watch List access is not enabled for " + email + "."
  };
}

function assertWatchListAccess_() {
  var access = checkWatchListAccess_();
  if (!access.allowed) throw new Error(access.message);
  return access;
}

function getWatchListAccess() {
  return checkWatchListAccess_();
}

function isWatchListAllowedEmail_(email) {
  var normalizedEmail = normalizeEmailForAccess_(email);
  var allowedEmails = typeof WATCH_LIST_ALLOWED_EMAILS === "undefined" ? [] : WATCH_LIST_ALLOWED_EMAILS;
  for (var i = 0; i < allowedEmails.length; i++) {
    if (normalizeEmailForAccess_(allowedEmails[i]) === normalizedEmail) return true;
  }
  return false;
}

function getSignedInUserEmail_() {
  try {
    return normalizeEmailForAccess_(Session.getActiveUser().getEmail());
  } catch (e) {
    return "";
  }
}

function getCoachAccessPolicyMap_() {
  var cached = getCachedJson_(APP_ACCESS_CACHE_KEY);
  if (cached && typeof cached === "object") return cached;

  var spreadsheet = COACHING_STUDENT_LOOKUP_SPREADSHEET_ID
    ? SpreadsheetApp.openById(COACHING_STUDENT_LOOKUP_SPREADSHEET_ID)
    : SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet ? spreadsheet.getSheetByName(APP_ACCESS_SHEET_NAME) : null;
  if (!sheet) {
    throw new Error("Missing sheet '" + APP_ACCESS_SHEET_NAME + "'. Run setupCoachesListSheet() or create it with an Email column.");
  }

  var values = sheet.getDataRange().getDisplayValues();
  if (!values || values.length < 2) return {};

  var headers = values[0];
  var emailColumn = findColumnIndexByHeaderLookup_(headers, APP_ACCESS_EMAIL_HEADER);
  var activeColumn = findColumnIndexByHeaderLookup_(headers, APP_ACCESS_ACTIVE_HEADER);
  var watchListColumn = findColumnIndexByHeaderLookup_(headers, APP_ACCESS_WATCH_LIST_HEADER);
  if (emailColumn === -1) {
    throw new Error("Missing '" + APP_ACCESS_EMAIL_HEADER + "' header on the Advisors list sheet.");
  }

  var coachAccess = {};
  for (var r = 1; r < values.length; r++) {
    if (activeColumn !== -1 && isInactiveCoachingStudentRow_(values[r][activeColumn])) continue;

    var email = normalizeEmailForAccess_(values[r][emailColumn]);
    if (email) {
      coachAccess[email] = {
        app: true,
        watchList: watchListColumn !== -1 && isTruthyAccessValue_(values[r][watchListColumn])
      };
    }
  }

  putCachedJson_(APP_ACCESS_CACHE_KEY, coachAccess, APP_ACCESS_CACHE_TTL_SEC);
  return coachAccess;
}

function getAuthorizedCoachEmailMap_() {
  var coachAccess = getCoachAccessPolicyMap_();
  var authorizedEmails = {};
  for (var email in coachAccess) {
    if (coachAccess.hasOwnProperty(email) && coachAccess[email] && coachAccess[email].app) {
      authorizedEmails[email] = true;
    }
  }
  return authorizedEmails;
}

function ensureCoachesListHeaders_(sheet) {
  var requiredHeaders = [APP_ACCESS_EMAIL_HEADER, APP_ACCESS_ACTIVE_HEADER, APP_ACCESS_WATCH_LIST_HEADER];
  var lastColumn = sheet.getLastColumn();
  var headers = [];

  if (lastColumn > 0 && sheet.getLastRow() > 0) {
    headers = sheet.getRange(1, 1, 1, lastColumn).getDisplayValues()[0];
  }

  var hasAnyHeader = false;
  for (var h = 0; h < headers.length; h++) {
    if (String(headers[h] || "").trim()) {
      hasAnyHeader = true;
      break;
    }
  }

  if (!hasAnyHeader) headers = [];
  for (var i = 0; i < requiredHeaders.length; i++) {
    if (findColumnIndexByHeaderLookup_(headers, requiredHeaders[i]) === -1) {
      headers.push(requiredHeaders[i]);
    }
  }

  sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight("bold");
  sheet.setFrozenRows(1);
  return headers;
}

function clearAppAccessCache_() {
  try {
    CacheService.getScriptCache().remove(APP_ACCESS_CACHE_KEY);
  } catch (e) { }
}

function normalizeEmailForAccess_(value) {
  return String(value || "").trim().toLowerCase();
}

function isTruthyAccessValue_(value) {
  var text = String(value || "").trim().toLowerCase();
  return text === "true" || text === "yes" || text === "y" || text === "1" || text === "allowed" || text === "view";
}

function createAccessDeniedOutput_(access) {
  var emailText = access.email ? "Signed in as " + escapeHtml_(access.email) + "." : "No signed-in email was available.";
  return HtmlService.createHtmlOutput(
    "<!doctype html><html><head><base target=\"_top\"><meta name=\"viewport\" content=\"width=device-width, initial-scale=1\">" +
    "<style>body{font-family:Arial,sans-serif;margin:0;background:#f8fafc;color:#0f172a;display:grid;min-height:100vh;place-items:center}" +
    "main{max-width:520px;padding:32px}h1{font-size:24px;margin:0 0 12px}p{line-height:1.5;color:#334155}</style></head>" +
    "<body><main><h1>Access denied</h1><p>" + escapeHtml_(access.message) + "</p><p>" + emailText + "</p></main></body></html>"
  )
    .setTitle("Access denied")
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function escapeHtml_(value) {
  return String(value || "")
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#39;");
}