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.)

http://bionicteaching.com/fridgepoetry/google_words.php?id=1KExHjArU6ZAR2l2r00XYWHAAUen-Z6WOQGchHWmPs-4

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.

<?php
//gets the sheet id from the URL 
$gid = $_GET['id'];
//puts the ID into the URL
$gslink = 'https://spreadsheets.google.com/feeds/list/' . $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.

<script>
        $(function() {
          $( ".draggable" ).draggable();
        });
        </script>

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.

4 thoughts on “Fridge Poetry – Google Sheets as Database

Comments are closed.