読者です 読者をやめる 読者になる 読者になる

GeekFactory

int128.hatenablog.com

スプレッドシートでカラムの更新日時を記録する

google apps script javascript

Google Appsスプレッドシートは便利ですね。特に、JavaScriptで独自の関数やトリガーを定義できるのでハックが捗りますね。本エントリではスプレッドシートで特定のカラムの更新日時を記録する方法を説明します。

題材

スプレッドシートで下図のようなタスク管理表を作っているとします。

f:id:int128:20151123174017p:plain

この時、タスクがopen/assigned/resolved/closedされた日時を記録しておくと、後から振り返ることができて便利です。人力でやるルールにするとついつい忘れてしまうので自動化したいですよね。

具体的には、ユーザがstatus列を更新したらstatusに対応する列に自動的に日時が入力されるようにします。例えば、タスクT100がassignedになったらT100行のassigned列(上の例ではF2)に現在日時が入力されます。

実現方式

Apps Scriptのトリガーを使います。スクリプトonEdit 関数を定義すると、スプレッドシートが更新された契機で関数が実行されます。引数には更新されたシート(source)や範囲(range)が渡されます。詳細は公式ドキュメントを参照してください。

Field Example Notes
e.authMode LIMITED A value from the ScriptApp.AuthMode enum
e.user amin@example.com A User object, representing the owner of the spreadsheet (only available in simple triggers, and only if the current user is allowed to know the owner's email address, depending on a complex set of security restrictions)
e.source Spreadsheet A Spreadsheet object, representing the Google Sheets file to which the script is bound
e.range Range A Range object, representing the cell or range of cells that were edited
e.value 10 Only available if the edited range is a single cell

https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events

スクリプトから範囲を直接参照する(例えばC1とかD:Eをハードコードしてしまう)と列を追加した時に動かなくなるため、名前付き範囲を定義しておきます。

f:id:int128:20151123175226p:plain

スクリプト

ざっくり以下を実行します。

  1. 更新されたシートが対象かどうか判定します。
  2. 更新範囲がstatus列かどうか判定します。
  3. 更新範囲のセルについて、それぞれ以下を行います。
  4. 更新された内容に対応する列(open/assigned/resolved/closed)を探して、その列に現在日時を入力します。

スクリプトはこんな感じです。

function onEdit(event) {
  if (event.source.getSheetId() == 1521996422 /* シートのID */) {
    var statusColumn = new StatusColumn(event.source.getRange('items_status'));
    var timestampColumns = new TimestampColumns(event.source.getRange('items_timestamp'));
    statusColumn.forEachChange(event.range, function (row, status) {
      timestampColumns.setValue(row, status, new Date());
    });
  }
}

function StatusColumn(range) {
  this.forEachChange = function (editedRange, callback) {
    if (editedRange.getColumn() >= range.getColumn() && editedRange.getLastColumn() <= range.getLastColumn()) {
      var editedValues = editedRange.getValues();
      var editedRowTopIndex = editedRange.getRow();
      editedValues.forEach(function (columns, rowIndex) {
        var status = columns[0];
        callback(editedRowTopIndex + rowIndex, status);
      });
    }
  };
  return this;
}

function TimestampColumns(range) {
  var headers = range.offset(0, 0, 1).getValues()[0];
  this.setValue = function (row, status, value) {
    headers.forEach(function (header, index) {
      if (header == status) {
        var cell = range.getCell(row, index + 1);
        if (cell.isBlank()) {
          cell.setValue(value);
        }
      }
    });
  };
  return this;
}

既知の問題

連続したカラムが更新された場合は動作しますが、飛び飛びのカラムが一度に更新された場合は先頭のカラムしか更新されません。また、「元に戻す」でカラムの内容が戻された場合も新しい更新日時が入力されます。 更新日時のセルを上書きしないようにしました(11/28追記)

まとめ

Apps Scriptのトリガーを使うとユーザの更新イベントに応じて任意の処理を実行できます。本エントリではステータスの更新日時を記録する方法を紹介しました。

ちなみに、プロダクトバックログリストの更新履歴からリードタイムを計測する試みでこの方法を活用する予定だったりします。