{"id":1411,"date":"2014-10-08T14:55:21","date_gmt":"2014-10-08T13:55:21","guid":{"rendered":"http:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/?p=1411"},"modified":"2014-10-08T15:55:50","modified_gmt":"2014-10-08T14:55:50","slug":"how-to-get-data-out-of-a-google-spreadsheet-using-jsonp","status":"publish","type":"post","link":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/2014\/10\/08\/how-to-get-data-out-of-a-google-spreadsheet-using-jsonp\/","title":{"rendered":"How to: get data out of a Google spreadsheet using JSONP"},"content":{"rendered":"

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<\/a> for example?<\/p>\n

Google has an API for obtaining the data using JSONP<\/a>; 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.<\/p>\n

To do this, you need to:<\/p>\n

    \n
  1. Make your spreadsheet public<\/li>\n
  2. Get the ID of your spreadsheet and worksheet<\/li>\n
  3. Include a script tag calling the API<\/li>\n
  4. Write a callback function to use the data<\/li>\n<\/ol>\n

    1. Make your spreadsheet public<\/strong><\/p>\n

    In Google Drive, go to File > Publish to the web …<\/strong> and click Publish<\/strong>. You can only obtain data from a sheet that is publicly readable.<\/p>\n

    2. Get the ID of your spreadsheet and worksheet<\/strong><\/p>\n

    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.<\/p>\n

    You can find the worksheet number by calling a URL constructed like so:<\/p>\n

    https:\/\/spreadsheets.google.com\/feeds\/worksheets\/your-spreadsheet-id<\/em>\/private\/full<\/p>\n

    Note that you must be logged in to Google Drive to do this, or the URL will return nothing at all!<\/p>\n

    Calling this URL will return an RSS feed that will contain something like this:<\/p>\n

    \r\n<entry>\r\n<id>https:\/\/spreadsheets.google.com\/feeds\/worksheets\/your-spreadsheet-id\/private\/full\/o10c0rt<\/id>\r\n<updated>2014-10-08T11:35:31.493Z<\/updated>\r\n<category\u00a0scheme="http:\/\/schemas.google.com\/spreadsheets\/2006"\u00a0term="http:\/\/schemas.google.com\/spreadsheets\/2006#worksheet"\/>\r\n<title\u00a0type="text">Form Responses 1<\/title>\r\n<\/pre>\n
    The information you need is in the <id> tag. The last part of the id is the worksheet identifier.<\/p>\n
    3. \u00a0Include a script tag calling the API<\/strong><\/p>\n
    In your HTML, include a script tag, like so:<\/p>\n
    <script src="https:\/\/spreadsheets.google.com\/feeds\/cells\/your-spreadsheet-id\/your-worksheet-id\/public\/values?alt=json-in-script&callback=sheetLoaded"><\/script><\/pre>\n
    Obviously you need to replace “your-spreadsheet-id” and “your-worksheet-id” with the values from the previous step.<\/p>\n
    4. Write a callback function to use the data<\/strong><\/p>\n
    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:<\/p>\n
    function sheetLoaded(spreadsheetdata) {\r\n \/\/ do something with spreadsheet data here\r\n console.log(spreadsheetdata);\r\n}<\/pre>\n
    Job done! Now you can actually start doing the clever D3 visualisation part…<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":245,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38150],"tags":[],"class_list":["post-1411","post","type-post","status-publish","format-standard","hentry","category-data-modelling-and-migration"],"_links":{"self":[{"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/posts\/1411","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/users\/245"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/comments?post=1411"}],"version-history":[{"count":6,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/posts\/1411\/revisions"}],"predecessor-version":[{"id":1421,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/posts\/1411\/revisions\/1421"}],"wp:attachment":[{"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/media?parent=1411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/categories?post=1411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/tags?post=1411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}