雖然運用 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');
}
// 取得初始資料
function getInitialData() {
var data = configSheet.getDataRange().getValues();
var questions = [];
// 1. 抓取 A1 (第1列第1欄) 作為網頁大標題
// 如果 A1 是空的，顯示預設文字
var pageTitle = (data.length > 0 && data[0][0]) ? data[0][0] : "線上投票活動";
// 2. 解析題目結構
// 根據新的版面：
// Row 1 (Index 0): 大標題
// Row 2 (Index 1): 空白分隔
// Row 3 (Index 2): 題目名稱
// Row 4 (Index 3): 題型 (單選/複選)
// Row 5 (Index 4): 選項開始
if (data.length > 2) { // 至少要有題目列才開始解析
var numColumns = data[0].length; // 欄位數量
for (var col = 0; col < numColumns; col++) {
// 讀取第 3 列 (Index 2) 當作題目
var title = (data.length > 2) ? data[2][col] : "";
// 讀取第 4 列 (Index 3) 當作題型
var typeRaw = (data.length > 3) ? data[3][col] : "";
if (!title) continue; // 如果該欄沒有題目，就跳過
var options = [];
// 從第 5 列 (Index 4) 開始抓選項
for (var row = 4; row < data.length; row++) {
if (data[row][col]) {
options.push(data[row][col]);
}
}
questions.push({
id: col,
title: title,
type: (typeRaw && typeRaw.toString().trim() === '複選') ? 'checkbox' : 'radio',
options: options
});
}
}
return {
pageTitle: pageTitle,
questions: questions,
results: getResults(questions)
};
}
// 檢查使用者狀態
function checkUserStatus(nickname) {
nickname = nickname.toString().trim();
var userAnswers = {};
var lastRow = voteSheet.getLastRow();
if (lastRow >= 2) {
var data = voteSheet.getRange(2, 2, lastRow - 1, voteSheet.getLastColumn() - 1).getValues();
for (var i = 0; i < data.length; i++) {
if (data[i][0].toString() == nickname) {
for (var j = 1; j < data[i].length; j++) {
var val = data[i][j];
userAnswers[j-1] = val;
}
break;
}
}
}
var hasVoted = Object.keys(userAnswers).length > 0;
return {
hasVoted: hasVoted,
userAnswers: userAnswers
};
}
// 接收投票
function submitVote(nickname, answers) {
if (!nickname) return { success: false, message: "沒有輸入暱稱" };
nickname = nickname.toString().trim();
var lock = LockService.getScriptLock();
try {
lock.waitLock(10000);
var lastRow = voteSheet.getLastRow();
var rowIndex = -1;
if (lastRow >= 2) {
var allIds = voteSheet.getRange(2, 2, lastRow - 1, 1).getValues().flat();
for (var i = 0; i < allIds.length; i++) {
if (allIds[i].toString() == nickname) {
rowIndex = i + 2;
break;
}
}
}
var configCols = configSheet.getLastColumn();
if (rowIndex === -1) {
// 新增
var newRow = [new Date(), 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, 3 + k).setValue(val || "");
}
}
var qData = getInitialData();
return { success: true, 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, 3, 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 answers = rawAnswer.toString().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;
}
.top-link:hover { background-color: #2196F3; color: white; }
/* 按鈕與輸入框 */
.big-btn { font-size: 20px; padding: 12px; border-radius: 15px; margin-top: 15px; font-weight: bold; }
.login-input { font-size: 22px; text-align: center; padding: 12px; border: 2px solid #FF9800; border-radius: 10px; width: 100%; background-color: #FFFDE7; outline: none; }
.login-input:focus { border-color: #E65100; background-color: #fff; }
/* 題目區塊 */
.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] { width: 22px; height: 22px; margin-right: 12px; accent-color: #FF9800; }
input[type=checkbox] { width: 22px; height: 22px; margin-right: 12px; accent-color: #2196F3; }
/* 看板模式 (Dashboard) 樣式 */
.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;}
.hidden { display: none; }
#result-controls { margin-top: 30px; padding-top: 20px; border-top: 2px dashed #FFCC80; }
</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">
<p class="w3-large w3-text-orange"><b>請問你的暱稱是？</b></p>
<input type="text" id="input-name" class="login-input" placeholder="例如：小明" onkeypress="handleEnter(event)" autocomplete="off">
<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 id="result-controls" class="hidden w3-center">
<h3 class="w3-text-orange">🎉 投票完成！</h3>
<p class="w3-text-grey">謝謝你的參與。</p>
<button class="w3-button w3-light-grey w3-border w3-hover-white w3-block big-btn" onclick="logout()">回投票首頁 ➜</button>
</div>
</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>
<script>
var currentUserName = "";
var allQuestions = [];
// 初始化：載入頁面標題與題目結構
window.onload = function() {
google.script.run.withSuccessHandler(function(data){
// 設定 A1 標題
document.getElementById('page-title').innerText = data.pageTitle;
document.title = data.pageTitle; // 瀏覽器分頁標題
allQuestions = data.questions;
}).getInitialData();
};
function handleEnter(e){
if(e.keyCode === 13) checkUser();
}
// === 看板模式邏輯 (Dashboard) ===
// 載入看板
function loadDashboard() {
toggleLoader(true);
google.script.run.withSuccessHandler(function(data){
toggleLoader(false);
// 確保用到最新的 A1 標題與題目
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">';
html += '<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 {
// 排序：票數多者在上面
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">';
html += '<div style="display:flex; justify-content:space-between; font-size:14px;">';
html += '<span>' + key + '</span>';
html += '<span>' + count + ' 票 (' + percent + '%)</span>';
html += '</div>';
html += '<div class="bar-bg"><div class="bar-fill" style="width:' + percent + '%"></div></div>';
html += '</div>';
});
}
html += '</div>';
});
container.innerHTML = html;
}
// === 投票邏輯 ===
function checkUser() {
var input = document.getElementById('input-name').value;
input = input ? input.trim() : "";
if (!input) { alert("請輸入暱稱！"); return; }
currentUserName = input;
toggleLoader(true);
google.script.run.withSuccessHandler(function(status){
toggleLoader(false);
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');
if(status.hasVoted){
statusText.innerHTML = "你已經填過囉！這是你之前的選擇：";
} else {
statusText.innerHTML = "請回答以下問題(點選項即可，不要點框框)：";
}
renderVoteForm(allQuestions, status.userAnswers);
// 重置按鈕狀態
document.getElementById('btn-submit').style.display = 'block';
document.getElementById('result-controls').classList.add('hidden');
}).checkUserStatus(currentUserName);
}
// 渲染投票表單 (含 Radio/Checkbox)
function renderVoteForm(questions, userAnswers) {
var container = document.getElementById('questions-container');
var html = '';
questions.forEach(function(q) {
var typeLabel = (q.type === 'checkbox') ? '複選 (可選多項)' : '單選';
html += '<div class="question-block">';
html += '<div class="question-title">' + (q.id + 1) + '. ' + q.title + '</div>';
html += '<div class="question-type">' + typeLabel + '</div>';
html += '<div id="opts-area-' + q.id + '">';
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+')">';
html += '<input type="' + q.type + '" name="q-' + q.id + '" value="' + opt + '" id="q'+q.id+'-opt'+idx+'" ' + checkedAttr + '>';
html += '<label for="q'+q.id+'-opt'+idx+'" style="cursor:pointer; flex-grow:1; margin-left:10px;">' + opt + '</label>';
html += '</div>';
});
html += '</div>';
html += '</div>';
});
container.innerHTML = html;
}
// 點擊選項 div 觸發 input
function selectInput(qId, optIdx) {
var input = document.getElementById('q'+qId+'-opt'+optIdx);
if (input.type === 'radio') {
input.checked = true;
} else {
input.checked = !input.checked;
}
}
function submitVote() {
var answers = {};
var allAnswered = true;
allQuestions.forEach(function(q) {
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) {
if(!confirm("有些題目還沒選(或複選題未勾選)，確定要送出嗎？")) return;
}
toggleLoader(true);
google.script.run.withSuccessHandler(function(response) {
toggleLoader(false);
if (response.success) {
document.getElementById('btn-submit').style.display = 'none';
document.getElementById('result-controls').classList.remove('hidden');
window.scrollTo({ top: 0, behavior: 'smooth' });
alert("投票成功！");
} else {
alert(response.message);
}
}).submitVote(currentUserName, answers);
}
function logout() {
document.getElementById('input-name').value = "";
document.getElementById('questions-container').innerHTML = "";
document.getElementById('vote-section').classList.add('hidden');
document.getElementById('login-section').classList.remove('hidden');
currentUserName = "";
window.scrollTo(0,0);
}
function toggleLoader(show) {
var loader = document.getElementById('loader');
if(show) {
loader.classList.remove('hidden');
document.getElementById('vote-section').style.opacity = "0.5";
document.getElementById('login-section').style.opacity = "0.5";
document.getElementById('dashboard-section').style.opacity = "0.5";
} else {
loader.classList.add('hidden');
document.getElementById('vote-section').style.opacity = "1";
document.getElementById('login-section').style.opacity = "1";
document.getElementById('dashboard-section').style.opacity = "1";
}
}
</script>
</body>
</html>
快來試試吧~~
沒有留言:
張貼留言
歡迎大家一起留言討論！