雖然運用 Google 表單就很方便了，
但想說問問 Gemini 是否可以利用 Google 試算表 Apps Script 來製作線上投票系統！
結果就生成下面的成果啦！
Apps Script 程式碼：
// Code.gs - 強效驗證修正版
var ss = SpreadsheetApp.getActiveSpreadsheet();
var configSheet = ss.getSheetByName("Setting");
var voteSheet = ss.getSheetByName("Votes");
function doGet() {
return HtmlService.createTemplateFromFile('Index')
.evaluate()
.setTitle('線上投票系統')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
.addMetaTag('viewport', 'width=device-width, initial-scale=1');
}
// 修正 1: 移除 'E'，避免 Excel/Sheet 將序號誤判為科學記號 (例如 1E20)
function generateShortID() {
// 移除了 I, 1, O, 0, E (避免科學記號)
var chars = 'ABCDFGHJKLMNPQRSTUVWXYZ23456789';
var result = '';
for (var i = 0; i < 4; i++) {
result += chars.charAt(Math.floor(Math.random() * chars.length));
}
return result;
}
function getInitialData() {
var data = configSheet.getDataRange().getValues();
var questions = [];
var pageTitle = (data.length > 0 && data[0][0]) ? data[0][0] : "線上投票活動";
if (data.length > 2) {
var numColumns = data[0].length;
for (var col = 0; col < numColumns; col++) {
var title = (data.length > 2) ? data[2][col] : "";
var typeRaw = (data.length > 3) ? data[3][col] : "";
if (!title) continue;
var type = 'radio';
if (typeRaw && typeRaw.toString().trim() === '複選') type = 'checkbox';
if (typeRaw && typeRaw.toString().trim() === '簡答') type = 'text';
var options = [];
if (type !== 'text') {
for (var row = 4; row < data.length; row++) {
if (data[row][col]) options.push(data[row][col]);
}
}
questions.push({ id: col, title: title, type: type, options: options });
}
}
return { pageTitle: pageTitle, questions: questions, results: getResults(questions) };
}
// 修正 2: 嚴格的比對邏輯
function checkUserStatus(nickname, accessCode) {
// 強制轉字串 + 去除頭尾空白
nickname = String(nickname).trim();
accessCode = String(accessCode).trim().toUpperCase();
var userAnswers = {};
var status = "NEW";
var savedCode = "";
var lastRow = voteSheet.getLastRow();
if (lastRow >= 2) {
// 讀取範圍：B:序號, C:暱稱, D~:答案
var data = voteSheet.getRange(2, 2, lastRow - 1, voteSheet.getLastColumn() - 1).getValues();
// 倒序搜尋 (從最新的一筆開始找)，防止有舊的重複資料干擾
for (var i = data.length - 1; i >= 0; i--) {
// 修正 3: 強制將資料庫取出的值轉為 String 再 trim，防止數字型態干擾
var dbCode = String(data[i][0]).trim().toUpperCase();
var dbName = String(data[i][1]).trim();
if (dbName == nickname) {
// 找到暱稱了
if (accessCode === "") {
status = "EXIST_NO_CODE";
} else {
if (dbCode == accessCode) {
status = "MATCH";
savedCode = dbCode;
// 載入答案
for (var j = 2; j < data[i].length; j++) {
userAnswers[j-2] = data[i][j];
}
} else {
status = "ERROR";
}
}
break; // 找到最新的一筆就停止
}
}
}
return { status: status, savedCode: savedCode, userAnswers: userAnswers };
}
function submitVote(nickname, accessCode, answers) {
if (!nickname) return { success: false, message: "沒有輸入暱稱" };
nickname = String(nickname).trim();
accessCode = String(accessCode).trim().toUpperCase();
var lock = LockService.getScriptLock();
try {
lock.waitLock(10000);
var lastRow = voteSheet.getLastRow();
var rowIndex = -1;
var finalCode = accessCode;
// 找人
if (lastRow >= 2) {
var authData = voteSheet.getRange(2, 2, lastRow - 1, 2).getValues();
// 同樣倒序搜尋
for (var i = authData.length - 1; i >= 0; i--) {
var dbCode = String(authData[i][0]).trim().toUpperCase();
var dbName = String(authData[i][1]).trim();
if (dbName == nickname) {
if (accessCode && dbCode == accessCode) {
rowIndex = i + 2; // 因為陣列是0-base，但Excel是從第2列開始，所以是 i + 2
finalCode = dbCode;
} else if (!accessCode) {
return { success: false, message: "暱稱已被使用，請輸入序號。" };
} else {
return { success: false, message: "序號錯誤。" };
}
break;
}
}
}
var configCols = configSheet.getLastColumn();
if (rowIndex === -1) {
// 新增
if (!finalCode) finalCode = generateShortID();
var newRow = [new Date(), finalCode, nickname]; // 強制寫入字串
for (var k = 0; k < configCols; k++) {
var val = answers[k];
if(Array.isArray(val)) val = val.join(",");
newRow.push(val || "");
}
voteSheet.appendRow(newRow);
} else {
// 更新
voteSheet.getRange(rowIndex, 1).setValue(new Date());
// 序號和暱稱不變，只更新答案
for (var k = 0; k < configCols; k++) {
var val = answers[k];
if(Array.isArray(val)) val = val.join(",");
voteSheet.getRange(rowIndex, 4 + k).setValue(val || "");
}
}
var qData = getInitialData();
return { success: true, userCode: finalCode, results: qData.results };
} catch (e) {
return { success: false, message: "錯誤: " + e.toString() };
} finally {
lock.releaseLock();
}
}
function getResults(questions) {
var lastRow = voteSheet.getLastRow();
var allCounts = {};
questions.forEach(function(q){ allCounts[q.id] = {}; });
if (lastRow < 2) return allCounts;
var voteData = voteSheet.getRange(2, 4, lastRow - 1, questions.length).getValues();
for (var i = 0; i < voteData.length; i++) {
var row = voteData[i];
for (var col = 0; col < row.length; col++) {
var rawAnswer = row[col];
if (rawAnswer && allCounts[col]) {
var qType = questions[col] ? questions[col].type : 'radio';
if (qType === 'text') {
var ans = String(rawAnswer).trim();
if(ans) {
if (!allCounts[col][ans]) allCounts[col][ans] = 0;
allCounts[col][ans]++;
}
} else {
var answers = String(rawAnswer).split(",");
answers.forEach(function(ans) {
ans = ans.trim();
if(ans) {
if (!allCounts[col][ans]) allCounts[col][ans] = 0;
allCounts[col][ans]++;
}
});
}
}
}
}
return allCounts;
}
Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<style>
body { font-family: "Microsoft JhengHei", "Heiti TC", sans-serif; background-color: #FFF3E0; }
.container { max-width: 600px; margin: 30px auto; background: #fff; padding: 25px; border-radius: 20px; box-shadow: 0 8px 16px rgba(0,0,0,0.1); border: 3px solid #FFB74D; position: relative; }
h2, h3 { color: #E65100; font-weight: bold; }
/* 右上角連結 */
.top-link {
position: absolute; top: 20px; right: 20px;
text-decoration: none; color: #2196F3; font-weight: bold; font-size: 0.9em;
border: 1px solid #2196F3; padding: 5px 12px; border-radius: 20px;
transition: 0.3s; cursor: pointer; background: #E3F2FD; z-index: 10;
}
.top-link:hover { background-color: #2196F3; color: white; }
.big-btn { font-size: 20px; padding: 12px; border-radius: 15px; margin-top: 15px; font-weight: bold; }
.input-label { color: #E65100; font-weight: bold; font-size: 1.1em; margin-top: 15px; display:block;}
.input-desc { color: #888; font-size: 0.85em; margin-bottom: 5px; display:block;}
.login-input { font-size: 20px; text-align: center; padding: 10px; border: 2px solid #FF9800; border-radius: 10px; width: 100%; background-color: #FFFDE7; outline: none; }
/* 序號顯示區塊 */
.code-display { font-size: 32px; font-weight: bold; letter-spacing: 5px; color: #E65100; background: #FFE0B2; padding: 10px; border-radius: 10px; margin: 10px 0; border: 2px dashed #E65100; }
.question-block { margin-bottom: 30px; padding: 15px; background: #fff; border-left: 5px solid #FF9800; }
.question-title { font-size: 1.2em; font-weight: bold; color: #555; margin-bottom: 5px; }
.question-type { font-size: 0.8em; color: #999; margin-bottom: 10px; display: inline-block; background: #eee; padding: 2px 6px; border-radius: 4px;}
.option-item { padding: 10px 15px; border: 2px solid #EEE; border-radius: 10px; margin-bottom: 8px; cursor: pointer; background: #FAFAFA; display: flex; align-items: center;}
.option-item:hover { background-color: #FFF8E1; border-color: #FFB74D; }
input[type=radio], input[type=checkbox] { width: 22px; height: 22px; margin-right: 12px; accent-color: #FF9800; }
textarea.text-answer { width: 100%; padding: 10px; border: 2px solid #ddd; border-radius: 10px; background-color: #fafafa; font-size: 16px; resize: vertical; }
/* 看板 */
.dashboard-item { margin-bottom: 25px; padding-bottom: 15px; border-bottom: 1px solid #eee; }
.dashboard-title { font-size: 1.1em; color: #333; margin-bottom: 8px; font-weight: bold; }
.bar-wrapper { margin-bottom: 6px; font-size: 0.9em; }
.bar-bg { background-color: #f1f1f1; border-radius: 5px; height: 20px; overflow: hidden; }
.bar-fill { height: 100%; background: linear-gradient(90deg, #4CAF50, #8BC34A); width: 0%; transition: width 0.8s; text-align: right; color: white; padding-right: 5px; line-height: 20px; font-size: 11px;}
.text-result-list { list-style: none; padding: 0; margin: 0; }
.text-result-item { background: #f9f9f9; padding: 8px 12px; margin-bottom: 5px; border-radius: 5px; font-size: 14px; display: flex; justify-content: space-between; align-items: center;}
.text-count { background: #eee; color: #555; font-size: 12px; padding: 2px 8px; border-radius: 10px; font-weight: bold;}
.hidden { display: none; }
/* 彈窗 */
#success-modal { position: fixed; top: 0; left: 0; width: 100%; height: 100%; background: rgba(0,0,0,0.7); display: none; align-items: center; justify-content: center; z-index: 1000; }
.modal-content { background: white; width: 90%; max-width: 400px; padding: 25px; border-radius: 20px; text-align: center; border: 4px solid #4CAF50; animation: popIn 0.3s; }
@keyframes popIn { from {transform: scale(0.8); opacity: 0;} to {transform: scale(1); opacity: 1;} }
</style>
</head>
<body>
<div class="container">
<a class="top-link" onclick="loadDashboard()">📊 看投票結果</a>
<header class="w3-center">
<h2 id="page-title">載入中...</h2>
</header>
<hr>
<div id="login-section" class="w3-center">
<label class="input-label">你的暱稱</label>
<span class="input-desc">第一次只需填這個</span>
<input type="text" id="input-name" class="login-input" placeholder="例如：01小明" onkeypress="handleEnter(event)" autocomplete="off">
<div id="code-wrapper" class="hidden" style="margin-top: 15px; padding: 10px; background: #FFF3E0; border-radius: 10px;">
<div style="display:flex; justify-content:space-between; align-items:center;">
<label class="input-label" style="margin-top:0;">系統序號</label>
<button class="w3-button w3-tiny w3-red w3-round" onclick="resetLogin()">❌ 換一個新暱稱</button>
</div>
<span class="input-desc w3-text-red"><b>暱稱已存在！如果是你，請輸入序號：</b></span>
<input type="text" id="input-code" class="login-input" placeholder="例如: A1B2" onkeypress="handleEnter(event)" autocomplete="off" style="background-color: #fff;">
</div>
<button class="w3-button w3-orange w3-text-white w3-hover-deep-orange w3-block big-btn" onclick="checkUser()">開始 / 驗證</button>
</div>
<div id="vote-section" class="hidden">
<div class="w3-center w3-margin-bottom">
<span class="w3-tag w3-round w3-orange w3-text-white" style="font-size:16px">我是：<span id="display-name"></span></span>
</div>
<p id="status-text" class="w3-center w3-text-blue">請回答以下問題：</p>
<div id="questions-container"></div>
<button id="btn-submit" class="w3-button w3-green w3-hover-teal w3-block big-btn" onclick="submitVote()">送出答案</button>
</div>
<div id="dashboard-section" class="hidden">
<h3 class="w3-center w3-text-green">📊 即時統計結果</h3>
<div class="w3-center w3-margin-bottom">
<button class="w3-button w3-small w3-white w3-border w3-round" onclick="refreshDashboard()">🔄 刷新數據</button>
</div>
<div id="dashboard-container"></div>
<div class="w3-center w3-margin-top">
<button class="w3-button w3-light-grey w3-block" onclick="backToHome()">⬅️ 回到投票首頁</button>
</div>
</div>
<div id="loader" class="hidden w3-center w3-margin-top"><h3 class="w3-text-grey"><i class="w3-spin">⟳</i> 讀取中...</h3></div>
</div>
<div id="success-modal">
<div class="modal-content">
<h2 class="w3-text-green" style="margin-top:0;">✅ 投票成功！</h2>
<p>你的專屬序號是：</p>
<div id="modal-code" class="code-display">----</div>
<p class="w3-small w3-text-red">系統已自動幫您記住！<br>下次打開網頁欄位會自動填入。</p>
<button class="w3-button w3-green w3-round-large w3-block big-btn" onclick="closeModal()">好，繼續看結果</button>
</div>
</div>
<script>
var currentUserName = "";
var currentCode = "";
var allQuestions = [];
window.onload = function() {
// 1. 載入標題
google.script.run.withSuccessHandler(function(data){
document.getElementById('page-title').innerText = data.pageTitle;
document.title = data.pageTitle;
allQuestions = data.questions;
// 2. 恢復記憶功能：自動填入但不登入
var storedName = localStorage.getItem("vote_name");
var storedCode = localStorage.getItem("vote_code");
if (storedName) {
document.getElementById('input-name').value = storedName;
}
if (storedCode) {
document.getElementById('input-code').value = storedCode;
}
}).getInitialData();
};
function handleEnter(e){ if(e.keyCode === 13) checkUser(); }
function loadDashboard() {
toggleLoader(true);
google.script.run.withSuccessHandler(function(data){
toggleLoader(false);
document.getElementById('page-title').innerText = data.pageTitle;
document.getElementById('login-section').classList.add('hidden');
document.getElementById('vote-section').classList.add('hidden');
document.getElementById('dashboard-section').classList.remove('hidden');
renderDashboard(data.questions, data.results);
}).getInitialData();
}
function refreshDashboard() { loadDashboard(); }
function backToHome() {
document.getElementById('dashboard-section').classList.add('hidden');
document.getElementById('login-section').classList.remove('hidden');
window.scrollTo(0,0);
}
function renderDashboard(questions, allResults) {
var container = document.getElementById('dashboard-container');
var html = '';
questions.forEach(function(q) {
var results = allResults[q.id] || {};
var total = 0;
for (var key in results) total += results[key];
html += '<div class="dashboard-item"><div class="dashboard-title">' + (q.id + 1) + '. ' + q.title + '</div>';
if (total === 0) {
html += '<p class="w3-small w3-text-grey" style="padding-left:10px;">(尚無回答)</p>';
} else {
if (q.type === 'text') {
html += '<ul class="text-result-list">';
for (var answerText in results) {
var count = results[answerText];
html += '<li class="text-result-item"><span>' + answerText + '</span>';
if(count > 1) html += '<span class="text-count">' + count + '人</span>';
html += '</li>';
}
html += '</ul>';
} else {
var sortedKeys = Object.keys(results).sort(function(a,b){return results[b]-results[a]});
sortedKeys.forEach(function(key) {
var count = results[key];
var percent = Math.round((count / total) * 100);
html += '<div class="bar-wrapper"><div style="display:flex; justify-content:space-between; font-size:14px;"><span>' + key + '</span><span>' + count + ' 票 (' + percent + '%)</span></div><div class="bar-bg"><div class="bar-fill" style="width:' + percent + '%"></div></div></div>';
});
}
}
html += '</div>';
});
container.innerHTML = html;
}
// 重置登入
function resetLogin() {
document.getElementById('input-name').value = "";
document.getElementById('input-code').value = "";
document.getElementById('code-wrapper').classList.add('hidden');
document.getElementById('input-name').focus();
}
// 檢查用戶
function checkUser() {
var name = document.getElementById('input-name').value;
var code = document.getElementById('input-code').value;
name = name ? name.trim() : "";
code = code ? code.trim() : "";
if (!name) { alert("請輸入暱稱！"); return; }
currentUserName = name;
currentCode = code;
toggleLoader(true);
google.script.run.withSuccessHandler(function(data){
toggleLoader(false);
// 狀況一：需要序號
if (data.status === "EXIST_NO_CODE") {
document.getElementById('code-wrapper').classList.remove('hidden');
document.getElementById('input-code').focus();
return;
}
// 狀況二：序號錯誤
if (data.status === "ERROR") {
alert("序號錯誤！驗證失敗。");
return;
}
// 狀況三：登入成功 (NEW 或 MATCH)
document.getElementById('login-section').classList.add('hidden');
document.getElementById('vote-section').classList.remove('hidden');
document.getElementById('display-name').innerText = currentUserName;
var statusText = document.getElementById('status-text');
var submitBtn = document.getElementById('btn-submit');
if (data.status === "MATCH") {
// === 修改模式 ===
statusText.innerHTML = "驗證成功！正在修改舊答案：";
currentCode = data.savedCode;
// 修改按鈕文字與顏色
submitBtn.innerText = "修改答案";
submitBtn.classList.remove('w3-green');
submitBtn.classList.add('w3-blue');
} else {
// === 新增模式 ===
statusText.innerHTML = "請回答以下問題：";
currentCode = "";
// 修改按鈕文字與顏色
submitBtn.innerText = "送出答案";
submitBtn.classList.remove('w3-blue');
submitBtn.classList.add('w3-green');
}
renderVoteForm(allQuestions, data.userAnswers);
}).checkUserStatus(currentUserName, currentCode);
}
function renderVoteForm(questions, userAnswers) {
var container = document.getElementById('questions-container');
var html = '';
questions.forEach(function(q) {
var typeLabel = '單選';
if(q.type === 'checkbox') typeLabel = '複選';
if(q.type === 'text') typeLabel = '簡答';
html += '<div class="question-block"><div class="question-title">' + (q.id + 1) + '. ' + q.title + '</div><div class="question-type">' + typeLabel + '</div><div id="opts-area-' + q.id + '">';
if (q.type === 'text') {
var val = (userAnswers && userAnswers[q.id]) ? userAnswers[q.id] : '';
html += '<textarea class="text-answer" rows="3" name="q-' + q.id + '" placeholder="請輸入...">' + val + '</textarea>';
} else {
q.options.forEach(function(opt, idx) {
var isChecked = false;
if(userAnswers && userAnswers[q.id]) {
var savedAns = userAnswers[q.id].toString().split(',');
if(savedAns.includes(opt.toString())) isChecked = true;
}
var checkedAttr = isChecked ? 'checked' : '';
var bgClass = isChecked ? 'w3-pale-yellow' : '';
html += '<div class="option-item ' + bgClass + '" onclick="selectInput('+q.id+', '+idx+')"><input type="' + q.type + '" name="q-' + q.id + '" value="' + opt + '" id="q'+q.id+'-opt'+idx+'" ' + checkedAttr + '><label for="q'+q.id+'-opt'+idx+'" style="cursor:pointer; flex-grow:1; margin-left:10px;">' + opt + '</label></div>';
});
}
html += '</div></div>';
});
container.innerHTML = html;
}
function selectInput(qId, optIdx) {
var input = document.getElementById('q'+qId+'-opt'+optIdx);
if(!input) return;
if (input.type === 'radio') input.checked = true;
else input.checked = !input.checked;
}
function submitVote() {
var answers = {};
var allAnswered = true;
allQuestions.forEach(function(q) {
if (q.type === 'text') {
var inputs = document.getElementsByName('q-' + q.id);
if(inputs.length > 0) {
var val = inputs[0].value.trim();
if(val) answers[q.id] = val; else allAnswered = false;
}
} else {
var inputs = document.getElementsByName('q-' + q.id);
if (q.type === 'radio') {
var val = null;
for(var i=0; i<inputs.length; i++){ if(inputs[i].checked) { val = inputs[i].value; break; } }
if(val) answers[q.id] = val; else allAnswered = false;
} else {
var vals = [];
for(var i=0; i<inputs.length; i++){ if(inputs[i].checked) vals.push(inputs[i].value); }
if(vals.length > 0) answers[q.id] = vals; else allAnswered = false;
}
}
});
if (!allAnswered && !confirm("有些題目還沒填，確定要送出嗎？")) return;
toggleLoader(true);
google.script.run.withSuccessHandler(function(response) {
toggleLoader(false);
if (response.success) {
// 儲存到 localStorage
localStorage.setItem("vote_name", currentUserName);
localStorage.setItem("vote_code", response.userCode);
document.getElementById('modal-code').innerText = response.userCode;
document.getElementById('success-modal').style.display = 'flex';
} else {
alert(response.message);
}
}).submitVote(currentUserName, currentCode, answers);
}
function closeModal() {
document.getElementById('success-modal').style.display = 'none';
loadDashboard();
}
function logout() {
localStorage.removeItem("vote_name");
localStorage.removeItem("vote_code");
resetLogin(); // 重置登入畫面
document.getElementById('vote-section').classList.add('hidden');
document.getElementById('login-section').classList.remove('hidden');
currentUserName = "";
currentCode = "";
window.scrollTo(0,0);
alert("已登出！");
}
function toggleLoader(show) {
var loader = document.getElementById('loader');
var opacity = show ? "0.5" : "1";
if(show) loader.classList.remove('hidden'); else loader.classList.add('hidden');
document.getElementById('vote-section').style.opacity = opacity;
document.getElementById('login-section').style.opacity = opacity;
document.getElementById('dashboard-section').style.opacity = opacity;
}
</script>
</body>
</html>
快來試試吧~~
沒有留言:
張貼留言
歡迎大家一起留言討論！