1. ทำสำเนา Google sheet
- สร้างรายชื่อนักเรียนในชีตชื่อ user
- ทำสำเนาชีต ม.xx/xx ตั้งชื่อตามระดับชั้นในคอลัมน์ D ของชีต user
เช่น ม.1/1 โดยทำสำเนาเท่ากับจำนวนชั้นเรียนในชีต user - คลิกที่ช่อง A2 แก้ไขสูตร จาก ม.xx/xx เป็นชื่อชีต เช่น ม.1/1
3. สร้างไฟล์ code.gs
code.gs/** Source code by https://guruchian.blogspot.com **/
function doGet() {
return HtmlService.createTemplateFromFile('index')
.evaluate()
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setTitle('ระบบตรวจสอบผลการเรียนออนไลน์')
.setFaviconUrl('https://semicon.github.io/img/logoKC.png')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}
/** @Include Files */
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
/** @Get URL */
function getScriptURL() {
return ScriptApp.getService().getUrl();
}
/** Name the main sheet. **/
const ss = SpreadsheetApp.getActiveSpreadsheet();
let wsName = "user"
/** Check Login */
function checkLogin(username,pwd){
let userinfo = []
const data = ss.getSheetByName(wsName)
.getDataRange()
.getDisplayValues()
.slice(1)
.filter(r => r[0] !== "");
data.map(r => {
if(r[1] == username && r[0] == pwd){
userinfo = [r[1],r[2],r[3]]
}
}).join("")
if(userinfo == ''){
userinfo = 'null';
}
return userinfo;
}
/** Find the user ID and read the data from the sheet. **/
function checkUsername(user_id,sheetName) {
const ws = ss.getSheetByName(sheetName);
const data = ws.getDataRange().getDisplayValues()
const credit = data.filter(r=> r[0]== 'หน่วย')
const header = data.filter(r=> r[0]== 'เลขประจำตัว')
const grade = data.filter(r=> r[0]== user_id)
const result = {header,credit,grade}
return result
}
css.html <style> @import url('https://fonts.googleapis.com/css2?family=K2D&display=swap'); *{ margin:0; padding: 0; box-sizing: border-box; font-family: 'K2D', sans-serif; } html{ height: 100vh; } body{ background-color:#000; place-items: center; text-align: center; background: url("https://c4.wallpaperflare.com/wallpaper/114/760/992/abstract-hexagon-digital-art-pattern-wallpaper-preview.jpg") no-repeat center center fixed; -webkit-background-size: cover; -moz-background-size: cover; -o-background-size: cover; background-size: cover; } .header{ padding: 30px 30px 30px 30px; color: rgb(247, 233, 233); } .logo{ width:120px; height:auto; padding-bottom: 10px; } /** login form **/ .content{ margin-bottom: 50px; width: 330px; border-radius: 10px; padding: 30px 30px; box-shadow: -3px -3px 9px #00abf0ff, 3px 3px 7px #00abf0ff; } .content .text{ font-size: 25px; font-weight: 400; margin-bottom: 35px; color: rgb(247, 233, 233); } .content .field{ height: 50px; width: 100%; display: flex; position: relative; } .field input{ height: 100%; width: 100%; padding-left: 45px; font-size: 18px; outline: none; border: none; color: #e0d2d2; border: 1px solid rgba(255, 255, 255, 0.438); border-radius: 8px; background: rgba(105, 105, 105, 0); } .field input::placeholder{ color: #e0d2d2a6; } .field:nth-child(2){ margin-top: 20px; } .field span{ position: absolute; width: 50px; line-height: 50px; color: #00abf0ef; } .button{ margin: 25px 0 0 0; width: 100%; height: 50px; color: rgb(238, 226, 226); font-size: 18px; font-weight: 400; border: 2px solid rgba(255, 255, 255, 0.438); border-radius: 8px; background: rgba(105, 105, 105, 0); margin-top: 40px; outline: none; cursor: pointer; border-radius: 8px; } .button:hover, .icon-button span:hover{ background-color: #00abf08c; } .footer { position: block; font-size: 1rem; width: 100%; height: 50px; left: 0; bottom: 0; color: #00abf0; text-align: center; } .table{ width:60%; } /** Loader **/ #loading{ position :fixed; top:0; left:0; z-index:1000; background-color :rgba(0,0,0,0.65); height:100vh; width:100vw; } #circle { position: absolute; top: 50%; left: 50%; transform: translate(-50%,-50%); width: 100px; height: 100px; } .loader { width: calc(100% - 0px); height: calc(100% - 0px); border: 8px solid rgba(250,250,250,0); border-top: 8px solid #09f; border-radius: 50%; animation: rotate 5s linear infinite; } @keyframes rotate { 100% {transform: rotate(360deg);} } /** resize table**/ @media (max-width: 576px) { .table{ width:100%; } } </style>
index.html<!DOCTYPE html><html lang="en"><head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- animate.css@4.1.1 --> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/4.1.1/animate.min.css"> <!-- bootstrap@5.2.2 --> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css" rel="stylesheet"> <!-- font-awesome@6.2.0 --> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css"> <!-- jquery@3.6.0 --> <script src="https://code.jquery.com/jquery-3.6.0.js"></script> <!-- include css file --> <?!=include('css')?></head><body> <!-- Navbar --> <section id="navbar" style="display:none"> <nav class="pt-3 px-3 text-light d-flex flex-row-reverse"> <span class="btn text-light" id="logout" style ="font-size:1.25rem;"> ออกจากระบบ <i class="fa-solid fa-person-walking-dashed-line-arrow-right fa-lg"></i> </span> </nav> </section> <div class="container"> <!-- Header --> <div class="header"> <img src="https://www.logo-th.com/wp-content/uploads/2018/08/%E0%B8%AA%E0%B8%9E%E0%B8%90.png" class="logo"> <h1>ตรวจสอบผลการเรียน ภาคเรียนที่ 1/2565</h1> </div> <!-- Login Page --> <section id='login_page'> <div class="d-flex justify-content-center"> <div class="content"> <div class="text">เข้าสู่ระบบ</div> <form id="myform"> <div class="field"> <span class="fa fa-user"></span> <input id="user_name" type="text" placeholder="เลขประจำตัวนักเรียน" required> </div> <div class="field"> <span class="fa fa-lock"></span> <input id="pass" type="password" placeholder="เลขประชาชน 13 หลัก"> </div> <button class="button" id="btn-login">ลงชื่อเข้าใช้</button> </form> </div> </div> </section> <!-- Grade report Page --> <section id='report_page' style="display:none"> <div class="table mx-auto" id = "add-content"></div> </section> </div> <!-- Loader..... --> <div id="loading" class="d-flex justify-content-center align-items-center invisible"> <div id="circle"> <div class="loader"> <div class="loader"> <div class="loader"> <div class="loader"> </div> </div> </div> </div> </div> </div> <!-- Footer --> <div class="footer"> <p>Copyleft <img src="https://mirrors.creativecommons.org/presskit/icons/heart.red.png" style="width:32px"> 2022, by <a href="https://guruchian.blogspot.com/" target="_blank"> kruChian </a> </p> </div> <!-- -------- Main Script -------- --> <script> /*************** Login Script **********************/ $("#btn-login").click(function(){ event.preventDefault(); loadingStart(); const username = $("#user_name").val(); const pwd = $("#pass").val(); google.script.run.withSuccessHandler(function(output){ /******* Show notification when data is true. *******/ if(output != 'null'){ let user_id = output[0]; let sheetName = output[2]; //console.log(output[0],output[1],output[2]) $("#navbar").show(); $("#report_page").show(); $("#login_page").hide(); $("#myform").trigger("reset"); //console.log(user_id,sheetName) /***** Retrieve and display the student's grades table. **********/ google.script.run.withSuccessHandler(function(outputx){ //console.log(outputx) const x = outputx.grade[0].length-1 //console.log(x) const average = outputx.grade[0][x]; let result = "<h3 class = 'text-center text-light p-3'>"+ ""+outputx.grade[0][1]+" ระดับชั้น "+sheetName+ "</h3>"+ "<div class='mt-3'>"+ "<table id='myTable' class='table bg-secondary table-borderless' style='width:100%'>"+ "<thead>"+ "<tr class = 'text-center text-light' style='background-color:#0072a0'>"+ "<th scope='col' style='width:10%'>ที่</th>"+ "<th scope='col'>รายวิชา</th>"+ "<th scope='col'>หน่วยกิต</th>"+ "<th style='width:15%'>คะแนน</th>"+ "<th scope='col' style='width:15%'>เกรด</th>"+ "</tr>"+ "</thead>"; let count = 0 for(i = 3; i < outputx.grade[0].length-2; i+=2) { count++ let bgcolor = (count%2 == 0 ? '#e1f6ff' : '#fff') result += "<tbody>"+ "<tr class ='text-center' style ='background-color:"+bgcolor+"'>"+ "<th scope='row'>"+ count+ "</th>"+ "<td class ='text-start'>"+ outputx.header[0][i]+ "</td>"+ "<td>"+ outputx.credit[0][i]+ "</td>"+ "<td>"+ outputx.grade[0][i]+ "</td>"+ "<th scope='row'>"+ outputx.grade[0][i+1]+ "</th>"+ "</tr>"; } result += ""+ "<tr class = 'bg-secondary text-center text-light'>"+ "<td colspan='3' class ='text-end'>"+ "ระดับผลการเรียนเฉลี่ย"+ "</td>"+ "<th colspan='2' scope='row'>"+ average+ "</th>"+ "</tr></tbody>"+ "</table>"+ "</div>"; $('#add-content').html(result); loadingEnd(); Swal.fire({ title: 'ยินดีต้อนรับ !', html: '<h3>'+output[1]+'</h3>', showClass: { popup: 'animate__animated animate__fadeInDown' }, hideClass: { popup: 'animate__animated animate__fadeOutUp' } }); }).checkUsername(user_id,sheetName); } /******* Show notification when data is false. *******/ else if(output == 'null'){ loadingEnd() Swal.fire({ title: 'ขออภัย !', html: '<h3>ข้อมูลไม่ถูกต้อง</h3>', showClass: { popup: 'animate__animated animate__fadeInDown' }, hideClass: { popup: 'animate__animated animate__fadeOutUp' } }); $("#myform").trigger("reset"); } //console.log(username,pwd) }).checkLogin(username,pwd); }); /*************** Logout Script **********************/ $("#logout").click(function(){ google.script.run.withSuccessHandler(function(url){ window.open(url,'_top'); }).getScriptURL(); $("#navbar").hide(); loadingStart(); }); /*************** Loader Script **********************/ $(window).on('load', function() { $('#loading').addClass('invisible'); }); function loadingStart(){ $('#loading').removeClass('invisible'); } function loadingEnd(){ $('#loading').addClass('invisible'); } </script> <!-- /-------- Sweetaler 2011 --------/ --> <script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script></body></html>
Password: 00000
Ref. Login Form designed by https://www.foolishdeveloper.com/