Chapter 03 · 第三章

Apps Script:
Questions That Write
Back to Your Site

Apps Script：會回寫到你網站的互動題目

About 14 minutes, and there is code you can copy. Six self-check questions at the end. Nothing on this page is recorded or uploaded.

約十四分鐘讀完，附可直接複製的程式碼。末尾六題自我檢查。本頁不記錄、不上傳任何作答。

Everything in this pack so far has ended with paper you have to carry home. This chapter removes the paper from the part that matters most — finding out who is stuck — and it does it with tools you already have and do not have to pay for.

這一包到目前為止的一切，最後都會變成你得帶回家的紙。這一章把紙從最重要的那個環節拿掉—— 找出誰卡住了——而且用的是你本來就有、也不必付費的工具。

Section 01What you are building, and why it is worth it

The end state: a set of questions living on your own site. A student answers, sees whether they were right, and their answer lands as a row in a spreadsheet only you can open. On Sunday evening you glance at one sheet and know which three students have not understood the target structure — before you teach them again on Monday.

最終的樣子：一組題目放在你自己的網站上。學生作答、當場看到自己對不對，而他的答案會變成一列資料，落進一份只有你打得開的試算表。星期天晚上你掃一眼，就知道哪三個學生沒懂那個目標句型——在星期一再教他們之前。

Google Forms can already collect answers. What it cannot do is live inside your own site as a seamless piece of it, give instant feedback in the shape you want, or be reshaped into something that is not a form. Apps Script is the step up, and it is a small step.

Google 表單本來就能收答案。它做不到的是：像網站的一部分那樣無縫嵌在你自己的站裡、用你要的方式即時回饋，或被改造成「不是一份表單」的東西。Apps Script 是往上的那一步，而且那一步不大。

You need three things, and you already have all of them

A Google account, a Google Sheet, and about forty minutes the first time. There is nothing to install and nothing to pay for. Once the first one works, the second takes ten minutes.

你需要三樣東西，而且都已經有了：一個 Google 帳號、一份 Google 試算表，以及第一次做時大約四十分鐘。不用安裝任何東西，不用付費。第一個跑起來之後，第二個只要十分鐘。

Section 02Build it — seven steps

Make the spreadsheet that will hold the answers

Create a new Google Sheet. Rename the first tab to responses and put a header row across the top: time, name, class, set, question, answer, result.

Look at the address bar. The long string between /d/ and /edit is the sheet ID — copy it, you need it in step 3.

開一份新的 Google 試算表，把第一個分頁改名為 responses，第一列打上欄位名稱。網址列中 /d/ 與 /edit 之間那一長串就是試算表 ID，複製起來，第三步要用。

Open Apps Script from inside that sheet

In the sheet, choose Extensions → Apps Script. A code editor opens in a new tab with an empty Code.gs file.

在試算表裡選擴充功能 → Apps Script，會開啟一個程式碼編輯器，裡面有一個空的 Code.gs。

Paste the server code

Replace everything in Code.gs with the code below, and put your own sheet ID in the first line.

把 Code.gs 裡的內容全部換成下面的程式碼，並在第一行填入你自己的試算表 ID。

Code.gs

const SHEET_ID   = 'PASTE_YOUR_SHEET_ID_HERE';
const SHEET_NAME = 'responses';

// Serves the page. ALLOWALL is what lets Google Sites embed it —
// without it the browser refuses to show the page in a frame.
function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index')
    .setTitle('Practice')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

// Called from the page when a student answers.
// One row per answer — see section 4 for why this shape matters.
function saveAnswer(payload) {
  const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
  sheet.appendRow([
    new Date(),
    payload.studentName,
    payload.className,
    payload.setId,        // which worksheet / unit this question belongs to
    payload.questionId,
    payload.answer,
    payload.correct ? 'correct' : 'wrong'
  ]);
  return 'saved';
}

Add the page the students see

In the editor, click + next to Files and choose HTML. Name it exactly Index — that name is what doGet looks for. Replace its contents with the code below.

在編輯器的 Files 旁按 +、選 HTML，命名為 Index（要完全一致，doGet 找的就是這個名字），內容換成下面這段。

