2-7 查詢畫面
2-8 密碼查詢
一、路由
doGet(e)
Route.path("form", form);
二、程式
program.gs
function form(e) {
var formData = {};
formData.webTitle = SCRIPT_PROP.getProperty("webTitle");//網站標題
formData.link = ScriptApp.getService().getUrl() ;
formData.adminEmail = SCRIPT_PROP.getProperty("adminEmail");//管理員email
formData.userEmail = Session.getActiveUser().getEmail();//訪客email，只能抓到擁有者
return render("form",{formData:formData});
}
三、樣板
form.html
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css" integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS" crossorigin="anonymous">
<title><? formData.webTitle ?></title>
</head>
<body>
<div class="container mt-3">
<div id="search" class="d-none">
<span>資料搜尋中....<img src="https://i.imgur.com/pm9SKUg.gif"></span>
</div>
<div id="main">
<h1 class="text-center mb-3"><?= formData.webTitle; ?></h1>
<form action="" id="myForm" class="">
<div class="row">
<div class="col-sm-12">
<div class="form-group">
<label>密碼<span class="text-danger">*</span></label>
<input type="password" class="form-control" name="pass" id="pass" value="">
</div>
</div>
<!--送出-->
<div class="col-sm-12">
<button id="submit" type="submit" class="btn btn-primary btn-block">送出</button>
</div>
</div>
</form>
</div>
</div>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.6/umd/popper.min.js" integrity="sha384-wHAiFfRlMFy6i5SRaxvfOCifBUQy1xHdJ/yoi7FRNXMRBu5WHdZYu1hA6ZOblgut" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/js/bootstrap.min.js" integrity="sha384-B0UglyR+jN6CkvvICOB2joaf5I4l3gm9GU6Hc1og6Ls7i6U/mkkaduKaBhlAXv9k" crossorigin="anonymous"></script>
<style>
.error{
color:red;
}
</style>
<script src="https://cdn.jsdelivr.net/npm/jquery-validation@1.19.1/dist/jquery.validate.min.js"></script>
<!-- 調用函式 -->
<script>
$(function(){
$("#myForm").validate({
submitHandler: function(form) {
insertDate();
},
rules: {
'pass': { required: true }
},
messages: {
'pass': { required: '必填'}
}
});
});
function insertDate(){
var formData = {};
formData.pass = document.getElementById("pass").value;
//樣板執行 函式 apps script function
google.script.run.withSuccessHandler(onSuccess).formDataInsert(formData);
document.getElementById('search').setAttribute("class", "");
document.getElementById('main').setAttribute("class", "d-none");
}
function onSuccess(html) {
var div = document.getElementById('search');
div.innerHTML = html;
}
</script>
</body>
</html>
四、程式
program.gs
/*==============================================
從form.html 樣板而來
回傳html 至 樣板
==============================================*/
function formDataInsert(formData){
//取得檔案
var ss = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("id"));
var ws = ss.getSheetByName("成績表");
var mainTitle = ws.getRange(1, 1, 1, ws.getLastColumn()).getValues()[0];//取得成績表中文欄名
//判斷密碼是否正確
if(formData.pass != SCRIPT_PROP.getProperty("pass")){
var mains = []; //
}else{
var mains = ws.getSheetValues(2,1,ws.getLastRow()-1,ws.getLastColumn()); //
}
formData.webTitle = SCRIPT_PROP.getProperty("webTitle");//網站標題
formData.link = ScriptApp.getService().getUrl() ;
//顯示查詢結果
var all_search = HtmlService.createTemplateFromFile("all_search");
all_search.mainTitle = mainTitle;
all_search.mains = mains;
all_search.formData = formData;
var html = all_search.evaluate().getContent();//組合樣板與變數
return html;
}
五、樣板
all_search.html
<? if(mains.length == 0){ ?>
<div class="row">
<div class="col-sm-12">
<h4 class="mt-3 mb-3 text-center">密碼不符</h4>
<a href="<?= formData.link + "?op=form" ?>" target="_blank" class="btn btn-primary btn-block">繼續查詢</a>
</div>
</div>
<? }else {?>
<div class="row mb-5">
<div class="col-sm-12">
<h4 class="mt-3 mb-3 text-center"><?= formData.webTitle; ?></h4>
</div>
<div class="col-sm-12">
<div id="" class="table-responsive mt-3">
<table class="table table-striped table-bordered table-hover">
<thead>
<tr>
<? for(i in mainTitle){ ?>
<?if(mainTitle[i] != "認證碼"){ ?>
<th class="text-center"><?= mainTitle[i] ?></th>
<? } ?>
<? } ?>
</tr>
</thead>
<tbody id="">
<? for(i in mains){ ?>
<tr>
<? for(j in mainTitle){ ?>
<?if(mainTitle[j] != "認證碼"){ ?>
<td class="text-center"><?= mains[i][j] ?></td>
<? } ?>
<? } ?>
</tr>
<? } ?>
</tbody>
</table>
</div>
<a href="<?= formData.link + "?op=form" ?>" target="_blank" class="btn btn-primary btn-block">繼續查詢</a>
</div>
</div>
<? } ?>