share      tweet      post

Google is a useful place for working on collaborative documents, in an environment that's easy to control access to. These include spreadsheets, documents, slides, fusion tables etc. And also the lesser known 'Apps Scripts'.

The Google Apps Script language is primarily JavaScript with a number of additions to simplify common scripting tasks, and integrate with other Google services.

For example, if you have a Google Analytics account it supports a set of script methods to allow you to easily query your Analytics data.

Analytics already provided API (Application Programming Interface) access, but this makes accessing data from Google services a lot easier, and removes authentication as a task. If you write an apps script and host it on your Google account then it can be given automatic access to your Analytics data. Similarly, it will provide easy read/write access to documents and spreadsheets that are in the same account. So you could query your Analytics account and update a Google Sheet with the data (if you wanted to).

When working on scripts, it also offers a decent development environment, allowing you to debug them (though debugging is noticeably slow).

Scripts are able to send out emails, at a limit of 100 per day, and can be set to run as part of a schedule. That also makes it useful as a notifications and alerts system, where otherwise any automation scripts would need to be hosted on a server.

As an example, the below script could be set to run as an automated task every day to check a library account, and send you an email if a loan is due for return within a certain time frame (e.g. within 5 days), and also to renew it if it is just about to go overdue. It uses the following Google script additions:

The script is only compatible with Axiell Arena web services. It effectively does the same thing as if you were using the library app and renewing a loan manually. The following UK public library authorities are supported.

Service URL Library ID
Hounslow 219001
Wiltshire 400001

Instructions to set up your own version are as follows:

  1. In your Google drive account select New > More > Google Apps Script
  2. In the script body paste all of the below script, overwriting anything that may be there already.
  3. You will need to replace the placeholder details: your member ID, PIN, and your email address.
  4. Change the Library ID, and Library URL to be the ones for your library service (see above).
  5. You then have the options on when to react to loan due dates. The number of days remaining on a loan at which it will automatically renew it, and the number of days remaining at which you'll simply receive an email reminder.
  6. You can then set schedules for it to run using . Probably the most sensible would be sometime in the evening each day.
function CheckLoans() {
    // Customise these variable to match your details, library service, and preferences.
    var memberId = '12345678';
    var PIN = '1234';
    var emailAddress = 'email@email@com';
    var libraryId = '400001';
    var libraryUrl = '';
    var daysToRenew = 1;
    var daysToSendEmail = 5;

    // setting up some data for the script
    // includes XML to post to the web service
    var d = new Date();
    var renewals = '';
    var today = new Date();
    var checkLoanPayload = '<soapenv:Envelope xmlns:soapenv="" xmlns:loan="" xmlns:loan1="" xmlns:util=""><soapenv:Header/><soapenv:Body><loan:GetLoans><loan1:loansRequest><util:arenaMember>' + libraryId + '</util:arenaMember><util:user>' + memberId + '</util:user><util:password>' + PIN + '</util:password><util:language>en</util:language></loan1:loansRequest></loan:GetLoans></soapenv:Body></soapenv:Envelope>';
    var renewPayload = '<soapenv:Envelope xmlns:soapenv="" xmlns:loan="" xmlns:ren="" xmlns:util="" xmlns:loan1=""><soapenv:Header/><soapenv:Body><loan:RenewLoans><ren:renewLoansRequest><util:arenaMember>' + libraryId + '</util:arenaMember><util:user>' + memberId + '</util:user><util:password>' + PIN + '</util:password><util:language>en</util:language><ren:loans>[[renewals]]</ren:loans></ren:renewLoansRequest></loan:RenewLoans></soapenv:Body></soapenv:Envelope>';
    var loansOptions = { 'method': 'POST', 'content-type': 'application/xml; charset=utf-8', 'payload': checkLoanPayload };
    var renewOptions = { 'method': 'POST', 'content-type': 'application/xml; charset=utf-8', 'payload': renewPayload };

    // Start: Get Loans data
    var getLoans = UrlFetchApp.fetch(libraryUrl, loansOptions);
    var responseText = getLoans.getContentText();

    // Extract a list of loans from the XML returned
    var docRoot = XmlService.parse(responseText).getRootElement();
    var ns = docRoot.getNamespace();
    var loansRequest = docRoot.getChildren('Body', ns)[0].getChildren()[0].getChildren()[0];
    ns = loansRequest.getNamespace();
    var loans = loansRequest.getChild('loans', ns);
    var loanItems = loans.getChildren();

    var emailText = 'Hello,\n';
    var sendEmail = false;
    var renew = false;

    // Loop through each loan and construct the email body (if necessary)
    for (var x in loanItems) {
        var loan = loanItems[x];
        ns = loan.getNamespace();
        var renewalDate = loan.getChild('loanDueDate', ns).getText().replace('+', 'T') + ':00.000Z';
        renewalDate = new Date(renewalDate);

        var reservedDate = new Date(loan.getChild('loanDate', ns).getText());
        var branch = loan.getChild('branch', ns).getText();

        var catalogueRecord = loan.getChildren()[1];
        ns = catalogueRecord.getNamespace();

        var title = catalogueRecord.getChild('title', ns).getText();
        var id = catalogueRecord.getChild('id', ns).getText();
        var author = catalogueRecord.getChild('author', ns).getText();
        var oneDay = 1000 * 60 * 60 * 24;
        var dateDifference = Math.ceil((renewalDate.getTime() - today.getTime()) / (oneDay));

        if (dateDifference <= daysToSendEmail) {
            sendEmail = true;
            if (dateDifference <= daysToRenew) {
                // it's so late we need to renew, but say this in the email.
                renew = true;
                emailText += 'The following item has been renewed: ' + title + ', ' + author + ' .  This was checked out on: ' + reservedDate + '.  Please remember to finish and return soon.\n';
                renewals += '<loan1:id>' + id + '</loan1:id>'
            else {
                // less than five days to go, will send an email each day.
                emailText += 'Your loan of ' + title + ', ' + author + ' , checked out on ' + reservedDate + ', is due back on: ' + renewalDate + '.\n';

    // renew whatever items are due to renew
    if (renew) {
        renewPayload = renewPayload.replace('[[renewals]]', renewals);
        UrlFetchApp.fetch(libraryUrl, renewOptions);

    // send out the email
    if (sendEmail) {
        MailApp.sendEmail(emailAddress, 'Library Notification Report', emailText);

comments powered by Disqus