Giáo án link (giaoan.link) chia sẻ đến các bạn một project mới Google sheet App script – CRUD New – V1. Với Project này bạn có thể tạo mới, đọc, cập nhật, xóa trên giao diện webapp – Thêm chức năng xác nhận mật khẩu khi xóa dữ liệu.
Bạn xem thêm các project excel ứng dụng khác:
- Google sheet apps script | Scan QR code – Filter and get data table display on webapp
- Google sheet apps script Filter to get data to display on webapp, fill background color for data row
- Google sheet Apps script Webapp | Project Quản lý đơn hàng – Cập nhật sản phẩm – In phiếu kiểm soát
- Google sheet Apps script Webapp | Tạo QR Code động – Tự động load mã QR mới khi nội dung mã hóa đổi
- Googlesheet Apps script Webapp | Tạo trang trắc nghiệm online như Quiz
- Google sheet Apps script | Trang trắc nghiệm Quiz – Cập nhật câu hỏi, trả lời, thời gian đếm ngược
- Google sheets | Number to text, Hàm đọc số thành chữ Ứng dụng taoh hóa đơn, phiếu chi.
- Googlesheet Apps script Webapp | Tạo mã QR Code từ nội dung nhập vào – QR Code Generator
- Google sheet apps script | Dropdown đơn giản lấy dữ liệu từ google sheet – Simple dropdown
- Apps script Webapp CSS – HTML – JS | Tạo ứng dụng Ghi chú trên nền tảng webapp – website
Code “Code.gs”
function doGet(request) {
return HtmlService.createTemplateFromFile('Index').evaluate()
.addMetaTag('viewport', 'width=device-width , initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL)
}
/** INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES */
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}
function globalVariables() {
var varArray = {
spreadsheetId: '1YaXqvPZq_tPSGps785xIbIbLWjdcdEHrUP6XVtxEYt0',
dataRange: 'Data!A3:H',
idRange: 'Data!A2:A',
lastCol: 'H',
insertRange: 'Data!A1:H1',
sheetID: '0'
};
return varArray;
}
/** PROCESS FORM */
function processForm(formObject) {
/**--Execute if form passes an ID and if is an existing ID */
if (formObject.RecId && checkID(formObject.RecId)) {
/**--Update Data */
updateData(getFormValues(formObject), globalVariables().spreadsheetId, getRangeByID(formObject.RecId));
} else {
/**--Execute if form does not pass an ID
**--Append Form Data */
appendData(getFormValues(formObject), globalVariables().spreadsheetId, globalVariables().insertRange);
}
//Return last 10 rows
return getAllData();
}
/** 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.email,
formObject.telp,
formObject.gender,
formObject.birthday,
formObject.city,
new Date().toLocaleDateString('vi-VN')
]
];
} else {
/** Reference https://webapps.stackexchange.com/a/51012/244121 */
var values = [
[new Date().getTime().toString(),
formObject.name,
formObject.email,
formObject.telp,
formObject.gender,
formObject.birthday,
formObject.city,
new Date().toLocaleDateString('vi-VN')
]
];
}
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();
}
/**
## 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 'Data!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 ALL RECORDS */
function getAllData() {
var data = readData(globalVariables().spreadsheetId, globalVariables().dataRange);
return data;
}
/*GET DROPDOWN LIST CITY */
function getDropdownListCity(range) {
var list = readData(globalVariables().spreadsheetId, range);
return list;
}
function getNewHtml(e) {
var html = HtmlService
.createTemplateFromFile('Index') // uses templated html
.evaluate()
.getContent();
return html;
}
Code “Index.html”
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<!-- See JavaScript.html file -->
<?!= include('JavaScript'); ?>
<!-- See stylecss.html file -->
<?!= include('Stylecss'); ?>
</head>
<body>
<div class="container-fluid">
<!-- The Modal Form -->
<div class="modal" id="myModal" data-bs-backdrop="static" data-bs-keyboard="false">
<div class="modal-dialog modal-lg modal-dialog-scrollable">
<div class="modal-content">
<!-- Modal Header -->
<div class="modal-header">
<h5>FORM NHẬP LIỆU - CHỈNH SỬA</h5>
<button type="button" id="btn-close" class="btn-close" data-bs-dismiss="modal" onclick="clearForm();"></button>
</div>
<!-- Modal body -->
<div class="modal-body">
<!-- See Form.html file -->
<?!= include('Form'); ?>
</div>
<!-- Modal footer -->
<div class="modal-footer">
<button type="submit" class="btn btn-primary btn-sm" form="myForm">Submit</button>
<button type="reset" class="btn btn-secondary btn-sm" value="Reset" form="myForm">Reset</button>
<button type="button" class="btn btn-danger btn-sm" data-bs-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</div>
<!-- DataTable -->
<div class="container-fluid">
<?!= include('DataTable'); ?> <!-- See DataTable.html File -->
</div>
<?!= include('SpinnerModal'); ?>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
</body>
</html>
Code “Form.html”
<div class="card">
<div class="card-header">Khung thông tin</div>
<div class="card-body">
<form id="myForm" onsubmit="handleFormSubmit(this)">
<div id="message"></div>
<input type="text" id="RecId" name="RecId" value="ID" style="display: none">
<!-- Input type text -->
<div class="form-group">
<label for="nama">Họ và tên</label>
<input type="text" class="form-control" name="name" id="name" required>
</div>
<!-- Input text grid -->
<div class="form-group">
<div class="row">
<div class="col">
<label>Email</label>
<input type="email" name="email" id="email" class="form-control" required>
</div>
<div class="col">
<label>Điện thoại</label>
<input type="number" name="telp" id="telp" class="form-control" required>
</div>
</div>
</div>
<!-- Input text grid -->
<div class="form-group">
<div class="row">
<div class="col">
<label>Giới tính</label>
<select class="form-select" name="gender" id="gender" required>
<option selected disabled hidden style='display: none' value=''></option>
<option value="Nam">Nam</option>
<option value="Nữ">Nữ</option>
</select>
</div>
<div class="col">
<label>Năm sinh</label>
<input type="text" class="form-control" name="birthday" id="birthday" placeholder="DD/MM/YYYY" required>
</div>
</div>
</div>
<!-- Input text grid -->
<div class="form-group">
<div class="row">
<div class="col">
<label>Thành phố</label>
<select class="form-select" name="city" id="city" required>
<option selected disabled hidden style='display: none' value=''></option>
</select>
</div>
</div>
</div>
</form>
</div>
</div>
Code “DataTable.html”
<div class="col" style="margin-top:10px; margin-bottom:-10px">
<p class="h4 mb-4 text-center"><strong>SHEET - APPS SCRIPT - WEB APP - CRUD - NEW V1</strong></p>
</div>
<hr>
<button class="btn btn-primary btn-sm" style="width:100px" type="button" id="tambah" data-bs-toggle='modal' data-bs-target="#myModal">Input</button>
<button class="btn btn-danger btn-sm" style="width:100px" type="button" id="refresh" onclick ="google.script.run
.withSuccessHandler(refreshApp)
.getNewHtml()">Refresh</button>
<hr>
<!-- The Data Table is inserted here by JavaScript ------------------ -->
<table id="dataTable" class="table table-striped table-bordered nowrap" style="width:100%"></table>
Code “SpinnerModal.html”
<div class="modal fade" id="spinnerModal" role="dialog" aria-labelledby="spinnerModalLabel">
<div class="modal-dialog modal-dialog-centered" role="document">
<div class="modal-content">
<div class="modal-body text-center">
<div class="spinner-border" role="status">
<span class="visually-hidden">Loading...</span>
</div>
<p class="mt-3">Loading...</p>
</div>
</div>
</div>
</div>
Code “JavaScript.html”
<script src = "https://code.jquery.com/jquery-3.5.1.js" ></script>
<script src = "https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.2.0/js/bootstrap.bundle.min.js" > </script>
<script src = "https://cdn.datatables.net/1.13.3/js/jquery.dataTables.min.js" > </script>
<script src = "https://cdn.datatables.net/1.13.3/js/dataTables.bootstrap5.min.js" > </script>
<script src = "https://cdn.datatables.net/responsive/2.4.0/js/dataTables.responsive.min.js" > </script>
<script src = "https://cdn.datatables.net/responsive/2.4.0/js/responsive.bootstrap5.min.js" > </script>
<script >
/**
* 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);
window.addEventListener("beforeunload", falseState, true);
/**
* INITIALIZE FUNCTIONS ONLOAD
* */
function functionInit() {
$('#spinnerModal').modal('show');
preventFormSubmit();
getAllData();
createCityDropdown();
};
/**
* HANDLE FORM SUBMISSION
* */
function handleFormSubmit(formObject) {
$('#spinnerModal').modal('show');
google.script.run.withSuccessHandler(createTable).processForm(formObject);
setTimeout(function() {
$('#myModal').modal('hide');
}, 2000);
document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Dữ liệu được thêm thành công!.</div>";
document.getElementById("myForm").reset();
var oTable = $('#dataTable').dataTable();
// Hide/show the column after initialisation
oTable.fnSetColumnVis( 0, false );
}
function refreshApp(newHtml) {
$('#spinnerModal').modal('show');
falseState();
document.open();
document.write(newHtml);
document.close();
$('#myModal').modal('hide');
}
function falseState() {
var dtTable = $('#dataTable').DataTable();
dtTable.state.clear();//Clear State
dtTable.destroy();//Destroy
}
/**
* Clear form when pop-up is closed.
* */
function clearForm() {
document.getElementById("message").innerHTML = "";
document.getElementById("myForm").reset();
}
/**
* GET ALL DATA
* */
function getAllData() {
//$('#spinnerModal').modal('show');
//document.getElementById('dataTable').innerHTML = "";
google.script.run.withSuccessHandler(createTable).getAllData();
}
/**
* CREATE THE DATA TABLE
* */
function createTable(dataArray) {
$('#spinnerModal').modal('hide');
if (dataArray) {
var result = "<div>" +
"<table class='table table-sm' style='font-size:1em'>" +
"<thead style='white-space: nowrap'>" +
"<tr>" +
//Change table headings to match witht he Google Sheet
"<th scope='col'>ID</th>" +
"<th scope='col'>Họ và Tên</th>" +
"<th scope='col'>Email</th>" +
"<th scope='col'>Điện thoại</th>" +
"<th scope='col'>Giới tính</th>" +
"<th scope='col'>Năm sinh</th>" +
"<th scope='col'>Thành phố</th>" +
"<th scope='col'>Ngày cập nhật</th>" +
"<th scope='col'>Edit</th>" +
"<th scope='col'>Delete</th>" +
"</tr>" +
"</thead>";
for (var i = 0; i < dataArray.length; i++) {
result += "<tr>";
for (var j = 0; j < dataArray[i].length; j++) {
result += "<td>" + dataArray[i][j] + "</td>";
}
result += "<td><i style='color: orange' class='fa fa-duotone fa-pen-to-square' data-bs-toggle='modal' data-bs-target='#myModal' onclick='editData(this);'></td>";
result += "<td><i style='color: red' class='fa fa-sharp fa-solid fa-trash' onclick='deleteData(this);'></td>";
result += "</tr>";
}
result += "</table></div>";
var div = document.getElementById('dataTable');
div.innerHTML = result;
$(document).ready(function() {
$('#dataTable').DataTable({
destroy: true,
responsive: true,
select: true,
stateSave: true,
ordering:true,
order: [[0, 'desc' ]],
pageLength: 5,
lengthMenu: [
[5, 10, 25, 50, 100, -1],
['5', '10', '25', '50', '100', 'All']
],
columnDefs: [{
targets: [1, 8, 9],
className: 'all',
},
{
targets: [0],
visible: false, //hide kolom pertama/0
searchable: true,
},
{
targets: [3],
className: 'dt-body-center',
"render": function(data, type, row, meta) {
if (type === 'display' && data.length > 5) {
data = row[3] + ' '+'<i class="fa-brands fa-whatsapp" style="font-size:20px;color: green"></i>' ;
}
return data;
}
},
]
});
});
}
}
/**
* DELETE DATA
* */
function deleteData(el) {
var oTable = $('#dataTable').dataTable();
// Hide the second column after initialisation
oTable.fnSetColumnVis(0, true);
Swal.fire({
title: 'Bạn có chắc xóa không?',
icon: 'warning',
html: `<input type="password" id="password" class="swal2-input" placeholder="Điền mật khẩu!">`,
showCancelButton: true,
confirmButtonColor: '#3085d6',
cancelButtonColor: '#d33',
cancelButtonText: 'Hủy',
confirmButtonText: 'OK, Xóa dữ liệu này!',
allowOutsideClick: false,
preConfirm: () => {
var pass = "123";
var password = Swal.getPopup().querySelector('#password').value
if (password == pass) {
var recordId = el.parentNode.parentNode.cells[0].innerHTML;
google.script.run.withSuccessHandler(createTable).deleteData(recordId);
oTable.fnSetColumnVis(0, false);
} else {
Swal.showValidationMessage('Mật khẩu không hợp lệ!')
}
},
}).then((result) => {
if (result.isConfirmed) {
Swal.fire(
'Chúc mừng!',
'Dữ liệu xóa thành công!',
'success',
)
} else {
Swal.fire(
'Đã hủy!',
'Đã hủy xóa tệp này:)',
'error',
)
oTable.fnSetColumnVis(0, false);
}
});
}
//FOR POPULATE FORM------------------------------------------------------------------------------------------------------
//RETRIVE DATA FROM GOOGLE SHEET FOR CITY DROPDOWN
function createCityDropdown() {
//SUBMIT YOUR DATA RANGE FOR DROPDOWN AS THE PARAMETER
google.script.run.withSuccessHandler(cityDropDown).getDropdownListCity("City!A1:A");
}
//POPULATE CITY DROPDOWNS
function cityDropDown(values) { //Ref: https://stackoverflow.com/a/53771955/2391195
var list = document.getElementById('city');
for (var i = 0; i < values.length; i++) {
var option = document.createElement("option");
option.value = values[i];
option.text = values[i];
list.appendChild(option);
}
}
/**
* EDIT DATA
* https://stackoverflow.com/a/32377357/2391195
* */
function editData(el) {
var oTable = $('#dataTable').dataTable();
// // Hide/show the column after initialisation
oTable.fnSetColumnVis( 0, true );
var recordId = el.parentNode.parentNode.cells[0].innerHTML;
google.script.run.withSuccessHandler(populateForm).getRecordById(recordId);
}
/**
* POPULATE FORM
* */
function populateForm(records) {
document.getElementById('RecId').value = records[0][0];
document.getElementById('name').value = records[0][1];
document.getElementById('email').value = records[0][2];
document.getElementById('telp').value = records[0][3];
document.getElementById('gender').value = records[0][4];
document.getElementById('birthday').value = records[0][5];
document.getElementById('city').value = records[0][6];
document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Update [ID: " + records[0][0] + "]</div>";
}
</script>
Code “Stylecss.html”
<link href="https://cdn.datatables.net/v/bs5/jszip-2.5.0/b-2.3.5/b-html5-2.3.5/datatables.min.css" />
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css">
<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.2.0/css/bootstrap.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/1.13.3/css/dataTables.bootstrap5.min.css" rel="stylesheet">
<link href="https://cdn.datatables.net/responsive/2.4.0/css/responsive.bootstrap5.min.css" rel="stylesheet">
<style>
body {
font-family: sans-serif;
font-size: 14px;
}
input {
font-family: sans-serif;
font-size: 14px;
}
.form-group {
position: relative;
margin-bottom: 1.5rem;
}
</style>