Google sheet Apps script Webapp – Project đăng nhập, Lập danh sách, chấm điểm thí sinh
Giaoan.link chia sẻ đến các bạn một project mới khá hây về Google sheet Apps script Webapp – Project đăng nhập, Lập danh sách, chấm điểm thí sinh. Đây là project kết hợp Login Form và CRUD webapp để tạo ra một ứng dụng mới với các chức năng:
- Đăng nhập dành cho Admin: Lập danh sách, quản lý thông tin.
- Đăng nhập dành cho các giá khảo: Từng giám khảo chấm điểm độc lập.
Dưới đây là video hướng dẫn cùng code cơ bản trong trang lập danh sách. Bạn xem video sẽ biết cách cấu hình cho các Giám khảo chấm điểm.
Phần code và thực hiện Login form, bạn xem video và code ở đây
Mã trang “Code.gs”
//CONSTANTS
const SPREADSHEETID = "1E_lIdvPHLyjSmzt0VzLJMIWWgIgfRpB7qjVAG8nXOHw";
const DATARANGE = "Data!A2:J";
const DATASHEET = "Data";
const DATASHEETID = "0";
const LASTCOL = "J";
const IDRANGE = "Data!A2:A";
//Display HTML page
function doGet(request) {
let html = HtmlService.createTemplateFromFile('Index').evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
let htmlOutput = HtmlService.createHtmlOutput(html).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');
return htmlOutput;
}
//PROCESS SUBMITTED FORM DATA
function processForm(formObject) {
if (formObject.recId && checkId(formObject.recId)) {
const values = [[
formObject.recId,
formObject.mathisinh,
formObject.tenthisinh,
formObject.diachi,
formObject.sodienthoai,
formObject.diemgiamkhao1,
formObject.ghichugiamkhao1,
formObject.diemgiamkhao2,
formObject.ghichugiamkhao2,
new Date().toLocaleString()
]];
const updateRange = getRangeById(formObject.recId);
//Update the record
updateRecord(values, updateRange);
} else {
//Prepare new row of data
let values = [[
generateUniqueId(),
formObject.mathisinh,
formObject.tenthisinh,
formObject.diachi,
formObject.sodienthoai,
formObject.diemgiamkhao1,
formObject.ghichugiamkhao1,
formObject.diemgiamkhao2,
formObject.ghichugiamkhao2,
new Date().toLocaleString()
]];
//Create new record
createRecord(values);
}
//Return the last 10 records
return getLastTenRecords();
}
/**
* CREATE RECORD
* REF:
* https://developers.google.com/sheets/api/guides/values#append_values
*/
function createRecord(values) {
try {
let valueRange = Sheets.newRowData();
valueRange.values = values;
let appendRequest = Sheets.newAppendCellsRequest();
appendRequest.sheetId = SPREADSHEETID;
appendRequest.rows = valueRange;
Sheets.Spreadsheets.Values.append(valueRange, SPREADSHEETID, DATARANGE, { valueInputOption: "RAW" });
} catch (err) {
console.log('Failed with error %s', err.message);
}
}
/**
* READ RECORD
* REF:
* https://developers.google.com/sheets/api/guides/values#read
*/
function readRecord(range) {
try {
let result = Sheets.Spreadsheets.Values.get(SPREADSHEETID, range);
return result.values;
} catch (err) {
console.log('Failed with error %s', err.message);
}
}
/**
* UPDATE RECORD
* REF:
* https://developers.google.com/sheets/api/guides/values#write_to_a_single_range
*/
function updateRecord(values, updateRange) {
try {
let valueRange = Sheets.newValueRange();
valueRange.values = values;
Sheets.Spreadsheets.Values.update(valueRange, SPREADSHEETID, updateRange, { valueInputOption: "RAW" });
} catch (err) {
console.log('Failed with error %s', err.message);
}
}
/**
* DELETE RECORD
* Ref:
* https://developers.google.com/sheets/api/guides/batchupdate
* https://developers.google.com/sheets/api/samples/rowcolumn#delete_rows_or_columns
*/
function deleteRecord(id) {
const rowToDelete = getRowIndexById(id);
const deleteRequest = {
"deleteDimension": {
"range": {
"sheetId": DATASHEETID,
"dimension": "ROWS",
"startIndex": rowToDelete,
"endIndex": rowToDelete + 1
}
}
};
Sheets.Spreadsheets.batchUpdate({ "requests": [deleteRequest] }, SPREADSHEETID);
return getLastTenRecords();
}
/**
* RETURN LAST 10 RECORDS IN THE SHEET
*/
function getLastTenRecords() {
let lastRow = readRecord(DATARANGE).length + 1;
let startRow = lastRow - 9;
if (startRow < 2) { //If less than 10 records, eleminate the header row and start from second row
startRow = 2;
}
let range = DATASHEET + "!A" + startRow + ":" + LASTCOL + lastRow;
let lastTenRecords = readRecord(range);
Logger.log(lastTenRecords);
return lastTenRecords;
}
//GET ALL RECORDS
function getAllRecords() {
const allRecords = readRecord(DATARANGE);
return allRecords;
}
//GET RECORD FOR THE GIVEN ID
function getRecordById(id) {
if (!id || !checkId(id)) {
return null;
}
const range = getRangeById(id);
if (!range) {
return null;
}
const result = readRecord(range);
return result;
}
function getRowIndexById(id) {
if (!id) {
throw new Error('Invalid ID');
}
const idList = readRecord(IDRANGE);
for (var i = 0; i < idList.length; i++) {
if (id == idList[i][0]) {
var rowIndex = parseInt(i + 1);
return rowIndex;
}
}
}
//VALIDATE ID
function checkId(id) {
const idList = readRecord(IDRANGE).flat();
return idList.includes(id);
}
//GET DATA RANGE IN A1 NOTATION FOR GIVEN ID
function getRangeById(id) {
if (!id) {
return null;
}
const idList = readRecord(IDRANGE);
const rowIndex = idList.findIndex(item => item[0] === id);
if (rowIndex === -1) {
return null;
}
const range = `Data!A${rowIndex + 2}:${LASTCOL}${rowIndex + 2}`;
return range;
}
//INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
//GENERATE UNIQUE ID
function generateUniqueId() {
let id = Utilities.getUuid();
return id;
}
function getCountryList() {
countryList = readRecord(DROPDOWNRANGE);
return countryList;
}
//SEARCH RECORDS
function searchRecords(formObject) {
let result = [];
try {
if (formObject.searchText) {//Execute if form passes search text
const data = readRecord(DATARANGE);
const searchText = formObject.searchText;
// Loop through each row and column to search for matches
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j++) {
const cellValue = data[i][j];
if (cellValue.toLowerCase().includes(searchText.toLowerCase())) {
result.push(data[i]);
break; // Stop searching for other matches in this row
}
}
}
}
} catch (err) {
console.log('Failed with error %s', err.message);
}
return result;
}
Mã trang “index.html”
<!DOCTYPE html>
<html>
<head>
<title>Nhập Thông tin Thí sinh</title>
<?!= include('JavaScript'); ?> <!-- See JavaScript.html file -->
<?!= include('CSS'); ?> <!-- See JavaScript.html file -->
</head>
<body>
<div class="container">
<div class="row">
<div class="col-lg-5">
<?!= include('FormProductDetails'); ?>
</div>
<div class="col-lg-7">
<?!= include('DataTable'); ?>
</div>
</div>
</div>
<?!= include('SpinnerModal'); ?>
</body>
</html>Mã trang “DataTable.html”
<br>
<div class="h4 form-check-inline" style="height: 30px; color:red;">DANH SÁCH THÍ SINH</div>
<hr>
<!-- SEARCH FORM-->
<form id="search-form" class="form-inline" onsubmit="handleSearchForm(this)">
<div class="input-group">
<input type="text" class="form-control form-control-sm mb-2" id="searchText" name="searchText" placeholder="Gõ Mã Thí sinh..." required>
<button type="submit" class="btn btn-sm btn-primary mb-2">Tìm kiếm</button>
</div>
</form>
<!-- END - SEARCH FORM-->
<div class="table-responsive">
<div id="dataTable">
<!-- The Data Table is inserted here by JavaScript -->
</div>
</div>
<button type="button" class="btn btn-success btn-sm" onclick="getAllRecords()">Hiển Thị Tất Cả</button>
<script>
let loading = "Đang lấy dữ liệu...";
document.querySelector('#result').innerHTML=loading;
function onSuccess(response){
let result = "<span>"+response+"</span>";
document.querySelector('#result').innerHTML=result;
}
google.script.run.withSuccessHandler(onSuccess)
.getDatacell();
</script>
Mã trang “JavaScript.html”
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/js/bootstrap.bundle.min.js"
integrity="sha384-ENjdO4Dr2bkBIFxQpeoTz1HIcje39Wm4jDKdf19U8gI4ddQ3GYNS7NTKfAdVQSZe" crossorigin="anonymous"></script>
<script>
// Prevent forms from submitting.
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener("load", functionInit, true);
//INITIALIZE FUNCTIONS ONLOAD
function functionInit(){
$('#spinnerModal').modal('show');
preventFormSubmit();
getLastTenRows();
createCountryDropdown();
};
//HANDLE FORM SUBMISSION
function handleFormSubmit(formObject) {
$('#spinnerModal').modal('show');
google.script.run.withSuccessHandler(createTable).processForm(formObject);
document.getElementById("ProductDetails").reset();
}
function deleteRecord(el) {
var result = confirm("Bạn chắc xóa?");
if (result) {
$('#spinnerModal').modal('show');
var recordId = el.parentNode.parentNode.cells[2].innerHTML;
google.script.run.withSuccessHandler(createTable).deleteRecord(recordId);
document.getElementById("ProductDetails").reset();
}
}
//GET LAST 10 ROWS
function getLastTenRows (){
google.script.run.withSuccessHandler(createTable).getLastTenRecords();
}
function editRecord(el){
$('#spinnerModal').modal('show');
let id = el.parentNode.parentNode.cells[2].innerHTML;
google.script.run.withSuccessHandler(populateForm).getRecordById(id);
}
function populateForm(data){
$('#spinnerModal').modal('hide');
document.getElementById('recId').value = data[0][0];
document.getElementById('mathisinh').value = data[0][1];
document.getElementById('tenthisinh').value = data[0][2];
document.getElementById('diachi').value = data[0][3];
document.getElementById('sodienthoai').value = data[0][4];
document.getElementById('diemgiamkhao1').value = data[0][5];
document.getElementById('ghichugiamkhao1').value = data[0][6];
document.getElementById('diemgiamkhao2').value = data[0][7];
document.getElementById('ghichugiamkhao2').value = data[0][8];
document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Cập nhật cho ID: ["+data[0][0]+"]</div>";
}
//CREATE THE DATA TABLE
function createTable(dataArray) {
$('#spinnerModal').modal('hide');
if (dataArray && dataArray.length) {
var result =
"<table class='table table-sm' style='font-size:0.8em'>" +
"<thead style='white-space: nowrap'>" +
"<tr>" +
"<th scope='col' style='text-align: center;background-color:#fffd51;'>Delete</th>" +
"<th scope='col'style='text-align: center;background-color:#fffd51;'>Edit</th>" +
"<th scope='col' style='display:none;'>ID</th>" + // Hide the ID column header
"<th scope='col'style='text-align: center;background-color:#fffd51;'>Mã Thí sinh</th>" +
"<th scope='col'style='text-align: center;background-color:#fffd51;'>Tên Thí sinh</th>" +
"<th scope='col'style='text-align: center;background-color:#fffd51;'>Địa chỉ</th>" +
"<th scope='col'style='text-align: center;background-color:#fffd51;'>Số Điện thoại</th>" +
"<th scope='col' style='text-align: center;background-color:#fffd51;'>Điểm Giám khảo 1</th>" +
"<th scope='col' style='text-align: center;background-color:#fffd51;'>Ghi chú Giám khảo 1</th>" +
"<th scope='col' style='text-align: center;background-color:#fffd51;'>Điểm Giám khảo 2</th>" +
"<th scope='col' style='text-align: center; background-color:#fffd51;'>Ghi chú Giám khảo 2</th>" +
"<th scope='col' style='display:none;background-color:#fffd51;'>Last Update</th>" +
"</tr>" +
"</thead>";
for (var i = 0; i < dataArray.length; i++) {
result += "<tr>";
result +=
"<td style='background-color:#eeeeee;'> <button type='button' class='btn btn-danger btn-custom deleteBtn' onclick='deleteRecord(this);'>Delete</button></td>";
result +=
"<td style='background-color:#eeeeee; text-align: center; padding: 2px 2px;'><button type='button' class='btn btn-warning btn-custom editBtn' onclick='editRecord(this);'>Edit</button></td>";
for (var j = 0; j < dataArray[i].length; j++) {
if (j === 0) {
result +=
"<td style='display:none;'>" + dataArray[i][j] + "</td>"; // Hide the ID column data
}else if(j===1){
result +=
"<td style='text-align: center; background-color:#ffffff;'>" + dataArray[i][j] + "</td>";
}else if(j===2){
result +=
"<td style='text-align: center; background-color:#eeeeee;'>" + dataArray[i][j] + "</td>";
}else if(j===3){
result +=
"<td style='ext-align: left;background-color:#ffffff;'>" + dataArray[i][j] + "</td>";
} else if(j===4){
result +=
"<td style='text-align: center; background-color:#eeeeee;'>" + dataArray[i][j] + "</td>";
}else if(j===5){
result +=
"<td style='text-align: center; background-color:#ffffff;color: red; font-weight: bold;''>" + dataArray[i][j] + "</td>";
}else if(j===6){
result +=
"<td style='text-align: left;background-color:#eeeeee;'>" + dataArray[i][j] + "</td>";
}else if(j===7){
result +=
"<td style='text-align:center; background-color:#ffffff; color: red; font-weight: bold;'>" + dataArray[i][j] + "</td>"; //
}else if(j===8){
result +=
"<td style='text-align: left;background-color:#eeeeee;'>" + dataArray[i][j] + "</td>";
}else if(j===9){
result +=
"<td style='display: none; text-align: left;background-color:#eeeeee;'>" + dataArray[i][j] + "</td>";
}else {
result += "<td style='text-align: center;''>" + dataArray[i][j] + "</td>";
}
}
result += "</tr>";
}
result += "</table>";
var div = document.getElementById("dataTable");
div.innerHTML = result;
document.getElementById("message").innerHTML = "";
} else {
var div = document.getElementById("dataTable");
div.innerHTML = "Không có dữ liệu nào được tìm thấy!";
}
}
//SEARCH RECORDS
function handleSearchForm(formObject) {
$('#spinnerModal').modal('show');
google.script.run.withSuccessHandler(createTable).searchRecords(formObject);
document.getElementById("search-form").reset();
}
function getAllRecords(){
$('#spinnerModal').modal('show');
google.script.run.withSuccessHandler(createTable).getAllRecords();
}
</script>Mã trang “FormProductDetails.html”
<form id="ProductDetails" onsubmit="handleFormSubmit(this)">
<br>
<div class="h4 form-check-inline" style="height: 30px; color: blue;">NHẬP THÔNG TIN THÍ SINH</div>
<hr>
<div id="message"></div>
<input type="text" id="recId" name="recId" value="" style="display: none">
<div class="mb-1">
<div class="form-check form-check-inline" >
<label style="font-weight: bold;" for="mathisinh" class="form-label">Mã thí sinh:</label>
<input style="width:100px" type="text" id="mathisinh" name="mathisinh" class="form-control form-control-sm" required>
</div>
<div class="form-check form-check-inline">
<label style="font-weight: bold;" for="tenthisinh" class="form-label">Tên thí sinh:</label>
<input style="width:340px" type="text" id="tenthisinh" name="tenthisinh" class="form-control form-control-sm" required>
</div>
</div>
<div class="mb-1">
<div class="form-check form-check-inline">
<label style="font-weight: bold;" for="sodienthoai" class="form-label">Số Phone:</label>
<input style="width:100px" type="text" id="sodienthoai" name="sodienthoai" class="form-control form-control-sm" required>
</div>
<div class="form-check form-check-inline">
<label style="font-weight: bold;" for="diachi" class="form-label">Địa chỉ:</label>
<input style="width:340px" type="text" id="diachi" name="diachi" class="form-control form-control-sm" required>
</div>
</div>
<div class="mb-1">
<label style="font-weight: bold;font-style: italic; color: red; " for="diemgiamkhao1" for="diemgiamkhao1" class="form-label">Điểm Giám khảo 1:</label>
<input type="text" id="diemgiamkhao1" name="diemgiamkhao1" class="form-control form-control-sm">
</div>
<div class="mb-1">
<label style="font-weight: bold;font-style: italic; color: red; " for="ghichugiamkhao1" class="form-label">Ghi chú Giám khảo1:</label>
<textarea id="ghichugiamkhao1" name="ghichugiamkhao1" class="form-control form-control-sm" rows="2"></textarea>
</div>
<div class="mb-1">
<label style="font-weight: bold;font-style: italic; color: red; " for="diemgiamkhao1" class="form-label">Điểm Giám khảo 2:</label>
<input type="text" id="diemgiamkhao2" name="diemgiamkhao2" class="form-control form-control-sm">
</div>
<div class="mb-1">
<label style="font-weight: bold;font-style: italic; color: red; " for="ghichugiamkhao2" class="form-label">Ghi chú Giám khảo 2:</label>
<textarea id="ghichugiamkhao2" name="ghichugiamkhao2" class="form-control form-control-sm" rows="2"></textarea>
</div>
<button type="submit" class="btn btn-primary">Lưu</button>
</form>Mã trang “SpinnerModal.html”
<div class="modal fade" id="spinnerModal" tabindex="-1" role="dialog" aria-labelledby="spinnerModalLabel"
aria-hidden="true">
<div class="modal-dialog modal-dialog-centered" role="document">
<div class="modal-content">
<div class="modal-body text-center">
<div class="spinner-border" role="status">
<span class="visually-hidden">Loading...</span>
</div>
<p class="mt-3">Loading...</p>
</div>
</div>
</div>
</div>Mã trang “CSS.html”
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-KK94CHFLLe+nY2dmCWGMq91rCGa5gtU4mk92HdvYe+M/SXH301p5ILy+dN9+nJOZ" crossorigin="anonymous">
<style>
.btn-custom {
font-size: 15px;
padding: 2px 5px;
}
</style>