Data Entry Form Google sheet thêm chức năng Edit, Delete
Giaoan.link chia sẻ đến các bạn cách thực hiện một Data Entry Form Google sheet thêm chức năng Edit, Delete. Các chức năng cụ thể như sau:
- Ứng dụng trên Google sheets, apps script, html
- Các chức năng: thêm mới, chỉnh sửa, xóa mẫu tin.
- Chức năng load bảng dữ liệu lên giao diện web để xem và chỉnh sửa.
Video hướng dẫn cụ thể cách thực hiện Google sheet form ở cuối bài viết, mời bạn xem!
Các code trong bài viết với các trang tương ứng.
Code trang “Code.gs”
function doGet(request) {
return HtmlService.createTemplateFromFile('Index').evaluate();
}
/* DEFINE GLOBAL VARIABLES, CHANGE THESE VARIABLES TO MATCH WITH YOUR SHEET */
function globalVariables(){
var varArray = {
spreadsheetId : '1WX9LnwwWy4dDVg8FngrIqL2LxfuaStQJ2pbizBic6u8', //** CHANGE !!!
dataRage : 'Data!A2:F', //** CHANGE !!!
idRange : 'Data!A2:A', //** CHANGE !!!
lastCol : 'F', //** CHANGE !!!
insertRange : 'Data!A1:F1', //** CHANGE !!!
sheetID : '0' //** CHANGE !!! Ref:https://developers.google.com/sheets/api/guides/concepts#sheet_id
};
return varArray;
}
/*
# PROCESSING FORM ---------------------------------------------------------------------------------
*/
/* 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 getLastTenRows();//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
]];
}else{
var values = [[
new Date().getTime().toString(),
formObject.name,
formObject.gender,
formObject.dateOfBirth,
formObject.email,
formObject.phone
]];
}
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){
//https://developers.google.com/sheets/api/guides/batchupdate
//https://developers.google.com/sheets/api/samples/rowcolumn#delete_rows_or_columns
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 getLastTenRows();//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 '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 LAST 10 RECORDS */
function getLastTenRows(){
var lastRow = readData(globalVariables().spreadsheetId,globalVariables().dataRage).length+1;
if(lastRow<=11){
var range = globalVariables().dataRage;
}else{
var range = 'Data!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 ------------------------------------------------------------------------
*/
/* INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES */
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}Code trang “Index.html”
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
<?!= include('JavaScript'); ?> <!-- See JavaScript.html file -->
<?!= include('CSS'); ?> <!-- See CSS.html file -->
</head>
<body onload="createCountryDropdown()">
<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">
<?!= include('DataTable'); ?> <!-- See DataTable.html File -->
</div>
</div>
</div>
</body>
</html>Code trang “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">NHẬP THÔNG TIN</p>
<div id="message"></div>
<input type="text" id="RecId" name="RecId" value="" style="display: none">
<div class="form-group">
<label for="name" >Họ và tên</label>
<input type="text" class="form-control" id="name" name="name" placeholder="Điền họ và tên" required>
</div>
<div class="form-row">
<div class="form-group col-md-6">
<p>Gender</p>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="gender" id="Nam" value="Nam">
<label class="form-check-label" for="male">Nam</label>
</div>
<div class="form-check form-check-inline">
<input class="form-check-input" type="radio" name="gender" id="Nữ" value="Nữ">
<label class="form-check-label" for="female">Nữ</label>
</div>
</div>
<div class="form-group col-md-6">
<label for="dateOfBirth">Ngày tháng năm sinh</label>
<input type="date" class="form-control" id="dateOfBirth" name="dateOfBirth">
</div>
</div>
<div class="form-group">
<label for="email">Email</label>
<input type="email" class="form-control" id="email" name="email" placeholder="Điền địa chỉ email">
</div>
<div class="form-row">
<div class="form-group col-md-6">
<label for="phone">Điện thoại</label>
<input type="tel" class="form-control" id="phone" name="phone" placeholder="Điền số điện thoại">
</div>
</div>
<button type="submit" class="btn btn-primary">Gửi/Cập nhật</button>
<input class="btn btn-secondary" type="reset" value="Xóa các trường thông tin">
</form>Code trang “Javascrip.html”
<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);
//INITIALIZE FUNCTIONS ONLOAD
function functionInit(){
preventFormSubmit();
getLastTenRows();
};
//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'>Xóa</th>"+
"<th scope='col'>Sửa</th>"+
"<th scope='col'>ID</th>"+
"<th scope='col'>Họ và tên</th>"+
"<th scope='col'>Giới tính</th>"+
"<th scope='col'>Ngày sinh</th>"+
"<th scope='col'>Địa chỉ Email</th>"+
"<th scope='col'>Số điện thoại</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);'>Xóa</button></td>";
result += "<td><button type='button' class='btn btn-warning btn-xs editBtn' onclick='editData(this);'>Sửa</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.getElementById("message").innerHTML = "";
}else{
var div = document.getElementById('dataTable');
div.innerHTML = "Data not found!";
}
}
//DELETE DATA
function deleteData(el) {
var result = confirm("Want to delete?");
if (result) {
var recordId = el.parentNode.parentNode.cells[2].innerHTML;
google.script.run.withSuccessHandler(createTable).deleteData(recordId);
}
}
//EDIT DATA
function editData(el){
var recordId = el.parentNode.parentNode.cells[2].innerHTML; //https://stackoverflow.com/a/32377357/2391195
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(records[0][2]).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("message").innerHTML = "<div class='alert alert-warning' role='alert'>Cập nhật cho [ID: "+records[0][0]+"]</div>";
}
</script>Code trang “DataTable.html”
<p class="h4 mb-4 text-center">DANH SÁCH THÔNG TIN</p>
<div id="dataTable" class="table-responsive">
<!-- The Data Table is inserted here by JavaScript -->
</div>
<br>
<button type="button" class="btn btn-success btn-sm" onclick="getAllData()">Thêm dữ liệu</button>