18/12/2024
Latest:

Học Ms excel Kỹ năng vi tính 

Google sheet Apps script CRUD Web app, Generate PDF

giaoanppt 0 Comments

Giáo án link (giaoan.link) chia sẻ đến bạn project Google sheet Apps script CRUD Web app, Generate PDF. Với project này bạn sẽ xây dựng được một web app có các chức năng Tạo mới – Đoc – Cập nhật – Xóa – Tạo file PDF với có thông tin được lấy từ dữ liệu trên sheet và merge vào file pdf. Dưới đây là code apps script và video hướng dẫn bên dưới.

Các project excel ứng dụng khác:

Code.gs

/** 
 * Youtube: https://youtube.com/netmediacctv
 */

//CONSTANTS
const SPREADSHEETID = "DIEN SPREADSHEET ID";
const DATARANGE = "Data!A2:F";
const DATASHEET = "Data";
const DATASHEETID = "0";
const LASTCOL = "F";
const IDRANGE = "Data!A2:A";


//Display HTML page
function doGet(request) {
  let html = HtmlService.createTemplateFromFile('Index').evaluate();
  let htmlOutput = HtmlService.createHtmlOutput(html);
  htmlOutput.addMetaTag('viewport', 'width=device-width, initial-scale=1');
  return htmlOutput;
}

//PROCESS SUBMITTED FORM DATA
function processForm(formObject) {
  if (formObject.recId && checkId(formObject.recId)) {
//Generate pdf
    let slidefile = DriveApp.getFileById('Google Slides ID');
    let pdffolder = DriveApp.getFolderById('Folder drive ID');
    let copyFile = slidefile.makeCopy(formObject.hovaten);// Lay ten file pdf theo truong hovaten.
    let copyId = copyFile.getId();
    let copyDoc = SlidesApp.openById(copyId); 

        copyDoc.replaceAllText('{hovaten}',formObject.hovaten)
        copyDoc.replaceAllText('{condition}',formObject.condition)
        copyDoc.replaceAllText('{lop}',formObject.lop)        
        copyDoc.saveAndClose()

    let pdffile = pdffolder.createFile(copyFile.getAs("application/pdf"));   
    let pdfurl = pdffile.getUrl();    
        copyFile.setTrashed(true);
        

    //End Generate pdf  
    const values = [[
      formObject.recId,
      formObject.hovaten,
      formObject.condition,
      formObject.lop,
      pdfurl,   
      new Date().toLocaleString()  
    ]];
    const updateRange = getRangeById(formObject.recId);
    //Update the record
    updateRecord(values, updateRange);
  } else {
    //Prepare new row of data
    let values = [[
      generateUniqueId(),
      formObject.hovaten,
      formObject.condition,
      formObject.lop,  
      formObject.null,
      new Date().toLocaleString()
    ]];
    //Create new record
    createRecord(values);
  }

  //Return the last 10 records
  return getLastTenRecords();
}


/**
 * CREATE RECORD
 * REF:
 * https://developers.google.com/sheets/api/guides/values#append_values
 */
