Flipping Data Tables for Organizing Submitted Resources

A key characteristic of the Creative Commons Certification project is a goal to, as much as possible, use openly licensed content and activities within the activities people will do to earn a certification.

There is, of course, much material out there to explain, demonstrate, and help people learn about copyright, fair use, the public domain, open licenses, attribution, remix as a cultural concept, tools to use, etc. Who knows where these all exist?

You do.

As part of the current design phase, we will be publishing soon the draft specifications for the Core, Library, Government, and Higher Education Certifications, as well as a call for comment, and for example materials that should be used. I shared some ideas, prototypes on what these mechanisms might be (feedback welcome, Bueller? Bueller?).

Here I want to focus on how to collect the suggested resources, in both a manner that is well-organized for our Production Team to use, but also, ideally, to itself become a sharable open resource.

Not surprisingly, the first thing we did as a team is set up a shared Google Doc. This works fine to collect information, but what it grows into is a jumbled bulleted list of URLS, some with titles, some with descriptions, maybe loosely organized under headings.

In it’s current form, the Core Certificate has 61 objectives, each which will have (hopefully) supporting open-licensed resources. Add the specialty certificate layers on top… and we are talking about a lot of resources to manage.

A shared document does work, but it ends up unwieldy AND the information is not well structured.

On top of that, we have our team members working on the specialty certificates, and starting to make their own docs, or maybe even spreadsheets. I foresee a Babel of resource information strewn across documents.

So this is my idea, and because it helps one think, I have built out some prototypes.

First of all, to simply the collection process, and to keep the kind of information collected parallel, I made a public Google Form to collect information on resources:

A google form to collect information on certification resources

These were my ideas for what we should ask for, some of course obvious:

• Title of resource
• URL
• Source / Publisher / Site name
• Type (media, e.g. video, book, article, or if it is an activity; this is one I can use some input on the list of types)
• License (flavor of CC, or other open license, or none at all. This is a case where selecting from a form element will make sure we have the license names across resources)
• Certification Specific (A place to indicate if the resource is general that it applies to a core, or perhaos it is specific to the Library, Education, or Government specialty)
• Module (if known, which of the 6 broad modules does it apply to, this may not be a critical item to ask)
• Open ended notes / comments

So one hand, I might say, “Geez, Alan, filling out a form is tedious.” I wince when I get requests for survey forms. But if you think about it, the steps to enter information into a document, or a spreadsheet are exactly the same; a back and forth series of copy/past round trips. And I might suggest, that clicking a radio button or a check box for some of the items is even easier.

But most importantly, the information is now structured, and internally consistent. I am not having to juggle if licenses for “CC-BY” “BY Attribution” “CC BY-3.0” entered in different documents are the same.

Now we get the data in a nice spreadsheet:

For these kind of input efforts, I prefer to leave this raw data sheet alone, editing just calls for possible mangling if the front form ever changes. So I create a second spreadsheet, and use formulas to copy the data. I use a forumlas that checks if there is data in the source sheet cell, and if so copies it, otherwise puts a null character.

Using a formula to populate my working spreadsheet

So the first cell I need is the title, I use this formula referencing the source data from sheet Form Responses 1 in cell BS — the logic says if there is nothing in Cell BS put in “” otherwise, put in the data that is the source cell:

=IF(ISBLANK('Form Responses 1'!B2), "", 'Form Responses 1'!B2)

This means I can use the fill down/across tool (just drag the bottom blue box corner) to prepare for 100s of rows of input.

I can also ignore than columns I made not need, like the timestamp, freeze the headers.

The other thing I do is add some columns are team might use as we review the resources, coloring the form data fields in green to make it clear what is input data:

I am thinking we might want to put our name in when we review something, to indicate someone looked at it, and then when it is added to a specific objective, list it there, and maybe add some other notes for the team.

Now we have structured data, hopefully hundreds, maybe more of possible pieces of data. But again, how is this useful when we have to look? You can use browser find tools, or sort the columns to say, group by Resource type, but still, it’s a whole bunch of table cells.

I thought of looking at the kinds of database functions one could add to a spreadsheet, maybe to query from yet another sheet.

