Capstone · C2

Guided build: Class Quiz web app

This is a complete capstone, built step by step: a quiz web app that a teacher fills from a sheet, students open on their phones, and that marks itself, records the result and emails the score. Follow it as your project, or read it as a model for your own.

The plan

User: a teacher with 3 classes. Problem: marking short quizzes by hand takes an evening a week.

Student's phone                     Google (as the teacher)
───────────────                     ───────────────────────
opens /exec link  ───────────────▶  doGet()  → 10 random questions, NO answers
fills in answers
Submit  ── google.script.run ────▶  submitQuiz()
                                      checks input
                                      marks against the bank
                                      saves a row (with a lock)
                                      emails the score
shows the score   ◀───────────────  { score, outOf, percent }

Teacher opens the sheet → 🧪 Quiz menu → summary by room, hardest questions

File	Job	From chapter
`Config.gs`	Settings and the spreadsheet	A5
`Questions.gs`	Read, clean and shuffle the bank	B3, B4
`Web.gs` + `Quiz.html`, `Client.html`, `Styles.html`	The page	A2, A3
`Submit.gs`	Mark, save, email	I4, A5
`Teacher.gs`	Menu and analysis	I2, I1

The key design decision

The correct answers never leave the server. If the page knew them, any student could press F12 and read them. The browser gets only question IDs, text and options; marking happens in submitQuiz.

Next: the sheet →

Step 2: the spreadsheet

Create a spreadsheet called Class Quiz with two tabs.

Questions

	A	B	C	D	E	F
1	Question	A	B	C	D	Answer
2	Which keyword can't be reassigned?	var	let	const	fixed	C
3	getValues() returns…	a string	a 2D array	a Range		B
4	Which function serves a web app page?	onOpen	doGet	main	render	B

Write at least 15 questions (the B1–B5 quizzes are a good source). Some may have only two or three options.

Results

	A	B	C	D	E	F	G	H
1	Time	Name	Email	Room	Score	Out of	Percent	Answers

Format column G as a percentage and freeze row 1 on both tabs. Then Extensions → Apps Script, name the project Class Quiz, and add the config file:

Config.gs

// Config.gs: everything you might change, in one place.
const CONFIG = {
  sheetId: 'PASTE_THE_SPREADSHEET_ID_HERE',
  questionsTab: 'Questions',   // Question | A | B | C | D | Answer
  resultsTab: 'Results',       // Time | Name | Email | Room | Score | Out of | Percent | Answers
  quizTitle: 'Apps Script Basics Quiz',
  questionsPerQuiz: 10,        // pick this many at random each time
  emailResults: true,
};

function ss_() {
  return SpreadsheetApp.openById(CONFIG.sheetId);
}

Paste the spreadsheet's ID from its URL. The web app runs without an "active" spreadsheet, so ss_() opens it by ID everywhere.

Next: question bank →

Step 3: read the question bank

Questions.gs

// Questions.gs: read the bank and prepare a quiz WITHOUT the answers.
function readBank_() {
  const [, ...rows] = ss_().getSheetByName(CONFIG.questionsTab).getDataRange().getValues();
  return rows
    .map((r, i) => ({
      id: i + 2,                                   // the sheet row: a stable ID
      text: String(r[0]).trim(),
      options: ['A', 'B', 'C', 'D']
        .map((letter, j) => ({ letter, text: String(r[j + 1]).trim() }))
        .filter((o) => o.text !== ''),
      answer: String(r[5]).trim().toUpperCase(),
    }))
    .filter((q) => q.text && q.options.length >= 2 && q.answer);
}

// Shuffle a copy of an array (Fisher–Yates).
function shuffle_(arr) {
  const a = arr.slice();
  for (let i = a.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [a[i], a[j]] = [a[j], a[i]];
  }
  return a;
}

