【GAS】最終行が更新されたらスラック通知したい

Question

前提

Googleスプレッドシートで最終行が更新された際にスラックに通知したいです。

現状、メールからGSSに自動転記されるようなGASを組み、
目視でスラックに通知という列を作成して、指定セルの変更をトリガーに通知を飛ばしています。

実現したいこと

やりたいこと

• 最終行が更新されれば、更新された行のデータを拾ってスラックに通知
• 可能であれば、指定列が空白な行のデータをスラックに通知

▼詳細
・I列の最終が更新されれば、F・G・I列の情報を拾う（トリガー：更新）
・K列が空白であれば、上記と同様に通知（トリガー：指定時間）

現状のGAS

function myFunction() { 
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシート
  var activeSheet = activeSpreadsheet.getActiveSheet(); // アクティブシート
  if(activeSheet.getName() != "シート名"){
    return;
  }
  var activeCell = activeSheet.getActiveCell();// アクティブセル
 
 //指定セルの変更をトリガーに
  if(activeCell.getColumn() == 10 && activeCell.getValues() == "通知"){
    var newInputRow = activeCell.getRow();
    var time = activeSheet.getRange(activeCell.getRow(), 1).getValues();
    var product_no = activeSheet.getRange(activeCell.getRow(), 7).getValues();
    var slname = activeSheet.getRange(activeCell.getRow(), 8).getValues();
    var taiou = activeSheet.getRange(activeCell.getRow(), 9).getValues();

//スラックへの通知文

試したこと

var last_row = sh.getLastRow();　//スプレッドシートにデータが入力されている箇所の、最終行の数値を取得

で最終行を取得しようとしましたが、
・I列の指定
・F・G・I列の情報
を取れませんでした。

また、下記を参照しましたが
同様に変更行のその他のセル情報が取得できずに今回の質問となります。
https://teratail.com/questions/174346

Comments

[退会済みユーザー]

「I列の最終が更新されれば、F・G・I列の情報を拾う（トリガー：更新）」について
①「最終行が更新された際」の「更新された際」というのは、（スプレッドシートに"手入力で"最終行が更新された場合等ではなく）「GASによって、メールの指定した一部の内容が、転記対象のGoogleスプレッドシートの既存最終行の１つ下の行に"自動で”挿入されたとき」という理解でよろしいでしょうか。

それとも 「もともと何かが入力されていた行」の I列が上書きされたときに、そのF・G・I列の情報を拾う、という意味でよろしいでしょうか。
(「更新」という単語の意味は、一般的に考えれば、「上書き」のことと思いますので）

②「・I列の指定・F・G・I列の情報を取れませんでした。」とは、"具体的に"どういう意味でしょうか。

「GASによってメール内容がGSSへ転記される際に、ある条件で I セルにデータが自動入力されるようにしているが、activecell==10 としているにもかかわらず I セルへの自動入力を捕捉できない（activecell==10 が trueにならない）」という意味でしょうか？
　そもそも if 文の外側と内側に、console.log 等を置いて、if文が想定通り動いているかのか、動いていないのかを確認されたりしましたか？

[Tnatsu]

回答をいただきありがとうございます。
私の説明不足と、言葉の定義が間違っており申し訳ございません。

下記回答です。

＞「I列の最終が更新されれば、F・G・I列の情報を拾う（トリガー：更新）」について
①の認識です。
「GASによって、メールの指定した一部の内容が、転記対象のGoogleスプレッドシートの既存最終行の１つ下の行に"自動で”挿入されたとき」
をトリガーとして設定したいです。

＞②「・I列の指定・F・G・I列の情報を取れませんでした。」とは、"具体的に"どういう意味でしょうか。

やりたいこととしては
A～H列がGASで自動更新
I列以降は、VLOOKUPで他スプレッドシートとの突合を行う。
GASで最終行に挿入された際に、I列に情報がはいったタイミングで
該当行のF・G・I列に入っている項目をスラックに通知したい
となります。

＞「GASによってメール内容がGSSへ転記される際に、ある条件で I セルにデータが自動入力されるようにしているが、activecell==10 としているにもかかわらず I セルへの自動入力を捕捉できない（activecell==10 が trueにならない）」という意味でしょうか？

こちらは、手入力でI列に「通知」と記入すると問題ないのですが、
上記の通りVLOOKUPで自動で転記された際にアクティブセルとして拾わず、
トリガーが動かないという意味です。

確認が遅くなり申し訳ございませんが、
何卒よろしくお願いいたします。

Answer 1

Google Apps Script の現時点の仕様として、手入力以外によるスプレッドシートへの編集・変更が発生した際、当該編集・変更トリガーを確実に捕捉することはできず、また仮に捕捉できたとしても、どのセルが編集されたのかを確実に特定することはできません。

たとえば、当該スプレッドシートに紐ついていない別の GAS による自動転記や、Zappier 等他のウェブサービスによるスプレッドシートへの自動書き込みが行われた場合、書き込まれたことの検知を確実に行うことはできず、また、どの行・どの列・どのセルが書き込まれたかを確実に特定することもできません。

（特定のスプレッドシートの内容変更が行われたことを確実に捕捉したり、どのセルが変更されたかを確実に特定できるのは、手入力によって編集された場合に限ります）

したがって、本件におけるアプローチとしては、
スプレッドシートへ転記されたことを検知して転記された側のGASでSlackに飛ばすのではなく
Slack への通知のトリガーとなる元々の処理、つまり、特定内容のメールが届いたことを検知する処理に、Slack への通知内容の加工と通知処理を含めるしかないと考えられます。

（簡単にいうなら

• メール検知 → スプレッドシートへの転記 → 転記されたことの検知 → SLACKへの通知
  ではなく
• メール検知 → SLACKへの通知 　を同じGAS内で行う
  ということ）
  
  
  具体的には、下記のようになります。
  ① GMail から指定したメールが来たかどうか監視するスクリプトを「メール監視スクリプト」とします。
  仮にこの「メール監視スクリプト」が仮にスタンドアロンスクリプトならば、メール監視スクリプトを、スプレッドシートのコンテナバインドスクリプトに変更します。
  さらにここで、メール監視スクリプトに紐づけたスプレッドシートを、「コンテナシート」と呼称します。

② コンテナシートの内容は、現在メール内容を転記している別スプレッドシートと同じ構造にしておきます。つまり、VLOOKUP等の数式等必要なデータ全部、現在メール内容を転記しているスプレッドシートと同じように、コンテナシートに組む、ということです。

③ 「メール監視スクリプト」の内容については質問文に全く記載されていませんが、GASで組んでいるとのことなので、おそらく以下のような処理を行っていると推測されます。
１．トリガーを使用して一定間隔で特定のGMail アカウントを監視し、新着メールを取得、条件に合致するメールの有無を調べる
２．１で条件に合致するメールがあれば、そのメールの内容をスクレイピングして、必要なデータだけを取り出す。
３．２で取り出したデータを、別のスプレッドシート（質問文で問題にしているスプレッドシート）に書き込む。
４．終了(トリガーによる監視は継続）

---

このうち、３．の処理を、以下のように変えます。（コンテナシートは数式等をコピー済みですべて正しく動作しているという前提です）

3'-1：２．で取り出したメール内容のうち必要なデータを、コンテナシートの最新行の適切な位置に書き込む
↓
3'-2：SpreadsheetApp.flush() を実行してコンテナシートへの書き込み結果・計算式の計算結果を確実に更新する。
↓
3'-3：Slack に飛ばしたいデータを、コンテナシートの適切な部分から取得する。
↓
3'-4：3'-3 で取得したデータを加工し、Slackへ通知する。

※仮に、slackへ飛ばす内容を、コンテナシートとは別シート（質問で問題にしているスプレッドシート）へも記録しなければならない、という場合は、上記 3'-1 で別シートへの書き込み処理も追加すればよいでしょう。

---

別シートを直接操作することもできなくはないですが、 SpreadsheetApp.flush() に相当する動作がなく、更新後データを別シートから確実に取得できる保証はないので、上記のような処理（スクリプトが紐ついたスプレッドシートのデータを取得）としています。

---

また、仮にメールを監視するスクリプトを管理しているのが別の人であって、自分では直接編集できないという場合は、上記の処理を含めるように、その担当者に依頼すればよいと思います。

Comments

[Tnatsu]

ご丁寧にご教示いただきありがとうございます！
大変勉強になりました。

上記で再度組みなおしてみます。