Publish a Linked-Data-powered Google Chart on the Web in under 10 steps
In this post I use the new statistics.gov.scot site to show how it’s possible to find some specific data and create and share a Google chart which uses it.
Powered by PublishMyData, the data in statistics.gov.scot is flexibly queryable so you can re-use it for whatever custom requirements you may have. In this example I’m looking at using data to inform where I should work in Scotland.
Here’s a sneak-peek of what we’ll end up with at the end of the article:
- I wanted to find out where, as a woman I could earn the most in Scotland? I headed to statistics.gov.scot and searched for ‘Earnings’.
- The Earnings dataset has multiple dimensions, and I needed to make some selections to arrive at a tabular view. I am interested in Female earnings, so I selected Female, Median, Workplace-based earnings (by clicking the links in the dimensions summary).
… This left the reference period unselected, so this became the columns of this ‘slice’ (the rows are always areas).
- At this stage, there’s a download as CSV icon below the table, which looks just like the one below. Right click on it and copy the link address.
- In Google Sheets, make a new sheet and in the first cell type the following:
=IMPORTDATA(“I pasted the copied link here“)
This will import all the data from the table into your Google sheet.
- Select all the data (except the URI identifiers, as we don’t need these for the chart).
- Click the Insert Chart… button in the toolbar (the bar chart icon)
- For me, I was only interested in the most recent data (in this case 2012, in column
N), so I edited the default data range in the recommendation tab to show the columns I need.
- Then, in the customization tab, I edited the graph title and axis labels (I also reduced the size of the area labels so you can read them all on the chart).
(Note: if you press Insert before you’ve finished editing, you can go back and edit the chart via hovering over the chart and using the little drop down arrow at the top right, then Advanced Edit).
- The chart is now finished: the data here tells me I should head to Aberdeen!
Now you can share the graph with whoever you want. I hovered over it and a down arrow gave me the option to Publish Chart. You can choose either to embed your graph or link to it. Here’s the embedded link code:
<iframe width="700" height="650" seamless frameborder="0" scrolling="no" src="https://docs.google.com/spreadsheets/d/1rC64gMz4x7twvNdMDM-k1pCfT9D3lpfTX1Poj4k5bsc/pubchart?oid=877296683&format=interactive"></iframe>
And here is the final (interactive) result, embedded in this blog post (by copy-pasting the code above).
This article should give you a very accessible start to taking advantage of data on a PublishMyData-powered site.
Try it out for yourself: statistics.gov.scot; Open Data Communities; Hampshire Hub and Surrey Open Data are all sites powered by PublishMyData so you can work with all their data in the same way (or at least you can once they are all on the latest version - we’re in the process of upgrading some of them!). And watch this space for examples of how you can select and combine data, to create custom views, with PublishMyData’s new Data Cart.
We'd love it if you shared this article.