Capstone · C1

Capstone brief

The capstone puts everything together. You build one complete tool that a real person can use without ever opening the script editor, then you demonstrate it. Plan for about four weeks of lessons plus home study.

The brief

Build an Apps Script tool that saves someone real time. Whatever you choose, it must:

Use at least three Google services (for example Sheets + Gmail + Calendar).
Be used through a menu, sidebar, dialog or web app, never the editor.
Run at least one thing automatically: a trigger, a form submit or a web request.
Be safe to run twice and handle bad input with a clear message.
Keep settings in a CONFIG block and any secrets in Properties.

Work like a developer

Build the smallest version that works first, then add features one at a time. A simple tool that works every time scores higher than an ambitious one that half works. Keep a short diary of what you built each lesson and what went wrong; it makes the presentation easy.

Next: project options →

Choose one

🧪 Class Quiz web app

For: A teacher who wants quick quizzes that mark themselves.

Must have:

Questions written in a sheet
A web page students open on their phones
Marking on the server
Results saved and emailed
A teacher menu with a summary

Builds on: B4, I2, I4, A2, A3, A5

Fully worked in C2. Choose it if you want a guided path, then add two extensions of your own.

📚 Library loans

For: A school or class library.

Must have:

A Books sheet and a Loans sheet
A sidebar to lend and return a book
Due dates in Calendar
Daily overdue reminders by email
A monthly report Doc

Builds on: B4, I2, I3, I4, I5, A2

Good for students who like spreadsheets and triggers.

🎟️ Event sign-up and certificates

For: A club running a workshop or sports day.

Must have:

A Form or web page to sign up
A cap on places and a waiting list
Confirmation emails
Attendance check-boxes
A PDF certificate for everyone who attended

Builds on: A1, I2, I3, I4, A3, A5

Good for students who like documents and design.

💡 Your own idea

For: A real person with a real repetitive task: a teacher, a parent, a shop, a club.

Must have:

Uses at least three Google services
Has a menu, sidebar or web page (no editor needed)
Uses a trigger or web app
Approved by your teacher before you start

Builds on: Your choice

The best projects solve a problem someone actually has. Interview them first.

Extension ideas (any project)

A chart or dashboard tab that updates by itself.
A Thai/English switch on the web page.
Data from an outside API, cached (A4).
A PDF report emailed every Friday.
Narrow scopes in appsscript.json (A5).

Next: milestones →

Six milestones

#	Milestone	Done when
1	Plan	Your teacher has approved a one-page plan: user, problem, sheets and columns, list of functions.
2	Data	The sheets exist with realistic test data (at least 15 rows), including some messy rows.
3	Core	The main job works from the editor: read, process, write.
4	Interface	A menu, sidebar or web page runs it. Errors show as messages.
5	Automation and safety	Triggers or web app deployed; re-runs are safe; input checked; lock where needed.
6	Test and present	A real user has tried it, you fixed what they found, and your demo and guide are ready.

Test plan

For each feature, write a row before you test it:

	A	B	C	D
1	Test	Input	Expected	Actual
2	Empty name	Name blank, submit	Message "Please enter your name"	✓ as expected
3	Run twice	Run merge again	No second email	✗ sent twice → fixed with Sent column

Next: what to hand in →

What to hand in

The files: share the spreadsheet (and any Docs, Forms or folders) with your teacher as Editor, so the script can be read.
The web app link, if you made one, deployed with a version description.
Your plan and test plan as a Doc or a tab in the sheet.
A one-page user guide: what it does, how to use it, what to do if something goes wrong.
A 5-minute demo in class: the problem, a live run, one piece of code you're proud of, and what you'd add next.

Honesty

You may use examples from this course, the official documentation and AI assistants. Say where code came from in a comment, and be ready to explain every line in your demo. If you can't explain it, it doesn't count as yours.

Next: rubric →

Marking rubric (100)

Criterion	Looking for	Developing	Secure	Excellent
Planning (15)	A one-page plan: the user, the problem, the sheets and their columns, the functions and how they connect.	Plan is vague or missing columns.	Clear plan matches the finished project.	Plan includes edge cases and how each will be tested.
Functionality (30)	Does it do what the brief asks, for real data?	Main feature works only on the test sheet.	All required features work.	Works on messy real data, plus extensions.
Code quality (20)	CONFIG block, small named functions, comments that explain why, no copy-paste blocks.	One long function, magic numbers.	Organised into files and functions.	Easy for someone else to change next year.
Reliability and safety (15)	Batched calls, input checks, try/catch, lock, safe re-runs, least permission.	Breaks on bad input or a second run.	Checks input, can re-run safely.	Handles limits, concurrency and errors gracefully.
User experience (10)	Menus, messages, layout, phone friendliness.	User must open the editor.	Clear menu or page with messages.	Polished, accessible and tested with a real user.
Presentation (10)	A 5-minute demo and a short guide.	Demo misses main features.	Clear demo and guide.	Explains design choices and what you would do next.

Roughly: Developing earns up to 50% of a criterion, Secure up to 80%, Excellent the rest.

Next: the guided build (C2) →