Google Drive spreadsheets are a great way to collect data using their handy forms, but the visualisation tools sometimes aren’t sufficient. What if you wanted to do a visualisation using d3.js for example?
Google has an API for obtaining the data using JSONP; this means that the data is exposed in JSON format using a callback function – this gets around the “Same Origin Policy” restriction on accessing data from a different web domain.
To do this, you need to:
- Make your spreadsheet public
- Get the ID of your spreadsheet and worksheet
- Include a script tag calling the API
- Write a callback function to use the data
1. Make your spreadsheet public
In Google Drive, go to File > Publish to the web … and click Publish. You can only obtain data from a sheet that is publicly readable.
2. Get the ID of your spreadsheet and worksheet
This isn’t as obvious as it sounds. Your spreadsheet URL will contain some sort of long identifier, but this isn’t the only information you need – you also need the shorter worksheet ID as well.
You can find the worksheet number by calling a URL constructed like so:
https://spreadsheets.google.com/feeds/worksheets/your-spreadsheet-id/private/full
Note that you must be logged in to Google Drive to do this, or the URL will return nothing at all!
Calling this URL will return an RSS feed that will contain something like this:
<entry> <id>https://spreadsheets.google.com/feeds/worksheets/your-spreadsheet-id/private/full/o10c0rt</id> <updated>2014-10-08T11:35:31.493Z</updated> <category scheme="http://schemas.google.com/spreadsheets/2006" term="http://schemas.google.com/spreadsheets/2006#worksheet"/> <title type="text">Form Responses 1</title>
The information you need is in the <id> tag. The last part of the id is the worksheet identifier.
3. Include a script tag calling the API
In your HTML, include a script tag, like so:
<script src="https://spreadsheets.google.com/feeds/cells/your-spreadsheet-id/your-worksheet-id/public/values?alt=json-in-script&callback=sheetLoaded"></script>
Obviously you need to replace “your-spreadsheet-id” and “your-worksheet-id” with the values from the previous step.
4. Write a callback function to use the data
In your javascript code you need to implement the callback function named in the script tag, so in the above example we need to do something like:
function sheetLoaded(spreadsheetdata) {
// do something with spreadsheet data here
console.log(spreadsheetdata);
}
Job done! Now you can actually start doing the clever D3 visualisation part…
And there’s a nice set of google spreadsheet “formulas” or functions you can use to get data into a sheet from somewhere else on the web:
https://support.google.com/docs/answer/3093335?hl=en
i.e. try typing:
=IMPORTDATA(“http://www.census.gov/2010census/csv/pop_change.csv”)
into a cell.
[…] In a previous post I explained how to extract JSON data from a Google Spreadsheet via an API call. […]
[…] in this series I discussed how to get data out of a Google Spreadsheet in JSON format using an API call, and how to convert the JSON data into an array. Now I’m going to talk about how to visualise […]
worked a treat. put in https://spreadsheets.google.com/feeds/cells/your-spreadsheet-id/your-worksheet-id/private/full
then search for “tq?gid=”
the feed links for each sheet include this
Will it work with private sheets?
“https://spreadsheets.google.com/feeds/cells/1vEO4-aWvuZNlVij6dPnROPZ7C8t0wxJgQ259qD9svPo/om1el85/private/values?alt=json-in-script&callback=sheetLoaded”
If I am logged in?
Hi Nilesh,
No, as far as I’m aware you need to use a public sheet for this.
Thank you so much! Helped me a lot 🙂
One thing: I think it would be nice if you added that there might be different worksheet id’s, so the first one you find might not work because it belongs to the wrong worksheet…
Thats a very good point! Yes, if you have multiple tabs/worksheets you have to make sure you get the id for the right one.
Hi, for some reason it seems as though the JSON data I get from the Google spreadsheet is not updated everytime I open the webpage up… I want to use this method to update my graph on the website I have (So the data comes from the spreadsheet and I use the values into my graph and plot/show it on the website) however it doesn’t seem to update everytime there is an update in the spreadsheet as well….am I doing something wrong or am I not understanding how JSON works… Is JSON a one time grab data thing or can it constantly ‘update’?
Hi Richard – I can’t say for certain, but in general every time you GET the JSON data its the latest version.
There may be some caching going on in your case, which could possibly be in the code generating the graph, or the graph itself, rather than the JSON.