Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsleaflet

Filter data from Google Sheet on a leaflet map by group

I'm trying to create a map with markers using data from Google Sheet using leaflet. I managed that but there are some bugs that I can't fix:

  1. Filter by group - I can filter by group, but only one marker is displayed for each selected group, even tho I have multiple data in the same group. Here attached a photo of the output enter image description here
  2. Replacing markers - I wanted the map to show markers that correspond to the selected group only, i.e., if I select Male, then only show only data in the group Male, and vice versa. But for now, once I select a group (for example Male), when I attempt to select another group (Female), the marker for Male group still exists. enter image description here

Here is the sample data from Google Sheet enter image description here

Here's my code: Apps script:

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('map');
}

function getAddress(group) {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var groupSheet = ss.getSheetByName("Sheet1"); 
  var getLastRow = groupSheet.getLastRow();  
  var return_array = [];
  if (group === 'All'){
    return groupSheet.getRange(2, 1, getLastRow - 1, 5).getValues();  
  } else {
    for (var i = 2; i<= getLastRow; i++){
      if (groupSheet.getRange(i,3).getValue() === group){
        return_array.push(groupSheet.getRange(i,1,1,5).getValues());
      }
    }
    return return_array;
  }
}

HTML

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>Document</title>
    <link rel="stylesheet" href="https://unpkg.com/[email protected]/dist/leaflet.css"
        integrity="sha512-xodZBNTC5n17Xt2atTPuE1HxjVMSvLVW9ocqUKLsCC5CXdbqCmblAshOMAS6/keqq/sMZMZ19scR4PsZChSR7A=="
        crossorigin="" />
    <script src="https://unpkg.com/[email protected]/dist/leaflet.js"
        integrity="sha512-XQoYMqMTK8LvdxXYG3nZ448hOEQiglfqkJs1NOQV44cWnUrBc8PkAOcXy20w0vlaXaVUearIOBhiXZ5V3ynxwA=="
        crossorigin=""></script>
</head>
<body>
    <div id="divfilter" class="col12" onchange="onSelect()">
      Select a group:  
        <div class="col4">
            <input type="radio" name="filGroup" value="All" checked>All
        </div>
        <div class="col4">
            <input type="radio" name="filGroup" value="Male" >Male
        </div>
        <div class="col4">
            <input type="radio" name="filGroup" value="Female" >Female
        </div>
    </div>
    <div id="viewmap"></div>
    
<script>
  var map_init = L.map('viewmap',{
      center: [4.042649, 103.624396],
      zoom:8
  });
  var basemap = L.tileLayer ('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
      attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
  }).addTo (map_init);

  function onSelect(){
    var map = map_init;
    var group = document.querySelector('input[name="filGroup"]:checked').value;
    
    google.script.run.withSuccessHandler(function(ar){
      console.log(ar);
      ar.forEach(function(item, index){
        var group = document.querySelector('input[name="filGroup"]:checked').value;

        if (group === 'All'){
          var marker = L.marker([item[0], item[1]]).addTo(map);
          marker.bindPopup('<div id="text"><p>Name: ' + item[3] + '<br>DOB: ' + item[4] + '</p></div>' ); 
        } else{
          var marker = L.marker([item[index][0], item[index][1]]).addTo(map);  
          marker.bindPopup('<div id="text"><p>Name: ' + item[index][3] + '<br>DOB: ' + item[index][4] + '</p></div>' ); 
        }  
      });
    }).getAddress(group);    
  }

</script>

</body>
</html>


<style>
    body {
        margin: 0;
        padding: 0;
    }

    #viewmap {
        width: 100%;
        height: 100vh;
    }

    #text {
      font-family:Georgia,'Times New Roman', Times, serif;
    }
</style>

I'd be most grateful if anyone can help!

Solution

  • This is my first attempt at maps but I tried it and it works.

    Change your files accordingly.

    HTML_TestMap

    <!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>Document</title>
    <link rel="stylesheet" href="https://unpkg.com/[email protected]/dist/leaflet.css"
        integrity="sha512-xodZBNTC5n17Xt2atTPuE1HxjVMSvLVW9ocqUKLsCC5CXdbqCmblAshOMAS6/keqq/sMZMZ19scR4PsZChSR7A=="
        crossorigin="" />
    <script src="https://unpkg.com/[email protected]/dist/leaflet.js"
        integrity="sha512-XQoYMqMTK8LvdxXYG3nZ448hOEQiglfqkJs1NOQV44cWnUrBc8PkAOcXy20w0vlaXaVUearIOBhiXZ5V3ynxwA=="
        crossorigin=""></script>
    <style>
      body {
          margin: 0;
          padding: 0;
      }

      #viewmap {
          width: 100%;
          height: 100vh;
      }

      #text {
        font-family:Georgia,'Times New Roman', Times, serif;
      }
    </style>
  </head>
  <body>
    <div id="divfilter" class="col12" onchange="onSelect()">
      Select a group:  
        <div class="col4">
            <input type="radio" name="filGroup" value="All" checked>All
        </div>
        <div class="col4">
            <input type="radio" name="filGroup" value="Male" >Male
        </div>
        <div class="col4">
            <input type="radio" name="filGroup" value="Female" >Female
        </div>
    </div>
    <div id="viewmap"></div>
    
    <script>
      var map_init = null;
      var basemap = null;
      var map_markers = [];

      (function () {
        try {
          map_init = L.map('viewmap',{
            center: [4.042649, 103.624396],
            zoom:8
          });
          basemap = L.tileLayer ('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
            attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
          }).addTo (map_init);
        }
        catch(err) {
          alert(err);
        }
      })();

      function onSelect(){
        var map = map_init;
        var group = document.querySelector('input[name="filGroup"]:checked').value;
    
        google.script.run.withSuccessHandler(
          function(ar){
            try {
              map_markers.forEach( marker => marker.remove() );
              map_markers = [];
              ar.forEach(function(item, index){
                var group = document.querySelector('input[name="filGroup"]:checked').value;

                var marker = L.marker([item[0], item[1]]).addTo(map);
                marker.bindPopup('<div class="mapFlag" id="text"><p>Name: ' + item[3] + '<br>DOB: ' + item[4] + '</p></div>' );
                map_markers.push(marker);
              });
            }
            catch(err) {
              alert(err);
            }
          }
        ).getAddress(group);  
      }
    </script>
  </body>
</html>

    Code.gs

    function getAddress(group) {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var groupSheet = ss.getSheetByName("Sheet1");
    let values = groupSheet.getDataRange().getValues();
    values.shift(); // remove headers
    if( group !== 'All' ) {
      values = values.filter( row => row[2] === group )
    }
    return values;
  }
  catch(err) {
    Logger.log(err);
  }
}