Display information from a Google Sheet onto a leaflet map based on specified categories

Question

Display information from a Google Sheet onto a leaflet map based on specified categories

I am currently facing some challenges while creating a map with markers using data from Google Sheet and leaflet. Despite my efforts, I have encountered a few bugs that are proving to be difficult to resolve:

Group Filtering - Although I can successfully filter by group, only one marker is displayed for each selected group even when there are multiple data points within the same group. You can view the output in the attached photo https://i.stack.imgur.com/uGM8e.png.
Marker Replacements - My intention was for the map to display markers corresponding to the selected group exclusively. However, if I select a group (e.g., Male) and then try to switch to another group (Female), the marker representing the previous group remains visible. Check out the issue illustrated here: https://i.stack.imgur.com/wrQsG.png.

Here is a glimpse of the sample data retrieved from Google Sheet https://i.stack.imgur.com/BXnll.png.

Presented below is an excerpt of my code:

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 Structure

<!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/<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="741811151218110034455a435a45">[email protected]</a>/dist/leaflet.css"
        integrity="sha512-xodZBNTC5n17Xt2atTPuE1HxjVMSvLVW9ocqUKLsCC5CXdbqCmblAshOMAS6/keqq/sMZMZ19scR4PsZChSR7A=="
        crossorigin="" />
    <script src="https://unpkg.com/<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="036f6662656f667743322d342d32">[email protected]</a>/dist/leaflet.js"
        integrity="sha512-XQoYMqMTK8LvdxXYG3nZ448hOEQiglfqkJs1NOQV44cWnUrBc8PkAOcXy20w0vlaXaVUearIOBhiXZ5V3ynxwA=="
        crossorigin=""></script>
</head>
...Continued...
</style>

Your assistance in resolving these issues would be greatly appreciated!

javascript google-apps-script google-sheets leaflet

Answer 1

Answer №1

Trying out maps for the first time and seeing success.

Make necessary changes to your files.

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/<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f69a9397909a9382b6c7d8c1d8c7">[email protected]</a>/dist/leaflet.css"
        integrity="sha512-xodZBNTC5n17Xt2atTPuE1HxjVMSvLVW9ocqUKLsCC5CXdbqCmblAshOMAS6/keqq/sMZMZ19scR4PsZChSR7A=="
        crossorigin="" />
    <script src="https://unpkg.com/<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a6cac3c7c0cac3d2e69788918897">[email protected]</a>/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);
  }
}

Answer 2

Trying out maps for the first time and seeing success.

Make necessary changes to your files.

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/<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="f69a9397909a9382b6c7d8c1d8c7">[email protected]</a>/dist/leaflet.css"
        integrity="sha512-xodZBNTC5n17Xt2atTPuE1HxjVMSvLVW9ocqUKLsCC5CXdbqCmblAshOMAS6/keqq/sMZMZ19scR4PsZChSR7A=="
        crossorigin="" />
    <script src="https://unpkg.com/<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="a6cac3c7c0cac3d2e69788918897">[email protected]</a>/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);
  }
}

Display information from a Google Sheet onto a leaflet map based on specified categories

Answer №1

Similar questions

How to ensure unique results when using DFS for Combination Sum?

Converting Promises to Observables

Error in line 36: firebase.auth function is undefined

Issue encountered while trying to download Jade through npm (npm install -g jade)

"Execute asynchronous tasks in Javascript and receive the returned

Bring in the content to Notepad utilizing jQuery

Transforming ajax code into node.js

Having trouble with Vue component registration repeatedly failing

Switch the visibility of a div tag using Next.js

Identify the nested Object within the Json data

What could be the reason for the failure of the async await implementation in this particular code sample?

Preventing Vue.js SPA from accessing cached version when JWT expires: the solution

Send a request to templateUrl

Access to data retrieval was restricted by CORS policies on my Node.js/Express REST API server

I encountered an issue while constructing a React application. An error message popped up indicating: "Warning: Can't execute a React state update on a component that is not mounted"

Creating a conditional statement in jQuery that will append text to a specific DIV element after a form has been successfully

Error with WooCommerce checkout causing input values to disappear upon clicking or submitting

Searching for specific data within an embedded documents array in MongoDB using ID

Error: The property 'parentNode' cannot be read because it is null

Crop images in a canvas using a customized rectangle with the help of JQuery