あるセル列の値が一定以上になった時に、アラートメールを送信するようにしたいです。

Question

追記を繰り返すと長文になってしまうので、整理して書き直すことにします。
GASを24時間動かしたいのでUrlFetchを使う回数を減らすためにコードは書き直しています。

仮想通貨の３通貨を使ったアビトラのチャンスを伺うために取引所のCoinEXからコインの価格を
取ってきています。

アルトコイン>基軸通貨1>基軸通貨2>元のアルト　と交換する場合、交換手数料を含めないとしても
コインの枚数は増減します。仮に2%減った場合は、逆の回し方をして、アルト>基軸2>基軸1>アルト
と回せば増やせると思ってます。

CoinEXの価格データはtradingviewで拾えないのでAPIで直接取るしかないので
dosのbatファイル位しかやったことがなかったのですが、四苦八苦しながら進めています。

##一覧表は大まかにはできました
A列B列 >tickerデータをgetRangeでごちゃごちゃ置いてます。
C 列 >ルート名です　ATOM>BTC＞BCH　だとか　ATOM＞BCH＞BTC　といった具合です。
D列～R列 >計算式をいれてます。
S列 >アビトラ開始前のアルトが最終的に何%になったのかを出してます。99.34とあれば0.66%減ることになり
100.5とあれば0.5%増えた　という具合です。
U列 >判定列です。S列の値が101.5以上あった場合はメール通知したいので　=IF(S6>=101.5,"送信"," ")　のように
入れて、送信判定の時は送信というキーワードを表示させ、それ以外は空白にしています。送信というキーワード
がある行の、C,S列を本文としてメールを送信します。　ATOM>BTC>BCH　101.6　といった具合です。
一度に複数行が「送信」判定の場合に１回のメールにする方法があるのかもしれませんが、とりあえず
送信判定数のメールで構わないと思っています。
onEdit？　とかでトリガーを指定できるのかもしれませんがやり方がよくわからないので、
トリガーの追加から、スプレッドシートを変更時をトリガーとして設定できるかなと思ってます。
（が　まだ試してません。）

##tickerデータを取ってくるコード

function myFunction(){
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var response = UrlFetchApp.fetch("https://api.coinex.com/v1/market/ticker/all");
		var ATOMBTC = JSON.parse(response)["data"]["ticker"]["ATOMBTC"]
			ss.getRange("A9").setValue(ATOMBTC["buy"]);
			ss.getRange("A10").setValue(ATOMBTC["sell"]);
		var ATOMBCH = JSON.parse(response)["data"]["ticker"]["ATOMBCH"]
			ss.getRange("A11").setValue(ATOMBCH["buy"]);
			ss.getRange("A12").setValue(ATOMBCH["sell"]);
}

こんな感じで調べたい通貨ペアの買値と売値をA,B列に置いてます。
3行目のUrlFetchAppで5000文字以上のデータを一度に取ってきています。
データを一度に取ってからそのresponseを個別にパースしていいのかな？と思ったらできるみたいなので
これで進めようと思ってます。

このmyFunctionは分ベースのトリガーで動かしたいので1日に1440回起動することになります。
ということは1日20000回の縛りに収めるために、5秒ことにループさせてもいいのかなと思います
（が　その場合さやが開いてる間、５秒ごとにメールが来てしまいます）
ループさせる場合にSpreadsheetApp.flush();を使うのは前回こちらで教えてもらったので
こうなるのかなと思います。

function myFunction(){
var i = 1 ;
while(i <= 13) {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var response = UrlFetchApp.fetch("https://api.coinex.com/v1/market/ticker/all");
		var ATOMBTC = JSON.parse(response)["data"]["ticker"]["ATOMBTC"]
			ss.getRange("A9").setValue(ATOMBTC["buy"]);
			ss.getRange("A10").setValue(ATOMBTC["sell"]);
		var ATOMBCH = JSON.parse(response)["data"]["ticker"]["ATOMBCH"]
			ss.getRange("A11").setValue(ATOMBCH["buy"]);
			ss.getRange("A12").setValue(ATOMBCH["sell"]);
	SpreadsheetApp.flush()
	Utilities.sleep(5000)
	i++
	}

}

##なんかうまく行かない送信部分
「送信」というキーワードを含む行のC列S列を本文にいれたメールを送信する部分です。

function sendmail() {
	var ss = SpreadsheetApp.getActive().getSheetByName('シート1');
	var lastRow = ss.getLastRow();
	var address = '[e-mail]';
	var options = {name: 'アビトラアラート'};
	var MailText = rangeC +"   "+ rangeS;  
	var subject = "GASからの送信";
		  for(var i=2; i<=lastRow; i++)
			{
 			if (ss.getRange(i, 21).getValue() == "送信")
				{
				var rangeC = ss.getRange('C' + i).getValue();
				var rangeS = ss.getRange('S' + i).getValue(); 
				}
			}  

	GmailApp.sendEmail(address, subject, MailText,options );
	}

これを実行すると、本文とタイトルが「undefined undefined」のメールが１通だけ来ます。
GmailApp.sendEmailの行の前の　}　を１つ後ろに持ってくると同じメールが８つ程来てから
Exception: 1 日にサービス email を実行した回数が多すぎます。（行 17、ファイル「コード」）　というエラー
になりました。

さっきまでは、本文にC列S列が入ったメールが複数ずつ来てたのですが今回はundefinedです。
だんだんわからなくなってきました（汗）24時間sendEmailが使えなくなりました~

Comments

[mkk]

プログラムはcode記法（``` と ``` でプログラムを囲む）で記載して
インデントが分かるようにお願いします。
質問文を修正できますので、よろしくお願いします。

ちなみに何点か確認です。
①追記で書いていただいたsendmail()関数は、今時点ではどんなトリガーで呼ばれるのでしょうか？
②送信　という文字が入っている行が例えば3行あった場合、メールは別々に3通送りたいのでしょうか？

[khalipon]

書き込みありがとうございます。sendmailは今はテスト中なのでトリガーは使わずに実行ボタンでテストしてるだけです。　追記の繰り返しで長くなるので質問文を書き直します、、、

Answer 1

function sendmail() {
  var ss = SpreadsheetApp.getActive().getSheetByName('シート1');
  var lastRow = ss.getLastRow();
  var address = '[e-mail]';
  var options = {name: 'アビトラアラート'};
  var MailText = rangeC +"   "+ rangeS;  
  var subject = "GASからの送信";
  for(var i=2; i<=lastRow; i++) {
    if (ss.getRange(i, 21).getValue() == "送信") {
      var rangeC = ss.getRange('C' + i).getValue();
      var rangeS = ss.getRange('S' + i).getValue(); 
    }
  }  
  GmailApp.sendEmail(address, subject, MailText,options );
}

上記スクリプトだと、メールは1通しかきません。
また、MailText = rangeC +" "+ rangeS;がrangeC,rangeSの定義前にきているので、undefined　undefinedになります。

function sendmail() {
  var ss = SpreadsheetApp.getActive().getSheetByName('シート1');
  var lastRow = ss.getLastRow();
  var address = '[e-mail]';
  var options = {name: 'アビトラアラート'};
  var MailText = rangeC +"   "+ rangeS;  
  var subject = "GASからの送信";
  for(var i=2; i<=lastRow; i++) {
    if (ss.getRange(i, 21).getValue() == "送信") {
      var rangeC = ss.getRange('C' + i).getValue();
      var rangeS = ss.getRange('S' + i).getValue(); 
      GmailApp.sendEmail(address, subject, MailText,options );
    }
  }  
}

上記スクリプトだと、メールは、2行目から最終行までのU列に"送信"と入力されている行数分メールが届きます。
何行あるか知りませんが、メール送信の1日の上限に引っかかったのでしょう。

メールの部分に関しては以上。

Comments

[khalipon]

回答ありがとうございます。

U列に"送信"という言葉を入れた行は、３行だけにして
テストしてみました。
```
function sendmail() {
var ss = SpreadsheetApp.getActive().getSheetByName('シート1');
var lastRow = ss.getLastRow();
var address = '[e-mail]';
var options = {name: 'アビトラアラート'};
var MailText = rangeC +" "+ rangeS;
var subject = "GASからの送信";
for(var i=2; i<=lastRow; i++) {
if (ss.getRange(i, 21).getValue() == "送信") {
var rangeC = ss.getRange('C' + i).getValue();
var rangeS = ss.getRange('S' + i).getValue();
}
GmailApp.sendEmail(address, subject, MailText,options );
}
}
```
こちらだと、”undefined undefined”を本文とするメールがなぜか11通
届きました。
```
function sendmail() {
var ss = SpreadsheetApp.getActive().getSheetByName('シート1');
var lastRow = ss.getLastRow();
var address = '[e-mail]';
var options = {name: 'アビトラアラート'};
// var MailText = rangeC +" "+ rangeS;
// var subject = "GASからの送信";
for(var i=2; i<=lastRow; i++) {
if (ss.getRange(i, 21).getValue() == "送信") {
var rangeC = ss.getRange('C' + i).getValue();
var rangeS = ss.getRange('S' + i).getValue();
}
var MailText = rangeC +" "+ rangeS;
var subject = "GASからの送信";

GmailApp.sendEmail(address, subject, MailText,options );
}
}
```
こちらだと、やはり11通届きます。
”undefined undefined”が１通、U列にキーワードが入った行の
Ｃ列＋Ｓ列の単語がちゃんと入ったものが同じものが３通、３通、４通
と同時に来て合計１１通です。

テスト用のスプレッドシートには12行目までしかデータを入れていないので
i<=lastRow　を　 i<=12　にしても11通届きます。（Ｕ列に"送信"といれてるのは
3行だけです）

因みにi<=12にしたままで、シートの中の"送信"を2行だけにしても
”undefined undefined”が１通＋3通＋7通の１１通届きます。

上のように何回かテストすると、１日の上限100通に達したようで今日は動かせなくなりました（汗

[macaron_xxx]

間違ってました。申し訳ないです。
if (ss.getRange(i, 21).getValue() == "送信") {}内にメール送信部分を入れる必要がありますね。
回答を編集しますので、参考にしてください。

また、テスト時には、メール送信の代わりにログ出力で対応すればいいと思いますよ。
件数はわかりますから。

[khalipon]

ありがとうございます！　ばっちり動きました。
難しいものですね、、、
U列に判定列を作らずとも同機能は実現できそうなものですが、とりあえずGASをはじめて２週間程なので
わかるところから手探りで進んでいきたいです。

出勤前の忙しい時分にありがとうございました！

Answer 2

O列の値をスクリプトで設定しているなら設定する値が一定値を超えたら、O列の値はスプレッドシートの計算式で表示しているならスクリプトでも同等の計算をして一定値を超えたら、メール送ればいいのではないですか？

Comments

[khalipon]

回答ありがとうございます。本文を現行通りに書き直したので判定列はU列にしました。もちろんわざわざ判定列を作らなくてもコードで直接送信していいとも思いますが、とりあえず頭がうまく回らないので、回った通りにコードを書いてるありさまです。目下のところなんで複数メールがくるのかなやんでいます、、