蔵書管理を支える技術

これは Spreadsheets/Excel Advent Calendar 2020 4日目の記事です。
昨日は id:yunico_jp さんのスプレッドシートを使った超単純な進捗管理、過去最高に捗る説 - Yunicode でした。

こんにちは、 id:masawada です。突然ですが、みなさんが所属されている組織ではどのように蔵書を管理していますか?

組織において書籍や検証機など何らかの物品を貸し出すケースはごく日常的なものだと思います。貸し出す物品の数や人数が少なければホワイトボードなどに書いて管理することもできそうですが、膨大になってくるとそれでは立ち行かないこともあるでしょう。わざわざ物理的なカンバンを見に行かずにどこにどの物品があるのかを検索したいというニーズもありそうです。

この記事では、このような問題を解決するために自分が作った蔵書管理用のスプレッドシートをご紹介します。実際に自分が所属している会社ではこのスプレッドシートを利用して蔵書を管理しています。

https://docs.google.com/spreadsheets/d/1XshcYZmNPpKHAJjFEY-KdFJkYzjwj3lHtreRqfEvxXk/edit?usp=sharing

ファイル > コピーを作成 から自分のGoogle Driveにコピーを作成してご利用ください。

スプレッドシートは

貸出記録
蔵書一覧
所蔵場所
蔵書分類

の4つに分かれています。具体的にどこでどのようなテクニックを使っているのかを交えながら紹介します。

貸出記録

貸出記録シートには、以下のような機能があります

書籍IDを記入すると書名を自動でフィルインする
貸出日を記入すると返却日(貸出日の2週間後)を自動でフィルインする

f:id:masawada:20201204001639p:plain

書籍IDは、蔵書一覧シート(後述)において書籍ひとつひとつに対して割り振るIDです。蔵書一覧シートのA列が蔵書IDになっています。例えば貸出記録のC2セルは

=IF(ISBLANK(B2),,VLOOKUP(B2, '蔵書一覧'!$A$2:B, 2))

のようになっており、 B2(B列の同じ行)に何か値があったら、蔵書一覧の$A$2:Bの範囲からB2の値に合致するものを探し出してきて、その行の2番目のセルを表示 しています。

この例ではB2は 1 なので、蔵書一覧の $A$2:B から 1 に合致するものを探してきてその行の2番目のセルを表示します。

f:id:masawada:20201204002623p:plain

蔵書一覧の $A$2:B はこの範囲なので、書籍IDが1にマッチする 転生したらスプレッドシートだった件 が表示されます。

返却日のフィルインは

=IF(ISBLANK(E2),,E2+14)

のようになっており、貸出日(ここではE2)が入力されていたら E2+14 を返すとしています。日付の値が入っている場合は、単純に +14 とするだけで2週間後の値を計算できます。

蔵書一覧

蔵書一覧シートには、以下のような機能があります

貸出中かどうかを判定する機能
所蔵場所の選択肢を所蔵場所シートから引っ張ってくる機能
分類の選択肢を蔵書分類シートから引っ張ってくる機能

f:id:masawada:20201204003708p:plain

貸出中かどうかを判定する機能は、以下のように実現しています。

=IF(ISBLANK(B2), , IF(COUNTBLANK(QUERY('貸出記録'!$A$2:G, "select G where B = " & A2, -1)) > 0, "貸出中", "貸出可能"))

なかなか複雑ですね。分解してみましょう。まず一番外の IF は、蔵書名がなかった場合は何も表示しないための条件分岐です。この外側のIFがないと以下のようになります。

f:id:masawada:20201204003844p:plain

書籍がなければ貸し出し可能もなにもないのでこのIF文でガードしています。

次に COUNTBLANK(QUERY('貸出記録'!$A$2:G, "select G where B = " & A2, -1)) > 0 の部分を見てみましょう。まず QUERY で貸出記録シートの $A$2:G の範囲から "select G where B = " & A2 しています。& は単なる文字列結合で、A2はここでは 1 なので select G where B = 1 と等価です。貸出記録シートにおいてG列は返却日、B列は書籍IDなので、 書籍IDが1の行の返却日 を検索しています。これを包んでいるのが COUNTBLANK で、これは文字通り空セルを数えるものです。QUERYで引いた返却日リストに空セルがある場合、つまり貸出記録で書籍IDが埋まっているが返却日が記入されていない場合はそのセルの数を計算できます。これが1以上であれば貸出中という寸法です。

