Build a quiz deck from a question bank

Northwind runs an onboarding quiz for every new hire, and the questions live in a Sheet where they are easy to edit and review. Turning that bank into a presentation, though, means copying each question and its four options onto a slide by hand — slow, and easy to get out of step with the Sheet whenever a question changes.

This automation builds the deck straight from the Sheet. It creates one title-and-body slide per question, lists the four options in the body, and tucks the correct answer into the speaker notes so the presenter can see it without revealing it on screen. Edit the Sheet, re-run the script, and the deck is current again.

What you’ll need

• A Questions Sheet with a header row and one row per question. Columns: question, optionA, optionB, optionC, optionD, correct. Copy the Sheet ID from its URL.
• The correct column holding the answer letter (A, B, C or D) or the full answer text — whatever you want shown in the speaker notes.
• Permission to create files in your Drive (the script makes a new Slides file on every run).

The script

buildQuizDeck reads the Sheet and assembles the deck. readSheet is the small helper that turns each row into an object keyed by the header.

// The Sheet that holds the question bank. Copy the ID from its URL.
const QUESTIONS_SHEET_ID = '1abcQuestionsId';

// The option columns, in display order.
const OPTION_LETTERS = ['A', 'B', 'C', 'D'];

/**
 * Builds a quiz deck: a title slide plus one slide per question, with the
 * four options in the body and the correct answer in the speaker notes.
 */
function buildQuizDeck() {
  // 1. Read the question bank into an array of row objects.
  const questions = readSheet(QUESTIONS_SHEET_ID);
  if (!questions.length) {
    Logger.log('No questions in the Sheet — nothing to build.');
    return;
  }

  // 2. Create a fresh deck and set the title slide.
  const deck = SlidesApp.create('Northwind quiz');
  deck.getSlides()[0].getPlaceholders()[0].asShape().getText()
    .setText('Quiz');

  // 3. Add one title-and-body slide per question.
  questions.forEach((q, i) => {
    const slide = deck.appendSlide(SlidesApp.PredefinedLayout.TITLE_AND_BODY);

    // Title: a numbered question.
    slide.getPlaceholders()[0].asShape().getText()
      .setText(`Q${i + 1}: ${q.question}`);

    // Body: the four options, one per line, as "A) ...".
    slide.getPlaceholders()[1].asShape().getText().setText(
      OPTION_LETTERS.map((l) => `${l}) ${q['option' + l]}`).join('\n')
    );

    // Speaker notes: the correct answer, visible only to the presenter.
    slide.getNotesPage().getSpeakerNotesShape().getText()
      .setText(`Correct: ${q.correct}`);
  });

  Logger.log(`Built a ${questions.length}-question deck: ${deck.getUrl()}`);
  return deck.getUrl();
}

/**
 * Reads the first tab of a spreadsheet into an array of objects, keyed by
 * the header row.
 *
 * @param {string} id The spreadsheet ID.
 * @returns {Object[]} One object per data row.
 */
function readSheet(id) {
  const [header, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(header.map((k, i) => [k, r[i]])));
}

How it works

1. buildQuizDeck calls readSheet, which opens the question bank and turns every data row into an object keyed by the header — so a row becomes { question: ..., optionA: ..., correct: ... }.
2. If the Sheet has no data rows, the script logs a message and stops rather than creating an empty deck.
3. It creates a new presentation and writes Quiz onto the default title slide.
4. For each question it appends a title-and-body slide. The title placeholder gets the question, numbered Q1, Q2 and so on.
5. The body placeholder gets the four options. OPTION_LETTERS.map(...) builds the keys optionA through optionD and formats each as A) ... on its own line.
6. The correct answer goes into the slide’s speaker notes, so the presenter sees it in the notes pane while the audience sees only the question and options.
7. It logs and returns the deck URL.

Example run

The Questions Sheet holds:

question	optionA	optionB	optionC	optionD	correct
What does .clear() do on a Sheet?	Deletes the tab	Removes data and formatting	Hides the sheet	Locks the range	B
Where do API keys belong?	In the code	In a comment	In Script Properties	In the Sheet	C

Running buildQuizDeck produces a three-slide deck:

• Title slide: Quiz
• Slide 2 — title: Q1: What does .clear() do on a Sheet? body: A) Deletes the tab / B) Removes data and formatting / C) Hides the sheet / D) Locks the range notes: Correct: B
• Slide 3 — title: Q2: Where do API keys belong? with the four options in the body and Correct: C in the notes.

Run it

This is an on-demand job — run it whenever the question bank changes:

1. In the Apps Script editor, select buildQuizDeck and click Run.
2. Approve the authorisation prompt the first time.
3. Open the deck URL from the execution log.

To let non-editors rebuild it, add a custom menu to the Questions Sheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Quiz tools')
    .addItem('Build quiz deck', 'buildQuizDeck')
    .addToUi();
}

Watch out for

• Placeholder order is layout-dependent. This script assumes the title is placeholder [0] and the body is [1]. With a custom template, verify the indexes or match placeholders by type instead.
• Every option column must exist. A missing optionC value renders as C) undefined. Make sure all four option columns are filled, or add a guard in the map.
• A new file every run. Each call creates a separate deck, and they accumulate in Drive. Move old decks into a folder, or have the script reuse a deck by ID instead of calling SlidesApp.create.
• The default title slide must exist. SlidesApp.create gives you one blank slide; the script writes onto it. Deleting it first would break getSlides()[0].
• Long options overflow the body box. Slides does not auto-resize text past a point. Keep options short, or set a smaller font size on the body shape.