Google sheet App script – CRUD New – V1

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:

• VBA Excel Xây dựng hàm chuyển chuỗi có dấu thành không dấu, khoản trắng thành dấu –
• Google sheet Webapp | Lấy dữ liệu trên Sheet hiển thị dạng Bảng có phân trang trùy chọn trên website
• Web App Script | Trang trắc nghiệm online hỗ trợ hình ảnh, lưu file kết quả – Form nhập bộ câu hỏi
• Google sheet Webapp Quản lý Lịch dạy học giáo viên trong trung tâm-Lọc theo Tên-Lớp-Khung giờ -Môn
• Google sheet webapp | CRUD FORM – ĐIỀN THÔNG TIN TỪ FORM VÀO TEMPLATE GOOGLE DOC, KÈM HÌNH ẢNH 1
• Google sheet webapp | Dropdown phụ thuộc – Truy xuất Spreadsheet và các Sheet phụ thuộc từ Folder
• Tạo hóa đơn trên Google sheet – Gửi email đính kèm hóa đơn pdf – Quản lý url hóa đơn trên danh sách
• Google sheet Ứng dụng theo dõi đơn hàng – Danh sách sản phẩm mua, trạng thái đơn hàng
• Tip on Ms Word Hướng dẫn tạo ngắt trang, xóa ngắt trang đơn lẽ hoặc toàn bộ ngắt trang trên file doc
• Google sheet | Generate QR Codes – Mã hóa nội dung Cell – Thêm hình ở giữa mã QR code

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] + '&nbsp;'+'<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>

Xem video Demo và Hướng dẫn