{"id":1467,"date":"2014-10-09T15:15:19","date_gmt":"2014-10-09T14:15:19","guid":{"rendered":"http:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/?p=1467"},"modified":"2014-10-09T15:15:19","modified_gmt":"2014-10-09T14:15:19","slug":"how-to-convert-google-spreadsheet-json-data-into-a-simple-two-dimensional-array","status":"publish","type":"post","link":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/2014\/10\/09\/how-to-convert-google-spreadsheet-json-data-into-a-simple-two-dimensional-array\/","title":{"rendered":"How to: convert Google Spreadsheet JSON data into a simple two-dimensional array"},"content":{"rendered":"<p>In a previous post I explained <a title=\"How to: get data out of a Google spreadsheet using JSONP\" href=\"http:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/2014\/10\/08\/how-to-get-data-out-of-a-google-spreadsheet-using-jsonp\/\">how to extract JSON data from a Google Spreadsheet via an API call<\/a>.<\/p>\n<p>However, when you actually get the data, the JSON isn&#8217;t really in the kind of structure you would imagine. Instead of a matrix of rows and columns, Google returns an RSS-style linear feed of &#8220;entries&#8221; for all of the cells!<\/p>\n<p>So how to convert that into something that you can use in <a href=\"http:\/\/d3js.org\/\">D3.js<\/a> or <a href=\"http:\/\/www.r-project.org\/\">R<\/a>?<\/p>\n<p>We need to iterate over each entry in the feed, and push the values into an array, moving to a new &#8220;line&#8221; in the array each time we get to a cell that is at the beginning of a row in the spreadsheet.\u00a0I&#8217;ve written a JavaScript function to do the work necessary; you can <a href=\"https:\/\/github.com\/scottbw\/Google-SpreadSheet-to-D3-Dataframe\/\">get the code on Github<\/a>.<\/p>\n<p>Running this function we can then get the values from the resulting array using something like:<\/p>\n<p><code>data[1][5]<\/code><\/p>\n<p>Note that the function doesn&#8217;t differentiate the labels from a header row (which is something you&#8217;d commonly see, and which R would usually expect) so there is definitely room for improvement in the function.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In a previous post I explained how to extract JSON data from a Google Spreadsheet via an API call. However, when you actually get the data, the JSON isn&#8217;t really in the kind of structure you would imagine. Instead of &hellip; <a href=\"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/2014\/10\/09\/how-to-convert-google-spreadsheet-json-data-into-a-simple-two-dimensional-array\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/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-1467","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\/1467","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=1467"}],"version-history":[{"count":1,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/posts\/1467\/revisions"}],"predecessor-version":[{"id":1468,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/posts\/1467\/revisions\/1468"}],"wp:attachment":[{"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/media?parent=1467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/categories?post=1467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs-new.it.ox.ac.uk\/acit-rs-team\/wp-json\/wp\/v2\/tags?post=1467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}