function createRecord(values) {
  try {
    let valueRange = Sheets.newRowData();
    valueRange.values = values;

    let appendRequest = Sheets.newAppendCellsRequest();
    appendRequest.sheetId = SPREADSHEETID;
    appendRequest.rows = valueRange;

    Sheets.Spreadsheets.Values.append(valueRange, SPREADSHEETID, DATARANGE, { valueInputOption: "RAW" });
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
}

/**
 * READ RECORD
 * REF:
 * https://developers.google.com/sheets/api/guides/values#read
 */
function readRecord(range) {
  try {
    let result = Sheets.Spreadsheets.Values.get(SPREADSHEETID, range);
    return result.values;
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
}

/**
 * UPDATE RECORD
 * REF:
 * https://developers.google.com/sheets/api/guides/values#write_to_a_single_range
 */
function updateRecord(values, updateRange) {
  try {
    let valueRange = Sheets.newValueRange();
    valueRange.values = values;
    Sheets.Spreadsheets.Values.update(valueRange, SPREADSHEETID, updateRange, { valueInputOption: "RAW" });
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
}

/**
 * DELETE RECORD
 * Ref:
 * https://developers.google.com/sheets/api/guides/batchupdate
 * https://developers.google.com/sheets/api/samples/rowcolumn#delete_rows_or_columns
*/
function deleteRecord(id) {
  const rowToDelete = getRowIndexById(id);
  const deleteRequest = {
    "deleteDimension": {
      "range": {
        "sheetId": DATASHEETID,
        "dimension": "ROWS",
        "startIndex": rowToDelete,
        "endIndex": rowToDelete + 1
      }
    }
  };
  Sheets.Spreadsheets.batchUpdate({ "requests": [deleteRequest] }, SPREADSHEETID);
  return getLastTenRecords();
}

/**
 * RETURN LAST 10 RECORDS IN THE SHEET
 */
function getLastTenRecords() {
  let lastRow = readRecord(DATARANGE).length + 1;
  let startRow = lastRow - 9;
  if (startRow < 2) { //If less than 10 records, eleminate the header row and start from second row
    startRow = 2;
  }
  let range = DATASHEET + "!A" + startRow + ":" + LASTCOL + lastRow;
  let lastTenRecords = readRecord(range);
  Logger.log(lastTenRecords);
  return lastTenRecords;
}


//GET ALL RECORDS
function getAllRecords() {
  const allRecords = readRecord(DATARANGE);
  return allRecords;
}

//GET RECORD FOR THE GIVEN ID
function getRecordById(id) {
  if (!id || !checkId(id)) {
    return null;
  }
  const range = getRangeById(id);
  if (!range) {
    return null;
  }
  const result = readRecord(range);
  return result;
}

function getRowIndexById(id) {
  if (!id) {
    throw new Error('Invalid ID');
  }

  const idList = readRecord(IDRANGE);
  for (var i = 0; i < idList.length; i++) {
    if (id == idList[i][0]) {
      var rowIndex = parseInt(i + 1);
      return rowIndex;
    }
  }
}

//VALIDATE ID
function checkId(id) {
  const idList = readRecord(IDRANGE).flat();
  return idList.includes(id);
}

//GET DATA RANGE IN A1 NOTATION FOR GIVEN ID
function getRangeById(id) {
  if (!id) {
    return null;
  }
  const idList = readRecord(IDRANGE);
  const rowIndex = idList.findIndex(item => item[0] === id);
  if (rowIndex === -1) {
    return null;
  }
  const range = `Data!A${rowIndex + 2}:${LASTCOL}${rowIndex + 2}`;
  return range;
}

//INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
    .getContent();
}

//GENERATE UNIQUE ID
function generateUniqueId() {
  let id = Utilities.getUuid();
  return id;
}

//SEARCH RECORDS
function searchRecords(formObject) {
  let result = [];
  try {
    if (formObject.searchText) {//Execute if form passes search text
      const data = readRecord(DATARANGE);
      const searchText = formObject.searchText;

      // Loop through each row and column to search for matches
      for (let i = 0; i < data.length; i++) {
        for (let j = 0; j < data[i].length; j++) {
          const cellValue = data[i][j];
          if (cellValue.toLowerCase().includes(searchText.toLowerCase())) {
            result.push(data[i]);
            break; // Stop searching for other matches in this row
          }
        }
      }
    }
  } catch (err) {
    console.log('Failed with error %s', err.message);
  }
  return result;
}

HTMLForm.html

<form id="ProductDetails" onsubmit="handleFormSubmit(this)">
  <p class="h4 mb-4 text-center" style="color: blue;">THÊM/CẬP NHẬT THÔNG TIN</p>
  <div id="message"></div>
  <input type="text" id="recId" name="recId" value="" style="display: none">

 <div class="mb-1">
    <div class="form-check form-check-inline">
      <label style="font-weight: bold;" for="hovaten" class="form-label">Họ và Tên:</label>
      <input type="text" id="hovaten" name="hovaten" class="form-control form-control-sm" required>
    </div>
   
    <div class="form-check form-check-inline">      
      <input type="radio" id="Nam" name="condition" class="form-check-input" value="Nam" required>
      <label for="Nam" class="form-check-label">Nam</label>
    </div>
    <div class="form-check form-check-inline">
      <input type="radio" id="Nu" name="condition" class="form-check-input" value="Nu" required>
      <label for="Nu" class="form-check-label">Nữ</label>
    </div>
 </div>

  <div class="mb-1">
    <div class="form-check form-check-inline">
      <label style="font-weight: bold;" for="lop" class="form-label">Tên lớp:</label>
      <select id="lop" name="lop" class="form-select form-select-sm" required>
            <option value="">-Chọn lớp-</option>
            <option value="Điện tử">Điện tử</option> 
            <option value="Tin học">Tin học</option> 
            <option value="Xây dựng">Xây dựng</option>               
      </select>
    </div>
  </div> 
  <hr>
  <div class="mb-1">    
  <div class="form-check form-check-inline">    
   <button type="submit" class="btn btn-primary">Save/Update/Pdf</button> 
  </div>
  </div> 
</form>

DataTable.html

<p class="h4 mb-4 text-center" style="color: red;">DANH SÁCH SINH VIÊN</p>
<!-- SEARCH FORM-->
<form id="search-form" class="form-inline" onsubmit="handleSearchForm(this)">
  <div class="input-group">
    <input type="text" class="form-control form-control-sm mb-2" id="searchText" name="searchText" placeholder="Gõ từ khóa..." required>
    <button type="submit" class="btn btn-sm btn-primary mb-2">Tìm kiếm</button>
  </div>
</form>
<!-- END - SEARCH FORM-->

<div class="table-responsive">
  <div id="dataTable">
    <!-- The Data Table is inserted here by JavaScript -->
  </div>
</div>
<button type="button" class="btn btn-success btn-sm" onclick="getAllRecords()">Hiển Thị Tất Cả</button>

JavaScript.html

<script src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/js/bootstrap.bundle.min.js"
  integrity="sha384-ENjdO4Dr2bkBIFxQpeoTz1HIcje39Wm4jDKdf19U8gI4ddQ3GYNS7NTKfAdVQSZe" crossorigin="anonymous"></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); 
  
  //INITIALIZE FUNCTIONS ONLOAD
  function functionInit(){
    $('#spinnerModal').modal('show');  
    preventFormSubmit();
    getLastTenRows();
    createCountryDropdown();
  };  
  
//POPULATE COUNTRY DROPDOWNS
  function countryDropDown(values) { //Ref: https://stackoverflow.com/a/53771955/2391195
    var list = document.getElementById('countryOfOrigin');   
    for (var i = 0; i < values.length; i++) {
      var option = document.createElement("option");
      option.value = values[i];
      option.text = values[i];
      list.appendChild(option);
    }
  }    
  
  //HANDLE FORM SUBMISSION
  function handleFormSubmit(formObject) {
    $('#spinnerModal').modal('show');
    google.script.run.withSuccessHandler(createTable).processForm(formObject);
    document.getElementById("ProductDetails").reset();
  }
  
  function deleteRecord(el) {
    var result = confirm("Bạn chắc xóa?");
    if (result) {
      $('#spinnerModal').modal('show');
      var recordId = el.parentNode.parentNode.cells[2].innerHTML;
      google.script.run.withSuccessHandler(createTable).deleteRecord(recordId);
      document.getElementById("ProductDetails").reset();
    }
  }

  
  //GET LAST 10 ROWS
  function getLastTenRows (){
   google.script.run.withSuccessHandler(createTable).getLastTenRecords();
  }

  function editRecord(el){
    $('#spinnerModal').modal('show');
    let id = el.parentNode.parentNode.cells[2].innerHTML;
    google.script.run.withSuccessHandler(populateForm).getRecordById(id);
  }

  function populateForm(data){
    $('#spinnerModal').modal('hide');
    document.getElementById('recId').value = data[0][0];
    document.getElementById('hovaten').value = data[0][1];
    document.getElementById(data[0][2]).checked = true;
    document.getElementById('lop').value = data[0][3]; 
       
    document.getElementById("message").innerHTML = "<div class='alert alert-warning' role='alert'>Cập nhật cho ID: ["+data[0][0]+"]</div>";
  }

  //CREATE THE DATA TABLE
  function createTable(dataArray) {
    $('#spinnerModal').modal('hide');
  if (dataArray && dataArray.length) {
    var result =
      "<table class='table table-sm' style='font-size:0.8em'>" +
      "<thead style='white-space: nowrap'>" +
      "<tr>" +
      "<th scope='col' style='text-align: center;background-color:#fffd51;'>Delete</th>" +
      "<th scope='col'style='text-align: center;background-color:#fffd51;'>Edit</th>" +
      "<th scope='col' style='display:none;'>ID</th>" + // Hide the ID column header
      "<th scope='col'style='text-align: center;background-color:#fffd51;'>Họ và Tên</th>" +    
      "<th scope='col'style='text-align: center;background-color:#fffd51;'>Giới tính</th>" +
      "<th scope='col'style='text-align: center;background-color:#fffd51;'>Lớp</th>" +  
      "<th scope='col'style='text-align: center;background-color:#fffd51;'>PDF</th>" +    
      "<th scope='col'style='display:none; text-align: center;background-color:#fffd51;'>UPDATE</th>" +   
      "</tr>" +
      "</thead>";
    for (var i = 0; i < dataArray.length; i++) {
      result += "<tr>";
      result +=
        "<td > <button type='button' class='btn btn-danger btn-custom deleteBtn' onclick='deleteRecord(this);'>Delete</button></td>";
      result +=
        "<td ><button type='button' class='btn btn-warning btn-custom editBtn' onclick='editRecord(this);'>Edit</button></td>";
      for (var j = 0; j < dataArray[i].length; j++) {
        if (j === 0) {
          result +=
            "<td style='display:none;'>" + dataArray[i][j] + "</td>"; // Hide the ID column data
        }else if(j===5){
          result +=
            "<td style='display:none;'>" + dataArray[i][j] + "</td>"; // Date of Update 
        }else if(j===1){
          result +=
            "<td style='text-align: left'>" + dataArray[i][j] + "</td>"; // Date of Update 
        }else if(j===4){          
           result += 
           "<td style='text-align: center;'>"+ (dataArray[i][j]= /www |http/.test(dataArray[i][j]) ? "<a class='btn btn-primary text-white  btn-custom' target='_blank' href="+dataArray[i][j] + "><i class='far fa-arrow-alt-circle-down'></i> PDF</a>": dataArray[i][j]) + "</td>"; 
        }else {
          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 = "Không có dữ liệu nào được tìm thấy!";
  }
}

//SEARCH RECORDS
function handleSearchForm(formObject) {
  $('#spinnerModal').modal('show');
  google.script.run.withSuccessHandler(createTable).searchRecords(formObject);
  document.getElementById("search-form").reset();
}

function getAllRecords(){
    $('#spinnerModal').modal('show');
    google.script.run.withSuccessHandler(createTable).getAllRecords();
  }

</script>

Index.html

<!DOCTYPE html>
<html>

<head>
  <title>Quản Lý Sinh vien</title>
  <?!= include('JavaScript'); ?>  <!-- See JavaScript.html file -->
  <?!= include('CSS'); ?>  <!-- See JavaScript.html file -->
</head>

<body>
  <div class="container">
    <div class="row">
      <div class="col-lg-5">
        <?!= include('HTMLForm'); ?>
      </div>
      <div class="col-lg-7">
        <?!= include('DataTable'); ?>
      </div>
    </div>
  </div>
  <?!= include('SpinnerModal'); ?>  
</body>

</html>

CSS.html

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.1/css/all.min.css" integrity="sha512-MV7K8+y+gLIBoVD59lQIYicR65iaqukzvf/nwasF0nqhPay5w/9lJmVM2hMDcnK1OnMGCdVK+iQrJ7lzPJQd1w==" crossorigin="anonymous" referrerpolicy="no-referrer" />

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet"
  integrity="sha384-KK94CHFLLe+nY2dmCWGMq91rCGa5gtU4mk92HdvYe+M/SXH301p5ILy+dN9+nJOZ" crossorigin="anonymous">
<style>
  .btn-custom {
    font-size: 0.5rem;
    padding: 0.25rem 0.5rem;
  }
</style>

SpinnerModal.html

<div class="modal fade" id="spinnerModal" tabindex="-1" role="dialog" aria-labelledby="spinnerModalLabel"
  aria-hidden="true">
  <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>

Video hướng dẫn thực hiện