// What the browser gets: ids, text and options. Never the answers.
function quizForStudent_() {
  return shuffle_(readBank_())
    .slice(0, CONFIG.questionsPerQuiz)
    .map(({ id, text, options }) => ({ id, text, options }));
}

Each question's ID is its sheet row, so answers can be matched back even after shuffling.
Empty option cells are dropped, and rows with no text, fewer than two options or no answer are skipped. A teacher's half-typed row won't break the quiz.
shuffle_ is the Fisher–Yates shuffle: every order is equally likely. (Sorting by Math.random() is a common shortcut, but it isn't fair.)
quizForStudent_ copies only id, text and options. The answer is left behind.

Test before moving on

Add function test() { console.log(quizForStudent_()); }, run it, and check the log shows 10 questions with no answer field.

Next: the quiz page →

Step 4: the page

Web.gs

// Web.gs: the web app's entry point.
function doGet() {
  const t = HtmlService.createTemplateFromFile('Quiz');
  t.title = CONFIG.quizTitle;
  t.questions = quizForStudent_();
  return t.evaluate()
    .setTitle(CONFIG.quizTitle)
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

function include(name) {
  return HtmlService.createHtmlOutputFromFile(name).getContent();
}

Add three HTML files: Quiz, Client and Styles.

Quiz.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Styles') ?>
  </head>
  <body>
    <main>
      <h1><?= title ?></h1>

      <form id="quiz">
        <fieldset class="who">
          <label>Name <input name="name" required maxlength="80"></label>
          <label>School email <input name="email" type="email" required maxlength="120"></label>
          <label>Room
            <select name="room" required>
              <option value="">Choose…</option>
              <option>M.5/1</option><option>M.5/2</option><option>M.5/3</option>
            </select>
          </label>
        </fieldset>

        <? questions.forEach((q, n) => { ?>
          <fieldset class="q">
            <legend><?= n + 1 ?>. <?= q.text ?></legend>
            <? q.options.forEach((o) => { ?>
              <label class="opt">
                <input type="radio" name="q<?= q.id ?>" value="<?= o.letter ?>" required>
                <?= o.text ?>
              </label>
            <? }); ?>
          </fieldset>
        <? }); ?>

        <button id="send">Submit answers</button>
      </form>

      <section id="result" hidden></section>
    </main>
    <?!= include('Client') ?>
  </body>
</html>

The template builds one fieldset per question, with radio buttons named q + the question ID. Every value from the sheet goes through <?= ?>, so a question containing < or & displays correctly and can't inject HTML.

Client.html

<script>
  const form = document.getElementById('quiz');
  const button = document.getElementById('send');
  const result = document.getElementById('result');

  form.addEventListener('submit', (e) => {
    e.preventDefault();
    button.disabled = true;                      // no double submits
    button.textContent = 'Marking…';

    const data = new FormData(form);
    const answers = {};
    for (const [key, value] of data) {
      if (key.startsWith('q')) answers[key.slice(1)] = value;   // { "7": "C", "12": "A" }
    }

    google.script.run
      .withSuccessHandler(showResult)
      .withFailureHandler((err) => {
        button.disabled = false;
        button.textContent = 'Submit answers';
        alert(err.message);
      })
      .submitQuiz({
        name: data.get('name'),
        email: data.get('email'),
        room: data.get('room'),
        answers,
      });
  });

  function showResult(r) {
    form.hidden = true;
    result.hidden = false;
    const h = document.createElement('h2');
    h.textContent = `You scored ${r.score} / ${r.outOf} (${r.percent}%)`;
    const p = document.createElement('p');
    p.textContent = r.message;
    result.append(h, p);
  }
</script>

The button is disabled while marking, so a double tap can't submit twice. The result is shown with textContent, never innerHTML.

Styles.html

<style>
  body { margin: 0; font-family: Arial, sans-serif; background: #f6f8fc; color: #1f1f1f; }
  main { max-width: 640px; margin: 0 auto; padding: 16px; }
  fieldset { border: 1px solid #dadce0; border-radius: 12px; background: #fff; margin: 0 0 12px; padding: 12px 16px; }
  legend { font-weight: bold; padding: 0 4px; }
  .who label { display: block; margin: 6px 0; }
  .opt { display: block; padding: 8px; border-radius: 8px; cursor: pointer; }
  .opt:hover { background: #e8f0fe; }
  input, select, button { font: inherit; padding: 6px 8px; }
  button { background: #1a5fb4; color: #fff; border: 0; border-radius: 999px; padding: 10px 20px; cursor: pointer; }
  button:disabled { opacity: 0.6; }
</style>

Next: mark and save →

Step 5: mark, save, email

Submit.gs

// Submit.gs: mark on the server, save, email.
function submitQuiz(sub) {
  // 1. Check the input. Anyone with the link can call this function.
  const name = String(sub.name || '').trim().slice(0, 80);
  const email = String(sub.email || '').trim().toLowerCase().slice(0, 120);
  const room = String(sub.room || '').trim().slice(0, 10);
  if (!name || !/^[^@\s]+@[^@\s]+\.[^@\s]+$/.test(email)) throw new Error('Please enter your name and a valid email.');
  const answers = sub.answers || {};

  // 2. Mark against the bank on the server, where students can't see the key.
  const bank = new Map(readBank_().map((q) => [String(q.id), q]));
  let score = 0;
  let outOf = 0;
  const given = [];
  for (const [id, letter] of Object.entries(answers)) {
    const q = bank.get(id);
    if (!q) continue;                            // ignore made-up question IDs
    outOf++;
    if (String(letter).toUpperCase() === q.answer) score++;
    given.push(`${id}:${letter}`);
  }
  if (outOf === 0) throw new Error('No answers were received.');
  const percent = Math.round((score / outOf) * 100);

  // 3. Save with a lock, so simultaneous submissions don't collide.
  const lock = LockService.getScriptLock();
  lock.waitLock(15000);
  try {
    ss_().getSheetByName(CONFIG.resultsTab)
      .appendRow([new Date(), safe_(name), email, safe_(room), score, outOf, percent / 100, given.join(' ')]);
  } finally {
    lock.releaseLock();
  }

  // 4. Email the student (an email failure must not lose their result).
  if (CONFIG.emailResults) {
    try {
      MailApp.sendEmail(email, `${CONFIG.quizTitle}: your result`,
        `Hi ${name},\n\nYou scored ${score} out of ${outOf} (${percent}%).\n\nKru Jazzy`);
    } catch (err) {
      console.error('Email failed for ' + email, err);
    }
  }

  return {
    score,
    outOf,
    percent,
    message: percent >= 50 ? 'Well done!' : 'Have another look at the Basic chapters and try again.',
  };
}

// Stop text that starts with = + - @ from becoming a formula in the sheet.
function safe_(s) {
  return /^[=+\-@]/.test(s) ? "'" + s : s;
}

1 · Check

Trim and shorten every field; reject a missing name or a bad email with a message the page shows.

2 · Mark

Look up each submitted ID in the bank. Unknown IDs are ignored, so nobody can invent extra questions to score.

3 · Save

A script lock around appendRow, so 35 students submitting together can't collide.

4 · Email

Inside its own try/catch: if email fails (quota, bad address), the result is still saved and shown.

Why is submitQuiz not private (no _)? Because the page must call it through google.script.run. Every other helper ends in _, so the page can't call readBank_ and download the answers.

Next: teacher tools →

Step 6: tools for the teacher

Teacher.gs

// Teacher.gs: tools in the spreadsheet's own menu.
// The project is bound to the quiz spreadsheet, so onOpen adds this menu there.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🧪 Quiz')
    .addItem('Summary by room', 'summaryByRoom')
    .addItem('Hardest questions', 'hardestQuestions')
    .addToUi();
}

function summaryByRoom() {
  const [, ...rows] = ss_().getSheetByName(CONFIG.resultsTab).getDataRange().getValues();
  const rooms = {};
  rows.forEach((r) => {
    const room = r[3] || '(none)';
    (rooms[room] ||= []).push(r[6]);             // the Percent column
  });
  const lines = Object.entries(rooms)
    .sort()
    .map(([room, ps]) => {
      const avg = ps.reduce((a, b) => a + b, 0) / ps.length;
      return `${room}: ${ps.length} students, average ${Math.round(avg * 100)}%`;
    });
  const ui = SpreadsheetApp.getUi();
  ui.alert('Results by room', lines.join('\n') || 'No results yet', ui.ButtonSet.OK);
}

function hardestQuestions() {
  const bank = new Map(readBank_().map((q) => [String(q.id), q]));
  const [, ...rows] = ss_().getSheetByName(CONFIG.resultsTab).getDataRange().getValues();
  const stats = {};                                // id -> { right, total }
  rows.forEach((r) => {
    String(r[7]).split(' ').filter(Boolean).forEach((pair) => {
      const [id, letter] = pair.split(':');
      const q = bank.get(id);
      if (!q) return;
      const s = (stats[id] ||= { right: 0, total: 0 });
      s.total++;
      if (letter === q.answer) s.right++;
    });
  });
  const table = Object.entries(stats)
    .map(([id, s]) => [bank.get(id).text, s.total, s.right / s.total])
    .sort((a, b) => a[2] - b[2]);

  const sheet = ss_().getSheetByName('Analysis') || ss_().insertSheet('Analysis');
  sheet.clearContents();
  const out = [['Question', 'Attempts', '% correct'], ...table];
  sheet.getRange(1, 1, out.length, 3).setValues(out);
  if (table.length) sheet.getRange(2, 3, table.length, 1).setNumberFormat('0%');
}

	A	B	C
1	Question	Attempts	% correct
2	getValues() returns…	34	41%
3	Which function serves a web app page?	31	68%
4	Which keyword can't be reassigned?	33	91%

The Analysis tab lists the hardest questions first: that is what to reteach next lesson. Because each answer was stored as id:letter, the analysis still works after you add new questions.

Next: deploy and test →

Step 7: deploy, then try to break it

Deploy → Test deployments and open the /dev link. Take the quiz yourself.
Deploy → New deployment → Web app. Execute as: Me. Who has access: your school domain (students sign in with school accounts) or Anyone.
Share the /exec link, as a QR code on the board if you like.
After any code change: Manage deployments → Edit → New version. The link stays the same.

Test plan

Test	Expected
Open on a phone	Readable, no sideways scrolling
Reload the page	A different set or order of questions
View page source (Ctrl + U)	No answers anywhere
Submit with no email	Browser asks for it; if bypassed, server message
Name `=1+1`	Stored as text `'=1+1`, not a formula
Name `<b>Hi</b>`	Shown as text in the email and sheet
Double-tap Submit	One row in Results
Five friends submit together	Five rows, none lost
A half-typed question row	Skipped, quiz still loads
Teacher menu with no results	"No results yet", no error

Next: extensions →

Make it yours

If this is your capstone, add at least two of these and explain them in your demo:

⏱️ Time limit

Show a countdown; the server rejects answers that arrive too late (store the start time in CacheService with a token).

🔁 One attempt

With "Who has access: school domain", use Session.getActiveUser().getEmail() and refuse a second submission.

🗂️ Many quizzes

One tab per quiz; ?quiz=loops in the URL chooses the tab.

💬 Feedback

Add an Explanation column and email each student the questions they got wrong, with explanations.

📈 Dashboard

A chart of average score by room, refreshed by a daily trigger.

🖼️ Images

An Image URL column, shown above the question.

You've finished the course

From console.log('Hello') to a deployed, secure web app. The official reference at developers.google.com/apps-script lists every service and method; you now know enough to read it. Look around for jobs people repeat every week, and automate one.