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>Video hướng dẫn thực hiện
