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.
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 | 100+ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Argentina | 1032 | 3 | 6 | 11 | 4 | 3 | 2 | 3 | 1 | 0 | 0 | 0 |
| Australia | 1417 | 3 | 3 | 4 | 5 | 2 | 4 | 5 | 2 | 0 | 0 | 3 |
| Canada | 845 | 4 | 8 | 9 | 7 | 2 | 0 | 1 | 1 | 0 | 0 | 0 |
| England | 927 | 3 | 9 | 7 | 3 | 5 | 4 | 1 | 0 | 0 | 0 | 0 |
| Fiji | 730 | 7 | 9 | 7 | 8 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
| France | 1113 | 3 | 7 | 4 | 7 | 3 | 1 | 2 | 1 | 3 | 0 | 0 |
| Georgia | 1237 | 3 | 4 | 4 | 7 | 4 | 4 | 2 | 1 | 3 | 0 | 0 |
| Ireland | 1183 | 3 | 7 | 5 | 4 | 4 | 3 | 3 | 0 | 2 | 0 | 1 |
| Italy | 1301 | 4 | 5 | 8 | 4 | 3 | 0 | 1 | 1 | 1 | 2 | 3 |
| Japan | 1109 | 1 | 6 | 5 | 7 | 6 | 3 | 1 | 1 | 0 | 1 | 0 |
| Namibia | 596 | 8 | 12 | 5 | 2 | 3 | 1 | 0 | 0 | 0 | 0 | 0 |
| New Zealand | 1648 | 3 | 3 | 3 | 6 | 7 | 0 | 1 | 2 | 1 | 1 | 5 |
| Romania | 1275 | 6 | 3 | 4 | 2 | 1 | 6 | 2 | 4 | 3 | 0 | 0 |
| Samoa | 594 | 10 | 8 | 7 | 4 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
| Scotland | 837 | 5 | 12 | 6 | 3 | 2 | 2 | 0 | 0 | 0 | 1 | 1 |
| South Africa | 1477 | 2 | 7 | 4 | 4 | 2 | 2 | 2 | 4 | 2 | 0 | 3 |
| Tonga | 617 | 7 | 9 | 10 | 3 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| USA | 651 | 8 | 11 | 4 | 5 | 0 | 3 | 0 | 0 | 0 | 0 | 0 |
| Uruguay | 957 | 0 | 9 | 6 | 7 | 5 | 4 | 0 | 0 | 0 | 0 | 0 |
| Wales | 1243 | 10 | 4 | 2 | 4 | 3 | 3 | 3 | 1 | 1 | 2 | 1 |
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.
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
Creating a sidebar for a group of pages without messing about with stacks is an easy use-case for Universal Content Puller.
This sidebar is edited once, within the main addon page for Universal Content Puller.
It is then pulled into all UCP sub-pages using a UCP block.
The Content Source is Parent Page, set to pull the Sidebar area from 2 pages from the top. The Content Transform is Selector, set to remove container and row classes that, when unnecessarily nested, could mess up the Bootstrap grid. The Content Display is Plain, which just outputs the transformed text.
In the advanced settings, sanitization is disabled as we trust the source page and don't want to strip out any formatting or functionality from the pulled sidebar.