JSON APIs are a pretty powerful way of connecting your frontend app to a dataset through Javascript. Most of the big web apps provide their API in JSON format (Facebook, Twitter, Instagram) however, as you may know if you've ever tried to use these, they often require an OAuth login in order to access the API.

This obviously isn't ideal if you don't want your users to have to log in to their Twitter/Facebook/Instagram account every time they view your app or website!

Well, I've found a little bit of a workaround that may get you working with data in your front end without having to worry about stuff like OAuth.

A few months back I needed to deliver a Designing With Data class and began experimenting with JQuery and JSON APIs. During that time I stumbled across this post about how to use a Google Spreadsheet as a live JSON.

I thought that was pretty sweet and since then I have built several little applications using a Google Spreadsheet as a database, accessed via a JSON (including a full CMS, which is probably worth a blog post of its own, but was such an exhausting process that even thinking about it gives me shivers!).

In this tutorial we're going to be using ifttt.com (If That Then This) which, if you haven't used it before, is a free service that will make your life so much better - getting the internet to work for you by utilising the abundunce of APIs out there - and we're also going to be using Google Drive, so you'll need an account on both of these services. I'm also going to be using JQuery for my scripting, but the principal will be the same across all languages.

Get everything set up

I'll split this tutorial in to two bits: Getting the data in to a Google Spreadsheet via IFTTT, and then pulling that data in to your app using JQuery.

For the purposes of this tutorial I'm going to pull some data from Instagram - one of the APIs that requires OAuth.

Step One - IFTTT to Google Drive

First, log in to IFTTT and make the Instagram (or whichever service you want to pull data/content from) and Google Drive 'channels' live. This involves connecting IFTTT to those accounts and allowing it access - this is the key bit that handles the OAuth part of the API request on Instagram.

Once the channels are live we need to create a recipe that takes data/content from Instagram and puts it in to a Google Spreadsheet in my Google Drive.

I click on 'create recipe' and choose Instagram as my 'this' portion. I can now choose a trigger that makes the recipe fire, and in this case it's when any photo is tagged '#skateboarding' (because I'm rad and dope).

create recipe step one create recipe step two

Then I need to set that trigger to do something, in my case post to a Google Spreadsheet with the data fields I want. IFTTT calls these data fields 'ingredients'.

create recipe step three

On this screen you select your ingredients and tell IFTTT which spreadsheet to post the data to (you can set it so a new spreadsheet is created should you wish). The ||| triple pipes denote the different columns on the spreadsheet.

create recipe step four

You can edit which data fields IFTTT pulls from Instagram, and add more using the plus sign and drop down.

create recipe step four

That's it - you can now create the recipe and run it. Once the recipe triggers it will add a new row to the spreadsheet you selected in Google Drive.

Step Two - Google Drive to your App

As I mentioned, there's a tutorial here on how to convert a Google Spreadsheet to a JSON, so I won't repeat too much from there.

We should note that the JSON we are using is live and will update as the spreadsheet itself updates - so every time a new row is added via your recipe the JSON will update accordingly.

Once you have 'published to web' the important bit to grab is the spreadsheet key, and the URL in the tutorial that will show you the data as a JSON. It looks like this:


This is the URL we're going to use in our JQuery.

First thing I need to do is on document ready call the JSON and run a function.

  $(document).ready(function() {
    $.getJSON("https://spreadsheets.google.com/feeds/list/1yCtwA_lGucUP2a6Yx5GAI38G-XO1emd4ZQ3eh2v9Dms/od6/public/values?alt=json", function(data) {

In this demo I want to pull all of the data, so I'm going to run an 'each' function, however you may want to run a random call on some of the data, as I have done in this pen.

          var entry = data.feed.entry;
        $('.results').prepend('<img src="'+ this.gsx$image.$t+'" alt="'+this.gsx$caption.$t+'"/>');
    }); // end get
}); // end document.ready

Some notes on this code:

  • Most JSON files have their data quite near the top of the tree. Because Google Spreadsheet's weren't designed for this, we need to do a bit of digging to find our data. Stick the JSON URL in to your browser, and use an extension like JSON View for Chrome and you can see how the data tree looks
  • Each row on the spreadsheet is under 'feed > entry' within the JSON
  • The gsx$image.$t part of the code is the specific column that we want to get the data from. The gsx bit is Google spreadsheet specific, $image refers to the name of my column ('image', 'caption'), and $t is the content of the cell. Again, take a look at the JSON and you will see how this is structured.
  • I want to create a grid of Instagram images based on the skateboarding tag, so I am prepending each image inside a results div, which I'll then control with CSS.

And that's about it - pretty simple. Here is the completed pen:

There will obviously be restrictions with the sort of data IFTTT can get (Twitter, for example - IFTTT can only pull data from your account, no public searches) but there are absolutely loads of channels on IFTTT, so I'm sure there are plenty of possibilties for doing interesting stuff now they can all be turned in to live JSON files.

Enjoy, and if you make anything cool I'd love to hear about it!