Index.html

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    body { font-family: system-ui, sans-serif; font-size: 18px; padding: 16px; }
    input, button { font-size: 18px; }
    button { display: block; width: 100%; text-align: left;
             padding: 12px 14px; margin: 6px 0; cursor: pointer; }
    #done { display: none; font-weight: 700; margin-top: 14px; }
  </style>
</head>
<body>
  <p><label>Your name 姓名<br><input id="name"></label></p>

  <p id="q"><b>Q1.</b> The oysters come ____ the tidal flats.</p>
  <div id="opts">
    <button onclick="answer('from', true)">from</button>
    <button onclick="answer('at', false)">at</button>
    <button onclick="answer('on', false)">on</button>
  </div>

  <p id="done"></p>

  <script>
    function answer(choice, correct) {
      var name = document.getElementById('name').value.trim();
      if (!name) { alert('Please write your name first.'); return; }

      document.getElementById('opts').style.display = 'none';
      var done = document.getElementById('done');
      done.textContent = correct ? 'Correct! 答對了' : 'Not this time. 再想想';
      done.style.display = 'block';

      google.script.run
        .withFailureHandler(function (e) { alert('Not saved: ' + e.message); })
        .saveAnswer({
          studentName: name,
          className:   '5A',
          setId:       'U5-directions',   // same for every question on this page
          questionId:  'Q1',
          answer:      choice,
          correct:     correct
        });
    }
  </script>
</body>
</html>

Deploy it as a web app

Deploy → New deployment. Choose type Web app. Set Execute as: Me and Who has access: Anyone. Google will ask you to authorize the script the first time — it is your own script writing to your own sheet.

Copy the web app URL it gives you. It ends in /exec.

部署 → 新增部署作業，類型選網頁應用程式， 執行身分：我，誰可以存取：所有人。第一次會要求你授權—— 那是你自己的指令碼在寫你自己的試算表。複製它給你的網址，結尾是 /exec。

Test it before you show anyone

Open the /exec URL in a new tab, type a name, click an answer. Then look at your spreadsheet — a row should have appeared. If it did not, the sheet ID or the tab name is wrong; those two are the usual culprits.

在新分頁開那個 /exec 網址，打名字、按一個答案，然後回試算表看—— 應該多了一列。沒有的話，多半是試算表 ID 或分頁名稱錯了，這兩個是最常見的原因。

Embed it in your site

In Google Sites: Insert → Embed → By URL, paste the /exec URL, and place the block on the page. That is the whole integration — this is the step that ALLOWALL in step 3 exists for.

在 Google Sites：插入 → 內嵌 → 依網址，貼上 /exec 網址，把區塊放到頁面上。整個整合就是這樣——第三步那行 ALLOWALL 存在的理由就是為了這一步。

Section 03The three things that will trip you up

1. Editing the code does not change what students see

This is the one that wastes an entire evening. A deployment is frozen at a version. After you edit the code you must go to Deploy → Manage deployments, click the pencil, and set Version: New version. Only then does the /exec URL serve your changes — and the URL stays the same, so your site does not need updating.

這一條會浪費你一整個晚上。部署是「凍結在某個版本」的。改完程式碼之後，必須到 部署 → 管理部署作業、點鉛筆、把版本改成「新版本」， /exec 網址才會提供你的新程式碼——而網址不會變，所以網站那邊不必動。

2. "Anyone" really means anyone

Access set to Anyone is what makes it work for students who are not signed in to a school account — and it also means whoever has the URL can submit. Expect the occasional nonsense row. Never put anything confidential in the page or the sheet, and do not treat the results as a secure assessment record.

If every student has a school Google account, you have a better option: set access to Anyone with a Google account and Execute as: User accessing the web app, then read their identity on the server with Session.getActiveUser().getEmail() instead of asking them to type a name.

存取權設成所有人，是為了讓沒登入學校帳號的學生也能用——同時也代表拿到網址的人都能送資料。預期會有一些亂填的列。頁面和試算表裡不要放任何機密，也不要把這份結果當成正式的成績紀錄。如果每個學生都有學校 Google 帳號，還有更好的做法：存取權設成擁有 Google 帳戶的使用者、執行身分設成存取網頁應用程式的使用者，然後在伺服器端用 Session.getActiveUser().getEmail() 取得身分，不必叫他們自己打名字。

