How to get data from a Google Spreadsheet, a Google Calendar & Google Blogger.

By Michael Smith

26th April 2014


 

With a simple jquery ajax call, it is quite straight forward to get data from a Google Spreadsheet, a Google Calendar or a Google Blogger news feed.  Probably easy to get from other Google docs too, but I’m just documenting what I’ve had experience with.

So Steve says, “Can you make me an app?”

I say. “Sure Steve, I can make you an app.”

And Steve says “Great.”

So I plan this app.  Steve wants data from a Google Spreadsheet in the app, info from a Google Calendar in the app and the news feed from a Google Blogger webpage in the app.  So my plan is to make the app in HTML5 and javascript then build the ios and Android binaries with Phonegap Build – easy peasy.  The only worry I had was getting the data from the Google Spreadsheet, Calendar and Blogger news feed. I didn’t need to worry, it turns out to be extremely easy; you just need to make an ajax call and parse the info you want from the returned json object.  So I built my app, compiled and used Phonegap Build. I then uploaded to Google Play, Amazon and Apple’s App Store.

So I say to Steve, “Apps done.”

And Steve says, “Great.”

And Steve buys me a beer.

The End.

 

Oh yeah, the code:

Make an ajax call and parse the json data. Also if you are planning some DOM malarkey, do it in the the ajax call as the data will not be there if you try to use it immediately after the ajax call –  asynchronous.

Data from a Google Spreadsheet:
     $.ajax({ 
     url: 'http://spreadsheets.google.com/feeds/cells/'+SPREAD SHEET KEY+'/public/values?alt=json-in-script&callback=?',
     type: 'get',
     dataType: "jsonp",
     success: function(json){  Your code to parse the json object and get the data you require goes here.  Some example code:
     for (i = 0; i < json.feed.entry.length; i++)
     {
         entry = json.feed.entry[i];
         console.log(entry.gs$cell.row);
         console.log(entry.gs$cell.col);
         console.log(entry.gs$cell.$t)
     }
   also do the dom malarkey if it is needed as soon as you have data   }
     });

Obviously change SPREAD SHEET KEY for the key of your spreadsheet.  Also the share state of the spreadsheet needs to be public.

 

The calendar:
$.ajax({
     url: 'http://www.google.com/calendar/feeds/'+ PRIVATE CALENDAR ADDRESS +'/public/full?alt=json-in-script&callback=?&orderby=starttime&max-results='+ NUMBER OF EVENTS TO GET +'&singleevents=true&sortorder=ascending&futureevents=true',
     type: 'get',
     dataType: "jsonp",
     success: function(json) { parse json and do the dom thing }
});

For details on how to find your calendars private address have a look this page.

 

The Blogger news feed:
$.ajax({
 url: 'http://'+ BLOG ADDRESS +'/feeds/posts/default?alt=json-in-script&max-results='+ NUMBER OF POSTS TO GET +'&callback=?',
 type: 'get',
 dataType: "jsonp",
 success: function(json) { parse the json and do the dom }
});

 


 

To inspect the json object and plan your parsing code, use this:

     console.log(JSON.stringify(json));

You might also want to add:

beforeSend: function(){start a loading spinner/do something to indicate data loading}
complete: function(){stop loading spinner}
error: function() { do something here }

 

And there you go, hope you found it useful. If you’d like an e-mail whenever I post my narcissistic ramblings interesting articles, then please subscribe to my blog (top of page, right hand panel).

“To part is the lot of all mankind. The world is a scene of constant leave-taking, and the hands that grasp in cordial greeting today, are doomed ere long to unite for the the last time, when the quivering lips pronounce the word – ‘Farewell” - R.M. Ballantyne