Meet my friend Jenny. She recently started coding websites. She loved her job and was very happy until she met Steve, who has a reputation of being, let’s say, not a very easygoing client.
Steve had a lot of data in a spreadsheet and he wanted to display that on his website. Our friend (now Jenny is your friend too!) suggested that Steve make the charts in Excel and upload them as images to the website.
But Steve being Steve, he wanted the charts to be interactive. Not only that, he also wanted the charts to get updated whenever he made a change to the data in his spreadsheet .
Jenny didn’t know how to tackle this issue, so she came to me. And, being the good friend I am, I gave her this advice:
Her client agreed (luckily!), and Jenny and I coded rest of the solution. But how? Well, that’s what this tutorial will teach you.
I have divided this tutorial into five easy-to-follow steps:
- Exporting data from Google Sheets
- Fetching JSON from Google Sheets via Ajax
- Restructuring the data
- Creating a chart with FusionCharts.
- Customizing the chart
So without further ado, let’s dive in!
Exporting Google Sheets Data as JSON
Before delving into how to export data, let’s first create a sheet. Assuming you’ve got a Google account, you can do this by going to the Google Sheets page and hitting the Start a new spreadsheet button. In the spreadsheet that opens, create two columns: Actor and Income. Then fill your newly created sheet with some data. I’ve taken mine from here: The World’s Highest-Paid Actors 2015.
As you can see, the left column contains labels for our chart, the right one values corresponding to those labels. For those of you following along at home, you can grab a copy of this sheet here (go to File > Make a copy).
The data available in Google Sheets can be exported to multiple formats like JSON, XML etc. And once exported to any of those formats, it can be easily accessed via the web.
To open your document to the web, you need to make the following changes to the document settings:
- Set the sharing of the document to either Public on the web, or Anyone with the link. To achieve this, click the Share button in the top right-hand corner, then click the Advanced option that appears in the bottom right of the pop up.
- Publish the document to the web. This option is available under File > Publish to the web
With these two changes made, the data in the document can be accessed in JSON format via: https://spreadsheets.google.com/feeds/list/SPREADSHEET/od6/public/basic?alt=json
You will need to replace
SPREADSHEET with the ID of your Google Sheet, which in our case is
1Aoz_GcPYoEIMMNd1N_meYNOp8TJ0fCXpp1AoUhCpwZo. You can see the results here.
Using jQuery to Fetch JSON from Google Sheets
We will use jQuery’s get() method to fetch the data from Google Sheets. You can include jQuery from a CDN as shown: