Google Sheets Dependent Dropdowns with Apps Script Tutorial

Sandip Nigam | December 29, 2023 | Google Apps Script | No Comments

In this step-by-step tutorial, we’ll walk you through the process of creating dependent dropdown menus in Google Sheets using Apps Script. Learn how to make your data entry forms more efficient by dynamically populating options for selecting country, state, and city based on user choices.

जब भी हमे एक Dropdown की वैल्यू से अन्य किसी dropdown की Value fill करवाना हो तब हम dependent dropdown या Filter dropdown का उपयोग करते हैं। ऐसा करने से Dropdown मे ज्यादा Data fill नहीं करना होता हैं और हमारे प्रॉजेक्ट मे किसी भी प्रकार का Speed का इशू नहीं आता हैं।

Table of Contents

ऐसे Dropdown जिसमे Data पहले से न आकर किसी ओर Column की वैल्यू पर Depend होता हैं या कहे की वैल्यू Change होने पर उसमे Value Fill होती है।

जब किसी Dropdown मे बहुत ही ज्यादा Data होता हैं तो हमे उसमे Search करने मे समस्या होती हैं, जब हम Dependent Dropdown का उपयोग करते हैं तो हम Data को Filter कर लेते हैं जिससे की data पहले की तुलना मे कम हो जाता हैं और हम आसानी से Selection कर पाते हैं।

इसमे कोई ही Dropdown को हम पहले से Fill नहीं करते हैं हैं, जो भी Dropdown जी भी Dropdown पर depend होती हैं उसके Change पर Dropdown को fill करते हैं, आज के इस Article मे आप देखेंगे की जैसे ही हम Country Select करते हैं तब State की Dropdown Fill हो जाती हैं और State Fill होते हैं City की Dropdown Fill हो जाती हैं ।

Video के माध्यम से समझने के लिए देखे

आइए एक Live Example से समझते हैं की Dependent Dropdown कैसे काम करती हैं ।

Google Sheet

हमने सबसे पहले एक Google Sheet बनाई हैं जिसमे हमने Country, State और City का Data Example के लिए Enter कर लिया हैं ।

HTML Code

अब हम एक Simple HTML Bootstrap page design कर रहे हैं जिसमे की हमने 3 dropdown बनाई हैं ।

<!DOCTYPE html>
<html>

<head>
  <base target="_top">

  <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 ">
      <div class="form-group ">
        <label for="Country">Country </label>
        <select class="form-control ClsCountry"  autocomplete="off" onchange="getState(this.value) ">
          <option>Select</option>
        </select>
      </div>

      <div class="form-group mt-1">
        <label for="Country">State</label>
        <select class="form-control ClsState"  autocomplete="off"  onchange="getCity(this.value) ">
          <option>Select</option>
        </select>
      </div>

      <div class="form-group mt-1">
        <label for="Country">City</label>
        <select class="form-control ClsCity" autocomplete="off">
          <option>Select</option>
        </select>
      </div>

    </div>
  </div>


  <script> //Add JavaScript   </script>
   



</body>

</html>

आपने उपरोक्त Code मे देखा होगा की हमने

1. Document load पर getCountry() function को call किया है
2. Country के on change पर getState() को call किया हैं
3. State के on change पर getCity() को call किया हैं।

आइए अब इन तीनों Function को JavaScript मे समझते है ।

JavaScript Code

यह Javascript का कोड आप HTML File मे लिख सकते हैं ।

 <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>Select State</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>Select City</option>');
        $(".ClsCity").append(Options);           
    }).getCityList(State);
  }

  </script>

जब getCountry() Function Call होता हैं तब हम JavaScript से Apps Script का getCountryList() Function call करते हैं और उसके Return Data को हम Country वाली Dropdown पर Append कर देते हैं ।
जब कोई भी Country Select करता हैं तो उस Country के नाम के आधार पर हम JavaScript से Apps Script getStateList() function call करते हैं और केवल Select की गई Country की State ही लाते हैं ओर State की Dropdown की empty कर उस पर Value Append कर देते है ।
उपरोक्त जो प्रोसेस हमे Country से State लाने मे की बिल्कुल वैसी ही Process हम State से City लाने मे करते हैं। जिसमे हम getCityList() Apps Script Function को Call करते है।

यहाँ से Complete होने के बाद हम Apps Script Code को समझ लेते हैं।

Google Apps Script Code for Dependent Dropdowns

Apps Script मे हम सबसे पहले हमने जो Google Sheet बनाई थी उसे Connect कर लेंगे।

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;
}

removeDuplicates : इसमे सबसे पहले हम Function बनाएंगे ताकि हम Unique value get कर सके Country और State की वैल्यू की यदि आप Dropdown दिखाएंगे तब आपको उनकी Duplicate वैल्यू हटाना होगी
getCountryList : इस Function से हम A:A Range से Data pick करेंगे और Duplicate Remove कर return कर देंगे।
getStateList : इस Function से हम B:B Range से Data pick करेंगे और Duplicate Remove कर return कर देंगे। साथ मे हम इसमे वही Data लेंगे जिसकी Country हमने पहले Select की थी ।
getCityList : इस Function से हम C:C Range से Data pick करेंगे और Duplicate Remove कर return कर देंगे। साथ मे हम इसमे वही Data लेंगे जिसकी State हमने पहले Select की थी । इसमे आप यदि Duplicate Remove न भी करेंगे तो कोई इशू नहीं हैं। क्युकी इसमे Unique Value ही होगी

आशा हैं मुझे आपको Dependent Dropdown बहुत ही अच्छे से समझ मे आ गया हैं, इस Article से related कोई भी Query हो तो आप मुझे निःसंकोच comment कर सकते हैं । यह Article कैसा लगा Comment करना न भूले । अपना कीमती समय देने के लिए धन्यवाद ।

हमारे अन्य आर्टिकल

Add a Comment

© 2024 Developer Tips.