Querying Evergreen from Google Sheets with custom functions via Apps Script

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.