Generate a quiz bank from source material

Northwind’s training material gets written once and then it just sits there. Turning a process guide or an onboarding Doc into a quiz — the thing that actually checks whether anyone absorbed it — is its own chore: write the question, invent three plausible wrong answers, mark the right one, repeat twenty times. Most teams never get round to it.

This script reads a source Doc and asks Claude to write multiple-choice questions straight from the content — each with four options, a marked correct answer, and a one-line explanation. It appends every question as a row in a quiz spreadsheet you can use as a question bank or import into a quiz tool.

What you’ll need

• A Google Doc holding the source material — a training guide, a policy, an onboarding handbook.
• A Google Sheet to collect the questions — the script appends to the first tab, one question per row.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.

The script

// The Doc to read questions from and the Sheet to collect them in.
const SOURCE_DOC_ID = '1abcSourceDocId';
const QUIZ_SHEET_ID = '1abcQuizId';

// How many questions to generate per run.
const QUESTION_COUNT = 10;

// How much of the Doc to send in one pass — keeps the prompt within a
// sensible token budget. See "Watch out for".
const CONTEXT_CHARS = 8000;

/**
 * Reads the source Doc, asks Claude to write multiple-choice
 * questions, and appends each one as a row in the quiz spreadsheet.
 */
function generateQuiz(count = QUESTION_COUNT) {
  // 1. Read the source material.
  const text = DocumentApp.openById(SOURCE_DOC_ID)
    .getBody().getText().slice(0, CONTEXT_CHARS);
  if (!text.trim()) {
    Logger.log('Source Doc is empty — nothing to generate.');
    return;
  }

  // 2. Ask Claude for questions as strict JSON. A fixed schema is the
  //    difference between a parseable result and a parsing nightmare.
  const prompt =
    'Generate ' + count + ' multiple-choice questions from this ' +
    'Northwind material. Return ONLY a JSON array — no prose, no ' +
    'markdown — in this shape: [{"question": string, ' +
    '"options": [4 strings], "correctIndex": 0-3, "explain": string}]. ' +
    'Base every question on the text; do not invent facts.\n\n' + text;
  const qs = JSON.parse(stripFences(
    callClaude(prompt, 'claude-sonnet-4-6', 3000)));

  if (!qs.length) {
    Logger.log('No questions returned — nothing to write.');
    return;
  }

  // 3. Append one row per question: prompt, four options, the correct
  //    answer text, and the explanation.
  const sheet = SpreadsheetApp.openById(QUIZ_SHEET_ID).getSheets()[0];
  for (const q of qs) {
    sheet.appendRow([
      q.question, ...q.options, q.options[q.correctIndex], q.explain,
    ]);
  }
  Logger.log('Appended ' + qs.length + ' questions to the quiz sheet.');
}

/**
 * Claude occasionally wraps JSON in a ```json code fence. Strip it so
 * JSON.parse never chokes on the markdown.
 */
function stripFences(textValue) {
  return textValue.replace(/```(?:json)?/g, '').trim();
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt, model = 'claude-haiku-4-5-20251001', maxTokens = 400) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: maxTokens,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. generateQuiz opens the source Doc and reads the first CONTEXT_CHARS characters of its text.
2. If the Doc is empty, it logs a message and stops — no wasted API call.
3. It builds a prompt that pins the output to a strict JSON schema: an array of objects, each with a question, four options, a correctIndex, and an explain line.
4. It calls Claude Sonnet, which is worth the extra cost here because writing plausible wrong answers — distractors — needs real reasoning over the material. stripFences removes any code fence, then JSON.parse parses it.
5. If no questions come back, it logs a message and stops.
6. It appends one row per question to the first tab of the quiz sheet, spreading the four options into their own columns and resolving correctIndex to the answer text.

Example run

Say the source Doc explains Northwind’s refund policy. With QUESTION_COUNT at its default of 10, a run appends rows like this (one shown):

Question	Option A	Option B	Option C	Option D	Correct answer	Explanation
Within how many days can a Northwind customer request a refund?	7 days	14 days	30 days	90 days	30 days	The policy allows refunds within 30 days of purchase.

Ten rows like that, generated straight from the Doc — a ready question bank instead of a blank template.

Run it

This is an on-demand job — run it when training material is updated:

1. Set SOURCE_DOC_ID to the material and QUIZ_SHEET_ID to the question bank.
2. In the Apps Script editor, select generateQuiz and click Run.
3. Approve the authorisation prompt the first time.
4. Open the quiz sheet to review the new rows.

To make it self-serve, add a custom menu on the quiz sheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Quiz tools')
    .addItem('Generate questions', 'generateQuiz')
    .addToUi();
}

Watch out for

• Always review the questions before using them. Claude can write a question whose “correct” answer is debatable, or whose distractors are too obviously wrong — generation is a draft, not a final exam.
• Run it twice and you get a second batch of questions appended below the first, often overlapping. Clear the sheet between runs, or de-duplicate afterwards.
• Only the first CONTEXT_CHARS characters are read. For a long handbook, run it over each section so questions cover the whole document, not just the start.
• max_tokens is set to 3000 for ten questions. Ask for many more and the reply can be cut off mid-JSON — raise max_tokens in step with count.
• Source material is sent to the API. Keep confidential internal documents off this script, or only point it at material cleared for wider use.