But then I remembered this jQuery thing I helped Tom Woodward with a few years ago for a VCU site that is a directory of online courses (it looks like the site uses a different method, but it is a similar idea) — the data comes directly from their registration system as structured JSON, and some jQuery table stuff does some magic to make the data slice-able to view, all without doing repeated server hits for a search, it’s all done locally on the json data.

Columns cold be sorted, or searched, or selected with drop down menu filters, and the data viewed changes dynamically. The jQuery library we used is called DataTables and its advanced far from the version I used 2 years ago (though the old code works).

We were calling data from either a remote json via URL, or in my test site, a locally stored copy. The trick was, are there ways to request the data from a Google Spreadsheet? It took a bit of searching around the forums of the a href=”https://datatables.net/”>DataTables site, then I found a template in GitHub, but it used al older version of Databtables.

Finally I landed on Create a Searchable Table With a Google Spreadsheet in GitHub:

This repo creates bootstrap-styled searchable, sortable, responsive tables using the very cool Tabletop.js and the datatables jquery plugin.

It’s an update of Chris Essig and Chris Keller’s super useful datafeed to datatables repo. Keller’s template still works great, but this version uses the latest version of DataTables and removes a lot of deprecated code. The bootstrap implementation is also a little simpler and easier to read here.

Here is a plus for openly shared code, people in this case, keep making it better, passing it forward.

Tabletop.js (gives spreadsheets legs) is “code that makes it easy to use Google spreadsheet data in any kind of Javascript”. Sure you can read it, but I love the description so much, I want to mention it:

Tabletop.js takes a Google Spreadsheet and makes it easily accessible through JavaScript. With zero dependencies! If you’ve ever wanted to get JSON from a Google Spreadsheet without jumping through a thousand hoops, welcome home.

Tabletop.js easily integrates Google Spreadsheets with Backbone.js, Handlebars, and anything else that is hip and cool. It will also help you make new friends and play jazz piano.

I cannot wait for my new friends to compliment my jazz piano playing!

I got most of this going within an hour of downloading the template, because it’s all client side and uses remote libraries, it runs in GitHub pages. I have spent maybe 4 times as long trying to tinker with the interface.

But what we have now, is an interface for searching, filtering the resource collection (I have only 10 sample items in now), but you can play with it now.

You need to make the data sheet public, and get it’s HTML URL. In the graphic.js file, you can list the columns you want to use from the sheet (you do not need them all).

The URL field in the spreadsheet was long; my proud moment today was figuring out how to hide that column, and turn the title column into a hyperlink, adding the ColunDefs part to the code provided:

    $("#mySelection").DataTable({
      "autoWidth": false,
      "data": data,
      "columns": columns,
      "order": [
        [0, "asc"]
      ], //order on second column
      "pagingType": "simple" //no page numbers
        //uncomment these options to simplify your table
        //"paging": false,
        //"searching": false,
        //"info": false
        ,
        "columnDefs": [      
       		 {
                // add url to column 0 as link with url in column 1
                "render": function ( data, type, row ) {
                    return '' + data + '';
                },
                "targets": 0
            },
        	{ "visible": false,  "targets": [ 1 ] }
        ]
    });

When I type fair into the search field, the table shrinks to matches:

One could also use the sort options on the headings to re-arrange.

This is very much a work in progress. I would like to figure out, as I did before, how to add a search filter to a column, or even a drop down menu, to show, say all resources with a certain license, or all ones associated with the Library Certificate, or all ones assigned to objective 2.3

But also, this can be used, even in another format with fewer columns, as a public accessible directory of the open licensed resources that people share with us.

Is this sensible? Can the form be simplified at all when we go public? There is a neat feature, where if we had a link from the draft Library Certification, the link could be one that pre-selects that items or another link from Module 2 that would preselect that item.

And if you have some resources to suggest, give the form a try!

Featured Image Credit: Creative Commons Licensed (CC-BY) DeviantArt image by firstcultural.

 Share this barking on social media

If this kind of stuff has value, please support me by tossing a one time PayPal kibble or monthly on Patreon