Skip to content

Category: Tools and Technology

Farewell, dear LIShost

Well, let’s try this again. DNS confusion meant that last Friday, when I thought I was all set, I actually wasn’t. But apparently NOW I’m all set, so here’s the post that I thought would get posted last week. (Many thanks to Reclaim Host tech support for all their extremely helpful help.)


I’m writing now from my new home on a new server and a new domain registrar for the first time in my blog’s history. Up until now I’ve been happily living in my own little corner of the LIShost server, carefully tended by the wonderful Blake Carver. Sadly, LIShost is shutting down after 18 years of dedicated service to library folks. I can’t thank Blake enough for his approach to hosting, giving me as much leeway as I had the skills for, and as much help as I needed to cover those areas where my skills were lacking.

But welcome, now, to my new home, here in my own corner of the Reclaim Hosting servers. If things continue as they have begun, I think I’ll be quite happy here in my new digs. But if you notice anything wonky do let me know. I’m still unpacking and figuring out the light switches and hanging pictures, and there’s no guarantee I’ll notice everything.

Leave a Comment

Preparing for your database lists to malfunction on occasion

If you’re like us, you have a LibGuides-based list of databases for your library, and it went down for a bit this morning. If you’re like me, this pretty much cripples you until the list is back up and running. This kind of downtime could happen no matter your platform, so if you’re like me you might also want to have an option available to get around your database list during downtime. Here’s what I came up with about a year ago and was happy to have in place this morning.

Each month I download a CSV file of our databases – LibGuides has a nifty “export all” option and I click that. Then I paste that data dump information into a hidden sheet on a Google Sheet that I’ve made available to anyone with a link. From that messy data dump, I built formulae onto a visible sheet in that Sheet (really, Google has GOT to get better about its names for apps so that I don’t have refer to a sheet on a Sheet). These formulae help me display a full alphabetical list of all of our databases, their descriptions, their base URLs (a note on that in a second), vendor, and whether or not they need proxy access from off campus. Basically all the formulae say are “if the corresponding cell on that base sheet is blank, don’t put anything here, but if there’s information there, then display it here.” It looks like this:

=iF(isblank(Raw!B2),"",Raw!B2)

Then I use a slightly fancier formula to build a proxified version of that base URL into column dedicated to off-campus access. It goes like this:

=if(isblank(F2),"",if(F2="yes",CONCATENATE("http://ezproxy.carleton.edu/login?url=",C2),C2))

Translated, that formula means “If the cell in Column F that says whether this database needs a proxy string is blank, leave this cell blank (this just makes for a cleaner spreadsheet without a lot of error cells where the formula is there even if there’s no database listed). If that proxy cell is set to YES, then put together our proxy string and base URL, and put the resulting URL here. If the proxy cell is set NO, just put the base URL here.”

Then I hid the Proxy check column (column F) because nobody really needs to see that if they’re using the spreadsheet. I just needed it for calculation purposes. (Sure I could have referred to that proxy check cell on the base sheet, rather than bring it to the visible sheet and then hide it, but sometimes I just feel like doing things easiest way that occurs to me in the moment. Don’t judge!)

Finally, I gave this back-up spreadsheet a nicer URL: http://bit.ly/libedatabases And I posted this URL in places where librarians can find it when needed (such as our documentation for QuestionPoint cooperative librarians, our intranet, etc).

So now if our proxy server goes down (rendering our database list mostly useless), we can use the base URLs from this spreadsheet, at least from on campus or in combination with a VPN. It’s better than nothing. And if the whole database list goes down, we have access to all of our databases and their URLs from this list.


Edited to add: I forgot to mention that I’ve also built a script into Google Docs to unmerge cells. For whatever reason, exports from web-based products like Springshare tend to have random merged cells, which I don’t want. The only way I know of to get rid of these (other than looking for them all and then unmerging them individually) is via script.

So! In Google Sheets, click on the “Tools” menu and then “Script Editor” and then paste in the following:

function myFunction() {
   var breakRange = SpreadsheetApp.getActive().getRange('A:T');
 for(;;) {
   try {
     breakRange.breakApart();
     break;
   } catch(e) {
     breakRange = mySheet.getRange(
       breakRange.getRowIndex(),
       breakRange.getColumnIndex(),
       Math.min(
         breakRange.getHeight()+5,
         Sheet1.getMaxRows()-breakRange.getRowIndex()+1
       ),
       Math.min(
         breakRange.getWidth()+5,
         Sheet1.getMaxColumns()-breakRange.getColumnIndex()+1
       )
     );
   }
 }
 }

This will look at columns A through T (you can edit that in that second line if you need to) and unmerge any merged cells.

When you paste in a new export, just click “Tools” and “Script editor” and then the little “play” arrow to run the script, and it’ll unmerge all those merged cells for you.

Leave a Comment

Our Libguides Makeover!

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!

Leave a Comment

Spreadsheets: my not-so-secret love affair

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

Screenshot of spreadsheet showing examples of normalized titles for alphabetization.

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:

=IF(LEFT(E2,2)="A ",RIGHT(E2,len(E2)-2),IF(LEFT(E2,3)="An ",RIGHT(E2,len(E2)-3),IF(LEFT(E2,4)="The ",RIGHT(E2,len(E2)-4),IF(LEFT(E2,6)=""",SUBSTITUTE(E2,""",""),IF(LEFT(E2,5)="U.S. ",SUBSTITUTE(E2,"U.S. ","US "),IF(LEFT(E2,14)="United States ",SUBSTITUTE(E2,"United States ","US "),IF(LEFT(E2,4)="USA ",SUBSTITUTE(E2,"USA ","US "),IF(LEFT(E2,6)="U. S. ",SUBSTITUTE(E2,"U. S. ","US "),IF(LEFT(E2,11)="University ", SUBSTITUTE(E2,"University ","U "),IF(LEFT(E2, 5)="U.K. ",SUBSTITUTE(E2,"U.K. ","UK "),IF(LEFT(E2, 5)="U.N. ",SUBSTITUTE(E2,"U.N. ","UN "),IF(LEFT(E2,15)="United Nations ",SUBSTITUTE(E2,"United Nations ","UN "),IF(LEFT(E2, 15)="United Kingdom ",SUBSTITUTE(E2,"United Kingdom ","UK "),E2)))))))))))))))))))))))

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!

Leave a Comment