Fridge Poetry – Google Sheets as Database

Back in 2013 I built a refrigerator poetry page using javascript. I really wanted to make it so anyone could add any words they wanted in some easy way but didn’t have the skills to do that at the time. It’s been hanging out in the back of my mind since then and the bits and pieces I’ve learned since then now make it pretty straightforward.

This url will prompt you to copy a Google Sheet. That’s a nice little trick- just append /copy to the public sheet where it normally says /edit?usp=sharing if you copy the typical sharing URL. Sadly, it only works for Sheets.

Screenshot of directions in Google Sheet

Once we’ve published the sheet, we now have a JSON feed of the data and the little URL trick in the Google Sheets gives you the URL to the fridge poetry page with Sheet ID included as a parameter. (That’s the chunk after the question mark.)

The PHP to make all the words into divs is below. I usually do something like feed->entry->$t to parse out the JSON but that didn’t work with the Google Sheet data.1 I still don’t quite get things well enough to know why but this alternate path works well. Part of the difference there is using the TRUE parameter on the json_decode.

The other new element for me is $_GET. It’s simple but has some really interesting possibilities.

//gets the sheet id from the URL 
$gid = $_GET['id'];
//puts the ID into the URL
$gslink = '' . $gid . '/2/public/basic?alt=json';
$json = file_get_contents($gslink);
$data = json_decode($json, TRUE);
//makes the elements you need with the words
foreach ($data['feed']['entry'] as $item) {
  echo '<div class="draggable" id="word">' . $item['title']['$t'] . '</div>';

To make them draggable, I originally went an entirely different route but, like last time, I didn’t test it on mobile and then had to re-do things.2 In the end, I opted for this library to deal with it. As a result, the actual javascript is dead simple and it seems to work nicely across platforms.

There are some issues with printing out the results that Michael Price noticed for me. I’m debating either doing something with the CSS for printing or maybe trying to mess with the canvas element and capturing the image that way. I may also revive the 1970s avocado fridge background.

        $(function() {
          $( ".draggable" ).draggable();

Give it a shot if you’d like or make your own.

Screen Shot 2015-10-04 at 5.12.06 PM

This is a little thing but it’s one of those things that has lots of ways to expand. It’s the basic idea behind timeline.js and Martin Hawksey’s TAGs. Now I can start working on more sophisticated applications.

1 I think, in part, because of the use of the $t as the title. I wonder why that’s the way Google does it.

2 I rarely learn from my first mistake. I have a few scars, physical and emotional, to demonstrate that.

Trackbacks and Pingbacks on this post

  1. John's World Wide Wall Display said on October 8, 2015 at 11:58 am

    Never aggregate ephemeral sheep

    It is National Poetry Day. When I was in class I always wanted to do something for this, but only occasionally remembered. Although I don’t have a way with words I like working with poetry in the class. I also occasionally like twitter haiku and…

  2. Embed Collector Tool | Bionic Teaching said on October 8, 2015 at 8:47 pm

    […] pretty much a mashup of two recent projects – the personalized fridge poetry and the Angular/Google Sheets JSON […]

  3. Radioactive Collage Maker – Bionic Teaching said on February 9, 2016 at 10:05 pm

    […] seemed like a fun thing to do and within reach based on work I’d done earlier with Google Spreadsheet Fridge Poetry but I wanted a few more capabilities to help people create better visual products. I took my phone […]

  4. […] page” that would be dynamically populated depending on the SS ID passed in the URL (fridge poetry style). With that we could have done a bunch of other things like setting the banner, instructor contact […]