Imagine a collaborative project where you have your data stored on a shared Google Spreadsheet.  Anybody, anywhere can access and edit this document with permission.  Imagine this spreadsheet having locational parameters (latitudes and longitudes) and temporal attributes (date).  Imagine if you could then use this very spreadsheet to dynamically feed a geo-temporal map viewer web application…

To imagine this even happening, let’s take the first step of actually finding out how to extract data from a given Google Spreadsheet as a JSON feed using javascript.

Step 1: Prepare your Google Spreadsheet

1. In your google spreadsheet, click on the “share” button and choose “Anyone on the internet can find and view”
   
2. Go to “File”, “Publish to the web…” and publish the entire spreadsheet

Step 2: Building the URL string

1. Get the ID of the spreadsheet:
   Take note of the current spreadsheet URL in your web-browser.  It should look something like this:
   
   The highlighted part of the URL is the spreadsheet ID.
2. Get the ID of the specific worksheet:
   This is a bit harder to get.  First, you will have to use the spreadsheet ID from the previous step and replace the spreadsheetID in the following URL string with the spreadsheet ID which you just found:

   https://spreadsheets.google.com/feeds/worksheets/spreadsheetID/public/basic?alt=json

   Replace the spreadsheetID parameter with the ID obtained in the previous step, plug the url into a web browser.  Traverse the JSON object that is returned to find the child element for “entry”
   
   If you are using the first worksheet in your google doc, and if have made no changes to the name of the sheet, you will find an entry with title “Sheet 1”, and the worksheetID would be “od6”.  If you made changes to the spreadsheet, or are using a different sheet, you will need to scroll down further to find the correct worksheetID.

3. Now that you have the spreadsheet ID and the worksheet ID, plug them into the following URL string:

   https://spreadsheets.google.com/feeds/list/spreadsheetID/worksheetID/public/values?alt=json

Step 3: Using the JSON feed with jQuery+LeafletJS

Now that we have the ability to extract data from a Google Spreadsheet as a dynamic JSON object, we can build a web application that utilizes the data feed.  Here is an example:

<!-- jquery  -->
<script src="http://code.jquery.com/jquery-1.11.0.min.js"></script>
<script src="http://code.jquery.com/jquery-migrate-1.2.1.min.js"></script>

<!-- leaflet -->
<link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.7.3/leaflet.css" />
<script src="http://cdn.leafletjs.com/leaflet-0.7.3/leaflet.js"></script>

<!-- styling -->
<style type="text/css">
	#map { height: 400px; }
</style>

<!-- javascript -->
<script type="text/javascript">

var map;
var markerArray = [];

$(function() {
	// create a map in the "map" div, set the view to a given place and zoom
	map = L.map('map').setView([34,-118], 13);

	// add an OpenStreetMap tile layer
	L.tileLayer('http://{s}.tile.osm.org/{z}/{x}/{y}.png', {
	    attribution: '&copy; <a href="http://osm.org/copyright">OpenStreetMap</a> contributors'
	}).addTo(map);

	// map google data
	getGoogleData();

});

function getGoogleData()
{
	var spreadsheetID = '1iR3yUchfOtYZoCscpnbsaUFWEcGyF9S_GuoA4cOU_oc';
	var worksheetID = 'od6';
	var url = 'https://spreadsheets.google.com/feeds/list/'+spreadsheetID+'/'+worksheetID+'/public/values?alt=json';

	$.getJSON(url,function(data){
		$.each(data.feed.entry,function(i,val){

			// assign parameters for mapping and infowindow
			// note that this will be different depending on header titles
			var lng = val.gsx$x.$t;
			var lat = val.gsx$y.$t;
			var title = val.gsx$affiliated.$t;
			var content = val.gsx$notes.$t;

			var thisMarker = L.marker([lat,lng]).addTo(map)
			    .bindPopup('<h2>' + title + '</h2>' + content);

			// push marker into an array
			markerArray.push(thisMarker);

		});

		// put markers into a group to 
		var group = L.featureGroup(markerArray).addTo(map);
		map.fitBounds(group.getBounds());
	})


}

</script>

<div id="map"></div>

 

• news