JavaScript: Loop through a Google Spreadsheet

JavaScript: Loop through a Google Spreadsheet

In our post Coding marker maps with Leaflet.js we filled a map visualization with information given directly in the code, but we also learned how to loop through data in a JSON file to create markers and pop-ups. This time, we will loop through an public Google Spreadsheet. We will do this with the same example map as in the previous post. But this little trick will not only help you with building marker maps but can come in handy for any JavaScript application you need to automate the handling of your data.

So let’s start right away with setting up a data table with Google Spreadsheets.


These are some of the data driven teams in the German speaking area. We have their locations as latitude and longitude data, the name of the team, the newsroom’s company, city and the team’s head or a contact person, the path to the icon image and the wanted orientation of the icon, so if it should be displayed above the geolocation or next to it.

Thanks to a JavaScript Library called Tabletop, we will loop through this Google Spreadsheet to fill and update our map of data driven teams. Save the spreadsheet and enable it for everyone in the web to find and open. This is very important to make the data accessible for Tabletop.


Setting up the directory

Now for the Code: First of all, create the index.html with the path to the leaflet.css, leaflet.js, jquery.js, app.js and style.css just as in our map from the previous post. Exept that this time, you also include the path to tabletop.js. You can get all these files on our GitHub page. Save them in the same directory as the index.html to have them all together.

We won’t show you the content of the style.css file in this tutorial, because it’s just the same as in Coding marker maps with Leaflet.js. What’s different though is the part in app.js where we access and work with data.


Set up app.js just as in the previous post…

Now, to access and loop through the data, Tabletop needs an access code. You’ll find it in the URL of your spreadsheet:


Add the code as a variable code in app.js.

Then loop through the spreadsheet with Tabletop as shown below. We simply use a “for” loop that goes through all the rows of our data.

When i =1, we’re accessing the first row of the data, which would be sheet[1]. We save the data in a variable called data, then create a variable icon where we set the path to the icon, it’s size and so on. The path is saved on the spreadsheets column icon. We access the path of data = sheet[1] with typing data.icon.

And this is all you need to know. You can access every entry in this row by simply writing data.name_of_column. You can change the icon settings depending on whether the column data.iconori is set to “left” or “right”. You can get the team’s geolocation by typing [data.longitute, data.latitude], you can fill the popup window by pasting information like its name = data.newsroom with basic HTML style.

Save the file to your directory and open index.html in the browser:

Now, if you change or add information in your online spreadsheet and reload the map, your changes will be shown. This only works if you enabled the sheet for everyone in the web to find and read!

The code is hosted on our GitHub Page. If you have any problems, questions or feedback, send us a message or leave a comment! It’s also useful to have a look at Tabletops documentation.


Comments ( 4 )

  1. JavaScript: Interactive Leaflet.js map with search bar | Journocode
    […] our previous posts about Leaflet.js, we coded an interactive marker map and learned how to update our data with a google spreadsheet. In this short tutorial, we will show you how to make your map searchable so users can find a […]
  2. Replygp
    how to add a marker cluster layer?
  3. ReplyNewYears1978
    Could you please add code/tutorial to show how to change the marker sizes when zooming out?Been trying to get zoomend to work for like a week..but I can't figure it out.
  4. ReplyNewYears1978
    How do you make layer group/controls when using this method?

Leave a reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.