起こったこと
↓ スプレッドシートA の data シート
Aとは異なるスプレッドシートBの まとめ シートがこう↓
まとめ シートのA2セルには下記のquery関数が入っていて、スプレッドシートAの data シートのうちB列が「東京都」のデータだけを抽出しています。
=query(importrange("xxxxx","data!A2:C"),"where Col1 is not null and Col2 = '東京都'")
ここで、スプレッドシートBの まとめ のシートが更新されたら通知がほしいというリクエストがありましたが、スプレッドシートAは私達の管轄ではありません。(なのでAのスプレッドシートを触ることができない)
そこで、スプレッドシートBの更新を検知するために、スプレッドシートBに下記のGASを仕込みます。
function onEdit(e) {
Browser.msgBox("編集イベントきたよ");
}
この状態でスプレッドシートBの まとめ シートを手で編集する(例えばD1あたりに文字を入れてみる)と、「編集イベントきたよ」が出力されます。うん、ちゃんと効いてる。
しかし、スプレッドシートAの data シートに下記のように ID300 の行を追加してみると、query関数のおかげでスプレッドシートBには300のデータが追加されますが、イベントは発火しておらず、メッセージは出力されないじゃないですか。
参照先が更新されてもイベントは発火しない
もうすこし「小さく」検証してみます。
例えばスプレッドシートCに下記のようなシートを作ります。
A2は空欄のセル。B2は =A2 の式が入っています。
さらにスプレッドシートCにこんなGASを埋め込みます。
function onEdit(e) {
Browser.msgBox(e.range.getA1Notation());
}
その後、A2に「あああ」を入力すると下記のようになります。
シートとしては「A3」も編集？更新？されているけど、イベントで取得できるのは**「直接」編集された**「A2」だけ、ってことですね。
そりゃそうか。。。例えば importrange とか query とかで、大量に他シートを参照してたら大変なことになるもんね。
おまけ1
onEdit(e) の e に何が入っているのかは下記に書かれてます。
Event Objects | Apps Script | Google Developers
ちなみに、onEdit() と onChanged() の違いは
Google Spreadsheetのトリガーの「編集時」と「値の変更」の違いを検証してみた - まだ中学生のブログ
おまけ2
onEditがどんな時に発火するのか、は下記に書かれています。
Class SpreadsheetTriggerBuilder | Apps Script | Google Developers
Specifies a trigger that will fire when the spreadsheet is edited.
「スプレッドシートが編集されたときに発火するよ」とのことですが、今回の実験結果によると「他セルを参照してるときに、参照先が変わったことで自分自身のセルの値が変わっても、それはeditには含まれない」みたいです。確かに自分自身のセルには「式」が値として入っていて、その式自体は変わってないので編集されてない、、、ですもんね。
...という解釈でいいのだろうか。どなたか情報を知っていたら教えてください！
おまけ3
今回の例に挙げた件ですが、仕方ないので「スプレッドシートB」にGASを仕込んで、定期実行して「前回の起動時と比較して変更があれば通知する、で凌ぐことにしました。
Happy GAS Lifeを！