@ishi14posted at 2023-03-07

Googleスプレッドシートで特定セルが指定の数以上になった際にメールで通知（GAS）

Q&A

解決したいこと

Googleスプレッドシートで特定セル（行）が指定の数以上になった際にメールで通知したいと考えています。
特定文字列が含まれている際の通知については次の記事を参照してできました。

現在のソースコード

function test(){

  const subject = 'ここに通知メールタイトル'; //通知タイトル
  const body = "ここから通知メール本文" //メールの内容

  var recipient = 'xxx@gmail.com'; //通知を飛ばす先のgメールアドレス

  var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
  var mySheetName= mySheet.getSheetName(); //シート名を取得

  var myCell = mySheet.getActiveCell(); //アクティブセルを取得
  var myCol = myCell.getColumn();
  var myCellValue = myCell.getValue();

  if(mySheetName == 'count'){ //変更されたのが特定シートなら
  if(myCol == 3 ){ //変更されたのがC列なら
  if(myCellValue.indexOf('1000') > -1){ //C行に「1000」という数字が出現した場合に通知
  GmailApp.sendEmail(recipient, subject, body);//通知メールを送信
  }
  }
  }
}

スプレッドシートの関数

当該スプレッドシートC列には以下の関数を記述しています。

=SUM(COUNTA(Raw!C2:C),COUNTA(Raw!I2:L))

「RAW」はGoogleフォームの回答が連携されています。
C行に代表者の氏名、I~L行に代表者以外の氏名（最大4名ぶん）記載があります。

そもそも今回実現したいこと

あるイベントに1000人の申し込みがあった場合、申込者数を制限したいと考えています。

単純に回答件数が1000人になったらフォームを閉じるなどといったシンプルなものだったらよかったのですが、
1回のフォーム送信で複数名分の申し込みを可能としているため、上記のようなやや複雑な状態となっております。

別の方法でも実現したいことが達成できるのであれば問題ありません。

おわりに

いつも素人質問をしてしまい申し訳ありません。
何卒よろしくお願いいたします。

2Answer

@tanaike posted at 2023-03-08

下記の状況について、

単純に回答件数が1000人になったらフォームを閉じるなどといったシンプルなものだったらよかったのですが、1回のフォーム送信で複数名分の申し込みを可能としているため、上記のようなやや複雑な状態となっております。

この場合、OnSubmit トリガーを使ってフォームの投稿毎に人数を確認し、1000名を超えた時点でGoogle Formへの回答を停止させることは可能です。Google Formを停止した後にアクセスした人向けに人数制限を超えたなどのメッセージを表示させることも可能です。これらはGoogle Apps Scriptで行うことができます。

例えば、=SUM(COUNTA(Raw!C2:C),COUNTA(Raw!I2:L))が"Sheet1"シートのA1セルにあると想定しますと、Spreadsheetのバウンドスクリプトに対してOnSubmitトリガーを設定し、フォームの投稿時にスクリプトを実行させて"Sheet1"シートのA1セルの値が1000を超えた時点でGoogle Formを停止させることも可能です。もちろん、その際にメールを送信することもできます。

ご質問からは現状の問題点が不明だっため、想定できる方法について説明させていただきました。お役に立たないようですと申し訳ありません。

0Like

@STSynthe posted at 2023-03-08

今回検証していないので確証はありません。参考程度ぐらいに止めてください。

提示されているソースコードだとイベントトリガーを使ってtest()を呼び出すなどしないと動作しません。

色々面倒ですので記録されているスプレットシートに計算用のシートを作成してCOUNTBLANK関数を使い、名前が明記されて無いセルの数をカウントし、IF関数で条件設定したらカスタム関数を呼び出す方が手っ取り早いかと思われます。

C行に代表者の氏名、I~L行に代表者以外の氏名（最大4名ぶん）記載があります。

1回の送信で代表含め最大5人分ということでしょうか。間違っていたとしても置き換えてください。

C列、及びIからL列をCOUNTBLANK関数を使って空白セルの数を数えます。

ROWS関数を使って現在の最大行数*人数(5)から先ほどの空白セル分を引き算します。そうすることで現在代表含む氏名がいくつなのかを得られます。

コードにすると次のようになります。

=IF
(
	MINUS
	(
		MULTIPLY
		(
			ROWS('データシート名'!C:C),
			5
		),
		COUNTBLANK('データシート名'!C:C,'データシート名'!I:L)
	)
	>
	1000,
	CUSTOM_FUNCTION()
)

CUSTOM_FUNCTION()を除く使用されてる関数が分からないのであればご自分で確認なさってください。

Google スプレッドシートの関数リスト

あとはIF関数が真、つまり1000名以上となったときカスタム関数が呼び出されるはずです。スプレットシートに紐付いているApps Scriptにカスタムした関数¹を書き、Form.setAcceptingResponses(false)でフォームを自動的に閉じさせたりメールを送信させるなりのコードを書く。

function CUSTOM_FUNCTION() {
	// code...
}

私はこれ以上の解答しませんので、もし採用するにしてもご自身で工夫なさってください。

名前規則的なものはありませんが、シート関数に合わせて大文字にしています ↩