Coffee|Code : Dan Scott

Our staff were recently asked to check thousands of ISBNs to find out if we already have the corresponding books in our catalogue. They in turn asked me if I could run a script that would check it for them. It makes me happy to work with people who believe in better living through automation (and saving their time to focus on tasks that only humans can really achieve).

Rather than taking the approach that I normally would, which would be to just load the ISBNs into a table in our Evergreen database and then run some queries to take care of the task as a one-off, I opted to try for an approach that would enable others to run these sort of adhoc reports themselves. As with most libraries, I suspect, we work with spreadsheets a lot--and as our university has adopted Google Apps for Education, we are slowly using Google Sheets more to enable collaboration. So I was interested in figuring out how to build a custom function that would look for the ISBN and then return a simple "Yes" or "No" value according to what it finds.

Evergreen has a robust SRU interface, which makes it easy to run complex queries and get predictable output back, and it normalizes ISBNs in the index so that a search for an 10-digit ISBN will return results for the corresponding 13-digit ISBN. That made figuring out the lookup part of the job easy; after that, I just needed to figure out how to create a custom function in Google Sheets.

As it turns out, there's a dead-simple introductory tutorial for creating a custom function in Apps Script which tells you how to create a new function. And to make a call to a web service, there's the URLFetchApp class. After that, it's a matter of basic JavaScript. In the end, my custom function looks like the following:

/**
* A custom function that checks for an ISBN in Evergreen
*
* Returns "Yes" if there is a match, or "No" if there is no match
*/
function checkForISBN(isbn) {
  var hostname = 'https://example.org';
  var urlBase = hostname + '/opac/extras/sru';

  /* Supply a numeric or shortname library identifier 
   * to restrict the search to that part of the organization
   */
  var libraryID = '103';
  if (libraryID) {
    urlBase += '/' + libraryID;
  }
  urlBase += '?version=1.1&operation=searchRetrieve&maximumRecords=1&query=';
  
  var q = encodeURIComponent('identifier|isbn:' + isbn);
  var url = urlBase + q;
  var response = UrlFetchApp.fetch(url);
  if (response.getContentText().search('1') > -1) {
    return "Yes";
  }
  return "No";
}

Then I just add a column beside the column with ISBN values and invoke the function as (for example) =CheckForISBN(C2).

Given a bit more time, it would be easy to tweak the function to make it more robust, offer variant search types, and contribute it as a module to the Chrome Web Store "Sheet Add-ons" section, but for now I thought you might be interested in it.

Caveats: With thousands of ISBNs to check, occasionally you'll get an HTTP response error ("#ERROR") in the column. You can just paste the formula back in again and it will resubmit the query. The sheet also seems to resubmit the request on a periodic basis, so some of your "Yes" or "No" values might change to "#ERROR" as a result.

TLDR: Set the HDMI output resolution on your PVR to 1080i instead of 1080p, and the component outputs on your FibreOp PVR will start working again, and therefore your Slingbox will start working again. I purchased a Slingbox 350 a few years ago for the rare time I want to watch something on TV when I'm on the road. For the most part, it's worked well--although the very tiny bit of TV I watch hasn't really justified the cost. Recently, however, it stopped working, for no apparent reason. After a few hours of searching for similar problems and monkeying around with the firewall on our ActionTec router (which, BTW, has not had a firmware update since it was installed two and a half years ago: how about that mess of security vulnerabilities) to open up a port that was never required before, based on advice from about 4 years ago, I was no further ahead. Then I remembered that our PVR received an update just before Christmas that introduced a brand new UI and features like Santa Tracker. "Hmm", thought I, "could this be related to the problem?" I dug into posts about the update and discovered someone in the middle of a thread about a different feature complaining about their component output ports no longer working after the update, which lead to this thread pointing out that only the video on demand content was available in 1080p anyway. (Warning: those forums have obnoxious autoplay video ads!) "But wait", I thought to myself, "I've never changed that setting, why would it be set to 1080p now?" As far as I can tell, it was purely a result of the update. Changing the output resolution setting back to 1080i enabled the component outputs to start pumping out data again and gave the Slingbox something to sling. I wouldn't mind getting those troubleshooting hours of my life--more than I spend most weeks actually watching TV--back, even though figuring out the solution did eventually provide some satisfaction. For the most part, I have only myself to blame: I've put myself in a situation where I'm relying on a ton of proprietary software and hardware (Slingbox, ActionTec router, Arris PVR) over which I have very little control, and no ability to dig into the internals to see what has changed. Hopefully this helps someone jump directly to the solution.

OCLC recently asked EZProxy clients to fill out a survey about their experiences with the product and to get feedback on possible future plans for the product.

About half-way through, I decided it might be a good idea to post my responses. Because hey, if I'm working to help them, I might as well share it with my friends out in the library systems world So here are a few choice quotes from the comments section of the survey...

In response to a question about the "ease of use" of EZProxy

Nothing that requires configuration via a text file can be classed as "easy to use" these days.

When asked why I scored satisfaction lower than the maximum

The sluggishness to adopt current encryption protocols, and the unwillingness to use dynamically linked libraries, is a major black mark against the product.

Rather than "doing the right thing", it appears to be a marketing tactic to encourage adoption of 6.0 by making 5.7 fall behind. That's a disappointing tactic for a library cooperative to use in the world of libraries where security and protection of our patrons privacy is of great importance.

What one thing would I change about EZProxy

I would change the license to be an open source (GPL v3 or Apache 2.0) licence. OCLC could still derive revenue from providing hosted solutions and as the well-known trusted name being the product, but small segments of the community could vet the code and contribute enhancements that meet their needs (that they have been asking for without success for years now).

After being asked about the importance of five possible enhancements, three of which reflected a tighter integration with WorldShare services

(roughly) This is why I don't like the proprietary revenue model for the 6.x series--you're investing the revenue in shoring up your WorldShare offerings with features that are not useful to the customers that do not use the WorldShare platform.