25/11/2024
Latest:

Uncategorized 

Google sheet Apps script | Dependent Drop Down List – Danh sách thả phụ thuộc

giaoanppt 0 Comments

Giaoan.link chia sẻ đến các bạn về Google sheet Apps script | Dependent Drop Down List – Danh sách thả phụ thuộc. Danh sách thả xuống phụ thuộc có nghã là: danh sách thứ 3 sẽ phụ thuộc vào danh sách 2 và danh sách 2 phụ thuộc danh sách 1. Mời bạn xem video hướng dẫn và code ở bên dưới.

Các project khác: Bạn tìm kiếm thêm nhiều proeject click vào đây

Code trang “Code.gs”

let MySheets =SpreadsheetApp.getActiveSpreadsheet();
let CitySheet = MySheets.getSheetByName("city");  

function doGet(e) {
  var output = HtmlService.createTemplateFromFile('dropdown');
   
  return output.evaluate().setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
   
}

function removeDuplicates(data) {
  console.log(data);
  newArray = [];
  oldData="";
  data.forEach(function(value) {
    value = value[0];
    if (oldData != value)
      newArray.push(value);
    oldData = value;
 });
 return newArray;
}



function getCountryList() {
  let table = CitySheet.getRange("A2:A").getValues().filter(r => r.every(Boolean)); 
  table = removeDuplicates(table);
  return table;
}


function getStateList(Country) {
  let returnData = CitySheet.getRange("A:A").createTextFinder(Country).matchEntireCell(true).findAll();

    newArray = [];
    returnData.forEach(function (range) {
        let value = CitySheet.getRange(range.getRow(), 2).getValue();
        newArray.push([value]);
    });

    newArray = removeDuplicates(newArray);
    return newArray;
}

function getCityList(State) {
    let returnData = CitySheet.getRange("B:B").createTextFinder(State).matchEntireCell(true).findAll();
    newArray = [];
    returnData.forEach(function (range) {
          let value = CitySheet.getRange(range.getRow(), 3).getValue();
          newArray.push([value]);
    });

    newArray = removeDuplicates(newArray);
    return newArray;
}

Code trang ” dropdown.html”

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.9.1/font/bootstrap-icons.css">
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet"
    integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"
    integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous">
  </script>
  <script src="https://code.jquery.com/jquery-3.7.0.min.js"
    integrity="sha256-2Pmvv0kuTBOenSvLm6bvfBSSHrUJ+3A7x6P5Ebd07/g=" crossorigin="anonymous"></script>
</head>

<body onload="getCountry();">  
  <div class="d-flex align-items-center justify-content-center " style="height:400px;">
    <div class="border border-primary p-4 m-4 col-md-4 " style="border-radius: 10px">
        
        <div class="form-group fw-bold fs-5" style="color:red; text-align: center;">
          VÍ DỤ VỀ LỰA CHỌN PHỤ THUỘC
        </div>
        <div class="form-group fw-bold fst-italic fs-7 " style="color:blue; text-align: center; ">
          (Create a Dependent Drop Down List)
        </div>
        <br>

        <div class="col-md-20 mb-2">                    
          <div class="input-group">
            <span class="input-group-text bg-primary text-white"><i class="bi bi-list-check"></i></span>
            <select class="form-control ClsCountry"  autocomplete="off" onchange="getState(this.value) ">
            <option>Chọn tỉnh...</option>
            </select>
          </div>           
        </div>   

      <div class="col-md-20 mb-2">                    
        <div class="input-group">
          <span class="input-group-text bg-primary text-white"><i class="bi bi-list-stars"></i></span>
          <select class="form-control ClsState"  autocomplete="off"  onchange="getCity(this.value) ">
          <option>Lựa chọn...</option>
          </select>
        </div>           
      </div>


      <div class="col-md-20 mb-2">                    
          <div class="input-group">
            <span class="input-group-text bg-primary text-white"><i class="bi bi-option"></i></span>
            <select class="form-control ClsCity" autocomplete="off">
            <option>Lựa chọn...</option>
            </select>
          </div>           
        </div>  

      <div class="text-center">
        <a href="https://www.youtube.com/netmediacctv" target="_blank" style=" font-size:15px; color:gray;text-decoration:none"><i style="color:red; font-size:20px" class="bi bi-youtube"></i> Netmediacctv</a>&nbsp;<a href="https://www.facebook.com/giaoanppt" target="_blank" style=" font-size:15px; color:gray;text-decoration:none"><i style="color:blue; font-size:20px" class="bi bi-facebook"></i> Giaoanppt</a>
      </div>
    </div>                      
  </div>


  <script>
    function getCountry() {
      google.script.run.withSuccessHandler(function(data) {
          var Options="";                              
          $.each(data, function(key, value)            
          {
              Options = Options + '<option>' + value + '</option>';   
          });
          $(".ClsCountry").append(Options);           
      }).getCountryList();
    }

    function getState(Country) {
      
      google.script.run.withSuccessHandler(function(data) {
          var Options="";                              
          $.each(data, function(key, value)            
          {
              Options = Options + '<option>' + value + '</option>';  
          });
          $(".ClsState").empty().append('<option>Chọn huyện...</option>');
          $(".ClsState").append(Options);           
      }).getStateList(Country);
    }
    

    function getCity(State) {
      
      google.script.run.withSuccessHandler(function(data) {
          var Options="";                              
          $.each(data, function(key, value)            
          {
              Options = Options + '<option>' + value + '</option>';   
          });
          $(".ClsCity").empty().append('<option>Chọn xã...</option>');
          $(".ClsCity").append(Options);           
      }).getCityList(State);
    }

  </script>
</body>
</html>