SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsopencode-sheets-gas

opencode-sheets-gas

General & other

View repository

Updated 10-16 00:21

About this skill

Google Sheets + Apps Script Web App (Lazy Pack #05)

The webpages you made in the previous packs lost everything once you closed them. This pack lets your projectsremember things: whatever students fill in, vote on, or submit all goes into a Google Spreadsheet.

What this pack covers and what it doesn't

Covers

  • Using one Google Spreadsheet as a database
  • Using Apps Script's "Web App" as both thefront-end webpageand thebackend
  • Deploying it as a URL students can open directly, shortening it, and generating a QR Code
  • After updatingthe URL stays the same(no need to reprint the QR Code)

Doesn't cover

  • Multi-userreal-timesync (word clouds, buzzer races, live leaderboards) → see "When not to take this path" below
  • Student login, identity verification, grading systems
  • clasp, Node.js, anything that needs to be installed on a computer—this pack is zero-install throughout

⚠️ Let's be clear first: why this pack doesn't use clasp

Most GAS tutorials online teach clasp(Google's official command-line tool that can push local code up to Apps Script). This packdeliberately avoids that path. The reason isn't that "clasp is bad," it's thatit will choke at a workshop on site。

The clasp route This pack: the Web App route
Does it require installing anything? Yes, Node.js (version requirements keep rising with clasp releases), and npm install Nothing to install at all
Does it require extra configuration? You have to go to script.google.com/home/usersettings to manually enable the Apps Script API,and wait a few minutes for it to take effect No
Are there credential files? Yes (~/.clasprc.json), something that can easily get committed to GitHub by accident No credential files at all
Can school Google accounts use it? Often not. When admins block third-party apps, it throws admin_policy_enforced, or just shows "You do not have access to this service" Yes. A script the teacher wrote themselves is an internal script under their own account, not a third-party app, so it isn't subject to that policy
Does Windows trip you up? Yes. The PowerShell execution policy blocks globally installed clasp.ps1 No, there's no command to run at all
URL after updating You have to remember to update the same deployment; type the command wrong and the URL changes Click "Edit → New version" on the webpage,the URL stays the same

The decisive one：admin_policy_enforced half of the repair steps can only be done by the school network administrator—the teacher themselves is on siteno solution. In a class of twenty-some teachers, as long as three to five use their school Google Workspace accounts, the whole workshop will stop right there waiting for the network administrator to reply.

📌 If you're just tinkering at home by yourself and using a personal Gmail, clasp is a good tool. Butat a workshop venue, with multiple people doing it at the same time, this kit always takes the web app route.

First, confirm: do you really need a database?

Before you start, honestly answer these three questions. If you head in the wrong direction, everything after that is wasted effort.

Question If the answer is "yes"
I just want tocollect a form(registration, feedback, submission links), and I don't need a custom interface Just use Google Forms, zero code. Responses are automatically written to a spreadsheet anyway
What I want iseveryone seeing at the same time what others just submitted(word clouds, quick-response quizzes, live leaderboards) This route will get stuck. See "When not to take this route" below
I wanta custom web page interface, and what is submitted needs to be kept and still be there the next time it's opened ✅ This kit was written for you, keep reading

💬 For the Agent: when the user says "I want to make a web page that collects data,"ask these three questions first, and don't start writing GAS directly. Half the time the answer is "Google Forms," and in that case they can wrap up today.

What the whole route looks like

OpenCode 在你電腦上產出兩個檔      你在瀏覽器上做的事
┌──────────────────────┐        ┌───────────────────────────┐
│  Code.gs   （後端）   │        │  貼進 Apps Script 編輯器   │
│  index.html（前端）   │  ───▶  │  儲存 → 執行一次（授權）    │
└──────────────────────┘        │  部署 → 網頁應用程式        │
                                └───────────┬───────────────┘
                                            ▼
                            https://script.google.com/macros/s/xxx/exec
                                            │
                          ┌─────────────────┴─────────────────┐
                          ▼                                   ▼
                   學生用手機掃 QR 開網頁              資料寫進你的 Google 試算表

Key understanding: that /exec URLis both the web page and the backend. It's not "the web page goes here, the database goes there"; it's the same thing. That's why it won't run into problems.

Why not put the frontend on GitHub Pages? (Architecture decision)

Many people's instinct is: put the frontend on GitHub Pages and make it prettier, then use fetch() to hit the GAS URL and access the data. Don't do this. You'll run into CORS.

What happens:

  1. The browser sees that "the JS from site A wants to hit the API of site B," and it carries Content-Type: application/json
  2. so it first sends an OPTIONS preflight request to ask site B "do you allow me to hit you?"
  3. Apps Script does not respond to OPTIONS—it only has doGet and doPost
  4. The preflight fails, and your fetch is blocked by the browser before it can even be sent

There are workarounds (change Content-Type to text/plain;charset=utf-8 so the browser doesn't do a preflight, then have the backend parse it itself with JSON.parse(e.postData.contents) ). It works, but you've added a rule you always have to remember, and if you get it wrong there won't be an obvious error message—it will just "not respond".

A simpler approach: have the frontend be served directly by GAS's HtmlService .

前端和後端在同一個網域       →  沒有跨網域
用 google.script.run 溝通    →  根本不走 HTTP 請求，沒有預檢
                            →  CORS 這個詞你今天可以不用學

The cost is that you can't use the familiar GitHub Pages deployment method. But for "a data-collecting web page for classroom use," this trade is very worthwhile.

Prerequisites

  • completed 00-env-setup and 01-connect-model(OpenCode must be able to run)
  • Have a Google account
  • Browser (Chrome / Edge both work)
  • Noanything that needs to be installed ✅

🚨 At the workshop, always use a personal Gmail, not the school's Google Workspace account. For the reason, see the second of the "three sticking points" below. This is the first key to the success of this package.

⛔ Six rules for AI Agents

  1. Do not try to click the browser for the user. Authorization and deployment all happen on Google's web pages; the agent is only responsible for producing files,reading the steps aloud to the user, and waiting for them to report back。
  2. Do not fill in any Google account information on the user's behalf, and do not ask the user to paste their password or verification code to you.
  3. Do not hard-code the user's spreadsheet ID, Email, or deployment ID in the code and then tell them to upload it to GitHub. Before uploading, first remind them what these are.
  4. At every step, wait for the user to report what screen they seebefore moving on. Almost all GAS errors are "the previous step wasn't actually completed."
  5. Do not proactively teach clasp, unless the user explicitly says they want to use it. For the reason, see the comparison table above.
  6. If you see the user wanting to collect students' real names, ID numbers, or parents' phone numbers—stop, and read the "Data Protection" section aloud to them.
Please have OpenCode execute the following steps in order

Step 1: Create a spreadsheet to serve as the database

🖐️ The user does this step themselves(the agent reads the steps aloud):

  1. Open https://sheets.google.com, create a new spreadsheet
  2. Name it, for example "Classroom Feedback Database"
  3. Change the sheet name in the bottom left to 回覆(the program will look for this name later; the program will also create it automatically, but changing it first makes things less messy)
  4. Type the headers in the first row:時間、暱稱、內容

Verification: The first row of the spreadsheet has three column headers, and the sheet is named "Responses".

💡 One principle of field design:Always put a timestamp in the first column. Later, whether you want to sort, filter out test data, or find "the feedback from last week's class," you'll rely on it. If you only think to add it afterward, the earlier data won't have it.

Step 2: Open the Apps Script editor

There are two paths;Route A is recommended。

Route A: Open from the spreadsheet (recommended, one less chance of error)

On the spreadsheet you just created:Extensions → Apps Script

Benefit: The script is bound to the spreadsheet, so in the codeyou don't need to fill in the spreadsheet ID—one less pitfall of "copying the wrong URL."

Route B: Create a new project from script.google.com

  1. Open https://script.google.com
  2. Click "New project" in the top left
  3. This way of writing is called a "standalone script"; in the codeyou need to fill in the spreadsheet ID

The spreadsheet ID is the middle part of the URL,not the entire URL：

https://docs.google.com/spreadsheets/d/1AbC...這一段才是ID...XyZ/edit#gid=0
                                      └──────────┬──────────┘
                                            只複製這一段

Verification(common to both paths): You see a file called Code.gs with a section function myFunction() {}. Change the project name from "Untitled project" to something recognizable, for example "Classroom Feedback Backend".

Step 3: Paste the backend code Code.gs

🤖 This part is produced by OpenCode, and the user is only responsible for copying and pasting.

In the editor, Code.gs delete all of the original contentcompletely, and replace it with the following:

/**
 * 課堂回饋 —— 後端
 * 這份檔案做三件事：
 *   1. doGet        有人打開網址時，把 index.html 這個網頁送出去
 *   2. submitFeedback  前端按「送出」時，把一筆資料寫進試算表
 *   3. listFeedback    前端載入時，把最新幾筆讀回來顯示
 */

const SHEET_NAME = '回覆';   // 要跟試算表左下角的分頁名稱一模一樣

/** 有人打開網址 → 回傳網頁 */
function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('index')
    .setTitle('課堂回饋')
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

/** 前端呼叫：寫入一筆 */
function submitFeedback(data) {
  // 全班同時按送出時，用鎖排隊，避免兩個人寫到同一列互相蓋掉
  const lock = LockService.getScriptLock();
  try {
    lock.waitLock(10000);   // 最多等 10 秒
  } catch (err) {
    return { ok: false, error: '現在同時送出的人太多，請過幾秒再按一次' };
  }

  try {
    const message = String(data.message || '').trim().slice(0, 500);
    if (!message) {
      return { ok: false, error: '內容不能空白' };
    }
    const nickname = String(data.nickname || '').trim().slice(0, 20) || '匿名';

    const sheet = getSheet_();
    sheet.appendRow([new Date(), nickname, message]);
    return { ok: true, count: sheet.getLastRow() - 1 };
  } finally {
    lock.releaseLock();   // 不管成功失敗都要放開，否則後面的人會一直等
  }
}

/** 前端呼叫：讀最新 20 筆（新的在上面） */
function listFeedback() {
  const sheet = getSheet_();
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return [];               // 只有標題列 = 還沒有資料

  const start = Math.max(2, lastRow - 19);
  const rows = sheet.getRange(start, 1, lastRow - start + 1, 3).getValues();

  return rows.reverse().map(function (r) {
    const t = (r[0] instanceof Date) ? r[0] : new Date(r[0]);
    return {
      // ⚠️ 一定要轉成字串再回傳，google.script.run 傳不了 Date 物件
      time: Utilities.formatDate(t, 'Asia/Taipei', 'MM/dd HH:mm'),
      nickname: String(r[1]),
      message: String(r[2])
    };
  });
}

/** 內部用：拿到工作表，沒有就自動建一個並補上標題列 */
function getSheet_() {
  // 路線 A（從試算表的「擴充功能」開的）用這行：
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 路線 B（從 script.google.com 開的獨立腳本）改成這行，並填入你的試算表 ID：
  // const ss = SpreadsheetApp.openById('把試算表 ID 貼在這裡');

  let sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
    sheet.appendRow(['時間', '暱稱', '內容']);
  }
  return sheet;
}

/** 只給老師手動執行一次，用來觸發授權畫面（見步驟五） */
function 一鍵授權() {
  const sheet = getSheet_();
  Logger.log('工作表就緒：' + sheet.getName() + '，目前有 ' + (sheet.getLastRow() - 1) + ' 筆資料');
}

Press Save(the disk icon, or Ctrl + S）。

Verify: in the function dropdown menu at the top of the editor, you can see doGet、submitFeedback、listFeedback、一鍵授權. If the dropdown menu is empty or has only one item → the code has a syntax error; scroll down to look for the red wavy underline.

🔍 What is the underscore at the end of the function name? getSheet_() In GAS, this naming convention means a "private function"— it will not appear in the deployment menu, and the frontend cannot call it either. This is intentional, to prevent students from directly calling internal functions from the web page.

Step 4: Add the frontend web page index.html

On the left side of the editor, next to "Files", press + → HTML。

For the file name, enter index（Do not type .html; the editor will add it automatically; if you type it, it will become index.html.html, and then doGet will not be able to find it).

Delete all the default content and replace it with:

<!DOCTYPE html>
<html lang="zh-Hant">
<head>
  <base target="_top">
  <meta charset="utf-8">
  <style>
    body { font-family: system-ui, "Noto Sans TC", sans-serif;
           max-width: 640px; margin: 0 auto; padding: 16px; line-height: 1.6; }
    input, textarea, button { width: 100%; font-size: 16px; padding: 10px;
                              margin-top: 8px; box-sizing: border-box;
                              border: 1px solid #ccc; border-radius: 6px; }
    button { background: #1a73e8; color: #fff; border: 0; cursor: pointer; }
    button:disabled { opacity: .5; cursor: wait; }
    #status { min-height: 1.5em; color: #1a7f37; }
    ul { padding: 0; }
    li { list-style: none; border-bottom: 1px solid #eee; padding: 10px 0; }
    .meta { color: #666; font-size: 13px; }
  </style>
</head>
<body>
  <h2>課堂回饋</h2>

  <input id="nickname" placeholder="暱稱（可以留白）" maxlength="20">
  <textarea id="message" rows="3" placeholder="想說的話" maxlength="500"></textarea>
  <button id="send">送出</button>
  <p id="status"></p>

  <h3>最新回覆</h3>
  <ul id="list"></ul>

  <script>
    // ⚠️ 前端 JS 一定要寫在這裡（index.html 的 script 區塊裡）
    //    絕對不要另存成 .gs 檔，那會被當成伺服器程式執行然後爆掉
    var $ = function (id) { return document.getElementById(id); };

    function show(text) { $('status').textContent = text; }
    function lock(on)  { $('send').disabled = on; }

    function send() {
      var message = $('message').value.trim();
      if (!message) { show('內容不能空白'); return; }

      lock(true);
      show('送出中…');

      google.script.run
        .withSuccessHandler(function (res) {
          lock(false);
          if (res && res.ok) {
            $('message').value = '';
            show('已送出，目前共 ' + res.count + ' 筆');
            load();
          } else {
            show('送出失敗：' + ((res && res.error) || '未知原因'));
          }
        })
        .withFailureHandler(function (err) {
          lock(false);
          show('送出失敗：' + err.message);
        })
        .submitFeedback({
          nickname: $('nickname').value,
          message: message
        });
    }

    function load() {
      google.script.run
        .withSuccessHandler(function (rows) {
          var ul = $('list');
          ul.innerHTML = '';
          rows.forEach(function (r) {
            var li = document.createElement('li');
            var meta = document.createElement('div');
            meta.className = 'meta';
            meta.textContent = r.nickname + '　' + r.time;
            var body = document.createElement('div');
            // 用 textContent 不用 innerHTML，學生打進來的東西不會被當成程式執行
            body.textContent = r.message;
            li.appendChild(meta);
            li.appendChild(body);
            ul.appendChild(li);
          });
        })
        .withFailureHandler(function (err) { show('讀取失敗：' + err.message); })
        .listFeedback();
    }

    $('send').addEventListener('click', send);
    load();
  </script>
</body>
</html>

Press Save。

Verify: the file list on the left has two files:Code.gs and index.html. If the names are wrong, it is still not too late to change them now.

🧩 google.script.run.submitFeedback(...) What this line is doing: calling Code.gs inthe function with the same name. If you type the name wrong, there will be no error message; it will just "do nothing when pressed". The function names on the frontend and backend must beexactly the same。

Step 5: Run it manually once to complete authorization

This step cannot be skipped. If it is not authorized, the deployed URL will show an error page as soon as students open it.

  1. In the function dropdown menu at the top of the editor, select 一鍵授權
  2. Press Run
  3. "Authorization required" pops up → press Review permissions
  4. Select your Google account
  5. A very scary screen will pop up(see "Stuck Point 1" below) → press Advanced → Go to "your project name" (unsafe)
  6. Press Allow

Verify: the "Execution log" at the bottom of the editor shows:

工作表就緒：回覆，目前有 0 筆資料

Seeing this line = authorization is complete, and the code can read the spreadsheet.Two things verified at once.

⚠️ Later, if you add new features to the program(for example, sending Gmail or writing to Calendar), those are new permissions,and you need to run it manually again to reauthorize. Symptom: everything suddenly breaks on the student side, while everything is normal for you in the editor.

Step 6: Deploy as a web app

  1. In the upper right corner, Deploy → New deployment
  2. On the left, the gear "Select type" → Web app
  3. Fill in:
Field What to select Why
Description Write anything, e.g. "First version" Just a label for your own reference
Execute as Me (your email) Students don't have permission to your spreadsheet. Choosing "Me" = usingyour identityto write data; students don't need to log in, and the spreadsheet can stay private
Who has access Anyone Students can open it without logging into Google

The text on screen may differ slightly from here (Google adjusts it). Just look for the keywords:For "Execute as" choose "Me", for "Who has access" choose "Anyone"。

  1. Click Deploy
  2. Copy the web app URL, it looks like this:
https://script.google.com/macros/s/AKfycb.../exec
                                          └─┬─┘
                                     結尾一定是 exec

Verify: you now have a URL ending in /exec .

🚨 The other URL in the editor ending in /dev should not be sent to students. /dev Onlypeople with edit permissioncan open it, and it always runs the latest draft. You test it yourself and it works fine, but the moment a student opens it they get "You don't have access"—eight times out of ten it's because you copied the /dev。 What you send out must always be the /exec。

Step 7: Test with an incognito window (don't skip this)

🖐️ Open anincognito window（Ctrl + Shift + N), paste in the /exec URL.

Why incognito is a must: your browser is currently logged into your own Google account,so what you're testing is not what students see. Also, when multiple Google accounts are logged in at the same time, GAS often sends requests to the wrong account, causing baffling permission errors. An incognito window = clean, not logged in, the same state as a student's phone.

Test in order:

  1. The page opens, and you can see the input box
  2. Type a sentence and hit submit → "Submitted, 1 response so far" appears
  3. A new row immediately appears under "Latest responses"
  4. Switch back to the spreadsheet → row 2 really has that entry, and the timestamp is correct

It only counts as working if all four pass. If any one fails, check the "Common pitfalls" section below.

Step 8: Shorten the URL + QR Code

That /exec URL is long and ugly; nobody can copy it down.

Generate a QR Code (recommended: generate locally, don't send it out)

If you've done 02-file-toolkit, your computer already has Python's qrcode package. Just tell OpenCode:

幫我用 qrcode 套件，把這條網址做成 QR Code 圖檔，存到桌面：
https://script.google.com/macros/s/.../exec

Benefit: the URL never passes through any third-party service.

Shorten the URL (optional)

Only needed when you have to read it aloud for students to type in. You can use a no-registration URL shortener, or just project the QR code.

🔒 Think before you send it out: the URL shortening service will know this URL of yours. The URL itself doesn't contain a password, but it's the entrance to your work—once a page has collected data, don't hand its URL off to an external service。

🔁 Update without changing the URL (the most important page in this pack)

What teachers fear most: the QR Code is printed on the handout and distributed, then you fix a typo andthe URL changes。

After modifying the code, the correct procedure is:

部署  →  管理部署作業  →  找到那筆部署，按右上角的鉛筆（編輯）
      →  「版本」下拉選單選【新版本】
      →  部署

URLcompletely unchanged. The QR Code remains valid. Students' handouts don't need to be reprinted.

Incorrect procedure (will change the URL):

部署  →  新增部署作業     ← ❌ 這是「再開一個全新的」，會給你一條新網址
What you want to do Which path to take URL
Fix typos, add features, fix bugs Manage deployments → Edit → New version Unchanged ✅
The same code needs different entry points for two classes New deployment Gets a new one

💡 Mnemonic: Pencil = edit this one; "New deployment" = create another one. Before the workshop, walk through it yourself once: "change a word → Edit → New version → Deploy → reopen in incognito" to confirm the URL really doesn't change, so you'll dare to say it on stage.

⚠️ After redeploying, if the incognito window still shows the old screen, press Ctrl + F5 to force a reload. This is browser caching, not a deployment failure.

🚨 Three stumbling blocks you're guaranteed to hit

Before the workshop startsexplain firstthese three things, don't wait for them to happen.

Stumbling block 1: During authorization, "Google hasn't verified this app" pops up

The screen looks like this: a gray page with a warning symbol, saying "This app isn't verified by Google," with only a scary-looking "Back to safety" button.

A teacher's first reaction is to close the window. So explain it first.

Correct procedure：

點左下角很小的【進階】
  → 點【前往「你的專案名稱」（不安全）】
    → 點【允許】

Why there's nothing to fear: this "app" isthe very script you wrote yourself five minutes ago. Google's verification process is for software meant to be published for the whole world to use; your own script hasn't been submitted for review, so it doesn't recognize it— what it's warning about is "Google doesn't know this program," not "this program has a problem." The one you're authorizing is yourself.

🗣️ Say this on stage: "In a moment a scary red-text screen will appear; that's normal. The program it says is unsafe is the very one you just wrote yourself. Please click 'Advanced,' then click 'Go to (unsafe).'"

Stumbling block 2: School Workspace accounts may not see "Anyone"

Symptom: in the deployment settings, the "Who can access" dropdownonly has"Only myself" and "Everyone at ◯◯ School," there is no"Anyone" option.

Cause: the school's Google administrator has restricted external sharing. This is an institutional policy;the teacher cannot change it themselves。

Consequence: people outside the school, students using home accounts, parents—none of them can open it. The most common way things die at a workshop.

Solution：

Scenario Approach
At the workshop Always use a personal Gmail account. Don't use a school account
Back at school, for official launch If all students have school accounts, choosing "Everyone at ◯◯ School" also works
Needs to be open to off-campus access You can only ask the school network administrator to adjust the policy, or switch to a personal account

🚨 This is the one sentence in this pack that should be said earliest: "Today, please everyone first useyour personal Gmail to sign in to Google Sheets. If you use a school account, you'll find later when deploying that one option is missing, and then our whole class will have to stop and wait for the network administrator."

Sticking point 3: Multi-account sign-in causes session conflicts

Symptoms: The web page works sometimes and not others, inexplicable permission errors pop up, or it keeps spinning. You send the URL to the person next to you and it works fine for them.

Cause: When the browser is signed in to multiple Google accounts at the same time (for example, personal Gmail + school account), GAS sometimes attributes the request to the wrong account.

Solution：Always test in an incognito window。

This also applies to your everyday debugging:whenever the behavior is weird, open incognito first. Eighty percent of "weird things" disappear in an incognito window.

🔒 Data protection: what should not be put in

This spreadsheet is in your cloud drive, but that /exec URLanyone who gets it can open it. If the URL is forwarded around, it means anyone can send data in.

Do not collect：

  • ❌ Students' real names, ID numbers, birthdays
  • ❌ Parents' names, phone numbers, Email, addresses
  • ❌ Grades, counseling records, health conditions, family situations
  • ❌ Anything you would not want screenshotted and forwarded

Can collect：

  • ✅ Seat number + class code (de-identified; as long as you can map it back yourself)
  • ✅ Nicknames, codes
  • ✅ Feedback on course content, answers, votes
  • ✅ Work links

Also these habits：

Practice Why
The spreadsheet itselfKeep it private, do not set it to "anyone with the link can edit" When deploying, you chose "Execute as: Me"; GAS writes usingyour identity. The spreadsheet does not need to be open to the outside at all—many people mistakenly think it does, and end up making the whole dataset public
After the activity ends, set the deployment todisabled Deploy → Manage deployments → Archive. The URL becomes invalid
After collecting the data, save a separate backup and then clear it Reuse the same URL next class, but do not mix it with last time's data
Do not put the URL in a public GitHub repo That URL is equivalent to an externally open data entry point

💬 To the Agent: When the user asks to collect names or phone numbers,stop firstread the table above to them, suggest changing it to "seat number + class code," and wait for their reply before continuing. Do not silently comply.

⛔ When you should not take this path

This path is very useful, but it has a clear ceiling.If you hit any of the following, change paths.

Your need Why this path will not work Where to switch to
Multi-user real-time sync: word clouds, online battles, real-time leaderboards, whole-class quiz races Google Sheets writes are delayed, and every write has to queue.It starts lagging once about a dozen people write at the same time. When all thirty people in the class hit submit together, the ones at the back will wait until they time out 06-supabase(Realtime Database). The upgrade path extras/firebase is the same kind of approach
You only need to collect form responses, and don't need a custom web page interface You don't need to write any code at all Google Forms. Responses go into a spreadsheet automatically—zero code, zero deployment, zero pitfalls
You need to storelarge amounts ofdata (tens of thousands of rows or more) and do complex queries A spreadsheet is not a database. Once there's a lot of data, reads become so slow they're unusable A real database (beyond the scope of this workshop)
You need toupload files(photos, assignment files) It's possible, but very troublesome, and you easily run into capacity and permission issues Use the file upload question type in Google Forms, or ask students to hand in cloud links
You needstudents to sign in, and need to know who is who The combination "Execute as: Me + Anyone" isan anonymousdesign Switch to "Execute as: User accessing the web app" + a school account, or just use Google Forms (which can force sign-in and record accounts)

🎯 The most honest sentence: If Google Forms can solve it, use Google Forms. The value of this package is that middle ground where "forms can't do it, but it's not yet time to build a real database"— custom interfaces, reading data back, showing in real time what others have submitted. Get this straight, and you won't write code when you should be using Forms.

Common pitfalls

Symptom Cause Solution
Student opens the URL: "You don't have access" You sent them the /dev URL Send the /exec。/dev instead—only editors can open that one
Student opens the URL: "Sorry, this file cannot be opened right now" The authorization in Step 5 wasn't done, or you added new permissions without reauthorizing Go back to the editor, manually run any function once, and complete the authorization flow
You can't find "Anyone" in the deployment settings Institutional restrictions on school Workspace accounts See stumbling block 2. Switch to a personal Gmail
You press submit and "nothing happens", with no error message The function name called by the front end and the name in Code.gs aredifferent Compare them character by character. Case counts too
TypeError: ... is not a function Same as above, or Code.gs has a syntax error that prevents the whole thing from loading Look at the red underlines in the editor
Execution log:ReferenceError: document is not defined You saved the front-end JS as a .gs file Front-end JS can onlybe written in index.html of <script> inside
Execution log:ReferenceError: google is not defined Same as above, but reversed — put google.script.run into .gs it's been written That's a browser-side thing, it can't appear in .gs
"Script completed but returned no content" doGet does not have return doGet must return an HtmlOutput
What the front end receives is null or something weird google.script.run Cannot pass a Date object On the back end, first use Utilities.formatDate(...) to convert it to a string and then return it (the examples in this pack already do this)
Changed the code, but opening the URL still shows the old version Only clicked "Save", did not redeploy Saving ≠ going live. You need to go to "Manage deployments → Edit → New version"
Redeployed but still the old version Browser cache Ctrl + F5, or switch to an incognito window
The whole class submits at the same time, and some people's data disappears Concurrent overwrite The examples in this pack already use LockService to queue. If you really need real-time, see "When not to take this path"
Works sometimes, doesn't others; mysterious permission errors Multi-account session conflict Incognito window
doGet cannot find index HTML filename typed as index.html, becomes index.html.html When adding an HTML file, type only index, the extension is added by the editor
Chinese text becomes garbled index.html is missing <meta charset="utf-8"> Add it (the examples in this pack already include it)

❓ Items this pack cannot guarantee will stay unchanged (please verify on site yourself)

Item Situation
Field text in the deployment dialog Google may adjust UI wording.Recognize the keywords: for "Execute as" choose "Me", for "Who has access" choose "Anyone"
Button text on the unverified app warning page The wording occasionally changes, but the structure of "Advanced" → "Go to … (unsafe)" is stable
Available options for the school Workspace Entirely depends on how your school administrator sets it up; it varies by school
GAS execution time and concurrent execution limits There are limits, and they change with Google's policies.This pack does not hard-code numbers, refer to the official current documentation; in practice it just means "the whole class pressing submit at the same time will queue"

🤖 Machine-readable success signals

The followingallmust hold to consider this pack complete:

CHECK_1  Apps Script 專案中同時存在 Code.gs 與 index.html 兩個檔案
CHECK_2  index.html 的檔名為 index（不是 index.html.html）
CHECK_3  手動執行「一鍵授權」後，執行紀錄輸出「工作表就緒：回覆」，且無錯誤
CHECK_4  已建立「網頁應用程式」類型的部署，執行身分＝我、存取權＝所有人
CHECK_5  取得的網址以 /exec 結尾（不是 /dev）
CHECK_6  無痕視窗開啟該網址，網頁正常顯示輸入框（非任何錯誤頁）
CHECK_7  在無痕視窗送出一筆測試資料，前端顯示「已送出」
CHECK_8  Google 試算表「回覆」分頁中，該筆資料實際出現，且時間戳記正確
CHECK_9  修改程式碼後走「管理部署作業 → 編輯 → 新版本 → 部署」，網址與 CHECK_5 完全相同
CHECK_10 測試資料已從試算表清除

If any item is FAIL → check against "Common pitfalls" and handle accordingly,do not proceed to other quick-start guides。

Completion report template (for humans)

## Google Sheets ＋ GAS 網頁應用程式 完成

- 使用帳號類型：個人 Gmail / 學校 Workspace
- 試算表：已建立（分頁名稱：回覆）
- 腳本類型：容器繫結（路線 A）/ 獨立腳本（路線 B）
- Code.gs：CHECK_1 PASS / FAIL
- index.html 檔名正確：CHECK_2 PASS / FAIL
- 授權完成（執行紀錄）：CHECK_3 PASS / FAIL
- 部署設定（我＋所有人）：CHECK_4 PASS / FAIL
- 網址結尾為 /exec：CHECK_5 PASS / FAIL
- 無痕視窗可開啟：CHECK_6 PASS / FAIL
- 送出測試：CHECK_7 PASS / FAIL
- 資料確實寫入試算表：CHECK_8 PASS / FAIL
- 改版後網址不變：CHECK_9 PASS / FAIL
- 測試資料已清除：CHECK_10 PASS / FAIL

- 網頁應用程式網址：<.../exec>
- QR Code：已產生 / 未產生
- 資料保護提醒已告知使用者：是 / 否

### 待處理
- <把 FAIL 的項目與原因列在這裡；全部 PASS 就寫「無」>

If you mess it up, how to start over

Good news:this pack will not break your computer, because it installs nothing. Everything is inside your Google account.

From mild to severe:

  1. The code is just broken → Select all and delete Code.gs and index.html , paste the example again, and redeploy (Edit → New version)
  2. Wrong deployment settings(for example, access set to "Only myself") → Deploy → Manage deployments → Edit → Change settings → Deploy.The URL stays the same
  3. You want to scrap the whole deployment → Manage deployments → Archive. The URL stops working immediately
  4. Start the whole project over → Delete the project on the Apps Script home page and rebuild it from Step 2. The data in the spreadsheet is not affected
  5. You want to clear the data → Delete the rows directly in the spreadsheet. It is recommended to first use "File → Make a copy" to keep a copy

Tell OpenCode: "The last GAS package was messed up. Help me start over from Step 3 and generate a fresh copy of the code for me to paste."

➡️ Next step

Your project can store data now. Next, you can:

  • 06-supabase — when you really needreal-timesync (word cloud, quiz buzzer, real-time leaderboard), switch to that route
  • 07-github — back up your project's source code (rememberdo notput the /exec URL into a public repo)
  • 02-file-toolkit — download the collected data as .xlsx, and ask OpenCode to calculate statistics, rank them, and generate reports

💡 The pattern you learn in this package is more important than the package itself: Frontend and backend in the same place → no cross-origin issues → an entire class of errors that can never be traced is eliminated. From now on, whenever you see any tutorial that says "put the frontend here, put the API there," first ask, "Will this hit CORS?"