Q&A
ご返信ありがとうございます。ログを入れる？というのがよくわからないので見当違いだったらすみません。
丁度昨日作成されたばかりのスプレッドシートがあったのでスクリプトを組み込み、一晩寝かせた今朝方、トリガーの実行ログを確認しました。結果、エラー率1.72%、実行数466となっていました。新規のデータが1件(横に1行分程)入力されただけですが、そんな回数実行されるものなんでしょうか。そもそも通知は届いていません。
前提・実現したいこと
"スプレッドシートで特定の列(サンプルではQ列)に新規入力があった場合、gmailで通知メールを送る"
スクリプトを組み、通知を受け取れるよう試みました。
発生している問題・エラーメッセージ
スクリプトも組みあがり、デバックも問題なし。しかし実際に利用するシート(本番シート)上で試したところうまく動作しないようで、どれだけ待っても通知が送信させませんでした。
該当のソースコード
function notification(){
const recipient = 'sample@gmail.com'; //通知を飛ばす先のgメールアドレス
const subject = '新規入力あり';//通知タイトル
const body = 'https://docs.google.com/spreadsheets/d/samplxxxxxxxx';//確認するシートのURL
var mySheet = SpreadsheetApp.getActiveSheet(); //シートを取得
var myCell = mySheet.getActiveCell(); //アクティブセルを取得
if(myCell.getColumn()==17){ //Q列に入力があったか判定
GmailApp.sendEmail(recipient, subject, body);//通知メールを送信
}
}
試したこと
実際にはあらかじめテスト用シートを作成し動作を確認してみたのですが、そちらでは問題なく通知を飛ばすことができました。ソースコードとの具体的な相違点は、通知を飛ばす先のgメールアドレスを自分のアドレスに、確認するシートのURLを対象のシートのものに変えた2点のみ。そこはテスト用シートも本番シートもかわらないので、同様に動いてくれると思ったのですがうまくいきませんでした。
補足情報（FW/ツールのバージョンなど）
・スクリプトのトリガーは“スプレッドシートから－変更時”に設定しています。
・本当は更新時にリアルタイムで通知を受け取れればどのような手段でもいいのですが、何故かシートの通知ルール設定機能が動いてくれないのでスクリプトで実現しようと思った次第です。
・今後の拡張性を考えてQ列のセルの内容で判定するようにしています。
・初心者で至らないところもありますが、何卒よろしくお願いいたします。
回答2件
0
私の環境でも、同じコードとトリガーでメール受信できましたので、コード自体はあっているように思います。※なぜか、入力3~4回に1回しかメール受信できませんでした。。。
ですので、作成したスクリプト(プロジェクト)と、入力した際にメール送信させたいスプレッドシートが正しく紐づいているか、確認してみるのはいかがでしょうか。
スクリプト編集画面の左上、「青地に白矢印(従来のスクリプトエディタ)」か「Apps Script(新しいエディタ)」をクリックすると、自分が操作しているプロジェクトの確認ができるようです。
投稿2021/04/01 11:25
総合スコア5
0
ベストアンサー
まず確認することは、その処理が起動したかですが
ログでも入れて、実行数の所からログを確認してみてはいかがでしょうか。
実行結果はありますか？あればログ内容はどうなっていますか？
（追記）
今の処理が動かない理由は不明ですが、今の処理は不完全な部分もあるため
ちゃんと動かないスプレットシートの関数として実行するのをあきらめて
以下の処理を自分のスプレットシートの関数として定義し、それを時間起動型
（時間はレスポンスが欲しい適当な時間で）で実行させるのは如何でしょうか。
内容は
監視対象のスプレットシートのQ列の内容を、自分のスプレットシートのQ列の内容と比較
違っていれば、監視対象のデータを自分のスプレットシートにコピーした後にメールを行う
というものです。
これであれば複数列の修正にも対応できているので、こちらの方が良いのではないでしょうか。
以下にサンプルコードを提示します。これでどうでしょうか。
GAS
1function notification() { 2 var targetSpreadsheet=SpreadsheetApp.openById("（監視対象のスプレットシートのID）"); 3 var targetSheet= targetSpreadsheet.getSheetByName("（監視対象のシート名"); 4 var thisSpreadsheet=SpreadsheetApp.openById("（自分のスプレットシートのID）"); 5 var thisSheet= thisSpreadsheet.getSheetByName("（自分のシート名"); 6 var lastrow = targetSheet.getLastRow(); 7 var c17s=targetSheet.getRange(1,17,lastrow,1).getValues(); 8 var c17s2=thisSheet.getRange(1,17,lastrow,1).getValues(); 9 10 for (let i = 0; i < c6s.length; i++) { 11 if (c17s[i].toString() != c17s2[i].toString() ){ 12 console.log('違う'); 13 thisSheet.getRange(1,17,lastrow,1).setValues(c17s) 14 //ここでメール 15 break 16 } 17 } 18}
toString()で文字列化して比較しているのは日時データを生のまま比較すると
常に違う判定に私がテストした際にはなった為です。
投稿2021/03/31 20:43
編集2021/04/20 22:30
総合スコア1497
シート更新タイミングはどんな時なのですか？
あとエラーとなったのがq列の変更ではないでしょうか
エラー時のログは、どんな内容ですか？
エラー時のログの確認方法がわからない場合、その旨コメントお願いします。
更新タイミングですが、当該シートは他のユーザーが管理・作成したもので、新たな情報が入り次第手動で入力されます(フォーム回答からの自動転記ではありません)。
Apps Script ダッシュボードからエラー時のログを確認したところ、“ Exception: 範囲外のセル参照です ”とありました。時刻を見ると、どうやら上にも書いた1件の新規データが入力された時点で発生したようです。なお対象のシートに参照するセルの列(q列)は存在します。
現時点もシートに新たな入力があり、スクリプトも動いていますが、上記以降に実行エラーは出ていません。相変わらず数件の入力に対して実行回数が多すぎるうえに、こちらに通知は届きませんが。
色々試してみましたがよくわからないですね。
とりあえず、その対象のシートのQ列を編集させてもらい、直後にログを確認できないでしょうか。
確認出来たら、行番号も含め教えてください。
あと、スプレットシートにシートが他にもあれば
そのシートの編集時にもトリガーは実行されるので
そのシートへの入力回数よりも多いというのであれば
他のシートへ入力があるのではと思ったのですが
どうでしょうか。
間が空いてしまい申し訳ないです。
>>その対象のシートのQ列を編集させてもらい、直後にログを確認
今回取り扱っているスプレッドシートはある企業で業務管理に使われているものなので、
自分には必要時以外の編集権限がありません。実物を見せられないのが残念です。
>>あと、スプレットシートにシートが他にもあれば～
確かに、取り扱っているスプレッドシートには対象のもの以外にも複数のシートがありました！
ただ、今回のスクリプトは対象のシートに対するコンテナバインドとして紐付けられています。
また他のシートも現時点で頻繁に編集が行われる状況にはないので
そちらからも動作を拾っている線は薄そうです。
仕方がないので、編集時を狙ってシートとログを比較してみました。
結果、シート上のセルへの編集とスクリプトの実行タイミング・数は“おおよそ”一致していることがわかりました。(セルの編集がないはずの営業時間外にも何件か実行記録が見られること、シートが作成された日に編集されたセルの数と実行数の間に明らかな差が出ていることから“おおよそ”と判断しました)
実行数が多すぎるように思えたのはこちらの勘違いの可能性が大きくなってきました。
次にログの確認ですが、リストをクリックして各々詳細を確認してみたところいずれも
“この実行に関するログはありません”と表示されます。
スクリプトが正常に実行された場合はこの表示で問題ないのでしょうか。
正常に実行といっても、相変わらず通知は届きませんが…
まずコンテナバウンドでもスプレットシートに紐付くのであってシートでは無いと思います
本題ですが、同じコードで送信実績があるので、エラーが発生しているかいないかをまずは確認でしょうか。
実行数の画面でフィルターをステータスでかけて完了以外にチェックをつければ絞り込めれると思います
見つからなければ、本当にQ列の処理が入ってきてるかを確認するために、sendmailの後にthrow new Error('flg');でも入れて実行数の所でそのエラーが出ているか確認するのが良いかと思います。もしそのログが出ていれば、sendmailでエラーは発生していない証拠になります
まぁそこからまた調査になるかもですが…
最初のエラーログは、過去の7日間を過ぎてしまって拾えませんでした…
とりあえず、教えていただいたthrow new Error('flg'); を使ってみました。
Javaにも不慣れでよくわかっていないところがありますが
まずは自前のテストフォームにて実行ログに"Error: flg "以下のメッセージが表示されることを確認。
次に、対象のシートのスクリプトに先の一文を組み込んで実行の様子を確認してみました。
編集のタイミングは不定期かつ複数のセルが一気に編集される場合もあるので
このセルの編集に対してこの実行が…とまでは断言できませんがおおよそ以下の結果がみられました。
・スクリプトを組み込んでから現時点までの24時間で、実行回数227回に対してエラーは98回。
・実行に失敗した場合のログには全て
"エラー JavaScript エンジンで予期しないエラーが発生しました。エラーコード: INTERNAL。"
と表示される。
・複数のセルが纏めて編集された後に確認すると、実行ログの数が編集されたセルの数より一つ少ない。
どこかのセルに対する編集が拾われてない可能性がある。
また、編集の順番と実行タイミングから、それがQ列のセルである可能性がある。
エラーメッセージが出るには出たけれど、内容が違うということは
やはりsendmailでエラーが発生しているようです。
エラー INTERNAL。
の件は本日から発生した不具合のようです
https://teratail.com/questions/332184
なので元々のエラーとは関係なさそうです
ありがとうございます、上記エラーの件確認しました。
リンク先を参考にV8をオフにしてまた一日確認してみたのですが
やはり"Error: flg "の表示は出ませんでした。
変更後に行われた400回近い実行は全て"完了"扱いです。
念のため通知の送信先を確認してみてもアドレスを打ち間違えたりはしてはいませんし…
Error: flgが出ず、"完了"扱いということは編集がQ列にあったと判定されていないということになります。
確かに複数列同時更新でQ列が含まれている場合、提示のコードでは判定できませんが常にそうというわけではないでしょう。
ということで、コードを少し修正したのを試していただけないでしょうか。
function onEdit(e){
＜略＞
var myCell =e.range;
if(myCell.getColumn()==17){ //Q列に入力があったか判定
＜略＞
}
}
として、トリガーは削除して、シンプルなトリガーで実装したらどうなるでしょうか。
改善するかどうかわかりませんがQ列の更新が判定されていない状況のようですので、試してみてもいいかなと思います。
いつも迅速なご返信、とてもありがたいです…
早速テストフォームで試してみました。
頂いたスクリプトを組みデバックしてみたところ、ほんの一瞬だけ
“ TypeError: Cannnot read property `range' of Underfined(行6, ファイル「通知テスト」) ”
という表示が出ました。また実際に実行すると、 実行ログには
“ エラー Exception: スクリプトにはその操作を行う権限がありません。(以下～ ”
という表示が出ました。
調べてみたところ、この権限に関係してrangeを読み込めない場合があるという情報を見つけたので
エディタタブの[表示]から[マニフェストファイルの表示]にてファイルを開き
実行ログの（以下～ にリストアップされたマニュフェスト？を、追加して再度実行してみました。
しかし残念ながら、実行ログの通知は依然変わらず
“エラー Exception: スクリプトにはその操作を行う権限がありません。(以下～” のままでした。
念のためコード全体を記載いたします。ツギハギなので、もしこの時点で間違っていたらすみません。
function onEdit(e){
const recipient = 'sample@gmail.com'; //通知を飛ばす先のgメールアドレス
const subject = '新規入力あり';//通知タイトル
const body = 'https://docs.google.com/spreadsheets/d/sampl';//確認するシートのURL
var myCell =e.range;
if(myCell.getColumn()==17){ //Q列に入力があったか判定
GmailApp.sendEmail(recipient, subject, body);//通知メールを送信
}
}
とりあえずQ行への更新は確認できたようですが、シンプルなトリガーは権限が低いと書いてあったので、それじゃ送れないのかもしれません。試してみたということですみません。。。
とりあえずflgが出力される状態に戻して待つくらいですかね・・・・
GmailApp.sendEmailが正しく実行されるかどうかを確認するだけであれば
if文の外にだしてしまって、送れることを確認するという事は出来ますが
Q列の編集がヒットしないという問題は解消されないですね。
すみませんありがとうございます…
あれから別の列を指定して判定するパターン、列を判定せずとにかく通知を送るパターン
(これには成功しました。送信数が多すぎてすぐに実行上限を迎えてしまいましたけれど)、
別のスクリプトでデータを拾い通知を送るパターンを試してみましたが
やはり列の判定がネックのようです。
いずれも自前のテストシートでは問題なく動作するというのが引っ掛かります。
せめてどこに原因があるのかわかればいいんですけどね…もう少し頑張ってみます。
1点気になってたのですが、編集権限のないシートにプロジェクトって追加・編集出来たりするんですか？
とりあえず、トリガーが実行はされているから
var mySpreadsheet= SpreadsheetApp.getActiveSpreadsheet();
var mySheet= SpreadsheetApp.getActiveSheet();
var myCell =mySheet.getActiveCell();
console.log(mySpreadsheet.getName());
console.log(mySheet.getName());
こういう風にして、ちゃんと目標のスプレットシートになっているか
シートになっているかを確認してみる感じですかね…
で、もし違ってたら
var mySpreadsheet= SpreadsheetApp.openById(～);
var mySheet= mySpreadsheet.getActiveSheet();
に変えてみて、って感じなのでしょうか…
手さぐりですが。
getActiveSpreadsheet のパターンと シートIDを指定するパターンを試してみたのですが、
本番シートではいずれも
“ログが見つかりません。Logger API を使用してプロジェクトにログを追加してください。”
と表示されます。なお今回も、テストシートではきちんと対象シート名が表示されます。
編集権限の件ですが、正確には特定の列に対する編集(セルに存在するプルダウンに外部スタッフ＝私がチェックを入れる)のみ許可されているかたちです。
実は以前、管理担当者に通知スクリプトをシートに組みたいと相談したことがありました。その時には
“特に許可は必要ないのでご自由にどうぞ”と言われていたので可能だと思っていたのですが
やはり権限の問題でシート側から弾かれてる可能性があるのでしょうか。
(相談した担当者がシステムに詳しくなかった可能性は…正直考えられます…)
権限の状況了解しました。
つまりシートの編集権限はあるけれど
セルの保護で、一部列以外の編集は許可されていない。ということですね。
ログが出ない。というのはおかしいですね。
同じ関数名があるのでしょうか。
これは編集権限があるから確認できるかと思います。
あと言うまでも無い事かもしれませんが
実行数の画面では、関数の名前に「notification」が設定されたものを確認しているのですよね？
だとすると、まずはログを出力されることを確かめることが第一かもしれないですね。
あとQ列は編集できないかもしれませんが、Q列を選択した状態で
エディタで実行すれば、Q列が編集された時と同じような状態のテストになるかと思います。
他のスクリプトと関数名が被っていないか、実行しているのは対象のスクリプトか
改めて確認してみましたがそのあたりはおそらく大丈夫みたいです。
それでは本題です。
まず最初にテストシートにてconsole.logをLogger.log に変更してログ出力を試してみたところ
エディタのログ・実行数ページのログ共に対象のシート(テストシート）と対象のシート名を表示することができました。
同様に本番シートで試してみたところ
エディタのログでは対象のシート(本番シート)名を表示することができましたが
対象のシート名は現時点で編集が行われていないはずのシート名になっていました。
また実行数ページのログはこれまでと同様に“この実行に関するログはありません”
と表示されていました。
もしかしてこれが原因かと思い
今度はgetSheetByNameで対象のシート名まで指定して実行してみたところ
エディタのログでは対象のシート名まで表示されましたが
実行数ページのほうではやはり“この実行に関するログはありません”のままでした。
(試しに、今まで作ったスクリプトも全てシート名まで指定して実行してみましたが
やはり結果は変わりませんでした…)
今の処理がどうして動かないのかよくわからないのですが
今の処理では、複数列の修正時に対応していないとかもあるので
処理をつくりかえては如何でしょうか。回答の所に案を記載します。
できました！！・・・・やっとできました(泣)
1日動かしてみましたが、問題なくQ列の更新のみを拾って動作してくれているようです。
自分のシートで中継するとうまくいくということは、やはり元のシート自体に問題があったようですね。
ゆくゆくは同様の方法で以前作ったスクリプトに再挑戦したり
他のセルからも必要な情報抜き出して通知内容に加えられるように拡張していきたいと思います。
xail2222様のおかげでかなり仕事に余裕ができました。
何度も迅速かつ丁寧なご返答、本当にありがとうございました！！
やっとですね。ただ代替案で行くと早く決めていればもう少し早く解決したきもしますが、まあ上手くいって良かったです
元々の仕方で上手くいかないのが謎のままですが、まぁそういう事例もあると認識するにとどめておきます。
色々確認などの対応ありがとうございました。
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2021/04/02 02:53