GASで特定のセルが変更されたら処理したり、特定のセルに入力があれば処理するためのコピペで使えるサンプルコードを公開しています。
GASで特定のセルが変更されたら実行するには一般的には、onEditやトリガーが使われますが、
IMPORTRANGEを使っていたり、フォームの自動入力やGASでの処理などでの変更や編集には反応しない仕様のため、それを回避するために私が実際にやっている方法などを解説します。
GASで特定のセルが変更されたら処理する方法3選
GASで特定のセルが変更されたら処理を行うには主に3つ
onEditで特定セルを指定して実行する方法、
トリガーの変更時や編集時に設定して実行する方法、
参照用のセルを用意して定期的に実行させる方法があります。
3つ目の定期的に実行される方法は、IMPORTRANGEなどの参照しているセルでも変更時に処理を行うことができます。
onEditで特定セルが変更されたら処理をする
GASのonEditで特定のセルが変更されたかどうかを判断をして、特定のセルに入力があれば処理実行するというサンプルコードです。
function onEdit(e) {
//指定したいセル（A1など）を入力
var range = 'ここに指定したいセルを入力';
//編集されたセルの行数を取得
var row = e.range.getRow();
//編集されたセルの列数を取得
var col = e.range.getColumn();
//指定セル範囲の行列数取得
var rowlength = sheet.getRange(range).getValues().length;
var collength = sheet.getRange(range)
.getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
//編集されたセルが指定セルだったら実行
if(row === rowlength && col === collength){
//ここに行いたい処理を記入
//行いたい処理ここまで
}
}onEditでシートを指定して特定セルが変更されたら処理をする
シートが複数ある場合、このシートの特定のセルが変更・入力されたら処理をしたいという時は、シートも指定して処理されるようにします。
function onEdit(e) {
//対象にしたいシート名を入力
var sheetN = 'シート名を入力';
//指定したいセル（A1など）を入力
var range = 'ここに指定したいセルを入力';
//evant(e)からアクティブシート名を取得
var sheetName = e.source.getSheetName();
//編集されたセルの行数を取得
var row = e.range.getRow();
//編集されたセルの列数を取得
var col = e.range.getColumn();
//指定セル範囲の行列数取得
var rowlength = sheet.getRange(range).getValues().length;
var collength = sheet.getRange(range)
.getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
//編集されたシート名と対象にしたいシート名が一致したら実行
if(sheetName === sheetN){
//編集されたセルが指定セルだったら実行
if(row === rowlength && col === collength){
//ここに行いたい処理を記入
//行いたい処理ここまで
}
}
}onEditで特定セル（複数）が変更されたら処理をする
GASで特定のセルが変更されたら処理をしたい時に、複数のセルを指定したい時もあります。
その場合、特定セルの列と行をしてすることで可能です。
function onEdit(e) {
//指定したいセル列数（Aなら1、Bなら2）を入力
var colIdx = 'ここに指定したい列数を数字で入力';
//編集されたセルの行数を取得
var row = e.range.getRow();
//編集されたセルの列数を取得
var col = e.range.getColumn();
//編集されたセルが対象の列だったら実行
if(col === colIdx){
switch(collength){
case 1: //ここに指定したい行数を数字で入力(10行目なら「case 10:」)
////ここに行いたい処理を記入
case 2: //ここに指定したい行数を数字で入力
////ここに行いたい処理を記入
case 3: //ここに指定したい行数を数字で入力
////ここに行いたい処理を記入
}
}
}複数列、複数行を指定したい場合のサンプルコード
function onEdit(e) {
//指定したいセル列数1（Aなら1、Bなら2）を入力
var colIdx1 = 'ここに指定したい列数を数字で入力';
//指定したいセル列数2（Aなら1、Bなら2）を入力
var colIdx2 = 'ここに指定したい列数を数字で入力';
//指定したいセル列数3（Aなら1、Bなら2）を入力
var colIdx3 = 'ここに指定したい列数を数字で入力';
//編集されたセルの行数を取得
var row = e.range.getRow();
//編集されたセルの列数を取得
var col = e.range.getColumn();
//編集されたセルが対象の列1だったら実行
if(col === colIdx1){
switch(collength){
case 1: //ここに指定したい行数を数字で入力(10行目なら「case 10:」)
////ここに行いたい処理を記入
case 2: //ここに指定したい行数を数字で入力
////ここに行いたい処理を記入
case 3: //ここに指定したい行数を数字で入力
////ここに行いたい処理を記入
}
}
//編集されたセルが対象の列2だったら実行
if(col === colIdx2){
switch(collength){
case 1: //ここに指定したい行数を数字で入力(10行目なら「case 10:」)
////ここに行いたい処理を記入
case 2: //ここに指定したい行数を数字で入力
////ここに行いたい処理を記入
case 3: //ここに指定したい行数を数字で入力
////ここに行いたい処理を記入
}
}
//編集されたセルが対象の列3だったら実行
if(col === colIdx3){
switch(collength){
case 1: //ここに指定したい行数を数字で入力(10行目なら「case 10:」)
////ここに行いたい処理を記入
case 2: //ここに指定したい行数を数字で入力
////ここに行いたい処理を記入
case 3: //ここに指定したい行数を数字で入力
////ここに行いたい処理を記入
}
}
}onEditで特定シートが変更されたら処理をする
セル指定ではなく、シート指定で変更や入力されたら処理をしたい場合もあると思います。
その場合はシート指定の判定だけ行って処理をします。
function onEdit(e) {
//対象にしたいシート名を入力
var sheetN = 'シート名を入力';
//evant(e)からアクティブシート名を取得
var sheetName = e.source.getSheetName();
//編集されたシート名と対象にしたいシート名が一致したら実行
if(sheetName === sheetN){
//ここに行いたい処理を記入
//行いたい処理ここまで
}
}トリガーで特定のセルが変更されたら処理をする
onEditは、権限設定が低くやりたい処理が行えないこともあります。
そんな時はトリガーの変更時や編集時の設定を使って実行できるように設定できます。
トリガーの変更時と編集時の違いは、
編集時は、スプレッドシートの変更・編集時に実行されるのに対し、
変更時はセルの値が変更された時のみに実行されます。
セルの入力が変更された時に実行なら、変更時
罫線や削除、追加なども実行したければ編集時を使います。
処理するコードはonEditで使用したものと同じですが、
functionの部分（関数の名前）だけ何でもいいのでonEdit以外に変更します。
//関数名をonEdit以外に変える
function trigger(e) {トリガーの設定方法は、
GASエディットからトリガー画面を開き、「トリガーを追加」から作成します。
実行する関数を選択→ functionの後の関数の名前
実行するデプロイを選択→デフォルトのまま（Head）
イベントのソースを選択→デフォルトのまま（スプレッドシートから）
イベントの種類を選択→編集時、もしくは変更時
トリガーについて詳しくはこちらの記事で解説しています。
トリガーの作成・削除・一時停止、実行されない時の対処法
トリガーやonEditで実行されない処理を実行させる
onEditやトリガーは基本的にユーザーによる手動変更をトリガーとしているため、
IMPORTRANGE等の参照値が変更されても処理は行われません。
onEditなどで実行されないもの
- GASスクリプトによる編集（フォームからの自動入力なども含む）
- 数式等での自動参照（importrange,query,filterなど）
- 行もしくは列の削除（削除はトリガーの編集時であればOK）
私は仕事でIMPORTRANGEで複数シートから参照をするスプレッドシートを扱っていますが、IMPORTRANGEのような参照している値が変更された時にも処理を実行したかったため、
参照数値を使用しないセルに一旦コピーしておき、
コピーした値と参照している値が違ったら処理というGASをトリガーで15分おきに定期実行させるようにしています。
onEdit等では動かない特定のセルが変更されたら処理サンプルコード
function trigger() {
//指定したいシート名
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');
//IMPORTRANGEなどを使用している指定したい列数（A1なら1）を入力
var colIdx = '指定したい列数を入力';
//コピペ・値確認用で使用するセルの列数
var checkcolIdx = '確認用セルの列数を入力';
//指定したいセルの開始行
var rowIdx = '開始行を入力';
//指定したいセルの終了行
var rowIdxLast = '終了行を入力';
for(rowIdx; rowIdx <= rowIdxLast; rowIdx++){
//IMPORTRANGEなどを使用している指定セルの値を取得
var value = sheet.getRange(rowIdx,colIdx).getValue();
//指定セルをコピペしているセルの値を取得
var oldvalue = sheet.getRange(rowIdx,checkcolIdx).getValue();
//指定セルとコピペセルの値が違っていたら処理実行
if(value !== oldvalue){
//ここに値が変更された時の処理
Logger.log(rowIdx+'行の値が変更されています。');
//値が変更された時の処理ここまで
//新しい値をコピペセルにセット
sheet.getRange(rowIdx,checkcolIdx).setValue(value);
}
}
}このサンプルコードでは、指定したいセルの終了行を数値で指定していますが、
指定した列の最終行を取得して処理することも可能です。
その場合は「指定したいセルの終了行」の箇所を以下のコードに書き換えてください。
//指定したいセルのデータ最終行を取得
var rowIdxLast = sheet.getRange(rowIdx,colIdx)
.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();後は、トリガーで定期実行できるように、15分おき等の指定した間隔で設定しておけば、定期的に実行されるようになります。
GASで特定のセルが変更されたら自動実行サンプルデータ
サンプルデータはこちらからダウンロードできます。
【Copori】特定のセルが変更されたら自動実行サンプル
コピペで使えるサンプルコード付きです。
自動実行サンプルデータの使い方
1、【Copori】特定のセルが変更されたら自動実行サンプルを開いてコピーしてください
3、メニューから「拡張機能」⇨「Apps Script」を開きます
※複数アカウントでログインしているとエラーで開けないので注意してください
4、権限取得.gsを手動実行して権限を取得してください
権限の取得方法はこちら
4、シート1、シート2の「onEdit()を実行」のチェックボックスにチェックを入れるとonEditが実行されます。
このサンプルコードでは、実行結果に「onEdit実行完了」と入力するようになっています。
また、参照セルの変更確認.gsでは参照セルの値変更時に実行できるGASが入っています。（手動実行も可能）
5、初期化する時は、C9のチェックボックスにチェックを入れてください
（実行完了すると、シート1、シート2の実行結果のセルが空欄になってC9のチェックが外れます）
私はGASで特定のセルが変更されたり、特定のセルに入力があれば処理する内容は、
Gmailを送信したりLINEなどで通知を行う処理を使用しているので、
この辺りのやり方は今後記事にしていく予定です！
onEditについての詳しい記事はこちら
onEditで特定のシートやセルの変更で実行する方法まとめ
トリガーについてはこちら
トリガーの作成・削除・一時停止、実行されない時の対処法
ありがとうございました！
コメント