EDUPRO CONCEPTS
ORGANISATION

Excellence in Education — Spelling Bee Programme

🔐

Administrator Access

Restricted — authorised personnel only

📝 Question Manager

Year Class Group Section Category

Select options above to load questions.

📊 Bulk Marks: pts per question

Total: —

⚙️ Exam Settings & Links

Year Class Group Section Category

⏱ Duration (minutes)

🔑 Student Password All students use this same password simultaneously

✅ Exam Active Inactive

💡 Multiple students can log in at the same time using the same password. Each student gets their own independent timer and session.

EDUPRO CONCEPTS ORGANISATION

Results Report · Printed

📊 Results & Scores

Auto-refresh inactive

🔑 Re-attempt Approvals

When a student tries to retake an exam, their request appears here. Approve to grant one extra attempt.

0

Pending

0

Approved

⏳ PENDING REQUESTS

✅ APPROVED — AWAITING RE-TAKE

🟢 Google Sheets Integration

Google Sheets acts as your database. All submissions, questions, settings, and approvals are saved there in real time. The app still works offline using local cache and syncs when connectivity is restored.

1 Create a Google Sheet & open Apps Script

Go to sheets.google.com and create a new spreadsheet.
Name it anything (e.g. Spelling Bee Database).
Click Extensions → Apps Script in the top menu.

2 Paste the Apps Script backend code

Delete everything in the editor, paste the code below, then click Save (Ctrl+S). This creates 6 structured sheets automatically.

// ═══════════════════════════════════════════════════════════════
//  EDUPRO CONCEPTS ORGANISATION — Google Apps Script Backend v2
//  Deploy: Extensions → Apps Script → Deploy → Web App
//  Execute as: Me | Who has access: Anyone
// ═══════════════════════════════════════════════════════════════

const SHEET_KV       = 'KV_Store';
const SHEET_SUBS     = 'Submissions';
const SHEET_QUESTIONS= 'Questions';
const SHEET_SETTINGS = 'Settings';
const SHEET_APPROVALS= 'Approvals';
const SHEET_RANKINGS = 'Rankings';

