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.