Web備忘録 プログラミングを中心に技術的な事柄を忘れないように書き残します。

GAS入門 スプレッドシートのセル編集時のイベントトリガーまとめ

GAS入門 スプレッドシートのセル編集時のイベントトリガーまとめ
目次

GAS とは

GAS(Google Apps Script) は Google Apps(スプレッドシートとか) を自動化するためのスクリプト言語です。Excel VBA のマクロみたいなことをスプレッドシートで実現するには GAS を使います。

GAS は Javascript に似ている言語です。

スプレッドシートに対して GAS でいろいろ操作するためのトリガー(イベント)をまとめます。

スプレッドシートを開いたときやセルを編集した時などのタイミングでプログラムを実行するためトリガーは以下のページにまとめられています。

Simple Triggers | Apps Script | Google Developers

GAS のセル編集時のトリガー(イベント)

Excel マクロみたいにスプレッドシートを操作したい

Excel VBA のマクロみたいに、セルの操作をスクリプトから行いたかったのでその方法を調べました。

スプレッドシートでは、GAS(Google Apps Script)を使います。

今回やりたいことは、「あるセルに入力したらその行の別のセルにデータを書き出す」です。これを行うにはセルの編集時のイベントを拾ってやる必要があります。

セル編集時のイベントを拾うスクリプト

onEdit(e) は、ユーザーがスプレッドシートの値を変更したときに実行されます。

function onEdit(e) {
  var range = e.range;
  range.setNote('最終更新日時: ' + new Date());
}

e はイベント引数で編集時の情報もろもろが入っています。 e.range は編集されたセル範囲です。上の例では編集されたセルに最終更新日時をノートに書いています。

全シート全セルを対象にする

onEdit(e) はすべてのシート、すべてのセルに対してイベントが駆動します。これを制御するにはコードで必要に応じてシート名や行数列数で制御を入れる必要があります。

例えばシート1に対してのみセルの編集を監視したい場合は以下のようにします。

function onEdit(e) {
  // 編集のあったシート
  var sheet = e.source.getActiveSheet();
  // 編集したセル
  var cell = e.source.getActiveRange();

  if (sheet.getName() == "シート1" && cell.getColumn() == 1) {
    // シート1の1列目のセルが編集されたときのみ実行される
    // ..
  }
}

複数のセルが同時に編集されたとき

複数セルが同時に編集された場合、例えばコピペで一括でデータが張り付けられたりした場合などは e.source.getActiveRange() では起点となったセルしか取得できません。

その場合は、e.range で範囲を取得し、対象のセルをすべてループ等で走査する必要があります。

function onEdit(e) {
  var rowStart = e.range.rowStart;
  var rowEnd = e.range.rowEnd;
  var columnStart = e.range.columnStart;
  var columnEnd = e.range.columnEnd;

  for (var r = rowStart; r <= rowEnd; r++) {
    for (var c = columnStart; c <= columnEnd; c++) {
      // 編集されたセル(r, c)
      sheet.getRange(r, c).setNote(r + '行目, ' + c + '列目');
    }
  }
}

range から　rowStart, rowEnd, columnStart, columnEnd をそれぞれ取得します。これは範囲の開始終了の行/列数です。

あとは2重にループして全セルに対して処理を行えます。

編集前のセルの値と比較する

セル編集時のイベントで直前の値を参照することができます。

function onEdit(e) {
  // 編集前の値
  var oldValue = e.oldValue;
  // 編集後(現在)の値
  var newValue = e.value;

  e.range.setNote('old: ' + oldValue + '\n' + 'new: ' + newValue);
}

複数のセルが編集されたときは無理っぽいです。

まとめ

以上の3つの基本を押さえると、スプレッドシートのセルを編集した時の最低限の基本処理はすべて対応できると思います。

以上。

参考URL

GASカテゴリの最新記事