// ── CORS helper ───────────────────────────────────────────────────
function respond(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

// ── GET endpoint ──────────────────────────────────────────────────
function doGet(e) {
  try {
    const action = (e.parameter && e.parameter.action) || '';
    if (action === 'ping')   return respond({ ok: true, ts: Date.now() });
    if (action === 'init')   return respond({ ok: true, data: getAllData() });
    if (action === 'submissions') return respond({ ok: true, data: kvGet('bee_submissions') || [] });
    if (action === 'questions')   return respond({ ok: true, data: kvGet('bee_questions')   || {} });
    if (action === 'settings')    return respond({ ok: true, data: kvGet('bee_settings')    || {} });
    if (action === 'approvals')   return respond({ ok: true, data: {
      approved: kvGet('bee_approved') || {},
      pending:  kvGet('bee_pending')  || []
    }});
    if (action === 'taken')  return respond({ ok: true, data: kvGet('bee_taken') || {} });
    return respond({ ok: false, error: 'Unknown action: ' + action });
  } catch(err) {
    return respond({ ok: false, error: err.toString() });
  }
}

// ── POST endpoint ─────────────────────────────────────────────────
function doPost(e) {
  try {
    const body   = JSON.parse(e.postData.contents);
    const action = body.action || '';

    if (action === 'set') {
      kvSet(body.key, body.value);
      // Mirror to structured sheets when relevant
      if (body.key === 'bee_submissions') syncSubsSheet(body.value);
      if (body.key === 'bee_settings')    syncSettingsSheet(body.value);
      if (body.key === 'bee_questions')   syncQuestionsSheet(body.value);
      if (body.key === 'bee_approved' || body.key === 'bee_pending') syncApprovalsSheet();
      return respond({ ok: true });
    }

    if (action === 'appendSub') {
      const sub  = body.submission;
      const subs = kvGet('bee_submissions') || [];
      // Dedup by id
      if (!subs.find(s => s.id === sub.id)) {
        subs.push(sub);
        kvSet('bee_submissions', subs);
        appendSubRow(sub);
        updateRankingsSheet(subs);
      }
      return respond({ ok: true });
    }

    if (action === 'deleteSubs') {
      const ids = body.ids || [];
      let subs  = kvGet('bee_submissions') || [];
      subs = subs.filter(s => !ids.includes(s.id));
      kvSet('bee_submissions', subs);
      syncSubsSheet(subs);
      updateRankingsSheet(subs);
      return respond({ ok: true });
    }

    if (action === 'approveRequest') {
      const pending  = kvGet('bee_pending')  || [];
      const approved = kvGet('bee_approved') || {};
      const req = pending.find(p => p.id === body.pendingId);
      if (req) {
        if (!approved[req.examKey]) approved[req.examKey] = [];
        if (!approved[req.examKey].map(n=>n.toLowerCase()).includes(req.name.toLowerCase()))
          approved[req.examKey].push(req.name);
        kvSet('bee_approved', approved);
        kvSet('bee_pending', pending.filter(p => p.id !== body.pendingId));
        syncApprovalsSheet();
      }
      return respond({ ok: true });
    }

    if (action === 'denyRequest') {
      const pending = kvGet('bee_pending') || [];
      kvSet('bee_pending', pending.filter(p => p.id !== body.pendingId));
      syncApprovalsSheet();
      return respond({ ok: true });
    }

    return respond({ ok: false, error: 'Unknown action: ' + action });
  } catch(err) {
    return respond({ ok: false, error: err.toString() });
  }
}

// ── Bulk data fetch ───────────────────────────────────────────────
function getAllData() {
  return {
    bee_questions:      kvGet('bee_questions')      || {},
    bee_settings:       kvGet('bee_settings')       || {},
    bee_submissions:    kvGet('bee_submissions')     || [],
    bee_taken:          kvGet('bee_taken')           || {},
    bee_approved:       kvGet('bee_approved')        || {},
    bee_pending:        kvGet('bee_pending')         || [],
    bee_mock_questions: kvGet('bee_mock_questions')  || {},
    bee_init2:          true
  };
}

// ── KV_Store sheet helpers ────────────────────────────────────────
function getKVSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh   = ss.getSheetByName(SHEET_KV);
  if (!sh) {
    sh = ss.insertSheet(SHEET_KV);
    sh.appendRow(['Key', 'Value (JSON)', 'Last Updated']);
    sh.setFrozenRows(1);
    sh.setColumnWidth(1, 200); sh.setColumnWidth(2, 700); sh.setColumnWidth(3, 160);
    sh.getRange(1,1,1,3).setFontWeight('bold').setBackground('#1a1a2e').setFontColor('#f59e0b');
  }
  return sh;
}

function kvGet(key) {
  const rows = getKVSheet().getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    if (rows[i][0] === key) {
      try { return JSON.parse(rows[i][1]); } catch { return null; }
    }
  }
  return null;
}

function kvSet(key, value) {
  const sh   = getKVSheet();
  const rows = sh.getDataRange().getValues();
  const json = JSON.stringify(value);
  for (let i = 1; i < rows.length; i++) {
    if (rows[i][0] === key) {
      sh.getRange(i+1, 2, 1, 2).setValues([[json, new Date()]]);
      return;
    }
  }
  sh.appendRow([key, json, new Date()]);
}

// ── Submissions sheet ─────────────────────────────────────────────
function getSubsSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh   = ss.getSheetByName(SHEET_SUBS);
  if (!sh) {
    sh = ss.insertSheet(SHEET_SUBS);
    const hdr = ['ID','Full Name','School','Class','Region','Age','Parent Contact',
                 'Year','Group','Section','Category','Answers (JSON)',
                 'Score','Total Marks','Percentage','Auto Submit','Timestamp'];
    sh.appendRow(hdr);
    sh.setFrozenRows(1);
    sh.getRange(1,1,1,hdr.length).setFontWeight('bold').setBackground('#0f3460').setFontColor('#f59e0b');
    [80,160,160,80,120,50,130,60,110,200,110,200,60,80,80,90,160].forEach((w,i)=>sh.setColumnWidth(i+1,w));
  }
  return sh;
}

function subToRow(s) {
  const pct = s.total ? Math.round(s.score / s.total * 100) : 0;
  return [
    s.id, s.name, s.school, s.class, s.region, s.age, s.parentContact,
    s.year, s.groupId, s.sectionId, s.catId || 'main',
    JSON.stringify(s.answers || {}),
    s.score, s.total, pct + '%',
    s.autoSubmit ? 'Yes' : 'No',
    new Date(s.timestamp)
  ];
}