3. Collect the minimum, and decide that on purpose

You are now holding a file of children's names attached to their mistakes. Ask for a given name and a class, not a full name and a student number, unless you genuinely need more. Keep the sheet in your own Drive, do not share the link, and delete the file when the unit is over. None of this is difficult — it just has to be a decision rather than an accident.

你手上現在有一份「孩子的名字連著他們的錯誤」的檔案。除非真的需要，只要名字和班級就好，不要全名加學號。試算表放在自己的雲端硬碟、不要分享連結、單元結束就刪掉。這些都不難，重點是它必須是一個決定，而不是一個意外。

Section 04The spreadsheet is the point

The web app is just the collector. The spreadsheet is the thing you will actually live in, and five minutes of setting it up properly is the difference between a file full of rows and a back office that tells you what to do on Monday.

網頁應用程式只是收件的工具，試算表才是你真正會待著的地方。花五分鐘把它設定好，就是「一個裝滿資料列的檔案」和「一個會告訴你星期一該做什麼的後台」之間的差別。

One row per answer, not one row per student

The tempting layout is one row per student with a column for each question. Do not do it. The script writes one row per answer — name, class, set, question, answer, result — and that shape is why everything below is a single formula. Add an eleventh question and nothing changes; in the other layout you would be rebuilding the sheet.

很多人第一直覺是「一個學生一列、一題一欄」。不要那樣做。這支程式寫的是 一個答案一列——姓名、班級、單元、題號、作答、對錯—— 正因為是這個形狀，下面每一項分析都只要一條公式。加第十一題什麼都不用改；換成另一種排法，你得把整張表重做。

The set column is what lets one spreadsheet serve your whole year. Give every page of questions its own set name (U5-directions, U7-food), and you never need a second sheet or a second script — you just change one string in the query.

set 這一欄，是讓同一份試算表撐得住你整個學年的關鍵。每一組題目給一個自己的 set 名稱（U5-directions、U7-food），你就永遠不需要第二份試算表、第二支程式——查詢時改一個字串就好。

The one rule that keeps this working

Never type in the responses tab, never sort it, never insert rows. The script appends to the bottom; your hands go somewhere else. Make a second tab called dashboard and do every human thing there. When it is set up, use Data → Protect sheets and ranges on responses so you cannot break it by accident at eleven at night.

responses 分頁不要手動打字、不要排序、不要插入列。 程式是往最下面加資料的；你的手要放在別的地方。另開一個叫 dashboard 的分頁，所有人為的操作都在那裡做。設定好之後用資料 → 保護工作表和範圍鎖住 responses，免得你半夜十一點不小心把它弄壞。

Four formulas, four questions you actually have

Put these on the dashboard tab. Each one is typed into a single cell and fills the area below it by itself. Change U5-directions to whichever set you want to look at.

把這幾條放在 dashboard 分頁。每一條只要打進一格，下面的範圍會自己長出來。想看哪一組題目，就把 U5-directions 換成那一組的名稱。

1. Which question broke the class? The one you most need before you teach them again.

1. 哪一題打敗了全班？——再教他們之前，最需要知道的就是這個。