最後にIFで 貸出中 を表示するか 貸出可能 を表示するかを判定しています。

所蔵場所や分類の選択肢をそれぞれのシートから引っ張ってくる機能は、 データの入力規則 から設定できます。条件として リストを範囲で指定 して、シート名!範囲 を指定すれば良いだけです。例えば、所蔵場所シートのA2以降を指定したいときは 所蔵場所!A2:A1000 のような値を指定します。所蔵場所が1000を超えることはないので、適当に1000としています。

その他の機能

Google App Scriptを利用すると、返却日を過ぎていた場合にSlackに通知することもできます。以下のようなスクリプトを ツール > スクリプトエディタ から起動するエディタに貼り付けてSlackのwebhookのURLなどを設定しておき、時間をトリガーに doPost 関数を実行することで、返却日当日や返却日を過ぎていた場合にSlackで通知することができます。…多分動きます(未検証だけどだいたい同じものを運用しています。)

/// <reference path="typings/bundle.d.ts" />
// Configuration
var WEBHOOK_URL = 'SLACK_WEBHOOK_URL';
var SLACK_USERNAME = '蔵書管理';
var SLACK_ICON_EMOJI = ':books:';
var SHEET_NAME = '貸出記録';

// Slackにメッセージを送信する関数
function sendMessage (userId, message) {
  // send message
  var res = UrlFetchApp.fetch(WEBHOOK_URL, {
    method: 'post',
    payload: JSON.stringify({
      username: SLACK_USERNAME,
      icon_emoji: SLACK_ICON_EMOJI,
      link_names: 1,
      text: '@' + userId + ': ' + message,
    }),
  });
};

// 返却日を確認する本丸
function doPost () {
  // 貸出シートからsheetのインスタンスを得て行数を取得しておく
  var sheet = SHEET_NAME ? SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME) : SpreadsheetApp.getActiveSheet();
  var maxRows = sheet.getMaxRows();

  // 貸出シートを1行ずつ舐めていく
  for (var r = 2; r <= maxRows; r++) {
    var row = sheet.getRange(r, 1, r, 7);

    var bookName   = row.getCell(1, 3).getValue();
    var userId     = row.getCell(1, 4).getValue();
    var startDate  = row.getCell(1, 5).getValue();
    var endDate    = row.getCell(1, 6).getValue();
    var returnDate = row.getCell(1, 7).getValue();

    if (startDate === '') {
      break;
    }

    // 返却日をミリ秒で取得する
    var d = new Date(endDate).getTime();

    // 今日の00:00:00の値をミリ秒で取得する
    // `- 32400000` してるのは時差のためだったはず…(不明)
    var n = (function(d){return d - d % 86400000 - 32400000;})(new Date().getTime());

    if (d === n && returnDate === '') {
      sendMessage(userId, '本日が書籍 "' + bookName + '"の返却日です。');
    } else if (d < n && returnDate === '') {
      sendMessage(userId, '書籍 "' + bookName + '"の返却日を過ぎています。お早めにお返しください。');
    }
  }
};

トリガーは Edit > Current project's triggers のあたりで設定できます。これを押すと以下のような画面に遷移して設定できるようになります。

f:id:masawada:20201204010150p:plain

借りていたことを忘れるのはよくあることなので、実際に返却日にお知らせがあると便利です。

f:id:masawada:20201204011330p:plain

まとめ

蔵書管理システムでも、簡単なものであればスプレッドシートで実現できますよというご紹介でした。欠点としては、あまりにも貸出記録の行数が増えるとVLOOKUPが大量に走って重くなることでしょうか。一定期間ごとにrotateしてあげるのが良いと思います。

このシートを作ってみて、スプレッドシートは意外と表現力が柔軟で、かつ複雑な管理UIを作らなくても利用できて便利だなと感じました。あまりに複雑なものを作ってしまうとメンテナンス性が損なわれるのでユースケースにもよりそうですが、もっと活用していきたいですね。

あんパン

こしあん派

蔵書管理を支える技術

貸出記録

蔵書一覧

その他の機能

まとめ