Import Map Markers Spreadsheet

Query Markers of jVector Map from Google Spreadsheet


Lately I’ve been more and more digging into web-developement especially JavaScript and jQuery. During my research for a jQuery Plugin to map geo positions I found jVectormap. jVectormap is a great jQuery library for creating interactive maps for a website. One thing I noticed thought, is that it can be really tiring adding all the markers by hand. In this post I will describe a easy and powerful way to solve this issue with help of Google Spreadsheets. You will end up with a 100% realtime solution.

Do you want to try it out at first?

The final result, that you will end up after following this small guide.

The final result, that you will end up after following this small guide.

If you wan to give it a try at first simply click on this link.

How does it work?

Its actually pretty simple. Any Google Spreadsheet that is published on the internet can be queried in different formats (standard HTML, XML and JSON). By using the $.getJSON() Function we can get all the entries that are inside the our Google spreadsheet. The last step is to reformat the JSON Data a bit and to create a new WorldMap that is displaying our Markers.

Need more description? Simply follow these few easy steps:

1. Create a new Google Spreadsheet and insert some Data

For this example I’ve set up three columns (country, Latitude and Longitude) and inserted a few rows with some random data. I would recommend to keep the structure of the spreadsheet as in the image below, as you otherwise might run into a few problems (Part 3).

Create a new Spreadsheet and insert some data

Create a new Spreadsheet and insert some example data

2. Publish the Spreadsheet and retrieve your Spreadsheet ID

The next step is that we have to publish our spreadsheet. You can do this by going to File -> Publish to the web.

Publish Spreadsheet to the web

Publish Spreadsheet to the web

A dialog similar to this window should then appear.

Publish Google Spreadsheet

Copy the link from the TextBox and hit the publish button, the button should then change its text to published (like shown in the image above).

Your published Url will look similar to this:

The portion that we care about is everything that comes after /spreadsheets/d/ in my example  1pKlJmR28U53uU9uOHtqm8w2EOya8XW37jtACjDYXShc . This part is called the SpreadsheetID.

3. Copy this Code and adjust it to your needs

One thing that you have to change for sure is the JavaScript variable url. Make sure that you leave the Url as it is, only replace the SpreadsheetID that comes after /feeds/list/ part. Replace the SpreadsheetID with your very own from the previous step. You might also want to use a different map then the standard world map.

var url ='https://spreadsheets.google.com/feeds/list/1pKlJmR28U53uU9uOHtqm8w2EOya8XW37jtACjDYXShc/od6/public/basic?alt=json';

Change to:

var url ='https://spreadsheets.google.com/feeds/list/InsertYourSpreadsheetID/od6/public/basic?alt=json';

4. Test it!

This is the final step! Open up your favourite browser and check if everything is working as expeceted. If anything is working properly you will end up with an view similar to this.

Import Map Markers Spreadsheet

If this post was useful for you feel free to share and like it! If you have any questions or if you simply want to share your results feel free to comment below.

Cheers Alex


2 thoughts on “Query Markers of jVector Map from Google Spreadsheet

  1. Something like that would be cool for my genealogy. Also if it were possible to add a date field and a timer that reran the query with an incremented date value you could show a geographic time sequence of movement around the map.


    • Hey Grey,

      thanks for your comment. 🙂

      You could add another column to the spreadsheet that stores the dates. In the JavaScript Script you would then need a timer that redraws the map and searches up the JSON files that for the appropriate date (earlier or equal date), that way over the time more and more points would be added to the map.

      That sounds like a really interesting task, probably I want to try it next week. 😉

      Cheers Alex


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s