18/12/2024
Latest:

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

Google sheet Apps script App web tạo Dropdown phụ thuộc 3 cấp Dependent dropdown

giaoanppt 0 Comments ,

Giáo án link (giaoan.link) chia sẻ đến các bạn project về Google sheet Apps script App web tạo Dropdown phụ thuộc 3 cấp Dependent dropdown. Các bạn có thể thực hiện thông qua video hướng dẫn bên dưới cùng đoạn code apps script mẫu.

Các bài thực hành excel khác:

Code Apps Script

--Code.gs--
function doGet(e) {
return HtmlService.createTemplateFromFile("form").evaluate() 
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
    .getContent();
}

function getObject(){
  const obj={}
  const id = '11KQ_7eCKvTyZiQgKQsdkw-ZPeL6FwNaj7efhS2J4yW8';
  const name = 'Options';
  const data = SpreadsheetApp.openById(id).getSheetByName(name).getDataRange().getDisplayValues().slice(1);
  data.forEach( ([subjectKey, topicKey, chapter]) => {
    const subject = obj[subjectKey]
    if(!subject){
      obj[subjectKey] = {}
      obj[subjectKey][topicKey] = [chapter]
      }else{
        const topic = subject[topicKey]
        if(!topic){
          subject[topicKey] = [chapter]
          }else{
            topic.push(chapter)
          }
      }
  })

return obj;
}


function save(data) {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/11KQ_7eCKvTyZiQgKQsdkw-ZPeL6FwNaj7efhS2J4yW8/edit#gid=0");
  var sheet = ss.getSheetByName("Data");
  var date = new Date().toLocaleDateString('vi-VN');
  
  sheet.appendRow([data.sub, data.tp, data.ch, data.inp,date])
  return true;
}



--Formm.html--
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<?!=include("CSS")?>
</head>
<body>
  <div class="container mt-2">
    <div>
      <h3>VÍ DỤ VỀ DROPDOWN PHỤ THUỘC<br>Denpendent Dropdown</h3>
      <div class="col-12 mb-3 mt-5"> 
        <h5 style="color: red">Loại sách:</h5>
        <select class="form-control" name="subject" id="subject"> 
          <option values="" selected="selected">Chọn thể loại sách</option>
        </select>
      </div>
      <div class="col-12 mb-3">
        <h5 style="color: blue">Khối lớp:</h5>
        <select class="form-control" name="topic" id="topic">
        <option value="" selected="selected">Vui lòng chọn Thể loại sách trước</option>
        </select>
      </div>
      
      <div class="col-12 mb-3">
        <h5 style="color: green">Môn học:</h5>
        <select class="form-control" name="chapter" id="chapter">
        <option value="" selected="selected">Vui lòng chọn Khối lớp trước</option>  
      </select>
    </div>
    
    <div class="col-12 mb-3">
      <h5 style="color: brown">Để lại lời nhắn:</h5>
      <textarea class="form-control" id="input4" placeholder="Ghi nội dung..."></textarea>
    </div>
    
    <div class="col-12 mb-3">
      <button class="btn btn-primary" id="submit">SUBMIT</button>
    </div>
    </div>
  </div>
  <?!=include("Javascript")?>
</body>
</html>


---Javascript---
<script>
  window.onload = () =>{
    google.script.run.withSuccessHandler(loadObject).getObject(); 
    }
  
  const loadObject = function(subjectObject) {
    var subjectSel = document.getElementById("subject");
    var topicSel = document.getElementById("topic");
    var chapterSel = document.getElementById("chapter");
    for (var x in subjectObject) {
      subjectSel.options[subjectSel.options.length] = new Option(x, x);
    }
    subjectSel.onchange = function() {
      //empty Chapters- and Topics- dropdowns
      chapterSel.length = 1;
      topicSel.length = 1;
      //display correct values
      for(var y in subjectObject[this.value]){
        topicSel.options[topicSel.options.length] = new Option (y, y);
      }
    }
    
    topicSel.onchange = function() {
      //empty Chapters dropdown 
      chapterSel.length = 1;
      //display correct values
      var z = subjectObject[subjectSel.value][this.value];
      for(var i = 0; i < z.length; i++) {
        chapterSel.options[chapterSel.options.length] = new Option(z[i], z[i]);
      }
    }
  }
  
  var subj = document.getElementById("subject");
  var tpc = document.getElementById("topic");
  var chapt = document.getElementById("chapter");
  var inp4 = document.getElementById("input4");
  
  document.getElementById("submit").addEventListener("click", saveData);

  function saveData(){ 
    var data ={
      sub: subj.value,
      tp: tpc.value,
      ch: chapt.value,
      inp: inp4.value
    };    
   
    google.script.run.withSuccessHandler(afterSubmit).save(data);
  }
  
  function afterSubmit() {
    subj.selectedIndex = 0;
    tpc.selectedIndex = 0;
    chapt.selectedIndex = 0;
    inp4.value = "";
}
</script>



----CSS----
<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">
<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>
<style>
  body {
    font-family: 'Tahoma';
  }
  .container {
    max-width: 500px;
    margin: auto;
    background: white;
    padding: 10px;
  }
  h3{text-align: center;}
  </style>

Xem video hướng dẫn