flickr photo shared by Internet Archive Book Images with no copyright restriction (Flickr Commons)
This is a modification of the old refrigerator poetry concept based on a request from some of our World Languages professors but it’ll likely have some broader applicability.
It essentially allows for three major things.
- You can create draggable elements (words, phrases, any HTML) from a Google spreadsheet
- You can create destination areas for those elements (also through the Google ss)
- You can make it so that elements that don’t match those destinations won’t “stick” there (spreadsheet again)
It might be easier to see what’s up through the short video below.
This was done mostly in jQuery but there are also a few Google Script elements that make life easier. The page below has everything except the CSS. It’s decently commented I think. The only real trouble I had was figuring out where/when to feed in the draggable/droppable elements. It kicked through after a bit of experimentation but I have a long way to go with javascript.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>polygot</title>
<meta name="generator" content="SUBLIME" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="style.css">
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script type="text/javascript">
var docId = getQueryVariable("docId");
console.log(docId);
var url = "https://spreadsheets.google.com/feeds/list/"+ docId +"/1/public/values?alt=json";
$.getJSON(url, function(data) {
var html = '';
var entry = data.feed.entry;
var upperLimit = entry.length;
var categoryDest =[]; //hold destination array
//get words
var i = 0;
while (i < upperLimit) {
var sticky = "";
if (entry[i]['gsx$categories']['$t']!=""){
categoryDest.push(entry[i]['gsx$categories']['$t']);
}
// add class element to 'sticky' drag items
if (entry[i]['gsx$sticky']['$t']!="") {
sticky = entry[i]['gsx$sticky']['$t'];
html = '<div class="drag item ui-widget-content '+ sticky +'" id="item'+ i +'">' + entry[i]['gsx$wordsphraseshtml']['$t'] + '</div>';
$('.words').append(html);
$( function() {
$( "#item"+i ).draggable( {revert: "invalid",} );
}
);
i++;
}
//regular drag items
else {
html = '<div class="drag item ui-widget-content '+ sticky +'" id="item'+ i +'">' + entry[i]['gsx$wordsphraseshtml']['$t'] + '</div>';
$('.words').append(html);
$( function() {
$( "#item"+i ).draggable();
} );
i++;
}
}
//make drop zones if designated
var dropZones = unique(categoryDest);
var zoneLength = dropZones.length;
var z = 0;
while (z < zoneLength){
drop = '<div class="drop ' + dropZones[z] + ' col-md-' + zoneSize(zoneLength) + ' col-sm-12 well well-lg" id="' + dropZones[z] + '"><h2>'+ dropZones[z] +'</h2></div>'
$('.destination').append(drop);
if (sticky !=""){
$('#'+dropZones[z]).droppable({ accept: '.'+dropZones[z]}); //if sticky is set apply to destinations
}
z++;
}
});
//function to get unique from array
function unique(array){
return array.filter(function(el, index, arr) {
return index === arr.indexOf(el);
});
}
function zoneSize(number){
if (number<4){
return 12/number
} else {
return 4
}
}
//gets variable from URL
function getQueryVariable(variable)
{
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var i=0;i<vars.length;i++) {
var pair = vars[i].split("=");
if(pair[0] == variable){return pair[1];}
}
return(false);
}
</script>
</head>
<body >
<div class="container-fluid">
<div class="row bar">
<div class="col-md-12 col-sm-12">
<h2>Playing with Words</h2>
</div>
</div>
<div class="row">
<div class="words col-md-12 col-sm-12"></div>
</div>
<div class="row destination">
</div>
</body>
</html>
Google Script
There’s a directions sheet in the spreadsheet where I wrote a custom function.
function getId(input) {
var ss = SpreadsheetApp.getActive();
var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
var destinationUrl = 'https://rampages.us/extras/world-lang/?docId='+ssId;
return destinationUrl;
}
That allows me to write =getId() in the cell and have it generate the right URL for whatever spreadsheet it is in.
You can also automatically copy my spreadsheet by going to the following URL.
https://docs.google.com/spreadsheets/d/1bIZxuiM5z00lf7MhIoh05ppYz1rO9kBxktLd7nJIG1I/copy
Further Work
I’ll likely add the option to drop some common background images in there- a venn diagram for instance, stuff like that. I’m also considering narrowing the header, adding an option to capture the results as an image, and looking at better ways to deal with larger numbers of words.
Hi Tom,
Just wanted to say that I’m really glad I found this site. I am ESL teacher in England, and I’m been thinking, for some time, of creating a platform for use in class. The code above is great and has given me a lot of ideas. Is your code open sourced? Would you mind if I used it as a starting point?
What led me here was a google search for “magnetic poetry google sheets”.
Best wishes
Matt
Awesome. It’s CC so go for it. I’d love to see what you end up doing with it.
Hi Tom,
You have some great tips and tools on this site – thanks for sharing! I am brand new to coding and would love to get some insight. Is there an email address at which I can run a few quick questions by you?
Sure. Email sent.