Skip to content

Instantly share code, notes, and snippets.

@arpeggio068

arpeggio068/search_CRUD

Last active January 4, 2024 05:33
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save arpeggio068/b62cd2a1051a072ae65924699df091c4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save arpeggio068/b62cd2a1051a072ae65924699df091c4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
search_CRUD
//code.gs
const spreadsheetId = '1eFacH7dqvPoT-LzWXQVNmTvpyJJ-B88y-dNlzHiro0U';
const ss = SpreadsheetApp.openById(spreadsheetId);
function doGet(e) {
var tmp = HtmlService.createTemplateFromFile("index");
return tmp.evaluate()
.setTitle("ค้นหาง่ายๆ")
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function uniqueId() {
var uuid_array = [];
var dataSheet = ss.getSheetByName("data");
var getLastRow = dataSheet.getLastRow();
if(getLastRow > 1) {
var uuid_values = dataSheet.getRange(2, 1, getLastRow - 1, 1).getValues();
for(i = 0; i < uuid_values.length; i++){
uuid_array.push(uuid_values[i][0]);
}
var x_count = 0;
do {
var y = 'false';
var uuid_value = Utilities.getUuid();
if(uuid_array.indexOf(uuid_value) == -1.0){
y = 'true';
//Logger.log(uuid_value);
return uuid_value;
}
x_count++;
} while (y == 'false' && x_count < 5);
} else {
return Utilities.getUuid();
}
}
function updateRecord(record_id, dataInfo) {
var dataSheet = ss.getSheetByName("data");
var getLastRow = dataSheet.getLastRow();
var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 5).getValues();
for(i = 0; i < table_values.length; i++){
if(table_values[i][0] == record_id){
dataSheet.getRange(i+2, 2).setValue(dataInfo.stu_number);
dataSheet.getRange(i+2, 3).setValue(dataInfo.name);
dataSheet.getRange(i+2, 4).setValue(dataInfo.stu_class);
dataSheet.getRange(i+2, 5).setValue(new Date());
}
}
return 'SUCCESS';
}
function deleteRecord(record_id){
var dataSheet = ss.getSheetByName("data");
var getLastRow = dataSheet.getLastRow();
var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 5).getValues();
for(i = 0; i < table_values.length; i++){
if(table_values[i][0] == record_id){
var rowNumber = i+2;
dataSheet.deleteRow(rowNumber);
}
}
return 'SUCCESS';
}
function addRecord(dataInfo) {
var newID = uniqueId();
var dataSheet = ss.getSheetByName("data");
dataSheet.appendRow([newID, dataInfo.stu_number, dataInfo.name, dataInfo.stu_class, new Date()]);
return 'SUCCESS';
}
function getDataForSearch() {
const dataSheet = ss.getSheetByName("data");
var getLastRow = dataSheet.getLastRow();
var table_values = dataSheet.getRange(2, 1, getLastRow - 1, 4).getValues();
Logger.log(table_values);
return table_values;
}
<!---index.html--->
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
</head>
<body>
<div style="padding: 10px;" >
<div id="addrecform" >
<div class="form-row">
<div class="form-group col-md-6">
<label for="stu_number">เลขประจำตัว</label>
<input type="number" id="stu_number" class="form-control" />
</div><!---form group md6--->
<div class="form-group col-md-6">
<label for="name">ชื่อ สกุล</label>
<input type="text" id="name" class="form-control" />
</div><!---form group md6--->
</div><!---form row--->
<div class="form-row">
<div class="form-group col-md-6">
<label for="stu_class">ระดับชั้น</label>
<select type="text" id="stu_class" class="form-control" >
<option value="" disabled selected></option>
<option>ม.1</option>
<option>ม.2</option>
<option>ม.3</option>
</select>
</div><!---form group md6--->
</div><!---form row--->
<div >
<input type="button" value="บันทึก" class="btn btn-success" onclick="addReccordFrontEnd()" id="add_button" />
<input type="button" value="ล้างข้อมูล" class="btn btn-info" onclick="clearRecordFrontEnd()" />
<div id="display_error" style="color: red" ></div>
</div>
<div id = "loading" style="font-size:18px;color:green;">
กำลังเรียกดูข้อมูล โปรดรอ...
</div>
<div class="form-row">
<div class="form-group col-md-12">
<label for="searchInput">ค้นหา</label>
<input type="text" id="searchInput" class="form-control" oninput="search()"/>
</div>
</div><!---form row--->
<div id="rowdata"></div>
</div><!---add rec form--->
</div><!---padding 10 px--->
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
<!-- Include Date Range Picker -->
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.4.1/js/bootstrap-datepicker.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.4.1/css/bootstrap-datepicker3.css"/>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.min.js" integrity="sha384-w1Q4orYjBQndcko6MimVbzY0tgp4pWB4lZ7lr30WKz0vr/aWKhXdBNmNb5D92v7s" crossorigin="anonymous"></script>
<script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script>
<script>
var data;
function setDataForSearch(){
loadingStart();
google.script.run.withSuccessHandler(function(dataReturned){
data = dataReturned.slice(); //set from global var name is data
loadingEnd();
}).getDataForSearch();
}
function search(){
var searchInput = document.getElementById("searchInput").value.toString().toLowerCase().trim();
var searchWords = searchInput.split(/\s+/);
var searchColumns =[1,2];
var row_number = 0;
var resultsArray = searchInput === "" ? [] :data.filter(function(r){ //search from global var name is data
return searchWords.every(function(word){
return searchColumns.some(function(colIndex){
return r[colIndex].toString().toLowerCase().indexOf(word) !== -1
});
});
});
var displayTable = '<form>';
displayTable += '<table class=\"table\" id=\"mainTable\" >';
displayTable += "<tr>";
displayTable += "<th>ข้อมูลนักเรียน</th>";
displayTable += "<th>ระดับชั้น</th>";
displayTable += "<th></th>";
displayTable += "</tr>";
resultsArray.forEach(function(r){
displayTable += "<tr id=\"ROWNUMBER:"+ row_number +"\" >";
displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-8\" ><label for=\"up_stu_number\">เลขประจำตัว</label> ";
displayTable += "<input type=\"number\" id=\"up_stu_number"+ row_number +"\" value=\""+r[1]+"\" class=\"form-control\" /> ";
displayTable += "<input type=\"hidden\" id=\"up_record_id"+ row_number +"\" value=\""+r[0]+"\" /></div></div> ";
displayTable += "<div class=\"form-row\"><div class=\"form-group col-md-8\"><label for=\"up_name\">ชื่อ สกุล</label> ";
displayTable += "<input type=\"text\" id=\"up_name"+ row_number +"\" value=\""+r[2]+"\" class=\"form-control\" /></div></div></td> ";
displayTable += "<td><div class=\"form-row\"><div class=\"form-group col-md-8\"><label for=\"up_stu_class\">ระดับชั้น</label> ";
displayTable += "<select type=\"text\" id=\"up_stu_class"+ row_number +"\" value=\""+r[3]+"\" class=\"form-control\" /><option>"+r[3]+"</option><option>ม.1</option><option>ม.2</option><option>ม.3</option></select></div></td> ";
displayTable += "<td><div style=\"padding: 5px\"> ";
displayTable += "<input type=\"button\" id=\"update_button"+ row_number +"\" value=\"แก้ไข\" class=\"btn btn-primary\" ";
displayTable += "onclick=\"updateRecordWapp("+row_number+")\" /></div> ";
displayTable += "<div style=\"padding: 5px\" > ";
displayTable += "<input type=\"button\" value=\"ลบ\" id=\"delete_button"+ row_number +"\" class=\"btn btn-danger\" ";
displayTable += "onclick=\"deleteRecordWapp("+row_number+")\" /></div> ";
displayTable += "</td>";
displayTable += "</tr>";
row_number++;
});
displayTable += '</table></form>';
document.getElementById("rowdata").innerHTML = displayTable;
}//end func search
function addReccordFrontEnd(){
document.getElementById("add_button").disabled = true;
var dataInfo = {};
dataInfo.stu_number = document.getElementById("stu_number").value;
dataInfo.name = document.getElementById("name").value;
dataInfo.stu_class = document.getElementById("stu_class").value;
if(dataInfo.stu_number != '' && dataInfo.name != '' && dataInfo.stu_class != '' ){
document.getElementById("display_error").innerHTML = "กำลังบันทึก...";
google.script.run.withSuccessHandler(function(return_string){
Swal.fire({
position: 'top',
icon: 'success',
title: 'บันทึกข้อมูลสำเร็จ',
showConfirmButton: true,
timer: 1500
})
document.getElementById("display_error").innerHTML = "";
setDataForSearch();
clearRecordFrontEnd();
}).addRecord(dataInfo);
}
else{
document.getElementById("display_error").innerHTML = "กรุณากรอกข้อมูลให้ครบ!";
setTimeout(function(){
document.getElementById("display_error").innerHTML = "";
},3000);
}
document.getElementById("add_button").disabled = false;
}
function clearRecordFrontEnd(){
document.getElementById("stu_number").value = '';
document.getElementById("name").value = '';
document.getElementById("stu_class").value = '';
document.getElementById("display_error").innerHTML = "";
}
function updateRecordWapp(row_number){
document.getElementById("update_button"+row_number).disabled = true;
var dataInfo = {};
var record_id = document.getElementById("up_record_id"+row_number).value;
dataInfo.stu_number = document.getElementById("up_stu_number"+row_number).value;
dataInfo.name = document.getElementById("up_name"+row_number).value;
dataInfo.stu_class = document.getElementById("up_stu_class"+row_number).value;
google.script.run.withSuccessHandler(function(return_string){
document.getElementById("update_button"+row_number).disabled = false;
Swal.fire({
position: 'center',
icon: 'success',
title: 'แก้ไขข้อมูลสำเร็จ',
showConfirmButton: true,
timer: 1500
})
setDataForSearch();
}).updateRecord(record_id, dataInfo);
}
function deleteRecordWapp(row_number){
var record_id = document.getElementById("up_record_id"+row_number).value;
Swal.fire({
position: 'center',
title: 'ยืนยันการลบข้อมูล?',
icon: 'warning',
showCancelButton: true,
confirmButtonColor: '#3085d6',
cancelButtonColor: '#d33',
confirmButtonText: 'ตกลง',
cancelButtonText: 'ยกเลิก'
}).then((result) => {
if(result.isConfirmed){
google.script.run.withSuccessHandler(function(return_string){
if(return_string == 'SUCCESS'){
document.getElementById('mainTable').deleteRow(document.getElementById('ROWNUMBER:'+row_number).rowIndex);
setDataForSearch();
}
}).deleteRecord(record_id);
Swal.fire(
'สำเร็จ!',
'ข้อมูลถูกลบแล้ว',
'success'
)
}//if result.isConfirmed
})//then
}
function loadingStart(){
document.getElementById("loading").classList.remove("invisible");
}
function loadingEnd(){
document.getElementById("loading").classList.add("invisible");
}
document.addEventListener("DOMContentLoaded",setDataForSearch);
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment