Example - Google Spreadsheet
Google Spreadsheet as a Table
Google Sheets are an easy and popular way to share spreadsheet data. Whilst publicly available Google Sheets could always be pulled using the URL content source, from v8.2.0, Universal Content Puller provides a dedicated Google Sheet content source to make using data from any publicly shared Google Sheet even easier.
Data can be pulled in CSV, JSON or HTML format. This will usually be followed by a transform into a Table. Where the Google sheet includes multiple individual sheets (layers or tabs), they be specified by gid or name.
Source Data
Here we have pulled a sheet titled RWC 2015 Age and Cap Profiles. This is some stats about the 20 teams in the 2015 Rugby World Cup.
The sheet provides a list of teams, the total caps of the squad (a cap is awarded for each time a player appears for the national team), then a breakdown of players in each cohort of 10 caps. We can see that the All Blacks (New Zealand) have a total of 1648 caps with just one player having 90-99 caps. Meanwhile Italy and Wales each have 2 players with 90-99 caps.
If you look at the Google Sheet rather than the data here, you will see it is listed in order of team caps, with New Zealand at the top and Samoa at the bottom. Here we decided to list the teams alphabetically. If it were our own sheet, the easy solution would be to adjust the source. But we can't do that here, so we have used a Pipeline Content Transform in 2 steps. The first step extracts the CSV from the sheet with Table from CSV. The second step sorts by the country with Table Sorter. We then pick the headings to show and rows and columns to filter in the Table display.
It is actually a little more complex than that, but rather than describe it minute detail here you can just View settings to copy the JSON and then Import it into UCP on your own site.
| Caps | 1-9 | 10-19 | 20-29 | 30-39 | 40-49 | 50-59 | 60-69 | 70-79 | 80-89 | 90-99 | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Argentina | 1032 | 3 | 6 | 11 | 4 | 3 | 2 | 3 | 1 | 0 | 0 |
| Australia | 1417 | 3 | 3 | 4 | 5 | 2 | 4 | 5 | 2 | 0 | 0 |
| Canada | 845 | 4 | 8 | 9 | 7 | 2 | 0 | 1 | 1 | 0 | 0 |
| England | 927 | 3 | 9 | 7 | 3 | 5 | 4 | 1 | 0 | 0 | 0 |
| Fiji | 730 | 7 | 9 | 7 | 8 | 1 | 1 | 0 | 0 | 0 | 0 |
| France | 1113 | 3 | 7 | 4 | 7 | 3 | 1 | 2 | 1 | 3 | 0 |
| Georgia | 1237 | 3 | 4 | 4 | 7 | 4 | 4 | 2 | 1 | 3 | 0 |
| Ireland | 1183 | 3 | 7 | 5 | 4 | 4 | 3 | 3 | 0 | 2 | 0 |
| Italy | 1301 | 4 | 5 | 8 | 4 | 3 | 0 | 1 | 1 | 1 | 2 |
| Japan | 1109 | 1 | 6 | 5 | 7 | 6 | 3 | 1 | 1 | 0 | 1 |
| Namibia | 596 | 8 | 12 | 5 | 2 | 3 | 1 | 0 | 0 | 0 | 0 |
| New Zealand | 1648 | 3 | 3 | 3 | 6 | 7 | 0 | 1 | 2 | 1 | 1 |
| Romania | 1275 | 6 | 3 | 4 | 2 | 1 | 6 | 2 | 4 | 3 | 0 |
| Samoa | 594 | 10 | 8 | 7 | 4 | 1 | 1 | 0 | 0 | 0 | 0 |
| Scotland | 837 | 5 | 12 | 6 | 3 | 2 | 2 | 0 | 0 | 0 | 1 |
| South Africa | 1477 | 2 | 7 | 4 | 4 | 2 | 2 | 2 | 4 | 2 | 0 |
| Tonga | 617 | 7 | 9 | 10 | 3 | 2 | 0 | 0 | 0 | 0 | 0 |
| USA | 651 | 8 | 11 | 4 | 5 | 0 | 3 | 0 | 0 | 0 | 0 |
| Uruguay | 957 | 0 | 9 | 6 | 7 | 5 | 4 | 0 | 0 | 0 | 0 |
| Wales | 1243 | 10 | 4 | 2 | 4 | 3 | 3 | 3 | 1 | 1 | 2 |
As a chart
We can also show the data as a chart using Universal Content Puller Charts. To prevent the chart becoming overwhelming, we just show country vs caps here. The detailed breakdown columns are sliced out of the table in the CSV transform before transposing the table so we can plot countries in our X-axis.
More Sheets
If you want more Google Sheets to experiment with, you can use a search query like:
https://www.google.co.uk/search?q=site:docs.google.com/spreadsheets+whatever+you+want+sheets+on
