Skip to content

Instantly share code, notes, and snippets.

@apivat60

apivat60/code.gs การค้นหา แก้ไข อัปเดต ลบข้อมูล

Last active May 14, 2020 09:17
Show Gist options
  • Save apivat60/4ea620deab4b9e482f47e0099267f59d to your computer and use it in GitHub Desktop.
Save apivat60/4ea620deab4b9e482f47e0099267f59d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
code.gs การค้นหา แก้ไข อัปเดต ลบข้อมูล
function doGet(e){
var op = e.parameter.action;
var ss=SpreadsheetApp.openByUrl("ใส่ url ชีต");
var sheet = ss.getSheetByName("ใส่ชื่อแผ่นงาน");
if(op=="insert")
return insert_value(e,sheet);
if(op=="read")
return read_value(e,ss);
if(op=="update")
return update_value(e,sheet);
if(op=="delete")
return delete_value(e,sheet);
}
function insert_value(request,sheet){
var id = request.parameter.id;
var country = request.parameter.name;
var flag=1;
var lr= sheet.getLastRow();
for(var i=1;i<=lr;i++){
var id1 = sheet.getRange(i, 2).getValue();
if(id1==id){
flag=0;
var result="มีรหัสนักเรียนนี้อยู่ในระบบแล้ว!!";
} }
if(flag==1){
var d = new Date();
var currentTime = d.toLocaleString();
var rowData = sheet.appendRow([currentTime,id,country]);
var result="เพิ่มข้อมูลเรียบร้อยแล้ว!!";
}
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
function read_value(request,ss){
var output = ContentService.createTextOutput(),
data = {};
//ถ้าลืมเปลี่ยนชื่อชีตตรงนี้ จะโหลดข้อมูลมาแสดงไม่ได้
var sheet="ใส่ชื่อแผ่นงาน";
data.records = readData_(ss, sheet);
var callback = request.parameters.callback;
if (callback === undefined) {
output.setContent(JSON.stringify(data));
} else {
output.setContent(callback + "(" + JSON.stringify(data) + ")");
}
output.setMimeType(ContentService.MimeType.JAVASCRIPT);
return output;
}
function readData_(ss, sheetname, properties) {
if (typeof properties == "undefined") {
properties = getHeaderRow_(ss, sheetname);
properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
}
var rows = getDataRows_(ss, sheetname),
data = [];
for (var r = 0, l = rows.length; r < l; r++) {
var row = rows[r],
record = {};
for (var p in properties) {
record[properties[p]] = row[p];
}
data.push(record);
}
return data;
}
function getDataRows_(ss, sheetname) {
var sh = ss.getSheetByName(sheetname);
return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
}
function getHeaderRow_(ss, sheetname) {
var sh = ss.getSheetByName(sheetname);
return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
}
function update_value(request,sheet){
var output = ContentService.createTextOutput();
var id = request.parameter.id;
var flag=0;
var country = request.parameter.name;
var lr= sheet.getLastRow();
for(var i=1;i<=lr;i++){
var rid = sheet.getRange(i, 2).getValue();
if(rid==id){
sheet.getRange(i,3).setValue(country);
var result="อัปเดทข้อมูลเรียบร้อยแล้ว!!";
flag=1;
}
}
if(flag==0)
var result="ไม่มีรหัสนักเรียนนี้ในระบบ!!";
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
function delete_value(request,sheet){
var output = ContentService.createTextOutput();
var id = request.parameter.id;
var country = request.parameter.name;
var flag=0;
var lr= sheet.getLastRow();
for(var i=1;i<=lr;i++){
var rid = sheet.getRange(i, 2).getValue();
if(rid==id){
sheet.deleteRow(i);
var result="ลบข้อมูลเรียบร้อยแล้ว!!";
flag=1;
}
}
if(flag==0)
var result="ไม่พบรหัสนักเรียนนี้ในระบบ!!";
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
Raw
index.html การค้นหา แก้ไข อัปเดต ลบข้อมูล
<html>
<head>
<style>
table, th, td
{
margin:10px 0;
border:solid 1px #333;
padding:2px 4px;
font:15px Verdana;
}
th {
font-weight:bold;
}
#loader {
border: 16px solid #0B96F7;
border-radius: 50%;
border-top: 16px solid #FFFF00;
border-bottom: 16px solid #FFFF00;
width: 30px;
height: 30px;
-webkit-animation: spin 2s linear infinite;
animation: spin 2s linear infinite;
visibility:hidden;
}
@-webkit-keyframes spin {
0% { -webkit-transform: rotate(0deg); }
100% { -webkit-transform: rotate(360deg); }
}
@keyframes spin {
0% { transform: rotate(0deg); }
100% { transform: rotate(360deg); }
}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.0/jquery.min.js"></script>
<script>
var script_url = "https://script.google.com/macros/s/AKfycby6Ws1Pvnff8D_GuTVJ5RA7Lx0tyVSR8IoF94XOZsN2h6ADCEw/exec";
//เพิ่มข้อมูล
function insert_value() {
$("#re").css("visibility","hidden");
document.getElementById("loader").style.visibility = "visible";
$('#mySpinner').addClass('spinner');
var id1= $("#id").val();
var name= $("#name").val();
var url = script_url+"?callback=ctrlq&name="+name+"&id="+id1+"&action=insert";
var request = jQuery.ajax({
crossDomain: true,
url: url ,
method: "GET",
dataType: "jsonp"
});
}
//อัปเดทข้อมูล
function update_value(){
$("#re").css("visibility","hidden");
document.getElementById("loader").style.visibility = "visible";
var id1= $("#id").val();
var name= $("#name").val();
var url = script_url+"?callback=ctrlq&name="+name+"&id="+id1+"&action=update";
var request = jQuery.ajax({
crossDomain: true,
url: url ,
method: "GET",
dataType: "jsonp"
});
}
//ลบข้อมูล
function delete_value(){
$("#re").css("visibility","hidden");
document.getElementById("loader").style.visibility = "visible";
$('#mySpinner').addClass('spinner');
var id1= $("#id").val();
var name= $("#name").val();
var url = script_url+"?callback=ctrlq&name="+name+"&id="+id1+"&action=delete";
var request = jQuery.ajax({
crossDomain: true,
url: url ,
method: "GET",
dataType: "jsonp"
});
}
// print the returned data
function ctrlq(e) {
$("#re").html(e.result);
$("#re").css("visibility","visible");
read_value();
}
function read_value() {
$("#re").css("visibility","hidden");
document.getElementById("loader").style.visibility = "visible";
var url = script_url+"?action=read";
$.getJSON(url, function (json) {
// สร้างตารางแสดงข้อมูล
var table = document.createElement("table");
var header = table.createTHead();
var row = header.insertRow(0);
var cell1 = row.insertCell(0);
var cell2 = row.insertCell(1);
row.style.background = "rgb(243,219,113)";
cell1.innerHTML = "<b>รหัสนักเรียน</b>";
cell2.innerHTML = "<b>ชื่อ สกุล</b>";
//นำข้อมูลลงในตาราง
for (var i = 0; i < json.records.length; i++) {
tr = table.insertRow(-1);
var tabCell = tr.insertCell(-1);
tabCell.innerHTML = json.records[i].รหัสนักเรียน;
tabCell = tr.insertCell(-1);
tabCell.innerHTML = json.records[i].ชื่อ_สกุล;
}
var divContainer = document.getElementById("showData");
divContainer.innerHTML = "";
divContainer.appendChild(table);
document.getElementById("loader").style.visibility = "hidden";
$("#re").css("visibility","visible");
});
}
</script>
</head>
<body>
<div align="center">
<img src="https://drive.google.com/uc?id=1TneNYEAxilIj4SN1oX9uZCUh-EYH9cZr" width="50%" class="img-fluid" alt="Responsive image">
<h3>พัฒนาโดย ครูอภิวัฒน์ สอนสร้างสื่อ</h3>
<!-- สร้างฟอร์ม -->
<form >
รหัสนักเรียน
<input type = "text" name ="id" id="id">
ชื่อ สกุล
<input type = "text" name ="name" id="name">
</form>
<input type = "button" style="font-size:12pt;color:white;background-color:#F76326;border:1px solid #000000;padding:2px"
id = "b1" onClick="insert_value()" value = "เพิ่มข้อมูล" ></input>
<input type="button" style="font-size:12pt;color:white;background-color:#6DA551;border:1px solid #000000;padding:2px"
onclick="read_value()" value="แสดงข้อมูล" />
<input type="button" style="font-size:12pt;color:white;background-color:#0298F7;border:1px solid #000000;padding:2px"
onclick="update_value()" value="อัปเดทข้อมูล" />
<input type="button" style="font-size:12pt;color:white;background-color:#975AE0;border:1px solid #000000;padding:2px"
onclick="delete_value()" value="ลบข้อมูล" />
<br>
<!-- เรียกตัว loder มาแสดง-->
<div id="loader"></div>
<p id="re"></p>
<div id="showData"></div>
<br>
<a href="https://docs.google.com/spreadsheets/d/1sa-loU4Ic2gf6JFP3pLPLKSKTJavuCgXUOR2xO1rnFs/edit?usp=sharing" target="_blank">ดูข้อมูลในชีต</a>
</div>
</body>
<html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment