Googleスプレッドシートの更新された値を知る

Googleスプレッドシートの特定セルの値が更新されたらかを知る方法についての説明です。

スプレッドシートでのプロジェクト計画管理については、スプレッドシートでプロジェクト計画を作成する方法 - Google Workspace ラーニング センター に説明があります。またこの中で更新を通知する方法として、通知を追加する でスプレッドシートの通知設定についての説明もあります。

これだけでも便利なのですが、ステータスが更新されたときなど特定のセルが更新されたときだけ通知をするなどは GoogleAppsScript での作成が必要となります。

onEdit()

GoogleAppsScriptではトリガーの設定をしなくても、特定のイベントに応じて動くシンプルトリガーという関数があります。詳細の説明は、Simple Triggers | Apps Script | Google Developers にありますが、スプレッドシートでは下記の３つの関数が使えます。

関数名	イベント
onOpen(e)	スプレッドシートを開いたとき
onEdit(e)	セルの値を変更したとき
onSelectionChange(e)	範囲選択をしたとき

onEdit を使えば、シートの値が変更されたときに実行されて、引数として変更された内容を取得することができます。慣習としてイベント時の引数として e を使っていますが、onEdit(parm) とすれば、parm を引数として使うことができます。引数で何が取得できるかの詳細は、Event Objects の Editに記載があります。

単一セルの変更なら、

• e.oldValue
• e.value

で、変更前の値、変更後の値も取得できますが、コピペで複数セルの値が変更された場合には使えません（残念）。但し、e.range で変更された場所を確認することができるので、行と列を指定して変更後の値であれば取得することができます。

変更されたシート名と変更内容を取得する

下記は、変更されたシート名と変更された範囲、変更内容を表示する簡単な例です。

sourceはスプレッドシートオブジェクトで、e.source.getActiveSheet() で変更イベントが発生したシートを取得できます。

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  console.log('シート名　 =', sheet.getName())
  console.log('変更範囲　 =', e.range)
  console.log('変更前の値 =', e.oldValue)
  console.log('変更後の値 =', e.value)
}

シート1のA1セル（空白セル）にaaaと入力してログを確認すると、下記のようにログが出力されます。

シート名 = シート1
変更範囲 = {columnEnd:1, columnStart:1, rowEnd:1, rowStart:1}
変更前の値 = undefined
変更後の値 = aaa

e.range の中身は、

• columnEnd（終了列）
• columnStart（開始列）
• rowEnd（終了行）
• rowStart（開始行）

となっています。順番が不思議な感じがしますが、全て1なので1行目1列の値が更新されたことを示しています。

※rangeを出力すると、"toString:" から "rowStart:" までの長い出力となります。次例のように必要な項目だけを指定すると出力内容を特定できます。(2022/05/05 追記)

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  console.log('シート名　 =', sheet.getName())
  console.log('変更範囲(開始行)　 =', e.range.rowStart)
  console.log('変更範囲(終了行)　 =', e.range.rowEnd)
  console.log('変更範囲(開始列)　 =', e.range.columnStart)
  console.log('変更範囲(終了列)　 =', e.range.columnEnd)
  console.log('変更前の値 =', e.oldValue)
  console.log('変更後の値 =', e.value)
}

変更前の値はないので、undefined となって、変更後の値は、入力した aaa がセットされています。

次にA1セルの値を aaa から bbb に変更すると、ログは下記のように出力されて aaa が bbb に変更されたことがわかります。

シート名　 = シート1
変更範囲　 = {columnEnd:1, columnStart:1, rowEnd:1, rowStart:1}
変更前の値 = aaa
変更後の値 = bbb

では次に B1に AAA, B2に BBB がある状態で、この値をA1-A2にコピペします。

複数セルが更新されたので、更新前も更新後の値も取得できません。変更の範囲は rowEnd が2になったので、1から2行目の1列目の値（A1,A2）が更新されたことがわかります。

シート名　 = シート1
変更範囲　 = {columnEnd:1, columnStart:1, rowEnd:2, rowStart:1}
変更前の値 = undefined
変更後の値 = undefined

