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

Googel sheet Apps Script Tìm kiếm dữ liệu từ Sheet, hiển thị lên webapp 2 bảng kết quả

giaoanppt

Giaoan.link chia sẻ đến các bạn về Googel sheet Apps Script Tìm kiếm dữ liệu từ Sheet, hiển thị lên webapp 2 bảng kết quả. Với webapp này bạn dễ dàng tìm dữ liệu và lấy kết quả hiển thị ở hai bảng kết quả trên webapp html. Bên dưới đây là code apps script và link youtube hướng dẫn cụ thể.

Các bài tập excel khác:

Code trang “Code.gs”

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate()
  .setTitle('WebApp - More Tables')
  .addMetaTag('viewport', 'width=device-width, inital-scale=1')
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

/* PROCESS FORM 1 */
function processForm(formObject){  
  var result = "";
  if(formObject.searchtext){//Execute if form passes search text
      result = search(formObject.searchtext);
  }
  return result;
}


//SEARCH FOR MATCHED CONTENTS 1
function search(searchtext){
  var spreadsheetId   = 'PASTE YOUR SHEET ID'; //** CHANGE !!!
  var dataRange        = 'Data!A2:G';                                    //** CHANGE !!!
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRange).values;
  var ar = [];
  
  data.forEach(function(f) {
    if (~[ f[0].toString().toLowerCase() ].indexOf(searchtext.toString().toLowerCase())) {
      ar.push([f[0],f[1],f[2]]);
    }
  });
  return ar;
}


/* PROCESS FORM 2 */
function processForm2(formObject){  
  var result = "";
  if(formObject.searchtext){//Execute if form passes search text
      result = search2(formObject.searchtext);
  }
  return result;
}
//SEARCH FOR MATCHED CONTENTS 2
function search2(searchtext){
  var spreadsheetId   = 'PASTE YOUR SHEET ID'; //** CHANGE !!!
  var dataRange        = 'Data!A2:G';                                    //** CHANGE !!!
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRange).values;
  var ar = [];
  
  data.forEach(function(f) {
    if (~[ f[0].toString().toLowerCase() ].indexOf(searchtext.toString().toLowerCase())) {
      ar.push([f[3],f[4],f[5], f[6]]);
    }
  });
  return ar;
}

