Google sheet App script – CRUD New – V1

01/01/202401/01/2024 giaoanppt 0 Comments crud, google apps script

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