【注意】シンプルトリガーは、スクリプトエディターから手動でも実行できますが、手動で実行した場合には当然ですが、引数には何も入っていません。動作を確認する場合にはシートの値を変更して確認してください。

複数範囲の変更された値を取得する

前のサンプルコードで rangeでどの範囲が変更されたかは行と列の開始と終了でわかるのでこの値を使って変更内容を表示させる例となります。

変更行と変更列で二重ループにして個々の値を取得します。

3-6行目は、変更開始/終了行、変更開始/終了列をそれぞれ取得しています。

また10行目の sheet.getRange(row, col).getValue() で指定した行と列のセルの値を取得しています。

function onEdit(e) {
  var sheet = e.source.getActiveSheet()
  var rowStart = e.range.rowStart //変更開始行
  var rowEnd = e.range.rowEnd //変更終了行
  var columnStart = e.range.columnStart //変更開始列
  var columnEnd = e.range.columnEnd //変更終了列

  //変更行の確認
  for (var row = rowStart; row <= rowEnd; row++) {
    //変更列の確認
    for (var col = columnStart; col <= columnEnd; col++) {
      console.log(row + '行' + col + '列目 =', sheet.getRange(row, col).getValue())
    }
  }
}

上の例と同じく B1に AAA, B2に BBB がある状態で、この値をA1-A2にコピペします。ログを確認すると、

1行1列目= AAA
2行1列目= BBB

A1セル（1行1列目）に AAA が、A2セル（2行1列目）がセットされたことがわかります。

onEditの限界

とってな便利なonEditですが、シンプルエティターとしての限界があります。例えば、特定のセルが更新された場合にメールを送信するなどをしようとすると、

Exception: スクリプトにはその操作を行う権限がありません。

と、メール送信の権限がないと言われてエラーとなってしまいます。

メールやチャットルールに投稿するなどをする場合には、onEdit関数ではなく、トリガーを設定する必要があります。

シート変更時に関数を実行する

トリガーを選択すると、スプレッドシートの起動時、変更時などに特定の関数を実行することができます。スプレッドシートで使えるイベントは下記の４種類です。

イベントの種類	関数が実行される条件
起動時	スプレッドシートを起動したとき
編集時	スプレッドシートを編集したとき
変更時	スプレッドシートを変更したとき
フォーム送信時	フォームを送信したとき

編集時と変更時の違い

「編集時」では、行の追加・削除、列の追加・削除を含めてスプレッドシートに何らかの変更があった場合に起動されますが、「変更時」では、セルの値が変更された場合だけ起動されます。

注意事項は、トリガーで起動した場合にパラメータで値が取得できるのは「編集時」だけ です。インベントの種類を「変更時」にすると、下記のようにエラーになってしまいますので、

TypeError: Cannot read property 'rowStart' of undefined

イベントの種類は「編集時」を選択 してください。

下記の例は、変更行と変更列の二重ループではなく、getRangeで変更された値を配列として取得する方法です。

function modifiedSells(e) {
  var sheet = e.source.getActiveSheet()

  //変更行数
  var numberOfRows = e.range.rowEnd - e.range.rowStart +1
  //変更列数
  var numberOfClums = e.range.columnEnd - e.range.columnStart+1
  //変更された値を取得
  var values = sheet.getRange(e.range.rowStart, e.range.columnStart, numberOfRows, numberOfClums).getValues()
  
  console.log(values);
}

getRangeは、

Sheetオブジェクト.getRange（開始行, 開始列, 行数, 列数）

で範囲を指定して複数の値を取得できます。

9行目で変更された行数を11行目で変更された列数を求めて、変更された値を Values に格納しています。

例えば、コピペによる複数行（２行）、複数列（2列）の値が更新された場合は、下記のように表示されます。

[ [ 'AAA', 'BBB' ], [ 'CCC', 'DDD' ] ]

最初の行の変更値は、values[0][0]、values[0][1]で（AAAとBBB）　で、

次の行の変更値が、values[0][0]、values[0][1]で（CCCとDDD）となります。