Leaflet Maps with Google Sheets

Sometimes you need to create a map that cannot be made easily with drag-and-drop tools, because you need to customize its appearance or add new layers of point, polygon, or polyline data. In these cases, consider making a copy of our Leaflet Maps with Google Sheets template on GitHub. It gives you more control over choosing colors, icons, and images, and also the option to display a data table of point markers. To customize your interactive map, you enter data into a Google Sheet template, which you link directly to your copy of the Leaflet code repository, as shown in Figure 10.1 and Figure 10.2.

TODO: Create and insert a new version of the demo, featuring ECGreenway route thru CT, points with photos, and pop density of towns to highlight how this bike route connects cities.

Figure 10.2: Explore the live Google Sheet template that feeds data into the Leaflet map above.

Tutorial Outline

Make sure you meet these requirements, and read this overview to prepare yourself for this multi-step tutorial.

Before you begin, you must:

In the first part of the tutorial, you will create and publish your copies of our GitHub and Google Sheets templates:

    1. Copy the GitHub template and publish your version with GitHub Pages
    1. File > Make a Copy of Google Sheet template, Share, and Publish
    1. Paste your Google Sheet browser address in two places in your GitHub repo
    1. Update your Google Sheet Options tab info and refresh your live map

In the second half, you will learn how to upload and display different types of map data, such as points, polygons, and polylines, and to edit colors, icons, and images, based on information you enter into the linked Google Sheet and upload to your GitHub repo.

    1. Geocode locations and customize new markers in the Points tab
    1. Remove or display point, polygon, or polylines data and legends

Then you will finalize your map by following either step G OR step H:

    1. Save each Google Sheets tab as a CSV file and upload to GitHub
  • OR
    1. Get your own Google Sheets API Key to insert into the code

If any problems arise, see the Fix Common Mistakes section of the appendix.

A) Copy the GitHub template and publish your version with GitHub Pages

  1. Right-click to open this GitHub code template in a new tab: https://github.com/handsondataviz/leaflet-maps-with-google-sheets

  2. In the upper-right corner of the code template, sign in to your free GitHub account.

  3. In the upper-right corner, click the green Use this template button to make a copy of the repository in your GitHub account. On the next screen, name your repo leaflet-maps-with-google-sheets or choose a different meaningful name in all lower-case. Click the Create repository from template button. Your copy of the repo will follow this format:

https://github.com/USERNAME/leaflet-maps-with-google-sheets

  1. In your new copy of the code repo, click the upper-right Settings button and scroll way down to the GitHub Pages area. In the drop-down menu, change Source from None to Master, keep the default /(root) setting, and press Save as shown in Figure 10.3. This step tells GitHub to publish a live version of your map on the public web, where anyone can access it in their browser, if they have the web address.
In Settings, go to GitHub Pages, and switch the source from None to Master.

Figure 10.3: In Settings, go to GitHub Pages, and switch the source from None to Master.

Note: TODO: GitHub recently announced it plans to change the default branch from Master to Main to eliminate its master-slave metaphor. GitHub recommends waiting until later in 2020 for their system to support this change. When that happens, we need to update repos, text, and screenshots. See more at https://github.com/github/renaming

  1. Scroll down to GitHub Pages section again, and copy the link to your published web site, which will appear in this format:

https://USERNAME.github.io/leaflet-maps-with-google-sheets

  1. Scroll up to the top, and click on your repo name to go back to its main page.

  2. At the top level of your repo main page, click on README.md, and click the pencil icon to edit this file.

  3. Delete the link to the our live site, and paste in the link to your published site. Scroll down and Commit to save your edits.

TODO: Insert image here

  1. On your repo main page, right-click the link to open your live map in a new tab. Be patient during busy periods on GitHub, when your website may take up to 1 minute to appear for the first time.

B) File > Make a Copy of Google Sheet template, Share, and Publish

  1. Open this Google Sheets template in a new tab

  2. Sign into your Google account, and select File > Make a Copy to save your own version of this Google Sheet on your Google Drive

  3. Click the blue Share button, and click Change to anyone with the link, then click Done. This publicly shares your map data, which is required to make this template work.

  4. Go to File > Publish to the Web, and click the green Publish button to publish the entire document, so that the Leaflet code can read it. Then click the upper-right X symbol to close this window.

  5. At the top of your browser, copy your Google Sheet address or URL (which usually ends in ...XYZ/edit#gid=0). Do NOT copy the Published to the web address (which usually ends in ...XYZ/pubhtml), as shown in Figure 10.4.

Copy the Google Sheet address at the top of the browser, NOT the Publish to the web address.

Figure 10.4: Copy the Google Sheet address at the top of the browser, NOT the Publish to the web address.

C) Paste your Google Sheet browser address in two places in your GitHub repo

