Site icon Tài liệu miễn phí cho Giáo viên, học sinh.

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:

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

Exit mobile version