はじめに
ＧＡＳ案件のほぼ９割がスプレッドシート絡みですが、onOpen以外のSimpleTriggerを使うことはあまりありません。そのうようなご要望があった場合には、特定のケースを除き、できる限り代替案を提案します。その理由としては概ね以下の通りです。
- トリガーとなるユーザー操作の範囲が広く、潜在バグとなる可能性がある
- 予定外の操作1でも処理が実行されてしまう、もしくは実行されない場合がある
- 概ね自動保存に伴って処理が実行されてしまう
- 数式やカスタム関数で実現できる内容が多い
-
UNDOで悲劇を招く場合がある - これらへの対応を考慮しなければならず、突き詰めると鬱になる
もちろん、onEditが有益となる特定のケースでは、積極的に利用することも検討しますが、そのようなケースはかなり限定的なものと考えています。
そこで今回は、onEdit関連の過去資産から、onEditが有益となる特定のケースを含め、その他のケースにおける注意点や対処例についてまとめました。
なお、原則としてここで触れているのはスプレッドシートにバインドしたSimpleTriggerであり、PC上での操作となります。
目的
onEditを実装したプロジェクトでは、ユーザーの操作内容によっては処理を実行しなかったり、意図した値を取得することができない場合があります。その原因と対処法を検証します。
検証
まずは特定セルへトリガーを設定し、その発火状況と値の取得状況について検証したいと思います。次に列単位のトリガーについても検証したいと思います。
以下、テストケースとして、hogeシートのA2が編集されたらその値をプロンプトで表示する、という単純なフローを想定しました。
function onEdit(e){
const sheet = e.source.getActiveSheet()
const range = e.range
const sheetname = sheet.getName()
// hogeシート.A2の変更でA2の値を表示 -----(1)
if(sheetname === 'hoge' && range.getA1Notation() === 'A2') Browser.msgBox(e.value)
/* 別ロジック -----(2)
if(sheetname === 'hoge' && (range.getColumn() === 1 && range.getRow() === 2)) Browser.msgBox(e.value)
*/
}
sample01.gsはonEditを使用する際、ネット上でも散見される王道的なロジックサンプルです。つまり、トリガーとなったEventObjectの情報を取得し、条件に合致していれば特定の処理を行うというものです。
以下は、想定される操作内容について、発火状況と処理が実行されたかどうか（プロンプト表示されたか）をまとめたものです。
なお、スクリプト実行による編集、数式や参照による（自動）入力はトリガーとならない2ため、検証リストには含めていません。
| 操作内容 | 発火 | プロンプト表示 |
|---|---|---|
| A2への入力、コピペ、削除など | ○ | ○ |
| マウスドラッグによる連続コピー （A2を「含む」範囲をドラッグ） |
○ | × |
| 連続したセル範囲へのコピペ （A2を「含む」範囲） |
○ | × |
| 連続したセル範囲の削除 （A2を「含む」範囲） |
○ | × |
| A2を含む行もしくは列が非表示時の編集 （A2を「含む」範囲） |
○ | × |
| フィルタ適用時の編集 （A2を「含む」範囲でありA2が非表示） |
○ | × |
| A2を含む行の削除もしくはA列の削除 | × | × |
| 元に戻す（アンドゥ） | ○ | ※１ |
※１ 直前の操作に準じます。しかし、処理内容によっては「元に戻す」ことが困難な場合があり、更に深く掘り下げる必要があるため割愛します。
冒頭で「トリガーとなるユーザー操作の範囲が広い」と述べていますが、概ねセル及びセル範囲の編集で発火することがわかります。（行や列の削除というのはシートに対する編集となるため発火しませんが、InstallableTriggerのChangeでは対象操作になります。3）
つまり、公式ガイド4を最大限に解釈すれば、onEditでは行や列単位を含めたセル及びセル範囲への想定されるあらゆる編集がトリガーとなると言えます。
故に、sample01.gsのロジックでは、単独セルへの編集時のみしかメイン処理が実行されず、予定外の操作には対応できないことになります。
連続したセル範囲やマウスドラッグによるコピペへの対処例
sample01.gsでは、トリガーとなる操作内容が「単独セルへの編集前提」となっています。このままでは連続したセル範囲やマウスドラッグによるコピペ操作に対しては、分岐判定の段階で弾かれてしまいます。これらの操作に対応するには、条件式を修正する必要があります。以下、連続したセル範囲への編集に絞り、改めて検証してみました。
function onEdit(e){
const sheet = e.source.getActiveSheet()
const range = e.range
const sheetname = sheet.getName()
// hogeシートの編集かどうか
if(sheetname !== 'hoge') return
// A2もしくはA2を含んだ範囲かどうか
if(range.getColumn() > 1 || range.getLastColumn() < 1) return
if(range.getRow() > 2 || range.getLastRow() < 2) return
// hogeシート.A2の変更でA2の値を表示
Browser.msgBox(e.value)
}
| 操作内容 | 発火 | プロンプト表示 |
|---|---|---|
| マウスドラッグによる連続コピー （A2を「含む」範囲をドラッグ） |
○ | ○ |
| 連続したセル範囲へのコピペ （A2を「含む」範囲） |
○ | ○ |
| 連続したセル範囲の削除 （A2を「含む」範囲） |
○ | ○ |
| A2を含む行もしくは列が非表示時の編集 （A2を「含む」範囲） |
○ | ○ |
| フィルタ適用時の編集 （A2を「含む」範囲でありA2が非表示） |
○ | ※２ |
※２ 実際にはA2が編集されていなくても、編集された範囲にA2が含まれているため、現在の値がプロンプト表示されます。
sample02.gsでは、「セル範囲の編集前提」で、そこにA2が含まれているかどうかを確認するように修正しました。結果、プロンプトは表示されますが、e.valueはundefindとなります。
この理由は明白で、EventObjectのプロパティであるvalue及びoldValueは単独セルの編集時にしか取得できない5ためです。それ以前にロジック崩壊していますが、e.valueの値によって処理を分岐させるケースでは、意図しない結果となる可能性があるということが言えます。6
単独セルと連続したセル範囲への対処例
以下、テストケースへの対処の一例です。特定の1つのセルの状態を監視するよう、シート名、セルアドレスを設定し、気持ち程度ですが汎用的にしています。また、フィルタ適用時、特定セルがフィルタによって非表示になっていないか確認するため、isRowHiddenByFilterによる事前チェックを追加しています。
function onEdit(e){
const [sheetName, rowIdx, colIdx] = ['hoge', 2, 1] // Sheetname, RowIndex, ColumnIndex
const sheet = e.source.getActiveSheet()
const range = e.range
// 特定のシートの編集かどうか
if(sheet.getName() !== sheetName) return
// 特定セルがフィルタによって非表示になっていないか
if(sheet.isRowHiddenByFilter(rowIdx)) return
// 特定セルもしくは特定セルを含んだ範囲かどうか
if(range.getColumn() > colIdx || range.getLastColumn() < colIdx) return
if(range.getRow() > rowIdx || range.getLastRow() < rowIdx) return
// 特定シートの特定セルが変更されたらその値を表示
Browser.msgBox(sheet.getRange(rowIdx, colIdx).getValue())
}
編集されたのがhogeシート.A2であることを確認した上で、A2から値を取るようにしています。ただ、利用場面は一意の単独セルの状態を監視する時限定と言えます。
特定の１列への対処例
以下は特定の１列を対象にした場合の対処例です。テストケースとして、hogeシートのA列が編集されたら、編集された値をプロンプトで表示する、というフローを設定しています。
まずは、特定列内の単独セル及び連続したセル範囲、もしくは特定列を含む範囲の編集かどうかを確認します。その後の処理を見越して行インデックスと値をオブジェクトとして取得し、一旦配列へ格納した後、値のリストをプロンプト表示するようにしています。
function onEdit(e){
const [sheetName, colIdx] = ['hoge', 1] // Sheetname, ColumnIndex
const sheet = e.source.getActiveSheet()
const range = e.range
// 特定のシートの編集かどうか
if(sheet.getName() !== sheetName) return
// 特定列もしくは特定列を含んだ範囲かどうか
if(range.getColumn() > colIdx || range.getLastColumn() < colIdx) return
// rangeから特定列の値を取得しオブジェクトへ
const sliceIdx = clmIdx - range.getColumn()
const objValues = range.getValues()
.map((v,i) => ({val:v[sliceIdx],row:rowIdx+i}))
.filter(v => !sheet.isRowHiddenByFilter(v.row))
const values = objValues.reduce((a,c,i) => {
a.push(c.val)
return a
},[])
// 特定シートの特定列が変更されたらその値を表示
Browser.msgBox(values.toString())
}
トリガー対象の確認という観点から、列単位の監視の場合はフィルタによる行の表示・非表示は影響がないという前提です。sample04.gsでは、テストケースに準じた結果を得るため、filterで絞り込んでいますが、テストケースのように編集された行を特定する必要がない場合は、filter以降の処理は不要です。処理内容に応じて適宜objValuesを走査すれば良いと思います。
最後に
結局の所、onEditは監視したいセルもしくはセル範囲の値を取得できればとりあえずなんとかなると言えます。とは言え、時としてユーザーの操作意図と結果（処理が実行されたかどうかを含む）に乖離が生じることがあり、トリガーとして利用するには、曖昧で不確実なものとも言えます。その点では、カスタムメニューやボタンクリックは、明示的かつ具体的で確実なものと言えます。
onEditの利用が有益なケース
個人的には積極的に利用したいというものではないですが、あえてonEditの利用が有益なケースを挙げるとすれば、自己完結タイプの処理7であり、予定外の操作であっても、EventObjectから確実にトリガー対象を絞り込めるケースと言えるでしょう。
onEditを利用する際の注意点など
本文中で明確に述べていない点も含め、その他onEditを利用する際の注意点などをまとめると以下のようになるかと思います。
- トリガーとなるユーザー操作の範囲が広いため、処理対象外時のスキップ処理を初期段階で行う
- 運用ルール策定も含めた予定外の操作への対応を検討する
-
e.value及びe.oldValueは単独セルの編集時にしか取得できない -
e.valueの値によって処理を分岐させるケースでは、意図しない結果となる可能性がある -
e.valueとe.range.getValues()では値が異なる場合がある8 - メイン処理はシンプルかつ工程の少ない処理に限定したほうが良い（自己完結型の処理が望ましい）
[追記]
[参考]
-
想定はされるが、できればしてほしくない操作という意味合い ↩
-
蛇足ですが、
onChangeというSimpleTriggerはありません。 ↩ -
The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet.（公式ガイドより引用） ↩
-
Only available if the edited range is a single cell.（公式ガイドより引用） ↩
-
一例として、
e.value !== undefindの場合は処理を実行する、といったケースがあります。ユーザーの操作内容によっては正常に動作しない可能性があります。 ↩ -
APIリクエストなどの外部連携や外部ファイル操作を含まない処理。
UNDOを実行してもこれらはキャンセル及び元に戻すことはできません。 ↩ -
e.valueとe.range.getValues()では、値の型が異なる場合があります。具体例として、checkboxでは、前者がString:'TRUE' || 'FALSE'、後者がBoolean:true || falseとなります。 ↩