1. สร้าง Google Sheet
2. ไปที่ส่วนขยาย > Apps Script
3. สร้างไฟล์ Code.gs
function doGet() {
return HtmlService.createTemplateFromFile('index').evaluate()
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}
function include(filename){
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function getData(){
const jsData = MyIMCLibrary.createMyJSONdata('Customers','A2:G2','A5:G')
console.log(jsData)
return jsData
}
function editCustomerById(id,customerInfo){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Customers");
const custIds = ws.getRange(5,1,ws.getLastRow()-4,1).getDisplayValues().map(r => r[0].toString().toLowerCase());
const posIndex = custIds.indexOf(id.toString().toLowerCase());
const rowNumber = posIndex === -1 ? 0 : posIndex + 5
ws.getRange(rowNumber,2,1,6).setValues([[
customerInfo.firstName,
customerInfo.lastName,
customerInfo.diagGroup,
customerInfo.admitDate,
customerInfo.biRegist,
customerInfo.biResult
]])
return true;
};
function deleteRecord(props){
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName('Customers')
const idCellMatched = ws.getRange("A5:A").createTextFinder(props.id).matchEntireCell(true).matchCase(true).findNext()
if(idCellMatched === null) throw new Error("No matching record")
const recordRowNumber = idCellMatched.getRow()
ws.deleteRow(recordRowNumber)
return true
}
function addRecord(testName,lastName,diagGroup,admitDate,biRegist,biResult){
const ss = SpreadsheetApp.getActiveSpreadsheet()
const ws = ss.getSheetByName('Customers')
const newId = MyIMCLibrary.createNewId()
ws.appendRow([
newId,
testName,
lastName,
diagGroup,
admitDate,
biRegist,
biResult
])
console.log(newId)
return newId
}
4. สร้างไฟล์ index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://unpkg.com/tabulator-tables@5.2.3/dist/css/tabulator.min.css" rel="stylesheet">
<?!= include("css"); ?>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-0evHe/X+R7YkIZDRvuzKMRqM+OrBnVFBL6DOitfPri4tjfHxaWutUpFmBp4vmVor" crossorigin="anonymous">
</head>
<body>
<!-- LOADING ******************************** -->
<div id="loading" class="d-flex flex-column justify-content-center align-items-center invisible">
<div class="spinner-grow text-primary" role="status" style="width: 5rem; height: 5rem;">
<span class="visually-hidden">Loading...</span>
</div>
</div>
<div class="container" id="app">
<center><h1 class="mt-3">โปรแกรมบันทึกข้อมูลผู้ป่วย</h1></center>
<div class="row">
<div class="col mt-3">
<label class="form-label">Search by Name</label>
<input type="text" class="form-control" id="search-input" >
</div>
<div class="col mt-3">
<label class="form-label">Diagnosis</label>
<select class="form-select" id="filter-diagGroup-input">
<option value="" selected>ทั้งหมด</option>
<option value="Stroke">Stroke</option>
<option value="TBI">TBI</option>
<option value="SCI">SCI</option>
<option value="Fx Hip">Fx Hip</option>
</select>
</div>
</div>
<button type="button" class="btn btn-primary mt-4" id="open-form-record-button">Register</button>
<button type="button" class="btn btn-primary mt-4" id="download-excel-button">Download</button>
<div class=" mt-2" id="data-table"></div>
<?!= include("addForm"); ?>
<?!= include("editForm"); ?>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.0-beta1/dist/js/bootstrap.bundle.min.js"></script>
<script type="text/javascript" src="https://unpkg.com/tabulator-tables@5.2.3/dist/js/tabulator.min.js"></script>
<script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script>
<script type="text/javascript" src="https://oss.sheetjs.com/sheetjs/xlsx.full.min.js"></script>
<script type="text/javascript" src="https://firebasestorage.googleapis.com/v0/b/free-program.appspot.com/o/CRUD%20%20BTS%20ver%2010%20-%20%E0%B9%81%E0%B8%88%E0%B8%81%E0%B8%9F%E0%B8%A3%E0%B8%B5.txt?alt=media&token=aca7f7fe-b122-4635-8ba0-53d33fada65a"></script>
<script>
const elements = {}
function loadingStart(){
document.getElementById("loading").classList.remove("invisible");
};
function loadingEnd(){
document.getElementById("loading").classList.add("invisible");
};
function pageLoad(){
loadingStart()
loadData()
};
// **********************************************************************
// START LOAD TABULATOR *************************************************
function loadData(){
google.script.run.withSuccessHandler((jsData)=>{
elements.table = new Tabulator("#data-table", {
height:"100%",
data: jsData,
reactiveData:true,
layout:"fitColumns",
pagination:"local",
paginationSize:10,
paginationSizeSelector:[ 10, 15, 20],
movableColumns:true,
paginationCounter:"rows",
index:"customerId",
columns:[
{title:"Customer ID", field:"customerId",visible:true, download:true}, // ตรง field ตัวเลขต้องตรงกัน
{title:"First Name", field:"firstName"},
{title:"Last Name", field:"lastName"},
{title:"Diagnosis", field:"diagGroup", headerFilter:true },
{title:"Admission Date", field:"admitDate",headerFilter:true},
{title:"BI Score", field:"biRegist"},
{title:"Result", field:"biResult"},
],
})
// DELETE RECORD
elements.table.on("rowDeleted", function(row){
//row - row component
console.log(row)
console.log(row._row.data.customerId) // id ที่ต้องการเข้าถึง
const id = row._row.data.customerId
google.script.run
.withSuccessHandler(()=>{
loadingEnd()
deleteSuccessAlert()
})
.withFailureHandler((er)=>{
})
.deleteRecord({id:id })
})
elements.table.on("rowClick", function(e, row){
const custId = row.getData().customerId
const firstName = row.getData().firstName
const lastName = row.getData().lastName
const diagGroup = row.getData().diagGroup
const admitDate = row.getData().admitDate
const biRegist = row.getData().biRegist
const biResult = row.getData().biResult
showModalEditForm(custId,firstName,lastName,diagGroup,admitDate,biRegist,biResult)
});
loadingEnd()
}).withFailureHandler((er)=>{}).getData()
};
function setHeaderFilterDiagGroup(e){
elements.table.setHeaderFilterValue("diagGroup", e.target.value)
};
function showModalEditForm(custId,firstName,lastName,diagGroup,admitDate,biRegist,biResult){
const myModalEditForm = new bootstrap.Modal(document.getElementById('myModal-edit-form'), {keyboard: false })
document.getElementById('customer_id_edit').value = custId
document.getElementById('first_name_edit').value = firstName
document.getElementById('last_name_edit').value = lastName
document.getElementById('diag_group_edit').value = diagGroup
document.getElementById('admit_date_edit').value = admitDate
document.getElementById('bi_regist_edit').value = biRegist
document.getElementById('bi_result_edit').value = biResult
myModalEditForm.show()
};
function openFormRecord(){
const myModalRegist = new bootstrap.Modal(document.getElementById('myModal-add-form'), {keyboard: false })
myModalRegist.show()
};
function downloadExcel(){
// loadingStart()
elements.table.download("xlsx", "data.xlsx", {sheetName:"รายชื่อผู้ป่วย IMC"},{
documentProcessing:function(workbook){
workbook.Props = {
Title: "SheetJS Tutorial",
Subject: "Test",
CreatedDate: new Date(2017,12,19)
};
return workbook;
}
})
};
function searchData(e){
elements.table.setFilter(
[[
{field:"firstName",type: "like",value: e.target.value},
{field:"lastName",type: "like",value: e.target.value},
]]
)
};
// ADD RECORD **************************************
function addRecord(){
loadingStart()
const firstName = document.getElementById('first_name_add').value
const lastName = document.getElementById('last_name_add').value
const diagGroup = document.getElementById('diag_group_add').value
const admitDate = document.getElementById('admit_date_add').value
const biRegist = document.getElementById('bi_regist_add').value
const biResult = document.getElementById('bi_result_add').value
google.script.run
.withSuccessHandler((newId)=>{
elements.table.addData([{customerId:newId, firstName:firstName , lastName:lastName , diagGroup:diagGroup, admitDate:admitDate , biRegist:biRegist, biResult:biResult } ], true);
document.getElementById('first_name_add').value = ""
document.getElementById('last_name_add').value = ""
document.getElementById('diag_group_add').value = ""
document.getElementById('admit_date_add').value = ""
document.getElementById('bi_regist_add').value = ""
document.getElementById('bi_result_add').value = ""
loadingEnd()
addCompleteAlert()
})
.withFailureHandler((er)=>{
console.log("Error Adding")
})
.addRecord(firstName,lastName,diagGroup,admitDate,biRegist,biResult)
};
// EDIT RECORD **********************************
function editRecord(){
loadingStart()
const custId = document.getElementById('customer_id_edit').value
const firstName = document.getElementById('first_name_edit').value
const lastName = document.getElementById('last_name_edit').value
const diagGroup = document.getElementById('diag_group_edit').value
const admitDate = document.getElementById('admit_date_edit').value
const biRegist = document.getElementById('bi_regist_edit').value
const biResult = document.getElementById('bi_result_edit').value
elements.table.updateData([{customerId:custId, firstName:firstName, lastName:lastName, diagGroup:diagGroup, admitDate:admitDate, biRegist:biRegist, biResult:biResult}]);
const customerInfo = {};
customerInfo.firstName = document.getElementById("first_name_edit").value;
customerInfo.lastName = document.getElementById("last_name_edit").value;
customerInfo.diagGroup = document.getElementById('diag_group_edit').value
customerInfo.admitDate = document.getElementById('admit_date_edit').value
customerInfo.biRegist = document.getElementById('bi_regist_edit').value
customerInfo.biResult = document.getElementById('bi_result_edit').value
const id = document.getElementById("customer_id_edit").value;
google.script.run.withSuccessHandler(function(res){
loadingEnd()
editCompleteAlert()
}).editCustomerById(id,customerInfo)
};
// DELETE RECORD **********************************
function deleteRecord(){
loadingStart()
const id = document.getElementById('customer_id_edit').value
elements.table.deleteRow(id)
};
// ALERT ***********************************
function emptyAlert(){
Swal.fire({
icon: 'error',
title: 'Oops...',
text: 'กรุณากรอกให้ครบทุกช่อง',
})
};
function deleteSuccessAlert(){
Swal.fire(
'Deleted!',
'success'
)
};
function confirmDeleteAlert(){
Swal.fire({
title: 'Are you sure?',
icon: 'warning',
showCancelButton: true,
confirmButtonText: 'Yes, delete it!'
}).then((result) => {
if (result.isConfirmed) {
deleteRecord();
}
})
};
function addCompleteAlert(){
Swal.fire(
'Added!',
'success'
)
};
function editCompleteAlert(){
Swal.fire(
'Edited!',
'success'
)
};
function clickEventHandler(e) {
if (e.target.matches("#open-form-record-button")){
openFormRecord();
}
if (e.target.matches("#add-record-button")){
if( document.getElementById("first_name_add").value == "" || document.getElementById("last_name_add").value == "" || document.getElementById("diag_group_add").value == "" || document.getElementById("admit_date_add").value == "" || document.getElementById("bi_regist_add").value == "" || document.getElementById("bi_result_add").value == ""){
emptyAlert()
} else {
addRecord(e);
}
}
if (e.target.matches("#edit-record-button")){
editRecord(e);
}
if (e.target.matches("#delete-record-button")){
confirmDeleteAlert()
}
if (e.target.matches("#download-excel-button")){
downloadExcel(e);
}
};
document.getElementById("app").addEventListener("click",clickEventHandler);
document.getElementById("app").addEventListener("input",inputEventHandler);
document.addEventListener('DOMContentLoaded',pageLoad)
</script>
</body>
</html>
5. สร้างไฟล์ css.gs
<!-- CUSTOM CSS -->
<style>
body {
font-family: "Sarabun"
}
.nav-link {
cursor:pointer;
}
#loading{
position: fixed;
top:0 ;
left:0;
z-index:10000;
width:100vw;
height:100vh;
background-color: rgba(255,255,255,0.9);
}
label {
display: block;
font: 1rem 'Fira Sans', sans-serif;
}
input,
label {
margin: .4rem 0;
}
.tabulator .tabulator-header .tabulator-col .tabulator-header-filter {
position: relative;
box-sizing: border-box;
margin-top: 2px;
width: 100%;
text-align: center;
display: none;
}
</style>
6. สร้างไฟล์ addForm.html
<!-- MODAL FORM ADD RECORD **************************************************** -->
<div class="modal" tabindex="-1" id="myModal-add-form" >
<div class="modal-dialog modal-lg">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">ลงทะเบียนผู้ป่วยรายใหม่</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<div class="row mt-2">
<div class="col">
<label class="form-label">ชื่อ</label>
<input type="text" class="form-control" id="first_name_add">
</div>
<div class="col">
<label class="form-label">นามสกุล</label>
<input type="text" class="form-control" id="last_name_add">
</div>
<div class="col">
<label class="form-label">กลุ่มโรค</label>
<select class="form-select" id="diag_group_add">
<option selected></option>
<option value="Stroke">Stroke</option>
<option value="TBI">TBI</option>
<option value="SCI">SCI</option>
<option value="Fx Hip">Fx Hip</option>
</select>
</div>
</div>
<div class="row mt-2">
<div class="col">
<label class="form-label">วันที่นอน รพ</label>
<input type="date" class="form-control" id="admit_date_add">
</div>
<div class="col">
<label class="form-label">คะแนน BI</label>
<input type="text" class="form-control" id="bi_regist_add">
</div>
<div class="col">
<label class="form-label">อาการ</label>
<select class="form-select" id="bi_result_add">
<option selected></option>
<option value="ดีขึ้น">ดีขึ้น</option>
<option value="เท่าเดิม">เท่าเดิม</option>
<option value="แย่ลง">แย่ลง</option>
</select>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-primary mt-4" id="add-record-button">Save</button>
</div>
</div>
</div>
</div>
7. สร้างไฟล์ editForm.html
<!-- MODAL FORM EDIT *********************************************************** -->
<div class="modal" tabindex="-1" id="myModal-edit-form" >
<div class="modal-dialog modal-lg">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">ข้อมูลผู้ป่วย</h5>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<label class="form-label">ID</label>
<input type="text" class="form-control" id="customer_id_edit" disabled>
<div class="row mt-2">
<div class="col">
<label class="form-label">ชื่อ</label>
<input type="text" class="form-control" id="first_name_edit">
</div>
<div class="col">
<label class="form-label">นามสกุล</label>
<input type="text" class="form-control" id="last_name_edit">
</div>
<div class="col">
<label class="form-label">กลุ่มโรค</label>
<select class="form-select" id="diag_group_edit">
<option selected></option>
<option value="Stroke">Stroke</option>
<option value="TBI">TBI</option>
<option value="SCI">SCI</option>
<option value="Fx Hip">Fx Hip</option>
</select>
</div>
</div>
<div class="row mt-2">
<div class="col">
<label class="form-label">วันที่นอน รพ</label>
<input type="text" class="form-control" id="admit_date_edit">
</div>
<div class="col">
<label class="form-label">คะแนน BI</label>
<input type="text" class="form-control" id="bi_regist_edit">
</div>
<div class="col">
<label class="form-label">อาการ</label>
<select class="form-select" id="bi_result_edit">
<option selected></option>
<option value="ดีขึ้น">ดีขึ้น</option>
<option value="เท่าเดิม">เท่าเดิม</option>
<option value="แย่ลง">แย่ลง</option>
</select>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-primary mt-4" data-bs-dismiss="modal" style="--bs-btn-padding-y: 0.4rem; --bs-btn-padding-x: 2rem; --bs-btn-font-size: 1rem;" id="edit-record-button"> Edit </button>
<button type="button" class="btn btn-danger mt-4" data-bs-dismiss="modal" id="delete-record-button">Delete</button>
</div>
</div>
</div>
</div>
ใส่รหัสสคริปต์: 1UZSdgjHwhKs7mUpgogBd6oT69JvDT0JbjZKPiGpPJjhrRcGu0DpOzFsD
9. สร้างและจัดการการทำให้ใช้งานได้
