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.

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.