Summer Reading Program Form: SO SIMPLE

Last time I posted I was still months away from having a baby, now I have a four month old. Time to get back into blogging. I have some things specifically about babies and being a working mother and so on, but those will have to wait.

A few weeks ago I asked around on Twitter about creating leaderboards, but couldn’t find a lot out there that worked for exactly what I needed. I was trying to make a simple application for a staff summer reading program that would allow participants to register the pages they’ve read and leave reviews, but without a lot of bureaucracy and overhead. The main tool I found was Leaderboarded, which was interesting, but way too much for the very simple thing I was trying to do. While I could have created a form on our website using Drupal or one of our PHP forms, I figured that the much easier solution would be to use a Google Form, since otherwise it would have been very difficult for the people running the program to monitor participation unless I built an entire web application, which was overkill for a temporary program.

After poking around a bit, I found this super simple solution from David Hay at the Elk Island Public Schools (in Alberta!) Technology blog. Create a form in Google Forms that asks for a name, and then any points to which the student is entitled. Then use pivot tables with the spreadsheet to get totals, and use charts to visualize these. This was very simple, and I also appreciated his script that copies formulas to the last row of the spreadsheet as people add new entries. This of course assumes you have a unique key of a person’s name added the same way each time, which for a small group of people for a short amount of time is probably a reasonable assumption.

Here’s what I ended up with. You won’t be able to complete the form without Loyola email address (to hopefully prevent spam), but here’s what happens. Most of the time people will just be entering the pages they read last, and won’t need to add anything else. They will fill in that information, and then submit the form. But if they’ve finished a book, they can leave a review for more points. Clicking the “Did you finish a book?” radio button will take them to page 2, which is below. They can fill in author and title only, or add a review for 50 points. They also get an additional 10 points for checking the book out of the LUC libraries.



On the back end, this form records to a spreadsheet. The points are added up in an additional column I added to the spreadsheet. It adds in additional points for the challenges to the pages read using conditionals, which I’d not really used in Google Forms before. They work just as you would expect.

=C2 [pages read]+(if(J2[LUC libraries]="Yes",10[condition if true],0[condition if false])+if(isblank(I2)[review box],0[condition if true],50[condition if false))

Then I use a series of pivot tables and charts to add up the total points for each person (which we will use internally for entering participants in a raffle and determining the overall winner) and departmental points, which we will use to cheer on departments and try to make them compete against each other. To check for the individuals who are ahead, I use a pivot table with a row grouped by name with the sum of total points, and the same thing for departments. Then I made a bar graph sorted by total to make a departmental leaderboard to post on the website, which you can see here (as of publication it’s blank since the program hasn’t started yet).

Since everything is stored in an online form, the group who is running this program will be able to make edits to the form and the data if they need to, and copy the reviews out of the spreadsheet to post on the library’s blog. Very simple, and not using a database when a spreadsheet will do!


.@FakeLibStats : 65% of librarians use a spreadsheet for what can be better and more easily accomplished by a database.

— Roy Tennant (@rtennant) May 8, 2014


Filed under: coding,Libraries

Comments Off

Posted by Margaret on May 21, 2014 @ 11:45 am

Review of Using OpenRefine by Ruben Verborgh and Max De Wilde

Using OpenrefineUsing Openrefine by Ruben Verborgh

My rating: 4 of 5 stars

Disclosure: the publisher of this book provided me with a free copy in exchange for a review. The opinions expressed in the review are my own.

While OpenRefine is an extremely useful “power tool for messy data”, its power can be difficult to master without a great deal of trial and error on the part of the user. Part of this stems from the evolving nature of the tool. It began life as Freebase Gridworks, with the purpose of cleaning up data in order to run it against linked data in Freebase. When the Freebase parent organization was acquired by Google, they rebranded the tool as Google Refine, but as Google’s priorities shifted, they stopped working on the tool and it became the open source OpenRefine. This legacy means that the tool has many pieces created by different people for different purposes. While there is quite a lot of good documentation out there on the OpenRefine site and elsewhere, this book puts it together in a easy to follow format. Like a lot of OpenRefine documentation, it is a series of “recipes” that explain how to do one specific task, but is written with the cover to cover reader in mind as well. The Google produced tutorial videos have similar coverage, but the book is more in depth, and has the advantage for readers coming from the cultural institution side of using a museum data set for examples. Another advantage is that the authors of the book have a particular interest in named entity recognition (part of the book covers the tool that one of them produced), which is particularly helpful for more abstract data sets with cultural data.

Using OpenRefine is useful for beginner or intermediate users of OpenRefine. As someone who has used OpenRefine for awhile and written about its use in libraries, this was more helpful than I expected initially, since there were pieces of functionality I’d not yet encountered in experimentation or documentation so far. My one criticism is that much of the book promises a complete explanation in the appendix of regular expressions and the Google Refine Expression Language that powers the software, but I found that the GREL documentation was less useful than I hoped, though I still learned from it. I would have preferred if that section had been earlier in the book. That aside, I would recommend this book to anyone who has been using OpenRefine or thinking about using it, and additionally for library and museum professional development collections.

View all my reviews

Filed under: technology,What I've been reading lately

Comments Off

Posted by Margaret on November 19, 2013 @ 12:23 pm

Suzanne Briet: Antelopes can be documents

In honor of Ada Lovelace Day, #LibTechWomen is blogging about one of the great early library and information science theorists, Suzanne Briet. You can read all our blog posts on Twitter using the hash tag #briet. In a shocking but not surprising turn of events, a recent textbook gave Paul Otlet credit for the one thing that everyone should be able to remember from the first semester of library school: a wild antelope is not a document, but an antelope in a zoo is, since it was collected, cataloged, and provides evidence. Unfortunately textbooks have a way of perpetuating wrong information from generation to generation, and critical thinking and research skills are woefully lacking. So, we are remembering Madame Documentation on a day dedicated to remembering and celebrating women in STEM fields.

My personal forever favorite tribute to Suzanne Briet comes in the form of a critical puppet show put on by the Self Preservation Working Group at the Read/Write Library. Watch and enjoy.

Filed under: Libraries

Comments Off

Posted by Margaret on October 15, 2013 @ 10:47 am

Next Page »