code.gs
function doGet() {
return HtmlService.createTemplateFromFile('Index').evaluate()
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
// .setFaviconUrl('https://drive.google.com/file/d/1edLz97zRkTjqksjx7eo_SXQavnPctOOG/view')
.setTitle('Sweet Alert Password Protect CRUD Free-Creating a Google Sheets Data Entry Form')
.setSandboxMode(HtmlService.SandboxMode.IFRAME)
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
function globalVariables(){
var varArray = {
spreadsheetId : '-------xxxxxxxxxxx-------', //** CHANGE !!!
dataRage : 'ข้อมูล!A2:G', //** CHANGE !!!
idRange : 'ข้อมูล!A2:A', //** CHANGE !!!
lastCol : 'G', //** CHANGE !!!
insertRange : 'ข้อมูล!A1:G1', //** CHANGE !!!
sheetID : '0' //** CHANGE !!!
};
return varArray;
}
/* PROCESS FORM */
function processForm(formObject){
if(formObject.RecId && checkID(formObject.RecId)){//Execute if form passes an ID and if is an existing ID
updateData(getFormValues(formObject),globalVariables().spreadsheetId,getRangeByID(formObject.RecId)); // Update Data
}else{ //Execute if form does not pass an ID
appendData(getFormValues(formObject),globalVariables().spreadsheetId,globalVariables().insertRange); //Append Form Data
}
return getAllData();//Return last 10 rows
}
/* GET FORM VALUES AS AN ARRAY */
function getFormValues(formObject){
/* ADD OR REMOVE VARIABLES ACCORDING TO YOUR FORM*/
if(formObject.RecId && checkID(formObject.RecId)){
var values = [[formObject.RecId.toString(),
formObject.name,
formObject.gender,
formObject.dateOfBirth,
formObject.email,
formObject.phone,
formObject.country]];
}else{
var values = [[new Date().getTime().toString(),//https://webapps.stackexchange.com/a/51012/244121
formObject.name,
formObject.gender,
formObject.dateOfBirth,
formObject.email,
formObject.phone,
formObject.country]];
}
return values;
}
/*
## CURD FUNCTIONS ----------------------------------------------------------------------------------------
*/
/* CREATE/ APPEND DATA */
function appendData(values, spreadsheetId,range){
var valueRange = Sheets.newRowData();
valueRange.values = values;
var appendRequest = Sheets.newAppendCellsRequest();
appendRequest.sheetID = spreadsheetId;
appendRequest.rows = valueRange;
var results = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId, range,{valueInputOption: "RAW"});
}
/* READ DATA */
function readData(spreadsheetId,range){
var result = Sheets.Spreadsheets.Values.get(spreadsheetId, range);
return result.values;
}
/* UPDATE DATA */
function updateData(values,spreadsheetId,range){
var valueRange = Sheets.newValueRange();
valueRange.values = values;
var result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, {
valueInputOption: "RAW"});
}
/*DELETE DATA*/
function deleteData(ID){
var startIndex = getRowIndexByID(ID);
var deleteRange = {
"sheetId" : globalVariables().sheetID,
"dimension" : "ROWS",
"startIndex" : startIndex,
"endIndex" : startIndex+1
}
var deleteRequest= [{"deleteDimension":{"range":deleteRange}}];
Sheets.Spreadsheets.batchUpdate({"requests": deleteRequest}, globalVariables().spreadsheetId);
return getAllData();//Return last 10 rows
}
/*
## HELPER FUNCTIONS FOR CRUD OPERATIONS --------------------------------------------------------------
*/
/* CHECK FOR EXISTING ID, RETURN BOOLEAN */
function checkID(ID){
var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange,).reduce(function(a,b){return a.concat(b);});
return idList.includes(ID);
}
/* GET DATA RANGE A1 NOTATION FOR GIVEN ID */
function getRangeByID(id){
if(id){
var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange);
for(var i=0;i<idList.length;i++){
if(id==idList[i][0]){
return 'ข้อมูล!A'+(i+2)+':'+globalVariables().lastCol+(i+2);
}
}
}
}
/* GET RECORD BY ID */
function getRecordById(id){
if(id && checkID(id)){
var result = readData(globalVariables().spreadsheetId,getRangeByID(id));
return result;
}
}
/* GET ROW NUMBER FOR GIVEN ID */
function getRowIndexByID(id){
if(id){
var idList = readData(globalVariables().spreadsheetId,globalVariables().idRange);
for(var i=0;i<idList.length;i++){
if(id==idList[i][0]){
var rowIndex = parseInt(i+1);
return rowIndex;
}
}
}
}
// /*GET LAST 10 RECORDS */
// function getLastTenRows(){
// var lastRow = readData(globalVariables().spreadsheetId,globalVariables().dataRage).length+1;
// if(lastRow<=11){
// var range = globalVariables().dataRage;
// }else{
// var range = 'ข้อมูล!A'+(lastRow-9)+':'+globalVariables().lastCol;
// }
// var lastTenRows = readData(globalVariables().spreadsheetId,range);
// return lastTenRows;
// }
/* GET ALL RECORDS */
function getAllData(){
var data = readData(globalVariables().spreadsheetId,globalVariables().dataRage);
return data;
}
/*
## OTHER HELPERS FUNCTIONS ------------------------------------------------------------------------
*/
/*GET DROPDOWN LIST */
function getDropdownList(range){
var list = readData(globalVariables().spreadsheetId,range);
return list;
}
/* INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES */
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
**************
Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/rowreorder/1.3.3/css/rowReorder.dataTables.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.4.1/css/responsive.dataTables.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/jquery.dataTables.css" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.1.3/css/bootstrap-grid.min.css" />
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.8.1/css/all.css
" integrity="sha384-50oBUHEmvpQ+1lW4y57PTFmhCaXp0ML5d60M1M7uH2+nqUivzIebhndOJK28anvf" crossorigin="anonymous" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.3.0/css/bootstrap.min.csscss" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.csscss" />
<link rel="stylesheet" href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap5.min.csscss" />
<?!= include('JavaScript');?> <!-- See JavaScript.html file -->
<?!= include('CSS');?> <!-- See CSS.html file -->
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Prompt">
<style>
body {
font-family: "Prompt", sans-serif;
font-size: 15px;
}
</style>
</head>
<body onload="createCountryDropdown()">
<!-- <div class="container-fluid" id="page1" style="display:none"> -->
<div class="container-fluid" >
<!-- <div class="container"> -->
<div class="row">
<div class="col-lg-6">
<?!= include('Form');?> <!-- See Form.html file -->
<br><br>
<div id="output"></div>
</div>
<div class="col-lg-6">
<p class="mt-3 h4 mb-4 text-center"><i class='fas fa-users'></i> ฐานข้อมูลรายชื่อทั้งหมด</p>
<!-- <table id="dataTable" class="display responsive nowrap" style="width:100%"></table> -->
<table id="dataTable" class="table dt-responsive nowrap" style="width:100%"></table>
<!-- <button type="button" class="btn btn-success btn-sm" onclick="getAllData()">ดูข้อมูลทั้งหมด</button> -->
<!-- <?!= include('DataTable');?> See DataTable.html File -->
</div>
</div>
</div>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.2/dist/umd/popper.min.js" integrity="sha384-IQsoLXl5PILFhosVNubq5LC7Qb9DXgDA9i+tQ8Zj3iwWAwPtgFTxbJ8NT4GN1R8p" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js" integrity="sha384-cVKIPhGWiC2Al4u+LWgxfKTRIcfu0JTxR+EQDz/bgldoEyl4H0zUF0QKbrJ0EcQF" crossorigin="anonymous"></script>
<script src="https://code.jquery.com/jquery-3.2.1.slim.min.js" integrity="sha384-KJ3o2DKtIkvYIK3UENzmM7KCkRr/rE9/Qpg6aAZGJwFDMVNA/GpGFF93hXpG5KkN" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/rowreorder/1.3.3/js/dataTables.rowReorder.min.js"></script>
<script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.1.3/js/bootstrap.min.js"></script>
<script src="https://code.jquery.com/jquery-3.7.0.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/responsive.bootstrap5.min.js"></script>
<script src="https://kit.fontawesome.com/6a972cf3a7.js" crossorigin="anonymous"></script>
</body>
</html>
*********
JavaScript.html
<script>
// window.addEventListener('load', function(){
// passedit()
// async function passedit(){
// const { value: password } = await Swal.fire({
// position: 'top',
// title: 'กรุณาใส่รหัสผ่าน',
// confirmButtonColor: 'violet',
// confirmButtonText: 'ตกลง',
// input: 'password',
// inputLabel: 'เข้าสู่การแก้ไข',
// inputPlaceholder: 'ใส่รหัสผ่านของคุณ',
// inputAttributes: {
// maxlength: 10,
// autocapitalize: 'off',
// autocorrect: 'off'
// }
// })
// if (password==="7654321") {
// $("#pag1").show()
// // getAllData();
// google.script.run.withSuccessHandler(createTable).getAllData();
// }else{
// await Swal.fire({
// position: 'top',
// icon: 'error',
// title: 'รหัสผ่านไม่ถูกต้อง',
// showConfirmButton: false,
// timer: 1500
// })
// passedit()
// }
// }
// })
// Prevent forms from submitting.
function preventFormSubmit() {
var forms = document.querySelectorAll('form');
for (var i = 0; i < forms.length; i++) {
forms[i].addEventListener('submit', function(event) {
event.preventDefault();
});
}
}
window.addEventListener("load", functionInit, true);
//INITIALIZE FUNCTIONS ONLOAD
function functionInit(){
preventFormSubmit();
// getLastTenRows();
getAllData();
};
//HANDLE FORM SUBMISSION
function handleFormSubmit(formObject) {
google.script.run.withSuccessHandler(createTable).processForm(formObject);
document.getElementById("myForm").reset();
}
// //GET LAST 10 ROWS
// function getLastTenRows (){
// google.script.run.withSuccessHandler(createTable).getLastTenRows();
// }
//GET ALL DATA
function getAllData(){
// document.getElementById('dataTable').innerHTML = "";
google.script.run.withSuccessHandler(createTable).getAllData();
}
//CREATE THE DATA TABLE
function createTable(dataArray) {
if(dataArray){
var result = "<table class='table table-sm' style='font-size:0.8em'>"+
"<thead style='white-space: nowrap'>"+
"<tr>"+ //Change table headings to match witht he Google Sheet
"<th scope='col'>ลบข้อมูล</th>"+
"<th scope='col'>แก้ไขข้อมูล</th>"+
"<th scope='col'>ไอดี</th>"+
"<th scope='col'>ชื่อ สกุล</th>"+
"<th scope='col'>เพศ</th>"+
"<th scope='col'>วันเกิด</th>"+
"<th scope='col'>อีเมล</th>"+
"<th scope='col'>เบอร์โทร</th>"+
"<th scope='col'>ระดับชั้น</th>"+
"</tr>"+
"</thead>";
for(var i=0; i<dataArray.length; i++) {
result += "<tr>";
result += "<td><button type='button' class='btn btn-danger btn-xs deleteBtn' onclick='deleteData(this);'>ลบข้อมูล</button></td>";
result += "<td><button type='button' class='btn btn-warning btn-xs editBtn' onclick='editData(this);'>แก้ไขข้อมูล</button></td>";
for(var j=0; j<dataArray[i].length; j++){
result += "<td>"+dataArray[i][j]+"</td>";
}
result += "</tr>";
}
result += "</table>";
var div = document.getElementById('dataTable');
div.innerHTML = result;
$(document).ready(function(){
$('#dataTable').DataTable({
destroy : true,
lengthMenu:
[ [7, 10, 25, 50, 100, -1], [7, 10, 25, 50, 100, "All"] ]
});
} );
document.getElementById("message").innerHTML = "";
}else{
var div = document.getElementById('dataTable');
div.innerHTML = "ไม่พบข้อมูล!";
}
}
// //DELETE DATA
function deleteData(el) {
passedit()
async function passedit(){
const { value: password } = await Swal.fire({
position: 'top',
title: 'กรุณาใส่รหัสผ่าน',
confirmButtonColor: 'red',
confirmButtonText: 'ตกลง',
input: 'password',
inputLabel: 'เข้าสู่การลบข้อมูล',
inputPlaceholder: 'ใส่รหัสผ่านของคุณ',
inputAttributes: {
maxlength: 10,
autocapitalize: 'off',
autocorrect: 'off'
}
})
if (password==="7654321321") {
// Swal.fire(`Entered password: ${password}`)
Swal.fire({
position: 'top',
title: 'คุณแน่ใจไหม? ที่จะลบข้อมูล',
text: "คุณไม่สามารถกู้คืนได้น่ะ ต้องการลบข้อมูล! ใช่หรือไม่",
icon: 'warning',
showCancelButton: true,
confirmButtonColor: '#3085d6',
cancelButtonColor: '#d33',
confirmButtonText: 'ตกลง, ต้องการลบข้อมูลจริงๆ!',
cancelButtonText: 'ยกเลิก',
}).then((result) => {
if (result.isConfirmed) {
var recordId = el.parentNode.parentNode.cells[2].innerHTML;
google.script.run.withSuccessHandler(createTable).deleteData(recordId);
}
})
}else{
Swal.fire({
position: 'top',
icon: 'error',
title: 'รหัสผ่านไม่ถูกต้อง',
showConfirmButton: false,
timer: 1500
})
}
}
}
//EDIT DATA
function editData(el){
passedit()
async function passedit(){
const { value: password } = await Swal.fire({
position: 'top',
title: 'กรุณาใส่รหัสผ่าน',
confirmButtonColor: 'violet',
confirmButtonText: 'ตกลง',
input: 'password',
inputLabel: 'เข้าสู่การแก้ไข',
inputPlaceholder: 'ใส่รหัสผ่านของคุณ',
inputAttributes: {
maxlength: 10,
autocapitalize: 'off',
autocorrect: 'off'
}
})
if (password==="7654321") {
// Swal.fire(`Entered password: ${password}`)
var recordId = el.parentNode.parentNode.cells[2].innerHTML; //https://stackoverflow.com/a/32377357/2391195
google.script.run.withSuccessHandler(populateForm).getRecordById(recordId);
}else{
Swal.fire({
position: 'top',
icon: 'error',
title: 'รหัสผ่านไม่ถูกต้อง',
showConfirmButton: false,
timer: 1500
})
}
}
}
//POPULATE FORM
function populateForm(records){
document.getElementById('RecId').value = records[0][0];
document.getElementById('name').value = records[0][1];
document.getElementById(records[0][2] =='ชาย'? 'male':'female').checked = true;
document.getElementById('dateOfBirth').value = records[0][3];
document.getElementById('email').value = records[0][4];
document.getElementById('phone').value = records[0][5];
document.getElementById("country").value = records[0][6];
document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Update Record [ID: "+records[0][0]+"]</div>";
}
//RETRIVE DATA FROM GOOGLE SHEET FOR COUNTRY DROPDOWN
function createCountryDropdown() {
//SUBMIT YOUR DATA RANGE FOR DROPDOWN AS THE PARAMETER
google.script.run.withSuccessHandler(countryDropDown).getDropdownList("ระดับชั้น!A1:A30");
}
//POPULATE COUNTRY DROPDOWNS
function countryDropDown(values) { //Ref: https://stackoverflow.com/a/53771955/2391195
var list = document.getElementById('country');
for (var i = 0; i < values.length; i++) {
var option = document.createElement("option");
option.value = values[i];
option.text = values[i];
list.appendChild(option);
}
}
</script>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
********
CSS.html
<style>
@import url('https://fonts.googleapis.com/css2?family=Nunito:ital,wght@0,200;0,300;0,400;0,600;0,700;1,200;1,300&display=swap');
</style>
<style>
.btn-group-xs > .btn, .btn-xs {
padding: .25rem .4rem;
font-size: .875rem;
line-height: .5;
border-radius: .2rem;
}
</style>
********
Form.html
<!-- ## The html code of the form goes here -->
<!-- ## This is included in the Index.html page using "include('Form')" function -->
<form id="myForm" class="p-2 border border-light rounded bg-light" onsubmit="handleFormSubmit(this)"> <!-- Call JavaScript function "handleFormSubmit" -->
<p class="h4 mb-4 text-center">ฟอร์มกรอกข้อมูล</p>
<div id="message"></div>
<input type="text" id="RecId" name="RecId" value="" style="display: none">
<div class="form-group">
<!-- <div class="form-group col-md-6"> -->
<label for="name" ><i class="fas fa-user-plus"></i>ชื่อ สกุล</label>
<input type="text" class="form-control" id="name" name="name" placeholder="ชื่อ สกุล" required>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<p><i class="fas fa-restroom"></i> เพศ</p>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="gender" id="male" value="ชาย">
<label class="form-check-label" for="male">ชาย</label>
</div>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="gender" id="female" value="หญิง">
<label class="form-check-label" for="female">หญิง</label>
</div>
</div>
<div class="form-group col-md-6">
<label for="dateOfBirth"><i class="fas fa-birthday-cake"></i> วันเกิด</label>
<input type="date" class="form-control" id="dateOfBirth" name="dateOfBirth">
</div>
</div>
<!-- <div class="form-group col-md-6"> -->
<div class="form-group">
<label for="email"><i class="fas fa-envelope"></i>อีเมล</label>
<input type="email" class="form-control" id="email" name="email" placeholder="อีเมล">
</div>
<div class="row my-3">
<!-- <div class="form-row"> -->
<!-- <div class="form-group"> -->
<div class="form-group col-md-4">
<label for="phone"><i class="fas fa-mobile-alt"></i> เบอร์โทร</label>
<input type="tel" class="form-control" id="phone" name="phone" placeholder="เบอร์โทร">
</div>
<div class="form-group col-md-4">
<label for="exampleFormControlSelect1"><i class="fas fa-user-graduate"></i> ระดับชั้น</label>
<select class="form-control" id="country" name="country">
<option>เลือกระดับชี่น</option>
</select>
</div>
</div>
<button type="submit" class="btn btn-primary">บันทึกข้อมูล</button>
<input class="btn btn-secondary" type="reset" value="รีเซ็ต">
</form>
ไม่มีความคิดเห็น:
แสดงความคิดเห็น
หมายเหตุ: มีเพียงสมาชิกของบล็อกนี้เท่านั้นที่สามารถแสดงความคิดเห็น