How to build a timed online quiz with Google Sheets and Apps Script
This guide walks you through building a timed online quiz using Google Sheets and Google Apps Script. You will create a question bank, a user-facing web form, timing logic, and automated grading so a single spreadsheet manages questions, responses, and results. Expect to spend about 60–120 minutes building the basic version and another 30–60 minutes testing and polishing.
Step 1: Plan quiz structure and timing
Decide the number of questions, time per question or total time, and scoring rules before you start. For example, pick 10 questions, 60 seconds per question, and 1 point per correct answer so you can implement consistent timing and feedback in the script.
[Illustration: diagram of quiz flow with 10 questions and 60s per question]
Step 2: Create a Google Sheet quiz backend
Make a new Google Sheet with three tabs: Questions, Responses, and Settings. In Questions list at least 20 question rows with columns for ID, text, choices A–D, correct choice, and point value so you can randomize and reuse items.
[Illustration: spreadsheet view showing Questions, Responses, Settings tabs and sample rows]
Step 3: Add Settings and timing values
In the Settings tab enter values such as totalTimeSeconds (e.g., 600), perQuestionSeconds (e.g., 60), and maxAttempts (e.g., 3). Storing these as named cells makes it easy to read and change from Apps Script without editing code.
[Illustration: sheet settings tab with cells named totalTimeSeconds, perQuestionSeconds, maxAttempts]
Step 4: Create a web app HTML interface
Use Apps Script to create an HTML file for the quiz UI that shows one question at a time, a countdown timer, and navigation buttons. Include simple CSS and clear IDs for JavaScript to update the timer and question text dynamically for responsive behavior across devices.
[Illustration: browser window mockup showing question text, four choice buttons, and countdown timer at top right]
Step 5: Write Apps Script backend functions
In Code.gs implement functions to serve the HTML, fetch randomized questions (using SpreadsheetApp), record start times, and grade answers. Use LockService to prevent race conditions and Utilities.formatDate to store timestamps consistently in UTC to avoid timezone issues.
[Illustration: code editor with Apps Script functions like doGet, getQuestions, submitAnswer, gradeQuiz]
Step 6: Implement timing logic on client and server
In client-side JavaScript run a per-quiz countdown using setInterval and send periodic heartbeats (every 10–15 seconds) to the server to validate that the participant hasn't exceeded time. On the server, compare timestamps and reject late submissions to enforce the time limit securely.
[Illustration: split-screen showing client timer ticking and server validation logs]
Step 7: Test, deploy, and collect results
Test with 5–10 trial runs, including edge cases (network loss, tab switch, quick answers). Deploy the Apps Script as a web app with appropriate access (only users in domain or anyone) and monitor the Responses tab for scores, timestamps, and attempt counts; adjust throttles and messages as needed.
[Illustration: deployment dialog and responses sheet with sample results]
- Use question IDs and keep choices in separate columns so you can randomize columns or shuffle order easily.
- Limit the size of each HTML payload; fetch only the next 3–5 questions at a time to reduce latency for long quizzes.
- Store timestamps in ISO 8601 or epoch seconds to simplify comparisons across timezones and daylight saving changes.
- Provide clear visible countdown warnings at 60s, 30s, and 10s remaining to reduce user anxiety and accidental timeouts.
- Implement autosave every 10–15 seconds to localStorage as a fallback for short network outages; sync to server on reconnect.
- Log user agent and IP address (respecting privacy rules) to help diagnose client-specific timing issues and troubleshooting.
- Client-side timers are not secure alone; always validate timestamps and enforce time on the server to prevent cheating.
- Avoid deploying the web app to 'anyone, even anonymous' if quiz results are sensitive—use domain-restricted access instead.
- Exceeding Apps Script quotas (e.g., write calls or triggers) can break large deployments; test with expected concurrent users and implement batching if needed.
- Do not store personal data without consent and comply with applicable privacy laws; delete or anonymize responses when required.
Was this guide helpful?
More Quizzes guides
How to create shareable result graphics for personality test outcomes
Creating attractive, shareable graphics for personality test results helps your audience celebrate and spread their outcomes. This guide walks you through practical, repeatable steps to design clear, on-brand images people will want to post. Expect to spend about 20–90 minutes per graphic depending on complexity.
How to design a multiple-choice trivia quiz for classroom use
Designing a multiple-choice trivia quiz for the classroom can be a fun way to review material, spark engagement, and assess comprehension. With a clear structure and a handful of best practices, you can create quizzes that are fair, varied, and useful for learning. Use this guide to craft a 10–20 question quiz that fits a single 20–30 minute class period.
How to design a psychometric quiz with norm-referenced scoring
Designing a psychometric quiz with norm-referenced scoring helps you compare individual test takers to a defined reference group. This guide walks you through practical steps from defining constructs to creating norms, with concrete actions and reasoning so you can produce reliable, interpretable results. Expect to spend several weeks to months for sampling, piloting, and analysis depending on scale.