Our next task is to link your Google Sheet to your Leaflet code in GitHub, so that content from your Sheet will appear in your map.

  1. At the top of your GitHub repo, click to open the file named google-doc-url.js, and click the pencil symbol to edit it.

  2. Paste your Google Sheet address or URL (which usually ends in ...XYZ/edit#gid=0) to replace the existing URL, as shown in Figure TODO. Be careful NOT to erase the single quotation marks or the semicolon at the end.

TODO: Insert image google-doc-url.png

  1. Scroll to bottom of page and press Commit to save your changes. Now your published Google Sheet is linked to your published Leaflet map code.

  2. Also, let’s paste your Google Sheets browser address in a second place to keep track of it. In your GitHub repo, click to open the README.md file, click the pencil symbol to edit it, and paste your Google Sheet browser address to replace the existing address. Scroll down and commit to save your changes.

TODO: Insert image here

D) Update your Google Sheet Options tab info and refresh your live map

Now that your published Google Sheet is linked to your live map, go to the Options tab and update any of these items:

  • Map Title
  • Map Subtitle
  • Author Name
  • Author Email or Website
  • Author Code Repo

Open the browser tab that displays your live map and refresh the page to see your changes. If your changes do not appear within a few seconds, see the Fix Common Problems section of the appendix.

E) Geocode locations and customize new markers in the Points tab

In the Points tab of your Google Sheet, you’ll see column headers to organize and display interactive markers on your map. Replace the demonstration data with your own, but do not delete or rename the column headers, since the Leaflet code looks for these specific names.

  • Group: Create any labels to categorize groups of markers in your legend.
  • Marker Icon: Search Font Awesome Icons and insert any standard icon name such as school or bus, or leave blank for no icon inside the marker. To create your own custom icon, see further below.
  • Marker Color: Search W3Schools Color Names and insert any standard name such as blue or darkblue. Or insert a web color code such as #775307 or rgba(200,100,0,0.5).
  • Icon Color: Set the color of the icon inside the marker. The default is white, which looks good inside darker-colored markers.
  • Custom Size: Leave blank, unless you are creating your own custom icon further below.

The next set of columns include items that appear when users click on point markers: - Name: Add a title to display in the marker pop-up window. - Image: Insert link to an external image link, or upload a small image to the media folder in your GitHub repo and add its pathname, such as media/trinity-college.jpg. TODO: add this to GSheet after code update - Description: Add text to appear in the marker pop-up window. You may include HTML web links in this format: <a href="url">link text</a>. If a map link should open in a new browser tab, set the target attribute to _blank. Learn about HTML syntax at W3Schools.

The next six columns help to place the Location of your marker on the map. The Leaflet code requires you to fill-in the Latitude and Longitude columns, which you can do using the built-in Geocoder menu in our Google Sheet template, which we introduced in chapter TODO: INSERT GEOCODER CHAPTER. Click the cell(s) you wish to geocode, and use shift-click to select all six columns from Location to Source (columns I to N). In the Geocoder menu, select either US Census or Google, as shown in Figure 10.5. The first time you run the Geocoder script, it will ask for your permission. TODO: since we still need Google to authorize our script, you will need to click Advanced and then Go to Geocoder (unsafe) near the bottom of the next screen. Trust us – it’s safe to use! Sorry for the inconvenience. Always inspect the accuracy of your geocoded results.

To use our built-in Geocoder menu, shift-click to select all columns from Location through Source.

Figure 10.5: To use our built-in Geocoder menu, shift-click to select all columns from Location through Source.

Optional: You can display a table of viewable markers at the bottom of your map, as shown in Figure 10.6. In the Options tab, set Display Table (cell B30) to On. You can also adjust the Table Height, and modify the display of Table Columns by entering the column headers, separated with commas.

One option is to display a table of viewable markers at the bottom of your map.

Figure 10.6: One option is to display a table of viewable markers at the bottom of your map.

Optional: You can create your own custom marker, such as the Trinity College Bantam mascot in the demo. Use an image editing tool to create a square image, 64 x 64 pixels or smaller, with a transparent background. Save it in PNG format with a filename using all lower-case letters with no spaces. Upload the image to the markers folder in your GitHub repo. In the Marker Icon column, set the pathname in this format: markers/custom-trinity-64.png. In the Custom Size column, set to 64x64 or similar.

Open the browser tab that displays your live map and refresh the page to see your changes. If your changes do not appear within a few seconds, see the Fix Common Problems section of the appendix.