Code trang “Index.html”

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-giJF6kkoqNQ00vy+HMDP7azOuL0xtbfIcaT9wjKHr8RbDVddVHyTfAAsrekwKmP1" crossorigin="anonymous">
        <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.9.1/font/bootstrap-icons.css">

        <style>
            table th, td{
              width: 30%;
              text-align: center;
            }
      
            #loadingData{
               position: fixed;
               top: 0;
               left: 0;
               z-index: 10000;
               width: 100vw;
               height: 100vh;
               background-color: rgba(255,255,255,0.5); 
            }
        </style>
        
    </head>
    <body>
        <div class="container">
            <br>
            <div class="row">
              <div class="col">            
                  <!-- ## SEARCH FORM ------------------------------------------------ -->
                  <center><form id="search-form" onsubmit="handleFormSubmit(this)">
                    <div class="form-group fw-bold fs-2" style="color:red; ">
                      TRA CỨU ĐIỂM SINH VIÊN
                    </div>
                    <div class="form-group fw-bold fst-italic fs-6 " style="color:blue; ">
                      (Ví dụ Webapp lấy thông tin từ Sheet lên 2 bảng kết quả)
                    </div>
                    <br>
                    <div class="col-md-6 mb-2">                    
                      <div class="input-group">
                        <span class="input-group-text bg-primary text-white"><i class="bi bi-people-fill"></i></span>
                        <input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Ghõ tên sinh viên...">
                      </div>
                    </div><p>

                    <button id="search" type="submit" class="btn btn-success mb-2">Tìm thông tin</button>
                    <span id="spinner" class="spinner-border spinner-border-sm d-none" role="status" aria-hidden="true"></span>
                  </form>
                  </center>
                  <!-- ## SEARCH FORM ~ END ------------------------------------------- -->              
              </div>    
            </div>

            <div class="row">
              <div class="col">            
                <!------------ ## TABLE OF SEARCH RESULTS 1 ------------>
                <div id="search-results" class="table-responsive mb-4">
                <!-- The Data Table is inserted here by JavaScript ----->
                </div>
                <!------ ## TABLE OF SEARCH RESULTS ~ END -------------->


                <!------ ## TABLE OF SEARCH RESULTS 2 ------------------>
                <div id="search-results2" class="table-responsive">
                  <!-- The Data Table is inserted here by JavaScript --->
                </div>
                <!------ ## TABLE OF SEARCH RESULTS ~ END -------------->             
              </div>
            </div>

        </div>
        <div id="loadingData" class="d-flex justify-content-center align-items-center invisible"> 
          <div class="spinner-border spinner-border-sm text-secondary" style="width: 3rem; height: 3rem;" role="status"></div>
        </div>
       <script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.5.4/dist/umd/popper.min.js" integrity="sha384-q2kxQ16AaE6UbzuKqyBE9/u/KzioAlnx2maXQHiDX9d4/zp8Ok3f+M7DPm+Ib6IU" crossorigin="anonymous"></script>
      <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta1/dist/js/bootstrap.min.js" integrity="sha384-pQQkAEnwaBkjpqZ8RU1fF1AKtTcHJwFl3pblpTlHXybJjHpMYo79HY3hIi4NKxyj" crossorigin="anonymous"></script>

      <!--##JAVASCRIPT FUNCTIONS ------------------------------------->
      <script>
        //PREVENT FORMS FROM SUBMITTING / PREVENT DEFAULT BEHAVIOUR
        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", preventFormSubmit, true); 
            
        
        //HANDLE FORM SUBMISSION
        function handleFormSubmit(formObject) {
          document.getElementById('search').innerHTML = "Loading";        
          document.getElementById('loadingData').classList.toggle('invisible');
          google.script.run.withSuccessHandler(createTable).processForm(formObject);
          google.script.run.withSuccessHandler(createTable2).processForm2(formObject);
          document.getElementById("search-form").reset();
        }
      
        //CREATE THE DATA TABLE 1
        function createTable(dataArray) {
          document.getElementById('search').innerHTML = "Tìm thông tin";
          document.getElementById('loadingData').classList.toggle('invisible');
          if(dataArray && dataArray !== undefined && dataArray.length != 0){
            var result = "<table class='table table-warning table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
                          "<thead style='white-space: nowrap'>"+
                            "<tr>"+                               //Change table headings to match with your Google Sheet
                            "<th scope='col'>TÊN SINH VIÊN</th>"+
                            "<th scope='col'>MÃ SINH VIÊN</th>"+
                            "<th scope='col'>KHOA ĐÀO TẠO</th>"+
                          "</tr>"+
                        "</thead>";
            for(var i=0; i<1; i++) {
                result += "<tr>";
                for(var j=0; j<dataArray[i].length; j++){
                    result += "<td>"+dataArray[i][j]+"</td>";
                }
                result += "</tr>";
            }
            result += "</table>";
            var div = document.getElementById('search-results');
            div.innerHTML = result;
          }else{
            var div = document.getElementById('search-results');              
            div.innerHTML = "Không tìm thấy sinh viên!";
          }
        }

        //CREATE THE DATA TABLE 2
        function createTable2(dataArray) {
          document.getElementById('search').innerHTML = "Tìm thông tin";
          document.getElementById('spinner').classList.add("d-none");
          if(dataArray && dataArray !== undefined && dataArray.length != 0){
            var result = "<table class='table table-dark table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
                          "<thead style='white-space: nowrap'>"+
                            "<tr>"+                               //Change table headings to match with your Google Sheet
                            "<th scope='col'>Môn Học</th>"+
                            "<th scope='col'>Lần Thi</th>"+
                            "<th scope='col'>Điểm Bài Thi</th>"+
                            "<th scope='col'>Đánh Giá</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 += "</tr>";
            }
            result += "</table>";
            var div = document.getElementById('search-results2');
            div.innerHTML = result;
          }else{
            var div = document.getElementById('search-results2');              
            div.innerHTML = "Không tìm thấy kết quả thi!";
          }
        }     
      
      </script>       
    </body>
</html>
Exit mobile version