Skip to content

Instantly share code, notes, and snippets.

@mhakrook

mhakrook/gs

Created May 15, 2020 05:00
Show Gist options
  • Save mhakrook/5fafb573efbb45a0f50338ccd797f2b1 to your computer and use it in GitHub Desktop.
Save mhakrook/5fafb573efbb45a0f50338ccd797f2b1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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);
if (op.indexOf("search") > -1)
var search = op.split("#")[1]
return search_value(e, sheet, search);
}
function insert_value(request, sheet) {
var id = request.parameter.id;
var name = 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, name]);
var result = "เพิ่มข้อมูลเรียบร้อยแล้ว!!";
}
result = JSON.stringify({
"result": result
});
return ContentService
.createTextOutput(request.parameter.callback + "(" + result + ")")
.setMimeType(ContentService.MimeType.JAVASCRIPT);
}
function read_value(request, ss) {
var sheet = "ใส่ชื่อแผ่นงาน";
var output = ContentService.createTextOutput(),
data = {};
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 name = 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.getRange(i, 3).setValue(name);
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 name = 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);
}
//////////////////////////////////////////////////////////////////////
function search_value(request, ss, search) {
var sheet = "ใส่ชื่อแผ่นงาน";
var output = ContentService.createTextOutput(),
data = {};
data.records = searchData_(ss, sheet, search);
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 searchData_(ss, sheetname, search, properties) {
if (typeof properties == "undefined") {
properties = getHeaderRow_(ss, sheetname);
properties = properties.map(function(p) { return p.replace(/\s+/g, '_'); });
}
var rows = searchDataRows_(ss, sheetname, search),
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 searchDataRows_(ss, sheetname, search) {
var sh = ss.getSheetByName(sheetname).etDataRange().getValues()
var data = ss.find(function a(row) {
return row[1] = search
})
return [data]
}
Raw
html
function search_value() {
var search = $("#ไอดีของช่องที่กรอกค้นหา").val();
$("#re").css("visibility","hidden");
document.getElementById("loader").style.visibility = "visible";
var url = script_url+"?action=search#"+search;
$.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);
var cell3 = row.insertCell(2);
row.style.background = "rgb(243,219,113)";
cell1.innerHTML = "<b>รหัสนักเรียน</b>";
cell2.innerHTML = "<b>ชื่อ สกุล</b>";
cell3.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].ชื่อ_สกุล;
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");
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment