網際網路應用軟體開發｜線上測驗

Static Vite + React + TypeScript exam app for GitHub/Vercel hosting. The frontend talks to a Google Apps Script Web App with fetch() and never contains correct answers.

Files

• src/api.ts: all Google Apps Script requests
• src/types.ts: frontend API and exam types
• src/components/: welcome, exam, result, and leaderboard UI
• apps-script/Code.gs: Apps Script backend
• .env.example: required frontend environment variable

1. Create The Google Sheet

Create a Google Sheet for the exam data. The Apps Script code uses the active spreadsheet, so the script should be bound to this sheet.

Required sheet tabs:

• Students
• Questions

The script will create Sessions automatically when students start exams.

2. Import exam_questions_324.csv

Open the Google Sheet and import exam_questions_324.csv into the Questions sheet.

Supported headers:

id,section,questionText,optionA,optionB,optionC,optionD,optionE,correctAnswer

optionE is optional. correctAnswer must stay in Google Sheets and Apps Script only. Do not export it into frontend JSON.

3. Create The Students Sheet

Create a sheet named Students with this structure:

Column A: studentId / 學號
Column B: studentName / 姓名
Column C onward: exam scores / attempts

Use the first row as headers. Add student data starting from row 2.

4. Deploy Apps Script As Web App

1. In the Google Sheet, go to Extensions -> Apps Script.
2. Replace the default code with apps-script/Code.gs.
3. Save the project.
4. Click Deploy -> New deployment.
5. Select type Web app.
6. Set Execute as to Me.
7. Set access to the audience you need. For a public exam link, use Anyone.
8. Deploy and copy the Web App URL ending in /exec.

When changing Apps Script code later, create a new deployment version or update the existing deployment.

Before connecting the frontend, open this URL in an incognito browser:

https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec?action=validateSetup

It must return JSON. If it shows a Google sign-in page, the Web App access is not public enough for the exam site.

5. Set GOOGLE_SCRIPT_URL

Create .env in the project root:

GOOGLE_SCRIPT_URL=https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec

For local development:

npm install
npm run dev

6. Deploy To Vercel

1. Push this exam folder to GitHub.
2. Import the repository in Vercel.
3. Set the framework preset to Vite.
4. Add this environment variable in Vercel:

GOOGLE_SCRIPT_URL=https://script.google.com/macros/s/YOUR_DEPLOYMENT_ID/exec

5. Deploy.

Build command:

npm run build

Output directory:

dist

7. Security Notes

• Correct answers must remain only in the Questions sheet and apps-script/Code.gs.
• The frontend receives questions without answer keys.
• The frontend submits { questionId, selectedKey } only.
• Apps Script selects random questions, stores sessions, evaluates answers, writes scores, and returns leaderboard data.
• Do not commit .env.
• Do not publish exam_questions_324.csv with correctAnswer in the public frontend repository unless the repository is private.

Apps Script Behavior

• startExam(studentId) checks the Students sheet, selects 50 unique random questions, shuffles options, stores a session, and returns questions without answers.
• submitExam(sessionId, studentId, answers) verifies the session owner, grades server-side, saves the score to the next empty score column from C onward, and returns the leaderboard.
• LockService is used while writing scores to reduce race conditions.