0. 作品介紹

作者 Rou

工具借用表單，可記錄總數及剩餘數量


45 2 0
非教學
預設封面圖 預設圖片

1. AI 提示詞

把這段指令貼給 AI，讓它幫你生成程式碼。

我想要建立一個 【工具借用表單】

幫我用Google 試算表建立 【工具資料庫】

我希望有些資料我可以直接在試算表更改首頁可以直接呈現

需求說明 【

1.要能增刪工具的種類及數量

2.要能統計同種工具借出的總數

3.要能統計各種工具借出後的剩餘數量】

要把它變成程式碼我要發佈

請完整敘述操作步驟 越詳細越好

作業環境

Google試算表

Google Apps Script

我看不懂程式碼，請給我後端gs完整程式碼、前端index完整程式碼

2. 資料表規則

請照下面規則建立分頁與欄位。

第1分頁： 庫存清單 A 欄： 器材名稱 B 欄： 總數量 C 欄： 已借出 D 欄： 剩餘數量 第2分頁： 借用紀錄 A 欄： 時間戳記 B 欄： 借用人姓名 C 欄： 器材名稱 D 欄： 借用數量

📄 解析結果（自動表格）

分頁：庫存清單
A 欄 B 欄 C 欄 D 欄
器材名稱 總數量 已借出 剩餘數量
分頁：借用紀錄
A 欄 B 欄 C 欄 D 欄
時間戳記 借用人姓名 器材名稱 借用數量

3. 開啟 Apps Script

從試算表開 Apps Script，才能直接用 SpreadsheetApp 讀寫資料。

  1. 試算表上方選單：擴充功能 → Apps Script
  2. 刪掉預設的程式碼（或全部選取覆蓋）
  3. 保留/建立檔案：
    • Code.gs
    • index.html

4. 貼上後端（Code.gs）

把後端程式貼到 Apps Script 的 Code.gs。

Code.gs（後端）
// Code.gs - 後端處理邏輯

/* * 1. 產生網頁介面 
 */
function doGet() {
  return HtmlService.createHtmlOutputFromFile('index')
    .setTitle('工具借用系統')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
    .addMetaTag('viewport', 'width=device-width, initial-scale=1');
}

/* * 2. 取得目前庫存資料 (給前端顯示用)
 * 會回傳：器材名稱、剩餘數量
 */
function getInventoryData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('庫存清單');
  
  // 取得從第2列開始的所有資料
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return []; // 如果沒資料
  
  // 讀取 A欄(名稱) 到 D欄(剩餘數量)
  var data = sheet.getRange(2, 1, lastRow - 1, 4).getValues();
  
  // 整理資料回傳給前端 [名稱, 剩餘數量]
  var result = data.map(function(row) {
    return {
      name: row[0],
      total: row[1],
      remaining: row[3]
    };
  }).filter(function(item) {
    return item.name !== ""; // 過濾掉空行
  });
  
  return result;
}

/* * 3. 處理表單提交
 */
function processForm(formObject) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var recordSheet = ss.getSheetByName('借用紀錄');
  var inventorySheet = ss.getSheetByName('庫存清單');
  
  var name = formObject.username;
  var toolName = formObject.toolSelect;
  var qty = parseInt(formObject.quantity, 10);
  
  // --- 驗證邏輯 ---
  // 1. 檢查數量是否合理
  if (isNaN(qty) || qty <= 0) {
    throw new Error("借用數量必須大於 0");
  }

  // 2. 檢查庫存是否足夠 (後端二次驗證)
  var inventoryData = inventorySheet.getRange(2, 1, inventorySheet.getLastRow()-1, 4).getValues();
  var currentStock = 0;
  var itemFound = false;
  
  for (var i = 0; i < inventoryData.length; i++) {
    if (inventoryData[i][0] == toolName) {
      currentStock = inventoryData[i][3]; // D欄是剩餘數量
      itemFound = true;
      break;
    }
  }
  
  if (!itemFound) throw new Error("找不到該器材");
  if (qty > currentStock) throw new Error("庫存不足！目前剩餘: " + currentStock);

  // --- 寫入紀錄 ---
  // 寫入：[時間, 姓名, 器材, 數量]
  recordSheet.appendRow([new Date(), name, toolName, qty]);
  
  return "借用成功！";
}
貼到 Apps Script 的 Code.gs 檔案

5. 貼上前端（index.html）

把前端程式貼到 Apps Script 的 index.html。