=QUERY(responses!A:G,
  "select E, count(G)
   where G = 'wrong' and D = 'U5-directions'
   group by E order by count(G) desc
   label E 'question', count(G) 'got it wrong'", 1)

2. Which students need you before Monday?

2. 星期一之前，哪些學生需要你？

=QUERY(responses!A:G,
  "select B, count(G)
   where G = 'wrong' and D = 'U5-directions'
   group by B order by count(G) desc
   label B 'student', count(G) 'wrong'", 1)

3. What did they choose instead? This is the one people forget, and it is the most useful of the four. A wrong option picked by fifteen students is not noise — it is a misconception with a name, and it is your next lesson.

3. 他們選的是什麼？——這是最常被忘記、卻最有用的一條。一個被十五個學生選走的錯誤選項不是雜訊，那是一個叫得出名字的迷思概念，也就是你的下一堂課。

=QUERY(responses!A:G,
  "select F, count(G)
   where E = 'Q4' and G = 'wrong'
   group by F order by count(G) desc
   label F 'chose this instead', count(G) 'how many'", 1)

4. One student's whole history, for the conversation with a parent, a homeroom teacher, or the student themselves.

4. 單一學生的完整紀錄——要跟家長、導師，或學生本人談的時候用。

=QUERY(responses!A:G,
  "select A, D, E, F, G
   where B = 'Ming'
   order by A desc
   label A 'when', D 'set', E 'question', F 'answer', G 'result'", 1)

Who has not done it at all

The rows tell you about the students who came. The ones you need to chase are the students who are not in the sheet — and no query over the responses can show you an absence. Paste your class roll down column A of a third tab, then put this beside the first name and drag it down:

資料列只告訴你「有來的人」。你真正要追的是不在表裡的那些學生—— 而任何針對作答資料的查詢，都變不出「不存在」這件事。在第三個分頁的 A 欄貼上你的班級名條，然後在第一個名字旁邊放這條、往下拖：

=COUNTIF(responses!$B:$B, $A2)

Zero means that child never opened it. That column is the one you act on first, and it is the single thing this whole setup gives you that a stack of paper never could — because on paper, the sheet you never received looks exactly like the sheet you lost.

0 代表那個孩子從來沒打開過。那一欄是你最該先處理的，也是整套做法給你、而一疊紙永遠給不了的東西—— 因為在紙本上，「沒有交」和「被你弄丟」看起來一模一樣。

Two more things worth five minutes

Color the numbers so you can read them at a glance. Select the count column on the dashboard, then Format → Conditional formatting → Color scale. After that you are reading a shape, not a table, and it takes five seconds instead of two minutes. 把數字上色，讓你一眼就讀得完。選取 dashboard 上的數量欄， 格式 → 條件式格式設定 → 色階。之後你讀的是形狀而不是表格，五秒鐘就夠，不用兩分鐘。
Let students redo it, and keep every attempt. Each retry is a new row, on purpose — "got it wrong twice then right" is more useful to you than a single clean score. Do not turn this into a gradebook; it is a diagnostic, and it is not a secure record anyway (see section 3). 讓學生重做，而且每一次都留著。每次重試都是新的一列，這是刻意的—— 「錯兩次之後對了」對你的價值高過一個乾淨的分數。不要把它變成成績簿；它是診斷工具，而且本來就不是安全的紀錄（見第三節）。

Section 05Making it worth more than paper

A digital worksheet that is just a paper worksheet on a screen is not worth the setup. Three things this can do that paper cannot:

一份「只是把紙搬到螢幕上」的數位學習單，不值得你花這些工。這個做法能做到、而紙做不到的有三件事：

Instant feedback, so being wrong is cheap. On paper a mistake is discovered days later, after the moment of caring has passed. On screen it is corrected in two seconds and the student tries again. 即時回饋，讓「答錯」的代價很低。在紙上，錯誤要好幾天後才被發現，那時想知道答案的那股勁早就過了；在螢幕上，兩秒鐘就更正，學生會再試一次。
A dashboard instead of a stack. Once the answers are in a sheet, the four formulas in section 4 tell you which question the class failed, which students need you, what they chose instead, and who never opened it — the things you actually need in order to plan Monday, and the things you never get from marking twenty-five sheets in a hurry. 用一個後台取代一疊紙。答案進了試算表之後，第四節那四條公式會告訴你：全班錯最多的是哪一題、哪些學生需要你、他們錯選了什麼、以及誰從來沒打開過——那些正是你排星期一的課真正需要的資訊，也是你趕著改二十五張紙時永遠得不到的。
It is evidence for your co-teacher. "Eighteen of them got question four wrong" is a conversation about the class. "I think they did not really understand" is a conversation about opinions. The co-teaching pack asked you to agree on one observable sign — this is one. 它是給協同老師看的證據。「第四題有十八個人錯」談的是這個班；「我覺得他們好像沒真的懂」談的是感覺。協同教學那一包要你們講好一個看得見的判準——這就是一個。

Section 06Three sentences to take into next week

Deploy a new version, or students keep seeing the old code. This is the mistake everyone makes once. 要部署「新版本」，否則學生看到的還是舊程式碼。這個錯每個人都會犯一次。
One row per answer, and never touch the responses tab by hand. That shape is what turns a file of rows into a back office. 一個答案一列，而且 responses 分頁絕不手動去動。是那個形狀把一個檔案變成一個後台。
The most valuable column is the one counting who never opened it. On paper, "never handed in" and "I lost it" look identical. 最有價值的一欄是「誰從來沒打開過」。在紙本上，「沒交」和「我弄丟了」長得一模一樣。

Check yourself

自我檢查

Six questions. Nothing here is scored, saved, or seen by anyone else.

六題。不計分、不存檔、不會有第二個人看到。

Q1.You edited your Apps Script code, but students still see the old questions. Why?

你改了 Apps Script 的程式碼，但學生看到的還是舊題目。為什麼？

A. Deploy → Manage deployments → pencil → Version: New version. The URL stays the same, so your site never needs updating — which is exactly why this mistake is so easy to miss. 部署 → 管理部署作業 → 鉛筆 → 版本：新版本。網址不會變，所以網站那邊完全不用動——這也正是為什麼這個錯特別容易被忽略。

Q2.What does setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL) do?

setXFrameOptionsMode(...ALLOWALL) 的作用是什麼？

C. Without it the browser refuses to render the web app inside the embed and you get an empty box on your site. Access permissions (A) and authorization (B) are set separately, at deployment. 沒有它，瀏覽器會拒絕在內嵌框裡顯示這個網頁應用程式，你的網站上就只會出現一個空白框。存取權限（A）與授權（B）是部署時另外設定的。

Q3.You test the web app and it works, but no row appears in the spreadsheet. What is the most likely cause?

你測試網頁應用程式，畫面正常，但試算表沒有新增資料列。最可能的原因是什麼？

D. Those two are the usual culprits — the ID is the string between /d/ and /edit in the sheet's address, and the tab must be named exactly responses to match SHEET_NAME. 這兩個是最常見的原因——ID 是試算表網址中 /d/ 與 /edit 之間那一串，而分頁名稱必須跟 SHEET_NAME 完全一致。

Q4.Every student in your school has a school Google account. What is the better deployment setting?

你們學校每個學生都有學校 Google 帳號。比較好的部署設定是什麼？

B. Session.getActiveUser().getEmail() then identifies them without typing, which is both more reliable and less data to handle. A collects more personal data than you need; D gives you rows you cannot act on. Session.getActiveUser().getEmail() 不必打字就能辨識身分，既可靠又少收資料。A 收了超過需要的個資；D 收到的資料你無法據以行動。

Q5.What should you do with the response sheet once the unit is finished?

單元結束後，這份作答試算表該怎麼處理？

C. This is a file of children's names attached to their mistakes. Collect the minimum, keep it unshared, delete it when done — and make that a decision rather than an accident. It is also not a secure assessment record, since anyone with the URL can submit. 這是一份「孩子的名字連著他們的錯誤」的檔案。只收最少、不要分享、用完就刪——而且要是一個決定，不是一個意外。它也不是正式的成績紀錄，因為拿到網址的人都能送資料。

Q6.Why does the script write one row per answer rather than one row per student?

為什麼這支程式是「一個答案一列」，而不是「一個學生一列」？

A. One row per student with a column per question breaks the moment you add a question — you would be rebuilding the sheet. The long shape is also what lets the set column carry your whole year in one spreadsheet: change one string in the query and you are looking at a different unit. 「一個學生一列、一題一欄」在你加一題的那一刻就垮了，你得把整張表重做。這個長形的排法，也正是 set 這一欄能讓一份試算表撐住整個學年的原因：查詢時改一個字串，看的就是另一個單元。

Sources & related

參考與延伸

Apps Script — Web Apps (Google for Developers): deployment types, execution identity, and access settings.
Enum XFrameOptionsMode — what ALLOWALL does and the security note attached to it.
Client-to-server communication — google.script.run, success and failure handlers.
Chapter 2: Making a Worksheet That Actually Works — the design rules still apply on a screen.
Menu names change. Google renames things in the Apps Script and Sites interfaces from time to time; if a menu item is not where this chapter says, the concept is still right and the label has moved. 選單名稱會變。Google 偶爾會調整 Apps Script 與 Sites 的介面用語；如果某個選項不在本章說的位置，觀念仍然正確，只是標籤搬家了。

Apps Script:Questions That WriteBack to Your Site