F) Remove or display point, polygon, or polylines data and legends

By default, the demo map displays three types of data—points, polygons, and polylines—and their legends. You can remove any of these from your map by modifying your linked Google Sheet:

To remove points:

  • In the Options tab, set Point Legend Position (cell B27) to Off to hide it.
  • In the Points tab, delete all rows of point data.

To remove polylines:

  • In the Options tab, set Polyline Legend Position (cell B36) to Off to hide it.
  • In the Polylines tab, delete all rows of polyline data.

To remove polygons:

  • In the Polygons tab, set Polygon Legend Position (cell B4) to Off to hide it.
  • Also in the Polygons tab, set Polygon GeoJSON URL (cell B6) to remove that data from your map.
  • In the next tab Polygons1, use the tab drop-down menu to select Delete to remove the entire sheet.

You’ve already learned how to add more markers in the Points tab as described above. But if you wish to add new polygon or polyline data, you’ll need to prepare those files in GeoJSON format using either the GeoJson.io tool tutorial or the MapShaper tool tutorial in Chapter 11.

After you’ve prepared your GeoJSON data, name the files using all lower-case characters and no spaces, and upload them into the geometry subfolder of your GitHub repo. Then update these settings in your linked Google Sheet:

To display polylines:

  • In the Options tab, make sure Polyline Legend Position (cell B36) is visible by selecting topleft or a similar position.
  • In the Polylines tab, enter the GeoJSON URL pathname to the file you uploaded to your GitHub repo, such as geometry/polylines-bike-lanes.geojson. Then insert a Display Name, Description, and Color.

To display polygons:

  • In the Polygons tab, make sure Polygon Legend Position (cell B4) is visible by selecting topleft or a similar position.
  • Also, in Polygon GeoJSON URL (cell B6) enter the pathname to the file you uploaded to your GitHub repo, such as geometry/polygons-town-population.geojson.
  • Also, you can change the Polygon Legend Title (cell B3) and add an optional Polygon Legend Icon (cell B5).
  • Also, edit the Polygon Data and Color Settings sections to modify the labels and ranges to align with the properties of your GeoJSON file. In the Property Range Color Palette, you can automatically select a color scheme from the ColorBrewer tool we described in the Map Design section of Chapter 6, or manually insert colors of your choice in the cell below.
  • Read the Hints column in the Polygons sheet for tips on how to enter data.
  • If you wish to display multiple polygon layers, use the Polygons tab drop-down menu to Duplicate the sheet, and name additional sheets in this format: Polygons1, Polygons2, etc.

Finalize Your Map

Now you’re ready to finalize your map. Read the options below and choose either step G OR step H.

G) Save each Google Sheets tab as a CSV file and upload to GitHub

If you have finished entering most of your data into your Google Sheets, save each tab as a CSV file and upload them to GitHub. The Leaflet map code will pull data directly from your CSV files, rather than your Google Sheets. And you can still edit your CSV files in GitHub. Moving your data from Google Sheets to CSV format is the best long-term preservation strategy, because it keeps your map and data together in the same GitHub folder, and removes the risk that your map will break due to an interruption to Google services, as described in Step H.

To move your map data from Google Sheets to CSV format, go to each tab and select File > Download As into CSV format, as shown in Figure 10.7, using these file names: TODO: should we warn to keep the first letter upper-case?

  • Options.csv
  • Points.csv
  • Polylines.csv
  • Polygons.csv (if needed, also use: Polygons1.csv, Polygons2.csv, etc.)
  • Notes.csv (or .txt)

Upload all of these CSV files into the main level (or root level) of your GitHub repository. The Leaflet code looks here first for the data, and when it locates them, will pull the data directly from the CSV files into your map, skipping over the Google Sheets. TODO: Confirm this code request is working

One way to finalize your map is to download each Google Sheets tab as a CSV file.

Figure 10.7: One way to finalize your map is to download each Google Sheets tab as a CSV file.

H) Get your own Google Sheets API Key to insert into the code

If you wish to keep using your Google Sheets to store your data, go to the chapter section named Get Your Own Google Sheets API Key, and insert it into the Leaflet map code as described, to avoid overusing our key. Google Sheets requires an API key to maintain reasonable usage limits on its service. You can get a free Google Sheets API key if you have a personal Google account, but not a Google Suite account provided by your school or business. TODO: confirm this detail

Warning: We reserve the right to change our Google Sheets API key at any time, especially if other people overuse or abuse it. This means that you must finalize your map using either step J or K above, since it will stop working if we change our key.

If problems arise, see the Fix Common Mistakes section of the appendix.

TODO: Start again here