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

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

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

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

f:id:int128:20151123175226p:plain

スクリプト

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

更新されたシートが対象かどうか判定します。
更新範囲がstatus列かどうか判定します。
更新範囲のセルについて、それぞれ以下を行います。
更新された内容に対応する列（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;
}