Things have been pretty intense around here for mumble-mumble years, and for most of the last 3-4 years I’ve been doing more than just my own job while we cover for open positions and run searches and stuff. (There was a lovely 6 months in there while we were fully staffed, but that didn’t last.) But now that my department is fully staffed again I find that my brain isn’t braining very well. Even after a lovely long visit to my family, apparently my brain has more resting to do before it’ll kick back into gear after such a marathon.
While I can’t seem to grapple with programs and ideas and other “big” things, I’m irresistibly drawn to the mundane, the minutia, the system code, and yes, the spreadsheet. Goodbye forest; hello trees.
I find myself looking for things to do that involve spreadsheets or CSS — big things, tiny things, it doesn’t matter. That’s all I want to do these days. Can I make you a pretty button to go around your website link? Please? (Ironically, my own blog is still looking pretty 2012… maybe sometime I should turn my attention to modernizing things around here… and get an SSL certificate.)
Maybe I’m so drawn to these projects because unlike my normal work you can always see change and/or progress while working on CSS or a spreadsheet. Or maybe it’s just that I don’t do a ton with these kinds of things during the school year, so it’s just a chance to change up what I’m thinking about. Whatever it is, I’m basically useless for normal stuff right now, but I have a whole lot of patience and energy for spreadsheets.
If you like to be able to re-use assets easily, you have to be pretty careful not to develop dozens and dozens of nearly identical assets in the Libguides Asset database. Otherwise it’s basically impossible to know which one is THE one you want to reuse. Plus, if a website changes, and therefore the instructions you write into a description here or there changes, it pays to be able to update that kind of thing all in once in one place rather than go through every version of the asset and see if it needs to be updated individually.
Unfortunately, the Libguides system makes it very very easy for duplicate assets to multiply like rabbits. If you copy a box or start from another guide as a template, any asset in that box or guide that you don’t own will automatically duplicate itself. And sure, there are some good reasons to have that as an option, but you don’t have an option in this case — it just happens. Plus as people make guides for last-minute classes or in the middle of working on 16 other things, mistakes happen and people make new assets when they could probably have re-used one. Life happens.
Anyway, all of this means that every summer for the last several years I’ve gone through and done a database clean-up project. I figure out which assets are possibly duplicates of each other, and then I knit the actual duplicates back together into a single “parent” asset. And every summer this means that we go from about 7-8000 assets in our system down to about 6000 assets. And every Fall term we start out with a nice clean database, and sharing is super easy, and it’s a veritable asset utopia… for about 30 seconds. But imagine what it would be like without that reset? The messier the assets get, the harder it is to reuse, and so the messier the assets get.
A few people have expressed interest in replicating or building on what I do, so here are some documents to look over if you’re interested: Our Asset clean-up process, an example of this year’s working spreadsheet, and the rules we’ve made for ourselves in a large part to keep the assets as clean as possible throughout the year. (The Local Practices rules are linked from the main editor interface in Libguides so that they’re handy whenever people are editing.)
Exciting times here in our local libguides world! Our old look and feel was starting to feel extremely dated to us, not least because it was designed to coordinate with the library website as it looked 2 updates ago. So over the last 2 months we worked through design iterations (and, as always with big projects like this, I leveled up my css skills a bit), and this week our new look and feel went live!
Here’s how things looked for the last few years:
And here’s how things look now:
We’re still fiddling with minor things here and there. For example, I’m still not 100% sold on white text in the side navigation, and there seem to be an unending set of “exceptions to the rule” elements that we keep finding and tweaking. But it was great to go through an accessibility validation and have things come out looking good. And it’s also great to get a lot more design consistency with the college’s website (including a fully functional version of their ubiquitous “Blue Bar” at the top).
I also really enjoyed digging into responsive design a lot more than I have before, so the locally coded elements of our new interface are now fully compatible with all kinds of screen sizes.
As always, if you want me to provide my code, or if you notice something I should improve, please get in touch!
I’ve gotta admit. I love a good spreadsheet now and again. Maybe it’s because they aren’t the only thing I work with day in and day out, but I’m very much a fan of computers doing my grunt work for me, and spreadsheets are very much a part of that picture for me (right along with most of the other light-weight coding I’ve dabbled in).
I’m NOT a fan of using a spreadsheet just because it has columns and rows. Call me a software snob, but if all you need is a table in a document to keep a bunch of text in order, that’s not a spreadsheet. No sense using an extra tool. But if it can automate processes or do your error-checking for you — that my friends is workplace GOLD.
I’ve used spreadsheets to take conference session information as submitted by presenters and wrap each bit up in html, string the html together, and then paste the resulting html into a web page (hundreds of sessions processed in minimal time with minimal human error). I’ve used it to reconfigure 14 years of reference, instruction, and consultation statistics to make the data from various systems from our past match the needs of our new system, flipping people’s names, combining, splitting, reshuffling, and reformatting. The list goes on. If you can teach a computer a pattern, you can probably teach it to do your fiddly work for you.
Case in point: Libguides asset management.
Most of us librarians at this point use Libguides. One of its great strengths is that you can reuse “assets” (links, books, etc) from one guide to another. But over time, duplicate assets multiply like rabbits, and old unused assets clutter up your search results for that one asset you really want to reuse. So over time it becomes easier to make new assets rather than see if that asset already exists in the system. And then you end up in a vicious cycle the spirals your assets out of control and makes one of the great features of Libguides functionally useless.
So every summer I do a big ol’ asset clean-up project. I ask Springshare to delete our unused assets for me (we mere mortals can’t do bulk deletions), and then I work to knit back together all the unnecessarily duplicated assets that have spawned in the system when the librarians either make a new one that already exists or copy boxes or guides to new guides (which duplicates all the assets in the box or guide — asset management hell).
This is where the massive spreadsheet comes in. I need to find all the assets that are actually the same thing, and then map them back together so that they ARE the same thing. And the first part of this is to sort them by name. So I download a spreadsheet of all assets, plunk it into Google Sheets (easier to work on from multiple computers or share with others in the department), and alphabetize by title. But as you may know, neither Libguides nor any spreadsheet software I’m aware of is smart enough to alphabetize by the first “real” word, or to know that “US,” “USA,” “U.S.,” U. S.,” and “United States” are all the same thing. And anything with a quotation mark in front of it will go up into the non-alphabet part of the sort. And the list goes on. Alphabetizing just doesn’t cut it.
So in my google sheet, I add a column for Sort Title, and in the first cell of that column I use a formal to teach it all of the patterns that I know will be a problem with the titles in my asset list. Then I drag that formula down through all 7-9,000 asset records, and Ta-Da! Alphabetizable titles!
I’m a little nervous about sharing my formula for this because I’m a rank amateur and probably used a million IF statements where a simpler solution is possible. But hey, I’m also a rank amateur, so if you’ve never done this you can join me and then improve on what I’ve found! So… if you want to try this out, the basic pattern is “If at the left of the string in the title column you see x string, substitute x string with y string.” The other basic pattern is “If at the left of the string in the title column you see x string, delete it.” If your Title column is in column E, and you’re starting on row 2 (because row 2 is your header row). My current formula, which accounts for the patterns I’m currently seeing in my title list, looks like this:
So, it’s IF(logical statement, result if true, result if false), and in the “result if false” section, that’s where I put the next IF statement. The very last “result if false” is just “show me the full title from the title column” because by that time the title is probably just fine without alteration. Each logical statement here tests the characters at the left of the title column, since that’s what I’ll be alphabetizing on. Then each “result if true” section tells it how to transform those left-hand characters so that they’ll alphabetize properly.
So that’s my spreadsheet love story of the week! I can’t believe that last year I sat there and used successive “find/replace” searches and thought I was being efficient. Live and learn!