index.html（前端）
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Sans+TC:wght@400;700&display=swap" rel="stylesheet">
    <style>
      @keyframes snowfall {
        0% { transform: translateY(-10px) rotate(0deg); opacity: 1; }
        100% { transform: translateY(100vh) rotate(360deg); opacity: 0.8; }
      }
      
      @keyframes twinkle {
        0%, 100% { opacity: 1; transform: scale(1); }
        50% { opacity: 0.5; transform: scale(0.9); }
      }
      
      @keyframes float {
        0%, 100% { transform: translateY(0px); }
        50% { transform: translateY(-8px); }
      }

      @keyframes shake {
        0%, 100% { transform: rotate(0deg); }
        25% { transform: rotate(-5deg); }
        75% { transform: rotate(5deg); }
      }

      body { 
        font-family: 'Noto Sans TC', sans-serif; 
        background: linear-gradient(180deg, #0f2027 0%, #203a43 50%, #2c5364 100%);
        padding: 20px;
        min-height: 100vh;
        position: relative;
        overflow-x: hidden;
      }
      
      /* 雪花效果 */
      .snowflake {
        position: fixed;
        top: -10px;
        color: white;
        font-size: 1em;
        animation: snowfall linear infinite;
        z-index: 1;
        pointer-events: none;
        text-shadow: 0 0 5px rgba(255,255,255,0.8);
      }

      .container { 
        max-width: 650px; 
        margin: 0 auto; 
        background: linear-gradient(135deg, #fffaf0 0%, #fff5f5 100%);
        padding: 40px 35px;
        border-radius: 30px; 
        box-shadow: 0 20px 60px rgba(0,0,0,0.4), 0 0 0 8px rgba(255,255,255,0.1);
        position: relative;
        z-index: 2;
        border: 4px solid #d4380d;
      }
      
      /* 裝飾燈串 */
      .lights {
        position: absolute;
        top: -18px;
        left: 0;
        right: 0;
        height: 35px;
        display: flex;
        justify-content: space-around;
        z-index: 3;
      }
      
      .light {
        width: 18px;
        height: 24px;
        border-radius: 50% 50% 0 0;
        animation: twinkle 1.8s infinite;
        box-shadow: 0 0 10px currentColor;
      }
      
      .light:nth-child(5n+1) { background: #ff4d4f; animation-delay: 0s; }
      .light:nth-child(5n+2) { background: #52c41a; animation-delay: 0.36s; }
      .light:nth-child(5n+3) { background: #fadb14; animation-delay: 0.72s; }
      .light:nth-child(5n+4) { background: #1890ff; animation-delay: 1.08s; }
      .light:nth-child(5n+5) { background: #eb2f96; animation-delay: 1.44s; }

      h1 { 
        text-align: center; 
        color: #cf1322;
        font-size: 36px;
        margin-bottom: 8px;
        text-shadow: 3px 3px 6px rgba(0,0,0,0.15);
        animation: float 3s ease-in-out infinite;
        letter-spacing: 2px;
      }
      
      .subtitle {
        text-align: center;
        color: #237804;
        font-size: 17px;
        margin-bottom: 28px;
        font-weight: 500;
      }
      
      .form-group { 
        margin-bottom: 22px; 
        position: relative;
      }
      
      label { 
        display: block; 
        margin-bottom: 8px; 
        font-weight: bold; 
        color: #cf1322;
        font-size: 17px;
      }
      
      label::before {
        content: "🎁 ";
        margin-right: 6px;
        animation: shake 2s ease-in-out infinite;
        display: inline-block;
      }
      
      input, select { 
        width: 100%; 
        padding: 15px; 
        border: 3px solid #ff7875; 
        border-radius: 16px; 
        box-sizing: border-box; 
        font-size: 16px;
        background: white;
        transition: all 0.3s;
        font-family: 'Noto Sans TC', sans-serif;
      }
      
      input:focus, select:focus {
        outline: none;
        border-color: #389e0d;
        box-shadow: 0 0 20px rgba(56, 158, 13, 0.3);
        transform: scale(1.02);
        background: #fcffe6;
      }
      
      button { 
        width: 100%; 
        padding: 18px; 
        background: linear-gradient(135deg, #cf1322 0%, #ff4d4f 100%);
        color: white; 
        border: none; 
        border-radius: 20px; 
        font-size: 21px; 
        cursor: pointer; 
        transition: all 0.3s;
        font-weight: bold;
        box-shadow: 0 6px 20px rgba(207, 19, 34, 0.4);
        position: relative;
        overflow: hidden;
      }
      
      button::before {
        content: "🎅 ";
        margin-right: 10px;
        font-size: 24px;
      }
      
      button::after {
        content: " 🎄";
        margin-left: 10px;
        font-size: 24px;
      }
      
      button:hover { 
        background: linear-gradient(135deg, #237804 0%, #52c41a 100%);
        transform: translateY(-4px);
        box-shadow: 0 10px 25px rgba(35, 120, 4, 0.5);
      }
      
      button:active {
        transform: translateY(-1px);
      }
      
      button:disabled { 
        background: #d9d9d9; 
        cursor: not-allowed;
        transform: none;
        box-shadow: none;
      }
      
      .status-section { 
        margin-top: 40px; 
        border-top: 4px dashed #ff7875; 
        padding-top: 28px; 
      }
      
      .status-section h3 {
        color: #237804;
        text-align: center;
        font-size: 26px;
        margin-bottom: 12px;
        text-shadow: 2px 2px 4px rgba(0,0,0,0.1);
      }
      
      table { 
        width: 100%; 
        border-collapse: collapse; 
        margin-top: 18px;
        background: white;
        border-radius: 16px;
        overflow: hidden;
        box-shadow: 0 5px 15px rgba(0,0,0,0.12);
      }
      
      th, td { 
        padding: 15px; 
        text-align: left; 
        border-bottom: 2px solid #fff1f0; 
      }
      
      th { 
        background: linear-gradient(135deg, #237804 0%, #52c41a 100%);
        color: white;
        font-weight: bold;
        font-size: 17px;
      }
      
      tbody tr:hover {
        background: #fffbe6;
        transition: background 0.2s;
      }
      
      tbody tr:last-child td {
        border-bottom: none;
      }
      
      .stock-num { 
        font-weight: bold; 
        color: #237804;
        font-size: 19px;
      }
      
      .warning { 
        color: #cf1322; 
        font-weight: bold;
        font-size: 17px;
      }
      
      #loading { 
        text-align: center; 
        color: #cf1322; 
        display: none;
        font-size: 19px;
        margin-top: 18px;
        font-weight: 600;
      }
      
      #loading::before {
        content: "🎁 ";
        animation: shake 1s ease-in-out infinite;
        display: inline-block;
      }
      
      #loading::after {
        content: " 🎁";
        animation: shake 1s ease-in-out infinite;
        display: inline-block;
      }
      
      #message { 
        margin-top: 18px; 
        text-align: center; 
        font-weight: bold;
        font-size: 18px;
        padding: 14px;
        border-radius: 14px;
      }
      
      small {
        color: #237804;
        font-weight: bold;
        display: block;
        margin-top: 6px;
      }
      
      /* 聖誕裝飾 */
      .deco {
        position: absolute;
        font-size: 50px;
        opacity: 0.25;
        z-index: 0;
        animation: float 4s ease-in-out infinite;
        filter: drop-shadow(0 0 10px rgba(0,0,0,0.2));
      }
      
      .deco-tl { top: 15px; left: -25px; }
      .deco-tr { top: 15px; right: -25px; animation-delay: 2s; }
      .deco-bl { bottom: 15px; left: -25px; animation-delay: 1s; }
      .deco-br { bottom: 15px; right: -25px; animation-delay: 3s; }
      
      .info-text {
        font-size: 13px;
        color: #8c8c8c;
        text-align: center;
        margin-top: 10px;
        font-style: italic;
      }

      /* 星星裝飾 */
      .star {
        position: absolute;
        color: #fadb14;
        font-size: 20px;
        animation: twinkle 2s infinite;
        text-shadow: 0 0 10px #fadb14;
      }

      .star1 { top: 50px; left: 50px; animation-delay: 0s; }
      .star2 { top: 100px; right: 60px; animation-delay: 0.5s; }
      .star3 { bottom: 150px; left: 40px; animation-delay: 1s; }
      .star4 { bottom: 100px; right: 50px; animation-delay: 1.5s; }
    </style>
  </head>
  <body>
    <!-- 雪花效果 -->
    <script>
      for(let i = 0; i < 35; i++) {
        let snowflake = document.createElement('div');
        snowflake.className = 'snowflake';
        snowflake.innerHTML = '❄';
        snowflake.style.left = Math.random() * 100 + '%';
        snowflake.style.animationDuration = (Math.random() * 3 + 2) + 's';
        snowflake.style.animationDelay = Math.random() * 5 + 's';
        snowflake.style.fontSize = (Math.random() * 12 + 10) + 'px';
        document.body.appendChild(snowflake);
      }
    </script>

    <div class="container">
      <!-- 裝飾燈串 -->
      <div class="lights">
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
        <div class="light"></div>
      </div>
      
      <!-- 聖誕裝飾 -->
      <div class="deco deco-tl">🎄</div>
      <div class="deco deco-tr">⛄</div>
      <div class="deco deco-bl">🎅</div>
      <div class="deco deco-br">🦌</div>
      
      <!-- 星星裝飾 -->
      <div class="star star1">⭐</div>
      <div class="star star2">✨</div>
      <div class="star star3">⭐</div>
      <div class="star star4">✨</div>
      
      <h1>🎅 工具借用表單 🎄</h1>
      <div class="subtitle">🎉 聖誕快樂！祝您一切順利 ✨</div>
      
      <form id="borrowForm" onsubmit="handleFormSubmit(this)">
        <div class="form-group">
          <label for="username">借用人姓名</label>
          <input type="text" id="username" name="username" placeholder="請輸入您的姓名 ☃️" required>
        </div>

        <div class="form-group">
          <label for="toolSelect">選擇器材</label>
          <select id="toolSelect" name="toolSelect" required onchange="checkMaxQty()">
            <option value="" disabled selected>載入中... 🎁</option>
          </select>
        </div>

        <div class="form-group">
          <label for="quantity">借用數量</label>
          <input type="number" id="quantity" name="quantity" min="1" value="1" required>
          <small id="maxQtyHint"></small>
        </div>

        <button type="submit" id="submitBtn">提交借用申請</button>
      </form>
      
      <div id="loading">處理中，請稍候...</div>
      <div id="message"></div>

      <div class="status-section">
        <h3>📊 目前庫存狀況 🎁</h3>
        <p class="info-text">(若在試算表更改數量，重新整理此網頁即可更新)</p>
        <table id="inventoryTable">
          <thead>
            <tr>
              <th>🎁 器材名稱</th>
              <th>📦 剩餘數量</th>
            </tr>
          </thead>
          <tbody>
          </tbody>
        </table>
      </div>
    </div>

    <script>
      var globalInventory = [];

      window.onload = function() {
        google.script.run.withSuccessHandler(showInventory).getInventoryData();
      };

      function showInventory(data) {
        globalInventory = data;
        var select = document.getElementById("toolSelect");
        var tbody = document.querySelector("#inventoryTable tbody");
        
        select.innerHTML = '<option value="" disabled selected>-- 請選擇器材 🎄 --</option>';
        tbody.innerHTML = '';

        data.forEach(function(item) {
          if (item.remaining > 0) {
            var option = document.createElement("option");
            option.value = item.name;
            option.text = item.name + " (剩餘: " + item.remaining + ")";
            select.appendChild(option);
          }

          var tr = document.createElement("tr");
          var remainingClass = item.remaining <= 0 ? 'warning' : 'stock-num';
          var remainingText = item.remaining <= 0 ? '🎅 已借光' : item.remaining;
          
          tr.innerHTML = '<td>' + item.name + '</td><td class="' + remainingClass + '">' + remainingText + '</td>';
          tbody.appendChild(tr);
        });
      }

      function checkMaxQty() {
        var selectedName = document.getElementById("toolSelect").value;
        var selectedItem = globalInventory.find(i => i.name === selectedName);
        var qtyInput = document.getElementById("quantity");
        var hint = document.getElementById("maxQtyHint");
        
        if (selectedItem) {
          qtyInput.max = selectedItem.remaining;
          hint.innerText = "🎁 最多可借: " + selectedItem.remaining + " 個";
        }
      }

      function handleFormSubmit(formObject) {
        event.preventDefault();
        
        var btn = document.getElementById("submitBtn");
        var loading = document.getElementById("loading");
        var msg = document.getElementById("message");
        
        btn.disabled = true;
        loading.style.display = "block";
        msg.innerText = "";

        google.script.run
          .withSuccessHandler(function(response) {
            msg.style.color = "#237804";
            msg.style.background = "#f6ffed";
            msg.style.border = "2px solid #b7eb8f";
            msg.innerText = "🎉 " + response + " 🎉";
            formObject.reset();
            
            google.script.run.withSuccessHandler(showInventory).getInventoryData();
            
            btn.disabled = false;
            loading.style.display = "none";
          })
          .withFailureHandler(function(error) {
            msg.style.color = "#cf1322";
            msg.style.background = "#fff1f0";
            msg.style.border = "2px solid #ffa39e";
            msg.innerText = "❌ 錯誤: " + error.message;
            btn.disabled = false;
            loading.style.display = "none";
          })
          .processForm(formObject);
      }
    </script>
  </body>
</html>
貼到 Apps Script 的 index.html 檔案

6. 部署

部署成 Web App 後，就會拿到可分享網址。

  1. Apps Script 右上：部署 → 新部署
  2. 類型選：Web app
  3. 執行身分：你
  4. 存取權限：任何人
  5. 完成後複製網址

💬 評論區

老師們的交流與提問

載入評論中...