9. GAS連接多個Web App檔
code.gs
function doGet(e){
// if ... else ... statement 來確認是否有參數來決定開啟哪一個網頁
//如果沒有參數
if (!e.parameter.page){
var file=HtmlService.createTemplateFromFile("index");
var evaluate = file.evaluate();
var html=evaluate.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
return html;
}
//如果有參數
else if (e.parameter.page){
var file=HtmlService.createTemplateFromFile(e.parameter.page);
var evaluate = file.evaluate();
var html=evaluate.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
return html;
}
}
// 取得URL
function getUrl(){
var url=ScriptApp.getService().getUrl();
return url;
}
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<h2>Welcome to My Website! </h2>
<p>點選下方連結來跳到特定網頁 </p>
<!-- create a list for links-->
<!-- import get url function from GAS -->
<?var url=getUrl();?>
<ol>
<li>
<a href="<?= url ?>">首頁</a>
</li>
<li>
<a href="<?= url ?>?page=profile">個人檔案</a>
</li>
<li>
<a href="<?= url ?>?page=contact">聯絡我 </a>
</li>
</ol>
</body>
</html>
profile.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<!-- create a list for links-->
<!-- import get url function from GAS -->
<?var url=getUrl();?>
<ol>
<li>
<a href="<?= url ?>">回到首頁</a>
</li>
<li>
<a href="<?= url ?>?page=contact">聯絡我 </a>
</li>
</ol>
<h4>個人檔案 <h4>
</body>
</html>
contact.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<!-- create a list for links-->
<!-- import get url function from GAS -->
<?var url=getUrl();?>
<ol>
<li>
<a href="<?= url ?>?page=profile">個人檔案 </a>
</li>
<li>
<a href="<?= url ?>">回到首頁 </a>
</li>
</ol>
<h4>聯絡資訊 <h4>
</body>
</html>
11. 表單上傳系統
11.3.3 Code.js程式輸入
// 連接到 Web App
function doGet() {
var file = HtmlService.createTemplateFromFile("form"); // 從名為 "form" 的 HTML 檔案建立模板
var evaluate = file.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1'); // 添加 meta 標籤，設定視窗寬度自適應
var html = evaluate.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); // 設置允許框架內嵌的模式
return html; // 回傳 HTML 內容
}
// 取得 URL 的函式
function getUrl() {
var url = ScriptApp.getService().getUrl(); // 取得目前 ScriptApp 的 Web App URL
return url; // 回傳 URL
}
// 引入 CSS 和 JavaScript 的函式
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent(); // 從指定檔案名稱引入內容
}
// 新增資料到試算表
function addData(rowData) {
// 獲取當前時間
var currentDate = new Date();
// 獲取當前啟用的試算表
var ss = SpreadsheetApp.getActiveSpreadsheet();
// 獲取名為 "Sheet1" 的試算表
var ws = ss.getSheetByName("Sheet1");
// 格式化日期為純文字
var formattedDate = Utilities.formatDate(currentDate, ss.getSpreadsheetTimeZone(), "MM/dd/yyyy");
var formattedInputDate = Utilities.formatDate(new Date(rowData.date), ss.getSpreadsheetTimeZone(), "MM/dd/yyyy");
// 插入資料到試算表
ws.appendRow([
new Date().getTime().toString(), // 插入目前的時間戳
formattedDate, // 插入格式化後的當前日期
rowData.name, // 插入名稱
rowData.email, // 插入電子郵件
formattedInputDate, // 插入格式化後的輸入日期
rowData.level, // 插入等級
rowData.gender, // 插入性別
rowData.comments, // 插入評論
rowData.subscribe // 插入訂閱狀態
]);
}
如果你想要有表單送出後有確認信功能，請把addData(rowData) 的函式改成如下：
//插入資料以及確認信功能
function addData(rowData) {
var currentDate = new Date();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("Sheet1");
var ownerEmail = ss.getOwner().getEmail(); // 獲取試算表擁有者的電子郵件
var formattedDate = Utilities.formatDate(currentDate, ss.getSpreadsheetTimeZone(), "MM/dd/yyyy");
var formattedInputDate = Utilities.formatDate(new Date(rowData.date), ss.getSpreadsheetTimeZone(), "MM/dd/yyyy");
ws.appendRow([
new Date().getTime().toString(),
formattedDate,
rowData.name,
rowData.email,
formattedInputDate,
rowData.level,
rowData.gender,
rowData.comments,
rowData.subscribe
]);
var subject = "✅ 表單上傳成功";
var body = "<!DOCTYPE html><html><head><meta charset='UTF-8'></head><body style='font-family: Arial, sans-serif; color: #333;'>" +
"<h2 style='color: #4CAF50;'>🎉 恭喜！您的表單已成功上傳！</h2>" +
"<div style='border: 2px solid #4CAF50; padding: 15px; border-radius: 10px; background-color: lightgray; max-width: 400px;'>" +
"<p><strong>📌 姓名:</strong> " + rowData.name + "</p>" +
"<p><strong>📧 電子郵件:</strong> " + rowData.email + "</p>" +
"<p><strong>📅 日期:</strong> " + formattedInputDate + "</p>" +
"<p><strong>📊 等級:</strong> " + rowData.level + "</p>" +
"<p><strong>🚻 性別:</strong> " + rowData.gender + "</p>" +
"<p><strong>💬 評論:</strong> " + rowData.comments + "</p>" +
"<p><strong>🔔 訂閱狀態:</strong> " + rowData.subscribe + "</p>" +
"</div>" +
"<hr style='border: 1px solid #ddd;'>" +
"<p>📩 有任何問題，請聯絡 <a href='mailto:cwcchannel@icloud.com'>cwcchannel@icloud.com</a></p>" +
"</body></html>";
MailApp.sendEmail({
to: rowData.email,
cc: ownerEmail,
subject: subject,
htmlBody: body
});
}
11.3.5 html, CSS, JavaScript 程式輸入
form.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<!-- 連結 Bootstrap -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-KyZXEAg3QhqLMpG8r+8fhAXLRk2vvoC2f3B09zVXn8CA5QIVfZOJ3BCsw2P0p/We" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.bundle.min.js" integrity="sha384-U1DAWAznBHeqEIlVSCgzq+c9gqGAJn5c/t99JyeKa9xxaYpSvHU5awsuZVVFIhvj" crossorigin="anonymous"></script>
<?!= include("css"); ?>
</head>
<body>
<!-- 建立連結清單 -->
<!-- 從 GAS 匯入取得 URL 的函式 -->
<?var url = getUrl(); ?>
<div class="container-contact" id="profile">
<h3>個人資料</h3>
<div id="msg"></div>
<div class="row">
<!-- 第一欄位 -->
<div class="col-sm-2"></div>
<!-- 第二欄位 -->
<div class="col-sm-8">
<form id="myForm">
<div class="row">
<!-- 第一個輸入 - 名字 -->
<div class="col-sm-6 form-group">
<label for="level">姓名</label>
<input class="form-control" id="name" name="name" placeholder="輸入姓名…" type="text" required>
</div>
<!-- 第二個輸入 - Email -->
<div class="col-sm-6 form-group">
<label for="level">Email</label>
<input class="form-control" id="email" name="email" placeholder="輸入 Email..." type="email" required>
</div>
</div>
<!-- 第三個輸入 - 生日 -->
<div class="row">
<div class="col-sm-6 form-group">
<label for="level">生日</label>
<input class="form-control" id="dateOfBirth" name="dateofbirth" placeholder="生日" type="date">
</div>
<!-- 第四個輸入 - 下拉式選單 -->
<div class="col-sm-6 form-group">
<label for="level">程式語言能力</label>
<br>
<select style="width:100%; margin-top:10px;" name="level" id="level">
<option value="">請選擇</option>
<option value="人才">人才</option>
<option value="專業">專業</option>
<option value="高手">高手</option>
</select>
</div>
</div>
<!-- 第五個輸入 - 性別 -->
<div class="col-sm-12 form-group">
<div style="margin:20px;" class="radio">
<label>性別</label>
<input type="radio" name="gender" id="male" value="男">
<label for="male">男</label>
<input type="radio" name="gender" id="female" value="女">
<label for="female">女</label>
</div>
</div>
<!-- 第六個輸入 - 留言 -->
<textarea class="form-control" id="comments" name="comments" placeholder="評論" rows="5"></textarea>
<!-- 第七個輸入 - 勾選條款 -->
<div class="col-sm-3 form-group">
<label style="display: flex; align-items: center" for="subscribe">
<input style="width: 20px; height: 20px; margin-right: 5px;" type="checkbox" id="subscribe" value="Agree"> 條約同意
</label>
</div>
<div class="row">
<!-- 提示訊息區 -->
<div id="warningMessage" style="display: none; background-color: pink; color: red;"></div>
<div id="successMessage" style="display: none; color: green;">上傳成功!</div>
<!-- 提交按鈕 -->
<div class="col-sm-12 form-group">
<input type="button" value="送出" onclick="getValues()">
</div>
</div>
</form>
</div>
<!-- 第三欄位 -->
<div class="col-sm-2"></div>
</div>
</div>
<h2 id="show"></h2>
<!-- JavaScript 程式 -->
<?!= include("js"); ?>
</body>
</html>css.html
<!-- CSS 格式修改 -->
<style>
.container-contact {
padding: 20px;
text-align: center;
color: white;
background-color: rgb(42, 165, 159);
width: 100%;
}
.container-contact h3 {
font-size: 30px;
text-align: center;
padding: 20px;
}
.btn {
width: 100%;
font-size: 20px;
margin: 20px 0px 20px 0px;
background-color: rgb(6, 117, 119);
color: rgb(255, 255, 255);
border-radius: 20px;
transition: 1s;
}
.btn:hover {
background-color: rgb(178, 224, 84);
color: black;
}
input {
margin-top: 10px;
}
a{
text-decoration:none;
}
/* 增加單選按鈕的大小 */
.radio input[type="radio"] {
transform: scale(2); /* 調整這裡的數值以增加單選按鈕的大小 */
}
/* 增加單選按鈕與標籤之間的間距 */
.radio label {
margin: 10px;
}
</style>js.html
<script>
// 抓輸入的值
function getValues() {
// 驗證姓名
if (document.getElementById("name").value === "") {
alert("Please enter name");
return;
}
// 驗證郵件
if (document.getElementById("email").value === "") {
alert("Please enter email");
return;
}
// 驗證生日
if (document.getElementById("dateOfBirth").value === "") {
alert("Please enter Date of Birth");
return;
}
// 驗證層級
if (document.getElementById("level").value === "") {
alert("Please enter level");
return;
}
// 驗證性別
if (!(document.getElementById('male').checked || document.getElementById('female').checked)) {
alert('Gender is not checked');
return;
}
// 驗證評論
if (document.getElementById("comments").value === "") {
alert("Please enter comment");
return;
}
// 驗證是否同意條款
if (!document.getElementById('subscribe').checked) {
alert('Checkbox not checked');
return;
}
// 獲取性別值
var gender = document.getElementById('male').checked ? document.getElementById("male") : document.getElementById("female");
// 獲取表單的值
var name = document.getElementById("name");
var email = document.getElementById("email");
var date = document.getElementById("dateOfBirth");
var level = document.getElementById("level");
var comments = document.getElementById("comments");
var subscribe = document.getElementById('subscribe').checked ? document.getElementById("subscribe") : document.getElementById("nosubscribe");
// 準備要上傳的資料
var rowData = {
name: name.value,
email: email.value,
date: date.value,
level: level.value,
gender: gender.value,
comments: comments.value,
subscribe: subscribe.value
};
var username = name.value;
// 上傳資料
google.script.run.addData(rowData);
// 清除表單上的資料
document.getElementById("myForm").reset();
// 顯示成功提交的訊息
// document.getElementById("show").innerHTML = "Hello!" + username + " <br>You submitted it successfully! <br><button><a href='<?= url ?>''>Go Back</a></button>";
// 完成後的訊息
alert("資料上傳成功");
// 將焦點設置回"Full name"字段以便進行下一個輸入
document.getElementById("name").focus();
}
</script> 12. 階層式登入系統
12.3.3 Code.js程式輸入
code.gs
// 連接到 Web 應用程式
function doGet() {
var file = HtmlService.createTemplateFromFile("index");
var evaluate = file.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');
var html = evaluate.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
return html;
}
// 登入系統
function checkLogin(username, password) {
var app = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = app.getSheetByName("Login"); // 請改成您自己的試算表名稱
var data = sheetName.getDataRange().getValues();
var userRole = '';
// var userPic = '';
for (var i = 1; i < data.length; i++) {
var row = data[i];
if (row[0].toString() === username && row[1].toString() === password) {
userRole = row[2].toString();
// userPic = row[5].toString();
userFirstName = row[3].toString();
break;
}
}
if (userRole !== '') {
return { role: userRole, username: username, userFirstName: userFirstName };
} else {
return null;
}
}
// 引入檔案
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
12.3.5 html, CSS, JavaScript 程式輸入
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<!-- Bootstrap 的樣式與腳本 -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-GLhlTQ8iRABdZLl6O3oVMWSktQOp6b7In1Zl3/Jr59b6EGGoI1aFkw7cmDA6j6gD" crossorigin="anonymous">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js" integrity="sha384-w76AqPfDkMBDXo30jS1Sgez6pr3x5MlQ1ZAGC+nuZB+EYdgRZgiwxhTBTkF7CXvN" crossorigin="anonymous"></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
<style>
.container {
margin-top: 30px;
width: 50%;
border: 2px solid lightgray;
padding: 20px;
border-radius: 20px;
}
.hidden {
display: none;
}
</style>
</head>
<body onload="onload">
<!-- 登入表單 -->
<?!= include('login'); ?>
<!-- 管理者內容 -->
<?!= include('admin'); ?>
<!-- 訪客內容 -->
<?!= include('guest'); ?>
<!-- JavaScript 程式碼 -->
<?!= include('js'); ?>
</body>
</html>login.html
<!-- 登入容器 -->
<div id="loginContainer" class="container mt-5">
<h1 class="mb-4" style="text-align:center;">登入系統</h1>
<form onsubmit="submitForm(event)">
<div class="form-group">
<label for="username">使用者名稱</label>
<input type="text" class="form-control" id="username" name="username" required>
</div>
<div class="form-group">
<label for="password">密碼</label>
<input type="password" class="form-control" id="password" name="password" required>
</div>
<br>
<button type="submit" class="btn btn-primary">登入</button>
</form>
<!-- 載入圖示 -->
<div id="progressIcon" class="progress-icon d-none">
<img style="width:40px; height:40px;" src="https://media.tenor.com/On7kvXhzml4AAAAj/loading-gif.gif" alt="載入中...">
</div>
<!-- 錯誤訊息 -->
<p id="errorMessage" class="text-danger"></p>
</div>
admin.html
<!-- Admin Container -->
<div id="adminContentContainer" class="container-inside hidden">
<div class="row justify-content-center">
<div class="col-12 col-md-6 col-lg-4">
<h1><span id="adminUsername"></span></h1>
<h2>管理者登入</h2>
<!-- Logout button -->
<button id="logoutButton" class="btn btn-danger" onclick="logout()">登出</button>
</div>
</div>
</div>
guest.html
<!-- Guest Container -->
<div id="guestContentContainer" class="container-inside hidden">
<div class="row justify-content-center">
<div class="col-12 col-md-6 col-lg-4">
<h1><span id="guestUsername"></span></h1>
<h2>會員登入</h2>
<!-- Logout button -->
<button id="logoutButton" class="btn btn-danger" onclick="logout()">登出</button>
</div>
</div>
</div>
</div>js.html
<script>
/////////////////登入系統
function submitForm(event) {
event.preventDefault(); // 防止表單的預設提交行為
var username = document.getElementById("username").value; // 獲取使用者名稱的值
var password = document.getElementById("password").value; // 獲取密碼的值
// 顯示載入圖示，在等待登入時
showProgressIcon();
// 使用 Google Apps Script 執行登入驗證，並設置成功處理程序
google.script.run.withSuccessHandler(loginSuccess).checkLogin(username, password);
}
function loginSuccess(data) {
// 登入檢查完成後隱藏載入圖示
hideProgressIcon();
if (data) {
var userRole = data.role; // 使用者角色
var username = data.username; // 使用者名稱
var userFirstName = data.userFirstName; // 使用者的名字
// 登入成功後隱藏登入容器
document.getElementById("loginContainer").style.display = "none";
// 登入成功後清空使用者名稱和密碼欄位
document.getElementById("username").value = "";
document.getElementById("password").value = "";
if (userRole === "admin") { // 如果使用者角色是管理者
document.getElementById("adminContentContainer").classList.remove("hidden"); // 顯示管理者內容容器
document.getElementById("adminUsername").textContent = "歡迎, " + userFirstName + "(管理者身份)!";
} else if (userRole === "guest") { // 如果使用者角色是訪客
document.getElementById("guestContentContainer").classList.remove("hidden"); // 顯示訪客內容容器
document.getElementById("guestUsername").textContent = "歡迎, " + username + " (會員身份)!";
}
} else {
// 顯示錯誤訊息
document.getElementById("errorMessage").textContent = "使用者名稱或密碼錯誤.";
}
}
function showProgressIcon() {
document.getElementById("progressIcon").classList.remove("d-none"); // 顯示載入圖示
}
function hideProgressIcon() {
document.getElementById("progressIcon").classList.add("d-none"); // 隱藏載入圖示
}
// 登出功能
function logout() {
// 隱藏管理者或訪客內容，顯示登入容器
document.getElementById("adminContentContainer").classList.add("hidden"); // 隱藏管理者內容
document.getElementById("guestContentContainer").classList.add("hidden"); // 隱藏訪客內容
document.getElementById("loginContainer").style.display = "block"; // 確保登入容器可見
}
/////////////////登入系統
</script>13. 模糊資料搜尋系統 – SQL
13.3.3 Code.js程式輸入
code.gs
function doGet() {
var file = HtmlService.createTemplateFromFile("search");
var evaluate = file.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1');
var html = evaluate.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
return html;
}
// 包含（include）檔案
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
// 將資料插入試算表
function insertDataToSheet(sheetName, cell, data) {
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
if (sheet) {
const cellRange = sheet.getRange(cell);
cellRange.setValue(data);
return "資料成功插入！";
} else {
return "找不到試算表！";
}
} catch (error) {
return "插入資料時出錯: " + error;
}
}
// 從試算表取得資料
function getDataFromSheet(sheetName) {
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
if (sheet) {
// 從L到T欄（假設資料在第2行到最後一行）取得資料
const data = sheet.getRange("L2:T" + sheet.getLastRow()).getValues();
// 修改B和E欄的日期格式為純文字
const modifiedData = data.map(row => {
const modifiedRow = row.slice(); // 複製該行資料以避免修改原始資料
// 將B欄（索引1）和E欄（索引4）的日期值轉換為日期物件
if (modifiedRow[1] instanceof Date) {
modifiedRow[1] = Utilities.formatDate(modifiedRow[1], ss.getSpreadsheetTimeZone(), "MM/dd/yyyy");
}
if (modifiedRow[4] instanceof Date) {
modifiedRow[4] = Utilities.formatDate(modifiedRow[4], ss.getSpreadsheetTimeZone(), "MM/dd/yyyy");
}
return modifiedRow;
});
return modifiedData;
} else {
return "找不到試算表！";
}
} catch (error) {
return "取得資料時出錯: " + error;
}
}
13.3.5 html, CSS, JavaScript 程式輸入
search.html
<!DOCTYPE html>
<html>
<head>
<title>搜尋引撆</title>
<!-- Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/css/bootstrap.min.css" rel="stylesheet">
<!-- Bootstrap JS (Place this at the end of the body) -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/js/bootstrap.bundle.min.js"></script>
<?!= include('css'); ?>
</head>
<body>
<div class="container mt-4">
<div class="row justify-content-center">
<div class="col-md-6">
<form class="d-flex" onsubmit="submitForm(event)">
<input id="searchInput" class="form-control me-2" type="search" placeholder="請輸入…" aria-label="Search">
<input class="btn btn-outline-primary" type="submit" value="搜尋">
</form>
</div>
</div>
</div>
<div id="loadingIcon" class="d-none">
<div class="spinner-border" role="status">
<span class="visually-hidden">讀取中...</span>
</div>
</div>
<div id="searchResultText" class="d-none mt-2"></div>
<div id="tableContainer">
<div class="container mt-4">
<div class="row justify-content-center">
<div class="col-md-12">
<table class="table">
<thead>
<tr>
<th scope="col">ID</th>
<th scope="col">輸入時間</th>
<th scope="col">姓名</th>
<th scope="col">Email</th>
<th scope="col">生日</th>
<th scope="col">程式能力</th>
<th scope="col">性別</th>
<th scope="col">評論</th>
<th scope="col">同意</th>
<!-- Add more columns as needed up to Column T -->
</tr>
</thead>
<tbody id="tableBody">
<!-- Table body content will be dynamically filled by JavaScript -->
</tbody>
</table>
</div>
</div>
</div>
</div>
<?!= include('js'); ?>
</body>
</html>js.html
<script>
// 處理表單提交的函數
function submitForm(event) {
event.preventDefault(); // 防止表單的預設提交行為
const searchInput = document.getElementById("searchInput");
const searchTerm = searchInput.value;
// 在獲取資料時顯示載入圖示
const loadingIcon = document.getElementById("loadingIcon");
loadingIcon.classList.remove("d-none");
// 檢查搜尋關鍵字是否為空
if (!searchTerm.trim()) {
// 顯示提示訊息並提前返回
const searchResultText = document.getElementById("searchResultText");
searchResultText.textContent = "請輸入搜尋關鍵字";
searchResultText.classList.remove("d-none");
loadingIcon.classList.add("d-none"); // 隱藏載入圖示
return;
}
// 使用 google.script.run 呼叫伺服器端的函數來插入資料到試算表
google.script.run.withSuccessHandler(function() {
// 資料插入後，從伺服器檢索並顯示資料
google.script.run.withSuccessHandler(function(data) {
const tableContainer = document.getElementById("tableContainer");
tableContainer.style.display = "block"; // 顯示表格容器
const tableBody = document.getElementById("tableBody");
tableBody.innerHTML = ""; // 清除表格中現有的資料
// 遍歷資料並在表格中創建新行
data.forEach(rowData => {
const row = document.createElement("tr");
rowData.forEach(cellData => {
const cell = document.createElement("td");
cell.textContent = cellData;
row.appendChild(cell);
});
tableBody.appendChild(row);
});
// 當資料接收並顯示後，隱藏載入圖示
loadingIcon.classList.add("d-none");
}).getDataFromSheet("Customers");
}).insertDataToSheet("Customers", "K2", searchTerm);
// 在頁面上顯示搜尋結果文字
const searchResultText = document.getElementById("searchResultText");
searchResultText.textContent = "您搜尋：" + searchTerm; // 在搜尋結果文字中加入 "您搜尋："
searchResultText.classList.remove("d-none");
searchInput.value = ''; // 提交後清除輸入框的內容
}
</script>
css.html
<style>
#loadingIcon {
display: flex;
justify-content: center;
align-items: center;
height: 100px;
}
#loadingIcon .spinner-border {
color: lightgray;
}
#searchResultText {
margin-top: 10px;
font-size: 16px;
color: gray;
text-align: center;
}
#tableContainer{
display: none;
}
</style>14. 資訊發布系統CRUD系統
14.3.3 Code.js程式輸入
code.gs
// 連接到 Web App
function doGet() {
var file = HtmlService.createTemplateFromFile("index"); // 載入前端 HTML 檔案
var evaluate = file.evaluate().addMetaTag('viewport', 'width=device-width, initial-scale=1'); // 設定響應式設計
var html = evaluate.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); // 允許 Web App 嵌入
return html;
}
// 包含
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
// 新增資料
function addData(rowData) {
var currentDate = new Date();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("data");
// 產生隨機6個字元的ID
var id = generateRandomID();
// 將生日字串轉換為日期物件
var birthdayDate = new Date(rowData.birthday);
var formattedBirthday = Utilities.formatDate(birthdayDate, ss.getSpreadsheetTimeZone(), "MM/dd/yyyy");
// 新增資料到試算表
ws.appendRow([id, rowData.name, rowData.email, rowData.phone, formattedBirthday]);
var updatedData = getData();
return JSON.stringify(updatedData); // 返回更新後的資料
}
// 產生隨機6個字母數字ID的函數
function generateRandomID() {
var characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
var result = '';
var length = 6;
for (var i = 0; i < length; i++) {
var randomIndex = Math.floor(Math.random() * characters.length);
result += characters[randomIndex];
}
return result; // 返回生成的ID
}
// 根據ID獲取資料
function getCustomerDataById(id) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("data");
var data = ws.getDataRange().getValues();
// 遍歷資料查找匹配的ID
for (var i = 0; i < data.length; i++) {
if (data[i][0] === id) { // ID位於A欄（索引0）
var birthday = data[i][4]; // 生日數據
if (birthday instanceof Date) {
// 如果 birthday 是 Date，則格式化
birthday = Utilities.formatDate(birthday, ss.getSpreadsheetTimeZone(), "yyyy-MM-dd");
} else {
// 否則，保留原始值（可能是字符串）
Logger.log("Invalid date format detected: " + birthday);
}
return {
name: data[i][1],
email: data[i][2],
phone: data[i][3],
birthday: birthday
};
}
}
return null; // 如果未找到，返回 null
}
// 獲取資料
function getData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("data");
var data = ws.getDataRange().getValues();
// 移除表頭
data.shift();
// 將日期格式轉換為純文本（MM/DD/YYYY）格式
data = data.map(function (row) {
if (row[4] instanceof Date) {
row[4] = Utilities.formatDate(row[4], ss.getSpreadsheetTimeZone(), "MM/dd/yyyy");
} else {
Logger.log("Skipping invalid date: " + row[4]); // Debugging log
}
return row;
});
Logger.log(data);
return data; // 返回資料
}
// 更新資料
function updateCustomerData(data) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("data");
var dataRange = ws.getDataRange();
var values = dataRange.getValues();
// Iterate over the rows and find the row with the matching ID
for (var i = 0; i < values.length; i++) {
if (values[i][0] === data.id) { // ID is in the first column (index 0)
// Update the row with the new data
ws.getRange(i + 1, 2).setValue(data.name); // Column B for name
ws.getRange(i + 1, 3).setValue(data.email); // Column C for email
ws.getRange(i + 1, 4).setValue(data.phone); // Column D for phone
ws.getRange(i + 1, 5).setValue(data.birthday); // Column E for birthday
// Exit once the row is updated
return;
}
}
// If the ID wasn't found, you can optionally handle this (e.g., alert or log)
Logger.log('ID not found: ' + data.id);
}
// 刪除資料
function deleteCustomer(id) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("data");
var data = ws.getDataRange().getValues();
// 記錄傳入的ID以進行除錯
Logger.log('Incoming ID to delete: ' + id);
// 查找並刪除指定ID的資料行
var rowDeleted = false;
for (var i = 0; i < data.length; i++) {
// 記錄每一行的ID以進行除錯
Logger.log('Row ID: ' + data[i][0]);
// 比對ID並刪除相符的行
if (data[i][0] == id) {
Logger.log('Deleting row: ' + (i + 1)); // 記錄刪除的行數
ws.deleteRow(i + 1); // 試算表中的行數是1基的
rowDeleted = true;
break;
}
}
if (!rowDeleted) {
Logger.log('No matching row found to delete.');
}
// 刪除後取得更新的資料
var updatedData = getData();
Logger.log('Updated Data: ' + JSON.stringify(updatedData));
return updatedData; // 返回更新後的資料
}
14.3.5 html, CSS, JavaScript 程式輸入
index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<!-- 匯入所需的外部 JavaScript 和 CSS 函式庫 -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css">
<?!= include('css'); ?>
</head>
<body>
<h1 style="text-align:center;padding-top:10px;">即時發布、編輯、刪除、顯示系統</h1>
<div class="container">
<div class="row">
<!-- 左邊欄位用於表單 -->
<div class="col-md-6">
<form id="upload-form" class="mt-4">
<!-- 表單欄位（姓名、Email、電話、生日） -->
<div class="form-group">
<!-- 隱藏的 ID 輸入框 -->
<input type="hidden" id="id" name="id">
</div>
<div class="form-group">
<label for="name">姓名</label>
<input type="text" class="form-control" id="name" name="name">
</div>
<div class="form-group">
<label for="email">電子郵件</label>
<input type="email" class="form-control" id="email" name="email">
</div>
<div class="form-group">
<label for="phone">電話</label>
<input type="tel" class="form-control" id="phone" name="phone">
</div>
<div class="form-group">
<label for="birthday">生日</label>
<input type="date" class="form-control" id="birthday" name="birthday">
</div>
<!-- 使用 input 標籤作為提交按鈕 -->
<input type="button" class="btn btn-primary uploadData" value="提交" onclick="submitForm()">
<input type="button" class="btn btn-success updateData" value="更新" id="update-button" style="display: none;" onclick="updateForm()">
<div id="success-message" class="mt-3" style="display: none; color: green;"></div>
</form>
</div>
<!-- 右邊欄位用於顯示表格 -->
<div class="col-md-6">
<br>
<!-- 顯示客戶資料的區域 -->
<div id="customer-list"></div>
</div>
</div>
</div>
<!-- 加入以下 JavaScript 程式碼來處理表單提交 -->
<?!= include('js'); ?>
</body>
</html>
js.html
<script>
function displayCustomers(customers) {
var customerListDiv = document.getElementById("customer-list");
// 清除先前的資料
customerListDiv.innerHTML = "";
// 遍歷資料並為每個創建一個框
customers.forEach(function (customer) {
var customerBox = document.createElement("div");
customerBox.className = "customer-box";
customerBox.innerHTML = `
<p><strong>ID:</strong> ${customer[0]}</p>
<p><strong>姓名:</strong> ${customer[1]}</p>
<p><strong>電話:</strong> ${customer[3]}</p>
<p><strong>Email:</strong> ${customer[2]}</p>
<p><strong>生日:</strong> ${customer[4]}</p>
<button class="btn btn-warning edit-btn" data-id="${customer[0]}">編輯</button>
<button class="btn btn-danger delete-btn" data-id="${customer[0]}">刪除</button>
`;
// 為編輯和刪除按鈕添加事件監聽器
customerBox.querySelector(".edit-btn").addEventListener("click", function() {
var id = this.getAttribute("data-id");
editCustomer(id);
});
customerBox.querySelector(".delete-btn").addEventListener("click", function() {
var id = this.getAttribute("data-id");
deleteCustomer(id);
});
customerListDiv.appendChild(customerBox);
});
}
// 編輯資料的功能
function updateForm() {
var id = document.getElementById('id').value;
var name = document.getElementById('name').value;
var email = document.getElementById('email').value;
var phone = document.getElementById('phone').value;
var birthday = document.getElementById('birthday').value;
// Convert the 'yyyy-MM-dd' format to 'MM/dd/yyyy' for the birthday
if (birthday) {
var dateParts = birthday.split('-');
if (dateParts.length === 3) {
// Format it as 'MM/dd/yyyy'
birthday = `${dateParts[1]}/${dateParts[2]}/${dateParts[0]}`;
}
}
// Send the updated data to Google Apps Script
google.script.run.withSuccessHandler(function(response) {
alert('資料已成功更新!');
// 隱藏更新按鈕
document.getElementById('update-button').style.display = 'none';
// 重新載入更新後的資料列表
google.script.run.withSuccessHandler(displayCustomers).getData();
// 更新成功後清空表單欄位
document.getElementById('id').value = '';
document.getElementById('name').value = '';
document.getElementById('email').value = '';
document.getElementById('phone').value = '';
document.getElementById('birthday').value = '';
}).withFailureHandler(function(error) {
alert('更新資料時出錯: ' + error.message);
}).updateCustomerData({ id, name, email, phone, birthday });
}
function editCustomer(id) {
google.script.run.withSuccessHandler(function(customerData) {
if (customerData) {
// 用資料填充表單欄位
document.getElementById('id').value = id;
document.getElementById('name').value = customerData.name;
document.getElementById('email').value = customerData.email;
document.getElementById('phone').value = customerData.phone;
// Convert birthday to 'yyyy-MM-dd' format for the input field
var birthday = customerData.birthday;
if (birthday) {
// Convert 'MM/dd/yyyy' to 'yyyy-MM-dd'
var dateParts = birthday.split('/');
if (dateParts.length === 3) {
birthday = `${dateParts[2]}-${dateParts[0]}-${dateParts[1]}`;
}
}
document.getElementById('birthday').value = birthday;
// 顯示更新按鈕
document.getElementById('update-button').style.display = 'inline-block';
} else {
alert('找不到資料!');
}
}).withFailureHandler(function(error) {
alert('獲取資料時出錯: ' + error.message);
}).getCustomerDataById(id);
}
// 刪除資料的功能
function deleteCustomer(id) {
if (confirm("確定要刪除嗎?")) {
google.script.run.withSuccessHandler(function() {
// 刪除後重新載入資料列表
google.script.run.withSuccessHandler(displayCustomers).getData();
}).deleteCustomer(id);
}
}
// 載入資料並顯示
google.script.run.withSuccessHandler(displayCustomers).getData();
// 驗證並提交表單的功能
function submitForm() {
// 驗證表單欄位
var name = document.getElementById("name").value;
var email = document.getElementById("email").value;
var phone = document.getElementById("phone").value;
var birthday = document.getElementById("birthday").value;
if (name.trim() === "") {
alert("請輸入姓名");
return;
}
if (email.trim() === "") {
alert("請輸入電子郵件");
return;
}
if (phone.trim() === "") {
alert("請輸入電話");
return;
}
if (birthday.trim() === "") {
alert("請輸入生日");
return;
}
// 準備表單資料
var formData = {
name: name,
email: email,
phone: phone,
birthday: birthday
};
// 將表單資料提交至 Google Apps Script
google.script.run.withSuccessHandler(function () {
// 提交成功後，重新載入資料並更新顯示
google.script.run.withSuccessHandler(displayCustomers).getData();
}).addData(formData);
// 清空表單欄位
document.getElementById("name").value = "";
document.getElementById("email").value = "";
document.getElementById("phone").value = "";
document.getElementById("birthday").value = "";
// 顯示成功訊息
var successMessage = document.getElementById("success-message");
successMessage.innerHTML = "資料上傳成功!";
successMessage.style.display = "block";
// 3秒後隱藏成功訊息
setTimeout(function () {
successMessage.style.display = "none";
}, 3000);
// 將焦點設置到姓名欄位，準備輸入下一筆資料
document.getElementById("name").focus();
}
// 載入資料並顯示
google.script.run.withSuccessHandler(displayCustomers).getData();
</script>
css.html
<style>
/* 自訂資料表和包裝器的樣式 */
.custom-table-wrapper {
width: 100%;
}
#data-table td, #data-table th {
white-space: nowrap;
}
.container {
max-width: 80%;
}
.col-md-6 {
width: 90%;
}
#id, #name, #phone, #birthday, #email {
width: 90%;
}
#upload-form {
border: 1px solid lightgray;
border-radius: 15px;
padding: 30px;
padding-left: 40px;
width: 90%;
}
.customer-box {
border: 1px solid lightgray; /* 加入一個淺灰色邊框 */
border-radius: 15px;
padding: 20px;
margin-bottom: 10px;
}
</style>15. 待辦事項－CRUD系統
15.3.3 Code.gs程式輸入
code.gs
// doGet 函數，處理 HTTP GET 請求
function doGet() {
return HtmlService.createTemplateFromFile('index')
.evaluate()
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
// 連結 CSS 和 JavaScript 檔案
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
// 新增 Create
function addItemToSheet(item) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("items");
if (!sheet) {
// 如果工作表不存在，創建一個新的工作表，命名為 "items"
sheet = ss.insertSheet("items");
// 添加標題行
sheet.appendRow(["Item", "Status"]);
}
// 將項目添加到工作表，並設置默認狀態為 0（圓形）
sheet.appendRow([item, 0]);
return item; // 返回添加的項目到客戶端 JavaScript
}
// 讀取 Read
function getAllItems() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("items");
if (!sheet) {
return []; // 如果 "items" 工作表不存在，返回空陣列
}
var dataRange = sheet.getDataRange();
var data = dataRange.getValues();
// 跳過標題行（如果存在）
if (data.length > 1) {
data.shift();
}
return data; // 返回所有數據，包括狀態
}
// 更新 Update
function updateItemInSheet(item, updatedItem) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("items");
if (!sheet) {
return; // 如果 "items" 工作表不存在，返回
}
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
// 搜索工作表中的項目並使用新值進行更新
for (var i = 0; i < values.length; i++) {
if (values[i][0] === item) {
values[i][0] = updatedItem;
break;
}
}
// 清除現有數據並將更新後的數據寫入工作表
sheet.clearContents();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
// 更新圖標狀態
function updateIconStatus(item, status) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("items");
if (!sheet) {
return; // 如果 "items" 工作表不存在，返回
}
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
// 搜索工作表中的項目並更新其狀態
for (var i = 0; i < values.length; i++) {
if (values[i][0] === item) {
values[i][1] = status; // 更新狀態欄
break;
}
}
// 清除現有數據並將更新後的數據寫入工作表
sheet.clearContents();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
// 刪除 Delete
function deleteItemFromSheet(item) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("items");
if (!sheet) {
return; // 如果 "items" 工作表不存在，返回
}
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
// 搜索工作表中的項目並從數據陣列中移除
for (var i = values.length - 1; i >= 0; i--) {
if (values[i][0] === item) {
values.splice(i, 1);
}
}
// 清除現有數據並將更新後的數據寫入工作表
sheet.clearContents();
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
15.3.5 html, CSS, JavaScript 程式輸入
index.html
<!DOCTYPE html>
<html lang="zh-Hant">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>新增按鈕與資料上傳</title>
<!-- 加入 Bootstrap CSS 連結 -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css">
<!-- 加入 Font Awesome CSS 連結 -->
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.3/css/all.min.css">
<?!= include("css"); ?>
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-md-12">
<div class="input-group">
<!-- 輸入欄位 -->
<input type="text" style="font-size:20px;" id="itemInput" class="form-control" placeholder="請輸入待辦事項...">
<!-- 按鈕（使用 input 標籤來代替 button） -->
<input type="button" id="addItemButton" class="btn btn-primary" value="新增">
</div>
</div>
</div>
</div>
<!-- 顯示已新增項目的區塊 -->
<div class="container mt-5">
<div class="row">
<div class="col-md-12">
<h3>待辦事項:</h3>
<div id="addedItems"></div>
</div>
</div>
</div>
<!-- 顯示訊息的區塊 -->
<div class="container mt-3">
<div class="row">
<div class="col-md-12">
<div id="messages"></div>
</div>
</div>
</div>
<?!= include("js"); ?>
</body>
</html>
js.html
<!-- 加入 Bootstrap JS 腳本（這是 Bootstrap 功能所必須的） -->
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.min.js"></script>
<!-- 加入自定義 JavaScript -->
<script>
// 顯示新增訊息，5秒後自動關閉
function displayMessage(message, messageType) {
var msgDiv = document.createElement("div");
msgDiv.className = "alert alert-" + messageType + " mt-2";
msgDiv.innerHTML = message;
document.getElementById("messages").appendChild(msgDiv);
// 5秒後移除訊息
setTimeout(function() {
msgDiv.remove();
}, 5000);
}
// 點擊時切換圖示
function toggleIcon(event, item) {
var icon = event.currentTarget;
var status = 0; // 預設為圓形圖示
if (icon.classList.contains("fa-square")) {
icon.classList.remove("fa-square");
icon.classList.add("fa-check-square");
icon.style.color = "#559af2"; // 設定打勾圖示顏色為藍色
status = 1; // 將狀態設為 1（勾選）
} else {
icon.classList.remove("fa-check-square");
icon.classList.add("fa-square");
icon.style.color = ""; // 重設圓形圖示顏色
status = 0; // 將狀態設為 0（未勾選）
}
// 更新 Google Sheets 中圖示的狀態
google.script.run.updateIconStatus(item, status);
}
// 更新 HTML 中的新增項目
function updateAddedItem(item, status) {
var itemDiv = document.createElement("div");
itemDiv.className = "item-container"; // 為項目容器添加類別
// 建立圖示元素
var icon = document.createElement("i");
icon.className = status === 1 ? "fas fa-check-square" : "fas fa-square";
icon.style.marginRight = "10px"; // 圖示後方增加空間
icon.addEventListener("click", function(event) {
event.stopPropagation();
toggleIcon(event, item);
});
itemDiv.appendChild(icon);
// 建立項目文字的 span 元素
var itemTextSpan = document.createElement("span");
itemTextSpan.className = "item-text"; // 為文字添加類別
itemTextSpan.innerHTML = item;
itemDiv.appendChild(itemTextSpan);
// 建立勾選圖示
var checkIcon = document.createElement("i");
checkIcon.className = "fas fa-check check-icon"; // 使用 check-icon 類別將勾選圖示移至左側
checkIcon.style.display = "none"; // 初始隱藏勾選圖示
checkIcon.addEventListener("click", function(event) {
// 防止點擊事件冒泡至項目容器
event.stopPropagation();
// 更新項目文字為輸入框的內容
itemTextSpan.textContent = editInput.value;
// 切換輸入框和勾選圖示的顯示狀態
editInput.style.display = "none";
checkIcon.style.display = "none";
itemTextSpan.style.display = "inline";
// 呼叫 Google Apps Script 函式來更新項目
google.script.run.withSuccessHandler(loadAllItems).updateItemInSheet(item, itemTextSpan.textContent);
});
itemDiv.appendChild(checkIcon);
// 建立編輯圖示
var editIcon = document.createElement("i");
editIcon.className = "fas fa-edit edit-icon"; // 使用 edit-icon 類別將編輯圖示移至右側
editIcon.addEventListener("click", function(event) {
// 防止點擊事件冒泡至項目容器
event.stopPropagation();
// 顯示勾選圖示並隱藏文字
checkIcon.style.display = "inline";
itemTextSpan.style.display = "none";
// 設定輸入框的內容為目前的項目文字
editInput.value = itemTextSpan.textContent;
// 顯示輸入框並聚焦，方便立即編輯
editInput.style.display = "inline";
editInput.focus();
});
itemDiv.appendChild(editIcon);
// 建立刪除圖示
var deleteIcon = document.createElement("i");
deleteIcon.className = "fas fa-trash-alt delete-icon"; // 使用 delete-icon 類別將刪除圖示移至右側
deleteIcon.addEventListener("click", function(event) {
// 防止點擊事件冒泡至項目容器
event.stopPropagation();
// 顯示確認刪除的提示
if (confirm("確定要刪除嗎？")) {
// 呼叫 Google Apps Script 函式來刪除項目
google.script.run.withSuccessHandler(function() {
// 從 HTML 中移除該項目
itemDiv.remove();
}).deleteItemFromSheet(item);
}
});
itemDiv.appendChild(deleteIcon);
// 建立編輯輸入框
var editInput = document.createElement("input");
editInput.type = "text";
editInput.style.display = "none"; // 初始隱藏輸入框
editInput.classList.add("edit-input"); // 添加樣式類別
itemDiv.insertBefore(editInput, itemTextSpan); // 在項目文字之前插入輸入框
document.getElementById("addedItems").appendChild(itemDiv);
}
// 從 Google Sheets 載入所有項目並更新 HTML
function loadAllItems() {
google.script.run.withSuccessHandler(function(items) {
var addedItemsContainer = document.getElementById("addedItems");
addedItemsContainer.innerHTML = ""; // 清空現有項目
// 使用 Set 來避免重複項目
const uniqueItems = new Set();
items.forEach(function(item) {
if (!uniqueItems.has(item[0])) {
uniqueItems.add(item[0]);
updateAddedItem(item[0], item[1]); // 更新項目文字和狀態
}
});
}).getAllItems();
}
// 頁面載入時初始化載入項目
document.addEventListener("DOMContentLoaded", loadAllItems);
document.getElementById("addItemButton").addEventListener("click", function() {
var item = document.getElementById("itemInput").value;
if (item.trim() === "") {
// 如果輸入空白，顯示錯誤訊息
var errorShown = document.querySelector(".alert-danger");
if (!errorShown) {
displayMessage("不能空白", "danger");
}
return; // 如果輸入為空，停止執行
}
google.script.run.withSuccessHandler(function(item) {
// 顯示成功訊息
var successShown = document.querySelector(".alert-success");
if (!successShown) {
displayMessage("新增成功", "success");
}
// 更新 HTML 顯示新增的項目
updateAddedItem(item);
}).addItemToSheet(item);
// 新增後清空輸入框
document.getElementById("itemInput").value = "";css.html
<style>
.item-container {
border: 1px solid #90a6fd;
background-color: #dcf2f9;
padding: 10px;
padding-left:20px;
margin-bottom: 5px;
border-radius:10px;
color:#559af2;
font-size:20px;
display: flex;
align-items: right;
justify-content: space-between; /* Move icons to the right */
}
.item-container:hover {
border: 1px solid #90a6fd;
background-color: blue;
color:lightblue;
transition:0.4s;
}
.edit-icon {
color: #8fce8f;
margin-left: auto;
padding-right: 10px;
}
.item-crossed {
text-decoration: line-through;
text-decoration-thickness: 2px;
text-decoration-color: red;
text-decoration-skip-ink: none;
}
.check-icon{
margin-left:10px;
font-size:30px;
color:
}
</style>
新增章節 第19章. Google試算表－功能列按鈕 － 跳出CRUD系統
以下為程式分享
code.gs
//加入功能列按鈕
//加入功能列按鈕
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('ℹ️更新管理')
.addItem('🆙更新', 'showUpdateModal')
.addItem('📁瀏覽', 'showBrowseModal')
.addToUi();
}
//顯示更新視窗
function showUpdateModal() {
const html = HtmlService.createHtmlOutputFromFile('UpdateModal')
.setWidth(800)
.setHeight(550);
SpreadsheetApp.getUi().showModalDialog(html, '更新');
}
//顯示瀏覽視窗
function showBrowseModal() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('更新');
const data = sheet.getDataRange().getValues();
// Format and sort data in descending order by date
const formattedData = data
.slice(1) // Exclude header row
.map(row => [formatDate(row[0]), row[1] || '']) // Ensure text format
.sort((a, b) => new Date(b[0]) - new Date(a[0])); // Sort by date (newest first)
const template = HtmlService.createTemplateFromFile('BrowseModal');
template.data = formattedData;
const html = template.evaluate().setWidth(800).setHeight(650);
SpreadsheetApp.getUi().showModalDialog(html, '瀏覽');
}
// 格式化日期
function formatDate(date) {
if (date instanceof Date) {
return Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy/MM/dd");
}
return date; // Return as is if not a valid date
}
//儲存紀錄
function saveData(date, content) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('更新');
// Get the last row with data
const lastRow = sheet.getLastRow();
// Fix timezone issue by adding time offset
let selectedDate = new Date(date);
selectedDate.setHours(selectedDate.getHours() + 8); // Adjust for time zone differences (UTC+8 for Taiwan/HK)
// Convert to yyyy/MM/dd format
let formattedDate = Utilities.formatDate(selectedDate, Session.getTimeZone(), "yyyy/MM/dd");
// Ensure content is treated as text
if (typeof content === 'number') {
content = "'" + content.toString(); // Prefix with a single quote to force text format
}
// Insert the formatted date and content in the next empty row
sheet.getRange(lastRow + 1, 1).setValue(formattedDate);
sheet.getRange(lastRow + 1, 1).setNumberFormat("yyyy/MM/dd"); // Ensure format
sheet.getRange(lastRow + 1, 2).setValue(content);
sheet.getRange(lastRow + 1, 2).setNumberFormat("@"); // Set format to text
}
//刪除紀錄
function deleteRow(date, content) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('更新');
const data = sheet.getDataRange().getValues();
for (let i = data.length - 1; i >= 1; i--) { // Skip header row
let sheetDate = Utilities.formatDate(new Date(data[i][0]), Session.getTimeZone(), "yyyy/MM/dd");
let inputDate = date.replace(/-/g, "/"); // Ensure format matches
// Ensure content is treated as a string
let sheetContent = data[i][1].toString();
let inputContent = content.toString();
if (sheetDate === inputDate && sheetContent === inputContent) {
sheet.deleteRow(i + 1);
break;
}
}
}
//更新紀錄
function updateRow(originalDate, originalContent, newDate, newContent) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('更新');
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) { // Skip header row
let sheetDate = Utilities.formatDate(new Date(data[i][0]), Session.getTimeZone(), "yyyy/MM/dd");
let inputOriginalDate = originalDate.replace(/-/g, "/");
// Ensure content is treated as a string
let sheetContent = data[i][1].toString();
let inputOriginalContent = originalContent.toString();
// Find the row to update
if (sheetDate === inputOriginalDate && sheetContent === inputOriginalContent) {
const rowNum = i + 1; // Row index in Google Sheets (1-based index)
// Fix timezone issue by adding time offset
let selectedDate = new Date(newDate);
selectedDate.setHours(selectedDate.getHours() + 8); // Adjust for UTC+8 (Taiwan/HK)
// Convert to yyyy/MM/dd format
let formattedDate = Utilities.formatDate(selectedDate, Session.getTimeZone(), "yyyy/MM/dd");
// Ensure newContent is treated as text
if (typeof newContent === 'number') {
newContent = "'" + newContent.toString(); // Prefix with a single quote to force text format
}
// Update date (Column A) and content (Column B)
sheet.getRange(rowNum, 1).setValue(formattedDate);
sheet.getRange(rowNum, 1).setNumberFormat("yyyy/MM/dd"); // Ensure format
sheet.getRange(rowNum, 2).setValue(newContent);
sheet.getRange(rowNum, 2).setNumberFormat("@"); // Set format to text
break;
}
}
}
UpdateModal.html
<!DOCTYPE html>
<html>
<head>
<style>
/* General Styling */
body {
font-family: 'Poppins', sans-serif;
background-color: #e3e6ed;
color: #2a3f5f;
padding: 20px;
margin: 0;
}
.container {
max-width: 100%;
margin: auto;
background: linear-gradient(135deg, #d1d6e0, #b0b8c7);
padding: 45px;
border-radius: 12px;
box-shadow: 0px 4px 12px rgba(0, 0, 0, 0.2);
border: 1px solid #9aa3b5;
}
h3 {
text-align: center;
margin-bottom: 20px;
font-size: 22px;
color: #1c2e4a;
font-weight: 600;
}
label {
font-size: 14px;
font-weight: 500;
margin-bottom: 5px;
display: block;
color: #324b6d;
}
input[type="date"], textarea {
width: 100%;
padding: 12px;
margin-bottom: 20px;
border-radius: 8px;
border: 1px solid #7f8fa6;
background-color: #e7ebf2;
color: #1c2e4a;
transition: all 0.3s ease-in-out;
font-size: 14px;
}
input[type="date"]:hover, textarea:hover {
border-color: #0056b3;
background-color: #dce2eb;
}
textarea {
resize: vertical;
min-height: 120px;
}
.button-container {
display: flex;
justify-content: space-between;
}
button {
width: 48%;
padding: 12px;
font-size: 15px;
border-radius: 8px;
border: none;
cursor: pointer;
font-weight: 600;
transition: all 0.3s ease-in-out;
}
.save-button {
background: linear-gradient(135deg, #0056b3, #003d80);
color: white;
box-shadow: 0px 3px 6px rgba(0, 86, 179, 0.4);
}
.save-button:hover {
background: linear-gradient(135deg, #004494, #002e66);
box-shadow: 0px 5px 10px rgba(0, 86, 179, 0.6);
transform: scale(1.05);
}
.cancel-button {
background: linear-gradient(135deg, #6c757d, #495057);
color: white;
}
.cancel-button:hover {
background: linear-gradient(135deg, #495057, #343a40);
transform: scale(1.05);
}
</style>
<script>
// Function to set today's date as the default value in the date picker
function setDefaultDate() {
const today = new Date();
const yyyy = today.getFullYear();
const mm = String(today.getMonth() + 1).padStart(2, '0'); // Months start from 0
const dd = String(today.getDate()).padStart(2, '0');
document.getElementById("date").value = `${yyyy}-${mm}-${dd}`;
}
function saveData() {
const date = document.getElementById("date").value;
const content = document.getElementById("content").value;
if (!date || !content) {
alert("請輸入日期與內容！");
return;
}
google.script.run.saveData(date, content);
google.script.host.close();
}
// Set default date when the modal loads
window.onload = setDefaultDate;
</script>
</head>
<body>
<div class="container">
<h3>新增更新</h3>
<div class="form-group">
<label for="date">日期：</label>
<input type="date" id="date" class="form-control" required>
</div>
<div class="form-group">
<label for="content">內容：</label>
<textarea id="content" class="form-control" rows="4" required></textarea>
</div>
<div class="button-container">
<button class="save-button" onclick="saveData()">儲存</button>
<button class="cancel-button" onclick="google.script.host.close()">取消</button>
</div>
</div>
</body>
</html>
BrowseModal.html
<!DOCTYPE html>
<html>
<head>
<!-- Include Bootstrap CSS -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.2/dist/css/bootstrap.min.css" rel="stylesheet">
<!-- Include DataTables CSS and JS -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>
<script>
$(document).ready(function() {
$('#dataTable').DataTable({
paging: true,
searching: true,
info: false,
order: [[0, "desc"]],
language: {
"processing": "處理中...",
"search": "搜尋：",
"lengthMenu": "顯示 _MENU_ 筆資料",
"info": "顯示第 _START_ 至 _END_ 筆，共 _TOTAL_ 筆",
"infoEmpty": "沒有資料可顯示",
"infoFiltered": "(從 _MAX_ 筆資料篩選)",
"loadingRecords": "載入中...",
"zeroRecords": "沒有符合的資料",
"paginate": {
"first": "首頁",
"last": "末頁",
"next": "下一頁",
"previous": "上一頁"
}
}
});
// Edit function
$('.edit-icon').click(function() {
const row = $(this).closest('tr');
const dateCell = row.find('td:first');
const contentCell = row.find('td:nth-child(2)');
const editButton = $(this);
const checkButton = row.find('.check-icon');
// Store original values
row.data('originalDate', dateCell.text());
row.data('originalContent', contentCell.text());
// Make fields editable
dateCell.html(`<input type="date" class="form-control" value="${dateCell.text().replace(/\//g, '-')}">`);
contentCell.html(`<input type="text" class="form-control" value="${contentCell.text()}">`);
editButton.hide();
checkButton.show();
});
// Save edited data
$('.check-icon').click(function() {
const row = $(this).closest('tr');
const originalDate = row.data('originalDate');
const originalContent = row.data('originalContent');
const dateInput = row.find('td:first input').val();
const contentInput = row.find('td:nth-child(2) input').val();
const checkButton = $(this);
const editButton = row.find('.edit-icon');
if (!dateInput || !contentInput) {
alert("日期與內容不能為空！");
return;
}
google.script.run.withSuccessHandler(() => {
row.find('td:first').text(dateInput.replace(/-/g, '/'));
row.find('td:nth-child(2)').text(contentInput);
checkButton.hide();
editButton.show();
}).updateRow(originalDate, originalContent, dateInput, contentInput);
});
// Delete function
$('.delete-icon').click(function() {
const row = $(this).closest('tr');
const date = row.find('td:first').text();
const content = row.find('td:nth-child(2)').text();
if (confirm("確定要刪除此記錄嗎？")) {
google.script.run.withSuccessHandler(() => {
row.fadeOut(300, function() { $(this).remove(); });
}).deleteRow(date, content);
}
});
});
</script>
<style>
.icon {
cursor: pointer;
font-size: 16px;
transition: 0.3s;
margin-right: 8px;
}
.edit-icon { color: blue; }
.edit-icon:hover { color: darkblue; transform: scale(1.2); }
.check-icon { color: green; display: none; }
.check-icon:hover { color: darkgreen; transform: scale(1.2); }
.delete-icon { color: red; }
.delete-icon:hover { color: darkred; transform: scale(1.2); }
</style>
</head>
<body>
<div class="container mt-3">
<h3>更新記錄</h3>
<table id="dataTable" class="table table-striped">
<thead>
<tr>
<th>日期</th>
<th>內容</th>
<th>操作</th>
</tr>
</thead>
<tbody>
<? for (var i = data.length - 1; i >= 0; i--) { ?>
<tr>
<td><?= data[i][0] ?></td>
<td><?= data[i][1] ?></td>
<td>
<span class="icon edit-icon">✏️</span>
<span class="icon check-icon">✔️</span>
<span class="icon delete-icon">🗑</span>
</td>
</tr>
<? } ?>
</tbody>
</table>
<button class="btn btn-secondary" onclick="google.script.host.close()">關閉</button>
</div>
</body>
</html>