Leaflet maps with data from Google Sheets - Chris Arderne

<div readability="5"> <head> <meta charset="utf-8"/><title>Leaflet maps with data from Google Sheets - Chris Arderne</title> <meta name="description" content="I love working with Python, but as soon as you want to put something online, JavaScript’s ability to process in the browser is a clear winner. In this post I’m going to go through making a web map in JavaScript that pulls data from Google Sheets – where non-coders can easily make updates."/> <meta property="og:type" content="article"/> <meta property="og:locale" content="enUS"/> <meta property="og:sitename" content="Chris Arderne"/> <meta property="og:title" content="Leaflet maps with data from Google Sheets"/> <meta property="og:url" content="https://rdrn.me/leaflet-maps-google-sheets/"/><meta property="og:description" content="I love working with Python, but as soon as you want to put something online, JavaScript’s ability to process in the browser is a clear winner. In this post I’m going to go through making a web map in JavaScript that pulls data from Google Sheets – where non-coders can easily make updates."/><meta property="article:publishedtime" content="2018-10-19T17:51:19+00:00"/><link rel="canonical" href="https://rdrn.me/leaflet-maps-google-sheets/"/> <link rel="alternate" href="https://rdrn.me/leaflet-maps-google-sheets/feed.xml" type="application/rss+xml" title="Chris Arderne"/> <meta name="viewport" content="width=device-width, initial-scale=1.0"/> </head> <body id="readabilityBody" readability="141.03595397891"> <nav> </nav> <header> Leaflet maps with data from Google Sheets <time datetime="2018-10-19T17:51:19+00:00">19 Oct 2018</time> </header> I wrote another <a href="https://rdrn.me/python-mapping-automatic-updating/" style="color: inherit; text-decoration: none;" name="readabilityLink-1">post here</a><a href="#readabilityFootnoteLink-1" class="readability-DoNotFootnote" style="color: inherit;">[1]</a>, outlining how this can be achieved without stepping out of a Python environment. I love working with Python, but as soon as you want to put something online, JavaScript’s ability to process in the browser is a clear winner. For one, you can have statically served HTML files performing complex tasks, whereas with Python you’d need a cloud instance running and a system for communicating between front- and back-end. In this post I’m going to go through making a web map in JavaScript that pulls data from Google Sheets – where non-coders can easily make updates. This seems to be a common request and something that is not well covered by the various GUI solutions available. There is a <a href="https://www.datavizforall.org/leaflet/with-google-sheets/" style="color: inherit; text-decoration: none;" name="readabilityLink-2">Data Visualization for All</a><a href="#readabilityFootnoteLink-2" class="readability-DoNotFootnote" style="color: inherit;">[2]</a> guide, but it didn’t seem to work well (potentially due to changes in Google’s API) and is overly complex. Skip the first two sections if you just want the juicy stuff, or go straight to <a href="https://github.com/carderne/leaflet-gsheets" style="color: inherit; text-decoration: none;" name="readabilityLink-3">the repo</a><a href="#readabilityFootnoteLink-3" class="readability-DoNotFootnote" style="color: inherit;">[3]</a>. <h2 id="client-side-programming">Client-side programming</h2> JavaScript is unique in being used on both the client- and server-sides of a web application. This means it can run directly in a user’s browser without anything happening on the server, but can also be used on the back-end (via <a href="https://nodejs.org/" style="color: inherit; text-decoration: none;" name="readabilityLink-4">Node.js</a><a href="#readabilityFootnoteLink-4" class="readability-DoNotFootnote" style="color: inherit;">[4]</a> for moving data around and doing the heavy lifting. For this example, I’m basically taking advantage of the user’s computer to do the processing, so that I don’t have to pay <a href="https://aws.amazon.com/" style="color: inherit; text-decoration: none;" name="readabilityLink-5">Amazon</a><a href="#readabilityFootnoteLink-5" class="readability-DoNotFootnote" style="color: inherit;">[5]</a> and friends to do it for me on a cloud instance. This dual nature of JavaScript is one of the reasons for its booming popularity in web development. <h2 id="mapping-libraries-available-in-javascript">Mapping libraries available in JavaScript</h2> Another reason for JavaScript’s ubiquity is the endless variety of libraries available, and the ease with which these can be used, thanks, in part, to <a href="https://blog.codinghorror.com/the-principle-of-least-power/" style="color: inherit; text-decoration: none;" name="readabilityLink-6">Atwood’s Law</a><a href="#readabilityFootnoteLink-6" class="readability-DoNotFootnote" style="color: inherit;">[6]</a>: <blockquote readability="6"> any application that can be written in JavaScript, will eventually be written in JavaScript </blockquote> As for web mapping, there are a few obvious choices: <ul> <li><a href="https://openlayers.org/" style="color: inherit; text-decoration: none;" name="readabilityLink-7">OpenLayers</a><a href="#readabilityFootnoteLink-7" class="readability-DoNotFootnote" style="color: inherit;">[7]</a> – the most mature and powerful option, but also heavy and quite complex to use</li> <li><a href="https://developers.google.com/maps/documentation/javascript/tutorial" style="color: inherit; text-decoration: none;" name="readabilityLink-8">Google Maps JavaScript API</a><a href="#readabilityFootnoteLink-8" class="readability-DoNotFootnote" style="color: inherit;">[8]</a> – limited basemap options and it’s closed source (noting the irony that this post is all about Google Sheets)</li> <li><a href="https://www.mapbox.com/mapbox-gl-js/api/" style="color: inherit; text-decoration: none;" name="readabilityLink-9">Mapbox GL JS</a><a href="#readabilityFootnoteLink-9" class="readability-DoNotFootnote" style="color: inherit;">[9]</a> (or the older <a href="https://www.mapbox.com/help/define-mapbox-js/" style="color: inherit; text-decoration: none;" name="readabilityLink-10">Mapbox.js</a><a href="#readabilityFootnoteLink-10" class="readability-DoNotFootnote" style="color: inherit;">[10]</a>) – great for integrating with <a href="https://www.mapbox.com/mapbox-studio/" style="color: inherit; text-decoration: none;" name="readabilityLink-11">Mapbox Studio</a><a href="#readabilityFootnoteLink-11" class="readability-DoNotFootnote" style="color: inherit;">[11]</a> (designing basemaps) and their <a href="https://www.mapbox.com/geocoding/" style="color: inherit; text-decoration: none;" name="readabilityLink-12">geocoding</a><a href="#readabilityFootnoteLink-12" class="readability-DoNotFootnote" style="color: inherit;">[12]</a> service</li> <li><a href="https://leafletjs.com/" style="color: inherit; text-decoration: none;" name="readabilityLink-13">Leaflet</a><a href="#readabilityFootnoteLink-13" class="readability-DoNotFootnote" style="color: inherit;">[13]</a> – light-weight, well-documented and easy to use for simple projects – goldilocks!</li> </ul> You can read a more complete <a href="http://ledeprogram.com/2015/absolutely-everything-you-need-to-know-about-mapping-tools/" style="color: inherit; text-decoration: none;" name="readabilityLink-14">overview here</a><a href="#readabilityFootnoteLink-14" class="readability-DoNotFootnote" style="color: inherit;">[14]</a>; it’s a bit out of date, but includes comparisons with non JavaScript solutions such as <a href="https://carto.com/" style="color: inherit; text-decoration: none;" name="readabilityLink-15">Carto</a><a href="#readabilityFootnoteLink-15" class="readability-DoNotFootnote" style="color: inherit;">[15]</a> (not cheap, unfortunately). <h2 id="pulling-data-from-google-sheets">Pulling data from Google Sheets</h2> In all the code excerpts, I’m stripping it to the bare essentials for readability. The full code is available at the <a href="https://github.com/carderne/leaflet-gsheets" style="color: inherit; text-decoration: none;" name="readabilityLink-16">repo</a><a href="#readabilityFootnoteLink-16" class="readability-DoNotFootnote" style="color: inherit;">[16]</a>. To make maps from Google Sheets, we first need to get data from Google Sheets. This is made exceedingly easy by <a href="https://github.com/jsoma/tabletop" style="color: inherit; text-decoration: none;" name="readabilityLink-17">Tabletop.js</a><a href="#readabilityFootnoteLink-17" class="readability-DoNotFootnote" style="color: inherit;">[17]</a>, a simple library for pulling in entire sheets as JSON objects. This is done in a few lines of code: <div class="language-plaintext highlighter-rouge" readability="34"><div class="highlight" readability="13"><pre class="highlight"><code>function init() { Tabletop.init({ key: sheetsUrl, callback: myFunction, simpleSheet: true }) } window.addEventListener('DOMContentLoaded', init) function myFunction(data, tabletop) { console.log(data); } </code></pre></div></div> You just need to get the public sharing link from your Sheet (follow the instructions at the Tabletop.js repo) and assign it to <code class="language-plaintext highlighter-rouge">sheetsUrl</code> and you’re done! The data I pulled in for this web map was two separate tables, which you can preview <a href="https://docs.google.com/spreadsheets/d/1kjJVPF0LyaiaDYF8zx23UulGciGtBALQ1a1pK0coRM/edit?usp=sharing" style="color: inherit; text-decoration: none;" name="readabilityLink-18">here</a><a href="#readabilityFootnoteLink-18" class="readability-DoNotFootnote" style="color: inherit;">[18]</a> and <a href="https://docs.google.com/spreadsheets/d/1EUFSaqi30b6oefK0YWWNDDOzwmCTTXlXkFHAc2QrUxM/edit?usp=sharing" style="color: inherit; text-decoration: none;" name="readabilityLink-19">here</a><a href="#readabilityFootnoteLink-19" class="readability-DoNotFootnote" style="color: inherit;">[19]</a>. The first has simple <code class="language-plaintext highlighter-rouge">lat</code> and <code class="language-plaintext highlighter-rouge">long</code> coordinates for a few points, while the second has a more complicated <code class="language-plaintext highlighter-rouge">geometry</code> column with polygon representations of each US state. In addition they each have extra columns with more information. <h2 id="putting-it-together-for-an-easy-web-map">Putting it together for an easy web map</h2> My objective for this web map was to show these point and polygon items on a map of the US, with pop-ups for each element showing additional information. With a few more steps, it’s easy to style each element based on other columns in the data. Firstly, let’s create a basic HTML file to hold our map. The example below provides the bare minimum of importing Tabletop.js, Leaflet.js and the Leaflet CSS styling. It then creates a <code class="language-plaintext highlighter-rouge">div</code> with <code class="language-plaintext highlighter-rouge">id="map"</code>, which is where our map will go, and then imports <code class="language-plaintext highlighter-rouge">leaflet-example.js</code>, which is where our new JavaScript code goes. <div class="language-plaintext highlighter-rouge" readability="32.5"><div class="highlight" readability="10"><pre class="highlight"><code><!DOCTYPE html> <html> <head> <script src='https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js'></script> <link rel="stylesheet" href="https://unpkg.com/leaflet@1.3.4/dist/leaflet.css"/> <script src="https://unpkg.com/leaflet@1.3.4/dist/leaflet.js"></script> </head> <body> <div id="map"></div> <script type="text/javascript" src="leaflet-example.js"></script> </body> </html> </code></pre></div></div> With that set up, let’s create a Leaflet map and insert it into our <code class="language-plaintext highlighter-rouge">div</code>, and add a beautiful basemap (basically a background map). A number of basemap options are demoed here [http://leaflet-extras.github.io/leaflet-providers/preview/] – just copy the provided URL into <code class="language-plaintext highlighter-rouge">baseMapURL</code> in the code below (and add the suggested attributions). <div class="language-plaintext highlighter-rouge" readability="33"><div class="highlight" readability="11"><pre class="highlight"><code>var map = L.map('map-div').setView([startLat, startLong], startZoom); var basemap = L.tileLayer(baseMapURL, { attribution: attributionText }); basemap.addTo(map); </code></pre></div></div> Next we need to add things to the map! The points are easier, and we can add them by simply looping through each item in the JSON and adding a Leaflet <code class="language-plaintext highlighter-rouge">marker</code>. This function is called by the init() function from further up, once the data has been retrieved from Google Sheets. <div class="language-plaintext highlighter-rouge" readability="32.5"><div class="highlight" readability="10"><pre class="highlight"><code>function addPoints(data, tabletop) { for (var row in data) { var marker = L.marker([ data[row].lat, data[row].long ]).addTo(map); marker.bindPopup(data[row].category); } } </code></pre></div></div> The polygons are slightly more complicated, as Leaflet needs a GeoJSON object to represent them. So in a new function like the previous one (also called by the <code class="language-plaintext highlighter-rouge">init()</code> function), we have the following code to create a single GeoJSON containing all of our polygons. <div class="language-plaintext highlighter-rouge" readability="35.5"><div class="highlight" readability="16"><pre class="highlight"><code>function addPolygons(data, tabletop) { // the empty GeoJSON waiting to be populated with features var polygons = { "type": "FeatureCollection", "features": [] } for (var row in data) { // JSON.parse converts the geometry strings into JSON objects var coords = JSON.parse(data[row].geometry); polygons.features.push({ "type": "Feature", "geometry": { "type": "MultiPolygon", "coordinates": coords }, "properties": { "name": data[row].name, } }); } } </code></pre></div></div> This sets up an empty GeoJSON and then loops through each element in <code class="language-plaintext highlighter-rouge">data</code> and inserts the coordinates and names as <code class="language-plaintext highlighter-rouge">Feature</code> elements within the GeoJSON. With this set up, it’s just a few lines to add this new object to our Leaflet map. The lines below can be added at the bottom of the <code class="language-plaintext highlighter-rouge">addPolygon</code> function for simplicity. <div class="language-plaintext highlighter-rouge" readability="33"><div class="highlight" readability="11"><pre class="highlight"><code>polygonMarkers = L.geoJSON(polygons, { onEachFeature: function (feature, layer) { layer.bindPopup(feature.properties.name); }, }).addTo(map); </code></pre></div></div> As for the points further up, this includes the code to add a popup, but nothing on styling. That’s probably a post for another day, but you can have a look at the <a href="https://github.com/carderne/leaflet-gsheets" style="color: inherit; text-decoration: none;" name="readabilityLink-20">repo</a><a href="#readabilityFootnoteLink-20" class="readability-DoNotFootnote" style="color: inherit;">[20]</a> if you want to see what I used for this example. And we’re done! The result (with styling) is shown below, or click <a href="https://rdrn.me/leaflet-gsheets" style="color: inherit; text-decoration: none;" name="readabilityLink-21">here</a><a href="#readabilityFootnoteLink-21" class="readability-DoNotFootnote" style="color: inherit;">[21]</a> to see it full screen. Every time a user loads this map in their browser, it will automatically hop over to the specified Google Sheets and pull the latest data to display it. </body> </div><div id="readability-footnotes"><h3>References</h3><ol id="readability-footnotes-list"><li><a href="#readabilityLink-1" title="Jump to Link in Article">^</a> <a href="https://rdrn.me/python-mapping-automatic-updating/" name="readabilityFootnoteLink-1">post here</a> (rdrn.me)</li><li><a href="#readabilityLink-2" title="Jump to Link in Article">^</a> <a href="https://www.datavizforall.org/leaflet/with-google-sheets/" name="readabilityFootnoteLink-2">Data Visualization for All</a> (www.datavizforall.org)</li><li><a href="#readabilityLink-3" title="Jump to Link in Article">^</a> <a href="https://github.com/carderne/leaflet-gsheets" name="readabilityFootnoteLink-3">the repo</a> (github.com)</li><li><a href="#readabilityLink-4" title="Jump to Link in Article">^</a> <a href="https://nodejs.org/" name="readabilityFootnoteLink-4">Node.js</a> (nodejs.org)</li><li><a href="#readabilityLink-5" title="Jump to Link in Article">^</a> <a href="https://aws.amazon.com/" name="readabilityFootnoteLink-5">Amazon</a> (aws.amazon.com)</li><li><a href="#readabilityLink-6" title="Jump to Link in Article">^</a> <a href="https://blog.codinghorror.com/the-principle-of-least-power/" name="readabilityFootnoteLink-6">Atwood’s Law</a> (blog.codinghorror.com)</li><li><a href="#readabilityLink-7" title="Jump to Link in Article">^</a> <a href="https://openlayers.org/" name="readabilityFootnoteLink-7">OpenLayers</a> (openlayers.org)</li><li><a href="#readabilityLink-8" title="Jump to Link in Article">^</a> <a href="https://developers.google.com/maps/documentation/javascript/tutorial" name="readabilityFootnoteLink-8">Google Maps JavaScript API</a> (developers.google.com)</li><li><a href="#readabilityLink-9" title="Jump to Link in Article">^</a> <a href="https://www.mapbox.com/mapbox-gl-js/api/" name="readabilityFootnoteLink-9">Mapbox GL JS</a> (www.mapbox.com)</li><li><a href="#readabilityLink-10" title="Jump to Link in Article">^</a> <a href="https://www.mapbox.com/help/define-mapbox-js/" name="readabilityFootnoteLink-10">Mapbox.js</a> (www.mapbox.com)</li><li><a href="#readabilityLink-11" title="Jump to Link in Article">^</a> <a href="https://www.mapbox.com/mapbox-studio/" name="readabilityFootnoteLink-11">Mapbox Studio</a> (www.mapbox.com)</li><li><a href="#readabilityLink-12" title="Jump to Link in Article">^</a> <a href="https://www.mapbox.com/geocoding/" name="readabilityFootnoteLink-12">geocoding</a> (www.mapbox.com)</li><li><a href="#readabilityLink-13" title="Jump to Link in Article">^</a> <a href="https://leafletjs.com/" name="readabilityFootnoteLink-13">Leaflet</a> (leafletjs.com)</li><li><a href="#readabilityLink-14" title="Jump to Link in Article">^</a> <a href="http://ledeprogram.com/2015/absolutely-everything-you-need-to-know-about-mapping-tools/" name="readabilityFootnoteLink-14">overview here</a> (ledeprogram.com)</li><li><a href="#readabilityLink-15" title="Jump to Link in Article">^</a> <a href="https://carto.com/" name="readabilityFootnoteLink-15">Carto</a> (carto.com)</li><li><a href="#readabilityLink-16" title="Jump to Link in Article">^</a> <a href="https://github.com/carderne/leaflet-gsheets" name="readabilityFootnoteLink-16">repo</a> (github.com)</li><li><a href="#readabilityLink-17" title="Jump to Link in Article">^</a> <a href="https://github.com/jsoma/tabletop" name="readabilityFootnoteLink-17">Tabletop.js</a> (github.com)</li><li><a href="#readabilityLink-18" title="Jump to Link in Article">^</a> <a href="https://docs.google.com/spreadsheets/d/1kjJVPF0LyaiaDYF8z_x23UulGciGtBALQ1a1pK0coRM/edit?usp=sharing" name="readabilityFootnoteLink-18">here</a> (docs.google.com)</li><li><a href="#readabilityLink-19" title="Jump to Link in Article">^</a> <a href="https://docs.google.com/spreadsheets/d/1EUFSaqi30b6oefK0YWWNDDOzwmCTTXlXkFHAc2QrUxM/edit?usp=sharing" name="readabilityFootnoteLink-19">here</a> (docs.google.com)</li><li><a href="#readabilityLink-20" title="Jump to Link in Article">^</a> <a href="https://github.com/carderne/leaflet-gsheets" name="readabilityFootnoteLink-20">repo</a> (github.com)</li><li><a href="#readabilityLink-21" title="Jump to Link in Article">^</a> <a href="https://rdrn.me/leaflet-gsheets" name="readabilityFootnoteLink-21">here</a> (rdrn.me)</li></ol></div>

keywords

No Items Found.

Add Comment

Other Items in maps

Leaflet maps with data from Google Sheets - Chris Arderne Absolutely everything* you need to know about online mapping tools - The Lede Program

Other Categories in Linx

ai apps articles cloud apps code coffee factorio games hardware health iphone lists minecraft music pi security stock images stock images tech vpn web dev web developer websites windows ai android api apps articles asp audio australia bio bitcoin blog books bootstrap career charts code editors codepens cooking css css animations daily linx data day trips design design elements drawing education emulators entrepreneur factorio fitness flowcharts fonts foobar2000 themes games hardware headphones health home automation html templates icons itunes javascript jquery js linux mac maps media mhx minecraft misc money motivation music network news nginx os osrs pantheon parenting phone phones php pi products programming radio random reading reddit rs3 runescape seo shopping sqlite stock images storage tax tech technology text formatting tools trains video video editor videos wallpapers weather web web design web development web sites webdev windows wine wordpress workout youtube

Welcome

This is my test area for webdev. I keep a collection of code here, mostly for my reference. Also if i find a good link, i usually add it here and then forget about it. more...

Latest News

## 🚀 AI Giants Hit Bullseye: Anthropic & OpenAI Achieve Product-Market Fit Anthropic and OpenAI have reached a significant milestone, finding product-market fit with their AI technologies, which means their products effectively meet the needs of their customers, driving growth and adoption. This achievement showcases the practical value of their innovations, enabling businesses and individuals to leverage AI for enhanced productivity and efficiency. With this alignment of product and market needs, these companies are poised to transform industries and shape the future of technology.

Leaflet maps with data from Google Sheets - Chris Arderne

Add Comment

Other Items in maps

Other Categories in Linx

Search Linx

Latest from Linx

Welcome

Random Quote

Latest News

Latest Articles

Code

Links

Quick Links

Quick Links

Sections