Load Google Spreadsheet to visualize with leaflet

1
votes

My data source for a map based on mapbox.js is hosted in a Spreadsheet on Google Drive. Actually I use Geo for Google Docs to export the locations to GeoJSON and Leaflet.markercluster to visualize them on a map.

I need to load the registers directly from Google Drive so changes will be reflected immediately without exporting the data. mapbox.js API has different methods to fullfill this but none of them works for me. So my question is (A) which of them is the preferred one and (B) how I can make this one work:

  1. markerLayer.loadURL() methode loads and shows GeoJSON data but I can't figure out how to directly access Google Drive file in GeoJSON format without exporting it first.

  2. I tried to use jQuery getJSON method but callback doesn't call defined function:

    $.getJSON("https://spreadsheets.google.com/feeds/list/KEY/od6/public/basic?alt=json-in-script&callback=loadMarkers");

  3. markerLayer.setGeoJSON(): I published the spreadsheet as CSV and used csv2geojson to convert it to GeoJSON similary to Loading CSV into Markers example. Using the following code I get an TypeError: t is undefined error at line 5 of mapbox.js.

    $.ajax({
    url: 'https://docs.google.com/spreadsheet/pub?key=KEY&output=csv',
    success: function csvLoad(csv) {
        markerLayer.setGeoJSON(csv2geojson.csv2geojson(csv, function(err, data) {
        }));
    }
});

Thanks for the help.

2
javascriptleafletgoogle-spreadsheet-apigeojsonmapbox

2 Answers

2
votes

Take a look at the recline.js, a library for building data applications. It has a Google Docs data source that allows you to read from a spreadsheet using the Google Docs JSON API like this:

recline.Backend.GDocs.fetch({
  url: 'https://docs.google.com/a/okfn.org/spreadsheet/ccc?key=0Aon3JiuouxLUdDlGV2lCakoydVh1U014cHRqcXpoWVE#gid=0'
})
  .done(function(result) {
  // structure of result is below
  console.log(result);
});

More details and examples at https://github.com/okfn/recline.backend.gdocs

0
votes

Option 2 of the above question actually works if you define callback as "?". So one way to use jQuery getJSON method with a callback is like this:

$.getJSON("https://spreadsheets.google.com/feeds/list/KEY/od6/public/basic?alt=json-in-script&callback=?", 
function(json) {
    console.log(json);
});