https://dexlaos-it.blogspot.com/
CRUD Web Apps 2023
.png)
Add, edit, delete image files in the Google Drive folder.
1. สร้างตาราง google sheets
2. เปิด App script สร้างไฟล์และเขียนโค้ดตามด้านล่างนี้
code.gs
const folder = DriveApp.getFolderById('xxxxxx') //change
const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('xxx') //change
const data = ss.getDataRange().getDisplayValues()
function doGet() {
return HtmlService.createTemplateFromFile('index').evaluate()
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}
function include(file){
return HtmlService.createHtmlOutputFromFile(file).getContent()
}
function getData() {
return data.slice(1)
}
function readId(id) {
let rowID = data.find(r => {
return r[1] == id
})
return rowID
}
function saveData(obj){
Logger.log(obj)
let myPic
let file
let rowID = data.findIndex (r=> r[1]== obj.sID)+1
if(rowID > 1){
if(obj.myFile.length == 0){
myPic = ss.getRange(rowID,15).getValue()
}else{
file = folder.createFile(obj.myFile).getId()
myPic = "https://lh3.googleusercontent.com/d/"+file
let arr=data[rowID-1][14].split('/')
let filex = DriveApp.getFileById(arr[4])
filex.setTrashed(true)
}
ss.getRange(rowID,2).setValue(obj.sID);
ss.getRange(rowID,3).setValue(obj.sID13);
ss.getRange(rowID,4).setValue(obj.classSTD);
ss.getRange(rowID,5).setValue(obj.room);
ss.getRange(rowID,6).setValue(obj.gender1);
ss.getRange(rowID,7).setValue(obj.sname);
ss.getRange(rowID,8).setValue(obj.fname);
ss.getRange(rowID,9).setValue(obj.lname);
ss.getRange(rowID,10).setValue(obj.birthday);
ss.getRange(rowID,11).setValue(obj.father);
ss.getRange(rowID,12).setValue(obj.mother);
ss.getRange(rowID,13).setValue(obj.weight);
ss.getRange(rowID,14).setValue(obj.height);
ss.getRange(rowID,15).setValue(myPic);
}else{
file = folder.createFile(obj.myFile).getId()
myPic = "https://lh3.googleusercontent.com/d/"+file
ss.appendRow([
Date.now().toString(),
"'"+obj.sID,
"'"+obj.sID13,
obj.classSTD,
obj.room,
obj.gender1,
obj.sname,
obj.fname,
obj.lname,
obj.birthday,
obj.father,
obj.mother,
obj.weight,
obj.height,
myPic
])
}
}
function deleteData(id) {
Logger.log("id"+id)
let rowID = data.findIndex(r => r[1] == id) + 1
Logger.log("row"+rowID)
if (rowID > 1) {
let arr=data[rowID-1][14].split('/')
let file = DriveApp.getFileById(arr[4])
file.setTrashed(true)
ss.deleteRow(rowID);
}
return true;
}dataTable.html
<link href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
<link href="https://cdn.datatables.net/responsive/2.2.9/css/responsive.dataTables.min.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/4.1.1/animate.min.css" />
<script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.10.2/dist/umd/popper.min.js"></script>
<link href='https://unpkg.com/boxicons@2.1.4/css/boxicons.min.css' rel='stylesheet'>
<link href='https://cdn.datatables.net/buttons/2.3.2/css/buttons.dataTables.min.css' rel='stylesheet'>
<script src="https://cdn.datatables.net/buttons/2.3.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.3.2/js/buttons.print.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.3.2/js/buttons.html5.min.js"></script>
<table id="example" class="display bg-light" style="width:100%">
<thead style="background: #0e94d4"></thead>
</table>
<script>
google.script.run.withSuccessHandler(showTable).getData() ;
function showTable(result){
console.log(result)
$(document).ready(function() {
$('#example').DataTable({
data:result,
destroy: true,
responsive: true,
columns:[
{'title':'#'},
{'title':'รหัส'},
{'title':'เลขบัตร'},
{'title':'ชั้น'},
{'title':'ห้อง'},
{'title':'เพศ'},
{'title':'คำนำ'},
{'title':'ชื่อ'},
{'title':'สกุล'},
{'title':'วันเกิด'},
{'title':'ชื่อบิดา'},
{'title':'ชื่อมารดา'},
{'title':'น้ำหนัก'},
{'title':'ส่วนสูง'},
{ title: 'Status',"width": "8%", render: function (data, type) {
if (type === "display") {
if(data != ''){data = '<a href="'+data+'" target="_blank"> <i class="bx bxs-file-jpg bx-sm"></i></a><span class="text-warning" onclick="editData(this);" data-bs-toggle="modal" data-bs-target="#modelEdit" ><i class="bx bx-edit bx-sm"></i></span> <span class="text-danger" onclick="delData(this);"><i class="bx bx-trash bx-sm"></i></span>'
}
}
return data;
},
},
],
columnDefs:[{
targets: [2,7,14],
className: 'all',
},
],
lengthMenu: [
[10, 25, 50, 100, -1 ],
['10', '25', '50','100', 'ทั้งหมด' ]
],
order: [[0, 'desc']],
dom: 'Bfrtip',
buttons: [
'copy', 'excel', 'pdf', 'print'
]
});
} );
}
</script>
formAdd.html
<!-- Modal -->
<div class="modal fade" id="modelEdit" data-bs-backdrop="static" tabindex="-1" aria-labelledby="exampleModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">แก้ไขข้อมูล</h5>
<button type="button" class="btn-close" id="close1" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<!-- ****************************************** -->
<form class="row g-2" id="myform" onsubmit="saveForm(this)">
<center><img src="" id="img" width="100" height="120" style="display:none"></center>
<div class="col-md-6" id="div0" style="display:none">
<label for="numSTD" class="form-label">ที่</label>
<input type="text" class="form-control" id="numSTD" name="numSTD">
</div>
<div class="col-md-3" id="div1">
<label for="sID" class="form-label">รหัสนักเรียน</label>
<input type="text" class="form-control" id="sID" name="sID" required>
</div>
<div class="col-md-4" id="div2">
<label for="inputPassword4" class="form-label">เลขบัตร</label>
<input type="number" class="form-control" id="sID13" name="sID13" maxlength="13" required>
</div>
<div class="col-md-3" id="div3">
<label for="classSTD" class="form-label">ชั้น</label>
<select id="classSTD" name="classSTD" class="form-select" required>
<option value="">เลือกห้องเรียน</option>
<option>ม.4</option>
<option>ม.5</option>
<option>ม.6</option>
</select>
</div>
<div class="col-md-2" id="div3">
<label for="room" class="form-label">ห้อง</label>
<input type="text" class="form-control" id="room" name="room" required>
</div>
<div class="col-md-4">
<label for="inputPassword4" class="form-label">คำนำหน้า</label>
<select id="sname" name="sname" class="form-select" required>
<option>เด็กชาย</option>
<option>เด็กหญิง</option>
</select>
</div>
<div class="col-md-4">
<label for="fname" class="form-label">ชื่อ</label>
<input type="text" class="form-control" id="fname" name="fname" required>
</div>
<div class="col-md-4">
<label for="inputPassword4" class="form-label">สกุล</label>
<input type="text" class="form-control" id="lname" name="lname" required>
</div>
<div class="col-md-6">
<p>เพศ</p>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="gender1" id="male" value="ชาย" checked>
<label class="form-check-label" for="male">ชาย</label>
</div>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="gender1" id="female" value="หญิง">
<label class="form-check-label" for="female">หญิง</label>
</div>
</div>
<div class="col-md-6">
<label for="birthday" class="form-label">วันเกิด</label>
<input type="text" class="form-control" id="birthday" name="birthday" required>
</div>
<div class="col-md-6">
<label for="father" class="form-label">ชื่อบิดา</label>
<input type="text" class="form-control" id="father" name="father" required>
</div>
<div class="col-md-6">
<label for="mother" class="form-label">ชื่อมารดา</label>
<input type="text" class="form-control" id="mother" name="mother" required>
</div>
<div class="col-md-6">
<label for="weight" class="form-label">น้ำหนัก</label>
<input type="text" class="form-control" id="weight" name="weight" required>
</div>
<div class="col-md-6">
<label for="height" class="form-label">ส่วนสูง</label>
<input type="text" class="form-control" id="height" name="height" required>
</div>
<div class="col-md-12 mb-2">
<label for="myFile" class="form-label">ภาพนักเรียน</label>
<input type="file" class="form-control" id="myFile" name="myFile">
</div>
<div class="col-12 text-center">
<button id="btn-submit" type="submit" class="btn btn-primary">บันทึกข้อมูล</button>
<div id="spin-loader"><span class =" spinner-border text-danger" role="status"></span> โปรดรอสักครู่</div>
</div>
</form>
</div>
</div>
</div>
</div>
index.html<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>Bootstrap demo</title> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet"> <style> @import url('https://fonts.googleapis.com/css2?family=Athiti:wght@400;500;600&family=Roboto&display=swap'); * { margin: 0; padding: 0; font-family: 'Athiti', sans-serif; } span { cursor: pointer; } </style> </head> <body> <nav class="navbar navbar-expand-lg bg-light shadow"> <div class="container-fluid"> <a class="navbar-brand" href="#">CRUD เต็มระบบ</a> <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarScroll" aria-controls="navbarScroll" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarScroll"> <ul class="navbar-nav me-auto my-2 my-lg-0 navbar-nav-scroll" style="--bs-scroll-height: 100px;"> <li class="nav-item"> <a class="nav-link active" aria-current="page" href="#">Home</a> </li> <li class="nav-item dropdown"> <a class="nav-link dropdown-toggle" href="#" role="button" data-bs-toggle="dropdown" aria-expanded="false"> Link </a> <ul class="dropdown-menu"> <li><a class="dropdown-item" href="#">Action</a></li> <li><a class="dropdown-item" href="#">Another action</a></li> <li> <hr class="dropdown-divider"> </li> <li><a class="dropdown-item" href="#">Something else here</a></li> </ul> </li> </ul> <div class="d-flex" role="search"> <button class="btn btn-success" id="btnadd" onclick="addData()" data-bs-toggle="modal" data-bs-target="#modelEdit"><i class="bx bx-plus" ></i>เพิ่มข้อมูล</button> </div> </div> </div> </nav> <div class="container-fluid bg-light p-3 mt-2"> <?!=include('dataTable')?> </div> <?!=include('formAdd')?> <?!=include('javascript')?> <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-kenU1KFdBIe4zVF0s0G1M5b4hcpxyD9F7jL+jjXkk+Q2h455rYXK/7HAuoJl+0I4" crossorigin="anonymous"> </script> </body> </html>javascript.html<script> function editData(el){ document.getElementById('exampleModalLabel').innerHTML = "แก้ไขข้อมูล" document.getElementById("sID").readOnly = true; document.getElementById('myform').reset() const id = el.parentNode.parentNode.cells[1].innerHTML; document.getElementById('img').style.display="none" document.getElementById('spin-loader').style.display = "none" document.getElementById('btn-submit').style.display = "block" google.script.run.withSuccessHandler(onSuccess).readId(id); } function onSuccess(response){ img = document.getElementById('img') img.src == "" ? img.style.display="none" : img.style.display="block" if(response !=null){ document.getElementById('numSTD').value = response[0]; document.getElementById('sID').value = response[1]; document.getElementById('sID13').value = response[2]; document.getElementById('classSTD').value = response[3]; document.getElementById('room').value = response[4]; document.getElementById(response[5] =='ชาย'? 'male':'female').checked = true; //checkbox document.getElementById('sname').value = response[6]; document.getElementById('fname').value = response[7]; document.getElementById('lname').value = response[8]; document.getElementById('birthday').value = response[9]; document.getElementById('father').value = response[10]; document.getElementById('mother').value = response[11]; document.getElementById('weight').value = response[12]; document.getElementById('height').value = response[13]; document.getElementById('img').src = response[14]; } } function saveForm(obj) { event.preventDefault(); document.getElementById('spin-loader').style.display = "block" document.getElementById('btn-submit').style.display = "none" google.script.run.withSuccessHandler(()=> { $('#modelEdit').modal('hide') Swal.fire({ position: 'center', icon: 'success', title: 'บันทึกข้อมูลเรียบร้อย', showConfirmButton: false, timer: 1500 }) document.getElementById('myform').reset() google.script.run.withSuccessHandler(showTable).getData() ; }).saveData(obj); } function delData(el){ const id = el.parentNode.parentNode.cells[1].innerHTML; google.script.run.withSuccessHandler(()=>{ alert('ลบข้อมูลเรียบร้อย') google.script.run.withSuccessHandler(showTable).getData() ; }).deleteData(id) } function addData(){ document.getElementById('exampleModalLabel').innerHTML = "เพิ่มข้อมูล" document.getElementById('spin-loader').style.display = "none" document.getElementById('btn-submit').style.display = "block" document.getElementById('img').style.display="none" document.getElementById('img').src = "" document.getElementById('myform').reset() document.getElementById("sID").readOnly = false; } </script> <script src="//cdn.jsdelivr.net/npm/sweetalert2@11"></script>