function syncSubsSheet(subs) {
  const sh = getSubsSheet();
  const last = sh.getLastRow();
  if (last > 1) sh.deleteRows(2, last - 1);
  if (!subs || !subs.length) return;
  sh.getRange(2, 1, subs.length, 17).setValues(subs.map(subToRow));
}

function appendSubRow(s) {
  getSubsSheet().appendRow(subToRow(s));
}

// ── Questions sheet ───────────────────────────────────────────────
function syncQuestionsSheet(allQs) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh   = ss.getSheetByName(SHEET_QUESTIONS);
  if (!sh) {
    sh = ss.insertSheet(SHEET_QUESTIONS);
    const hdr = ['Year','Group','Section','Category','Q#','Topic','Question',
                 'Option A','Option B','Option C','Option D','Correct Answer','Marks'];
    sh.appendRow(hdr);
    sh.setFrozenRows(1);
    sh.getRange(1,1,1,hdr.length).setFontWeight('bold').setBackground('#0f3460').setFontColor('#f59e0b');
  }
  const last = sh.getLastRow();
  if (last > 1) sh.deleteRows(2, last - 1);
  const rows = [];
  Object.entries(allQs || {}).forEach(([year, groups]) => {
    Object.entries(groups || {}).forEach(([groupId, sections]) => {
      Object.entries(sections || {}).forEach(([secId, cats]) => {
        Object.entries(cats || {}).forEach(([catId, qs]) => {
          (qs || []).forEach((q, i) => {
            rows.push([year, groupId, secId, catId, i+1,
              q.topic||'', q.q||'',
              q.opts[0]||'', q.opts[1]||'', q.opts[2]||'', q.opts[3]||'',
              q.ans||'', q.marks||1]);
          });
        });
      });
    });
  });
  if (rows.length) sh.getRange(2, 1, rows.length, 13).setValues(rows);
}

// ── Settings sheet ────────────────────────────────────────────────
function syncSettingsSheet(allSettings) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh   = ss.getSheetByName(SHEET_SETTINGS);
  if (!sh) {
    sh = ss.insertSheet(SHEET_SETTINGS);
    const hdr = ['Year','Group','Section','Category','Duration (min)','Password','Active'];
    sh.appendRow(hdr);
    sh.setFrozenRows(1);
    sh.getRange(1,1,1,hdr.length).setFontWeight('bold').setBackground('#0f3460').setFontColor('#f59e0b');
  }
  const last = sh.getLastRow();
  if (last > 1) sh.deleteRows(2, last - 1);
  const rows = [];
  Object.entries(allSettings || {}).forEach(([year, groups]) => {
    Object.entries(groups || {}).forEach(([groupId, sections]) => {
      Object.entries(sections || {}).forEach(([secId, cats]) => {
        Object.entries(cats || {}).forEach(([catId, cfg]) => {
          rows.push([year, groupId, secId, catId,
            cfg.duration||30, cfg.password||'', cfg.active ? 'Yes':'No']);
        });
      });
    });
  });
  if (rows.length) sh.getRange(2, 1, rows.length, 7).setValues(rows);
}

// ── Approvals sheet ───────────────────────────────────────────────
function syncApprovalsSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh   = ss.getSheetByName(SHEET_APPROVALS);
  if (!sh) {
    sh = ss.insertSheet(SHEET_APPROVALS);
    const hdr = ['Type','Student Name','Exam Key','Year','Group','Section','Category','Timestamp'];
    sh.appendRow(hdr);
    sh.setFrozenRows(1);
    sh.getRange(1,1,1,hdr.length).setFontWeight('bold').setBackground('#0f3460').setFontColor('#f59e0b');
  }
  const last = sh.getLastRow();
  if (last > 1) sh.deleteRows(2, last - 1);
  const rows = [];
  const pending  = kvGet('bee_pending')  || [];
  const approved = kvGet('bee_approved') || {};
  pending.forEach(req => {
    rows.push(['PENDING', req.name, req.examKey, req.year||'', req.groupId||'', req.secId||'', req.catId||'', new Date(req.timestamp)]);
  });
  Object.entries(approved).forEach(([key, names]) => {
    const [yr,gid,sid,cid] = key.split('||');
    (names||[]).forEach(name => rows.push(['APPROVED', name, key, yr||'', gid||'', sid||'', cid||'', '']));
  });
  if (rows.length) sh.getRange(2, 1, rows.length, 8).setValues(rows);
}

// ── Rankings sheet ────────────────────────────────────────────────
function updateRankingsSheet(subs) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sh   = ss.getSheetByName(SHEET_RANKINGS);
  if (!sh) {
    sh = ss.insertSheet(SHEET_RANKINGS);
    const hdr = ['Rank','Full Name','School','Region','Class','Section','Category','Score','Total','Percentage','Year'];
    sh.appendRow(hdr);
    sh.setFrozenRows(1);
    sh.getRange(1,1,1,hdr.length).setFontWeight('bold').setBackground('#0f3460').setFontColor('#f59e0b');
  }
  const last = sh.getLastRow();
  if (last > 1) sh.deleteRows(2, last - 1);
  if (!subs || !subs.length) return;
  const sorted = [...subs].sort((a,b) => {
    const pa = a.total ? a.score/a.total : 0;
    const pb = b.total ? b.score/b.total : 0;
    return pb - pa;
  });
  const rows = sorted.map((s, i) => {
    const pct = s.total ? Math.round(s.score/s.total*100) : 0;
    return [i+1, s.name, s.school, s.region, s.class, s.sectionId, s.catId||'main', s.score, s.total, pct+'%', s.year];
  });
  sh.getRange(2, 1, rows.length, 11).setValues(rows);
}

3 Deploy as a Web App

In Apps Script, click Deploy → New deployment.
Click the gear ⚙ next to Select type and choose Web app.
Set Execute as: Me.
Set Who has access: Anyone.
Click Deploy, then copy the Web app URL it gives you.

⚠️ You must click Authorise access the first time — this is normal. Choose your Google account and accept the permissions.

4 Connect the Web App URL — TWO ways

⭐ METHOD A — Best (all devices)

Open this HTML file in a text editor, find the line near the top of the <script> section that says:

const GOOGLE_SCRIPT_URL = "YOUR_GOOGLE_APPS_SCRIPT_WEBAPP_URL";

Replace the placeholder text with your actual URL, save the file, then re-host it. Every student device will automatically connect — no admin login needed on their phones.

Method B — This device only

Saves the URL in this browser's localStorage. Only works on this device/browser. Students on other phones won't connect unless Method A is used.

STATUS Not configured

5 Sheet structure created automatically

Your spreadsheet will have these tabs created automatically on first use:

KV_Store	Master JSON store — all questions, settings, taken records, approvals.
Submissions	One row per student — name, score, %, region, class, timestamp.
Questions	Every question with options, correct answer, and marks in readable rows.
Settings	Duration, passwords, and active status per exam configuration.
Approvals	Pending re-attempt requests and approved students.
Rankings	Auto-ranked leaderboard sorted by percentage score.

💡 Open Submissions or Rankings any time to filter, sort, or export to Excel/CSV directly in Google Sheets.

6 Hosting — share one URL with all students

For multi-device access, host this HTML file publicly. Two free options:

GitHub Pages

Create a GitHub repo
Upload this HTML as index.html
Settings → Pages → Deploy from main
Share the *.github.io URL

Netlify Drop

Go to netlify.com/drop
Drag & drop this HTML file
Netlify gives you a live URL instantly
Share the *.netlify.app URL

⚠️ After hosting, paste the new hosted URL into Admin → Exam Settings → Generate Link. This is what students open on their devices.

🔑 Change Admin Password

The admin password controls access to this panel. Keep it secure and share only with authorised staff. The default password is BEEADMIN2024.

Current Password

New Password

Confirm New Password

⚠️ Password is saved in this browser's localStorage. For all-device access, update the ADMIN_PW constant at the top of the HTML source file.

📝 Mock Practice Questions

Mock questions are free practice — no login, no timer, unlimited attempts. Students see a 📋 Mock Practice card in the Student Portal automatically on every device. Just pick a group, add your questions, and click Save — all devices update immediately via Google Sheets.

Class Group Section

Default Marks per Question: Total: 0 marks

Select a class group above to load or create mock questions.

Student Portal

Select the exam you want to take

Student Login

Loading…

Full Name *

Region *

School *

Class *

Age *

Parent's Contact *

Exam Password *

Timer begins when you click above.

Mock Practice

Exam

0/0 answered

—

Select your answers above

🏆

Exam completed and submitted

—

☁️ Saving to cloud…

Your results have been recorded. Thank you for participating in the EDUPRO CONCEPTS ORGANISATION Spelling Bee!

EDUPRO CONCEPTSORGANISATION