【GAS】指定の文字列が入力(手動入力ではない)されたら、Slack通知を飛ばしたい。

Question

前提・実現したいこと

GASで指定列に指定文字列が入力(手動入力ではない)されたら、Slack通知を飛ばすGASを作成したい。

発生している問題・エラーメッセージ

下記に貼り付けたコードを作成しましたが、Slack通知が飛びません。
調べてみると、スクリプト等で自動で入力される場合は、トリガーの「編集時」には当てはまらないということがわかりました。
ただ調べてみても、その問題の解決方法がよくわからず質問させていただきました。

該当のソースコード

//送付エラーのタイムスタンプ/件名/本文/メールIDをスプレッドシートに吐き出す

function getGmailToSS() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('errordata_gmail');

const searchlabel = '"送付エラー"';

const threads = GmailApp.search('label:(' + searchlabel + ') -label:処理済み');

const msgIdArray = sheet.getRange("D:D").getValues().filter(String).flat();
let lastRow = msgIdArray.length + 1;
 
for(const n in threads){
    const thread = threads[n];
    const msgs = thread.getMessages();
    msgs

for(m in msgs){
    const msg = msgs[m];
    const id = msg.getId();

    if(msgIdArray.includes(id)) continue;　//IDがD列に既に存在する場合はスルー


   const msgData = [[msg.getDate(),msg.getSubject(),msg.getPlainBody(),id]];
   sheet.getRange(lastRow,1,1,4).setValues(msgData);　//まとめて書き込み

    lastRow++
    }


const label = GmailApp.getUserLabelByName('処理済み');
    thread.addLabel(label);
 }


  // スプレッドシートから必要なデータを読み取り、スラックに投稿する。
  const staffErrorEmail = sh.getRange(writeRow, 5).getValue(); //5＝E列（送付エラーアドレス）
  const staffName = sh.getRange(writeRow, 6).getValue();　//6＝F列（スタッフ名）
  const temphireId = sh.getRange(writeRow, 7).getValue(); //7＝G列（ID）
  const slackId = sh.getRange(writeRow, 9).getValue(); //9＝I列（SlackID）

  const message = slackId + '\n' + staffName + '\n' + staffErrorEmail + temphireId;
  postToSlack(message);

}


function postToSlack(message){
  const postToMDS = "通知先のWebhooksURL"; // 

  const jsonData =
      {
        "text" : message,
      };
  
  const payload = JSON.stringify(jsonData);
  
  const options =
      {
        "method" : "post",
        "contentType" : "application/json",
        "payload" : payload,
      };
  
  // リクエスト
  UrlFetchApp.fetch(postToMDS, options);
}

補足情報

トリガー設定

関数：getActiveCell
デプロイ時に実行：Head
イベントソース：スプレッドシートから
イベント種類：編集時

GASのif文で条件にしているスプレッドシートのJ列には下記関数を入れております。
=ARRAYFORMULA(IF(I3:I="","","SlackID生成済み"))

スプレッドシートのI列には<@SlackID>が表示されるようにしています。

Slack通知させるプログラムをつくるのも初めての経験で、
自分が把握していないミスもあるかもしれませんが、何卒よろしくお願いいたします。

Comments

[退会済みユーザー]

J列に入っている関数は「I列に文字（SlackID）が入力されていたら、J列の同じ行に「SlackID生成済み」と表示する」という式のようですが、
I列への文字入力は手動なのでしょうか？それとも手動以外の入力方法によるものなのでしょうか？

[k_k]

I列の文字も手動以外の入力方法によるものです。

Answer 1

・特定の数式の計算結果が変更されたことを、変更時・更新時のトリガー（onEditやonChangeで直接検知することはできませんし、その計算結果が変わったセルはアクティブセルにはなりません。
（この例で言えば、I列に何らかの文字が入ったことによりJ列の当該行が「SlackID生成済み」という文字に変化しても、J列自体は、アクティブセルとして扱われません。）

・変更時・更新時のトリガーは、基本的に、スプレッドシートへ直接手動入力する場合以外は検知できません。
ただし、たとえば、GoogleスプレッドシートのAppSheetという機能による書き込みについては、「変更時」トリガーであれば書き込みがあったことを検知し、書き込まれたセルがActiveCellになるということは確認できています。（「編集時」トリガーでは検知不可）
したがって、限られた場面では、スプレッドシートへ直接手動入力する場合以外であっても「変更」トリガーで検知できる可能性はあるかもしれません。

コメントでの質問に対する回答によると、今回I列への文字入力は「手動入力ではない」とのことですが、どのような手段を用いて自動入力しているのかについての詳細が開示されておりませんので、質問者さんの使用状況で「変更時」でも検知できるかどうかはこちらではわかりかねます。

いずれにしても下記コードに直したうえで、トリガーを「編集時」から「変更時」に変更して保存した場合、もしかしたら検知できるかもしれません。

// トリガーは「編集時」から「変更時」に変える。
function getActiveCell() {
  const SS = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシート取得
  const Sh = SS.getActiveSheet(); // アクティブシート取得
  if(Sh.getName() != "errordata_gmail"){ //シート名取得　
    return;
  }
  const ActiveCell = Sh.getActiveCell(); //アクティブセル取得

  if(ActiveCell.getColumn() == 9 && ActiveCell.getValues() !== ""){ //条件：I列（＝9）に何らかの文字が入力されたとき通知
    const InputRow = ActiveCell.getRow(); //条件一致の行を取得
    //以下条件一致のセルを取得。取得したい値を変更したい場合は列数を変更。定数は適宜変更。
    const staffErrorEmail = Sh.getRange(ActiveCell.getRow(), 5).getValues(); //5＝E列（送付エラーアドレス）
    const staffName = Sh.getRange(ActiveCell.getRow(), 6).getValues();　//6＝F列（スタッフ名）
    const temphireId = Sh.getRange(ActiveCell.getRow(), 7).getValues(); //7＝G列（ID）
    const slackId = Sh.getRange(ActiveCell.getRow(), 9).getValues(); //9＝I列（SlackID）

    const message = slackId + '\n' + staffName + '\n'  + staffErrorEmail +  temphireId ;
    postToSlack(message);

---

コメントを受けて追記

「GASを使ってD列にメールアドレスを書き込んでいる」とのことなので、
アプローチを変えて、その書き込むGASの方を改造すればよいと思います。
（書き込まれるスプレッドシートの方で書き込まれたことを検知するのではなく、
書き込んでいるGASを使って最終的なSlackの投稿処理まで行う、ということです）

具体的には、書き込み用のGASでD列にメールアドレスを書き込んだ後、続けて数式が入っているセル（E列～I列まで）を読み取ってSlackに投稿する、という流れです。
以下のように、スプレッドシートのA列～D列に書き込む処理の後に、Slack投稿用のコードを継ぎ足してみてはいかがでしょうか？

（先頭が「+」となっている行は追加、「-」となっている行は削除）

//送付エラーのタイムスタンプ/件名/本文/メールIDをスプレッドシートに吐き出す

function getGmailToSS() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('errordata_gmail');

  const searchlabel = '"送付エラー"';

  const threads = GmailApp.search('label:(' + searchlabel + ') -label:処理済み');

  const msgIdArray = sheet.getRange("D:D").getValues().filter(String).flat();
  let lastRow = msgIdArray.length + 1;

  for (const n in threads) {
    const thread = threads[n];
    const msgs = thread.getMessages();
    msgs

    for (m in msgs) {
      const msg = msgs[m];
      const id = msg.getId();

      if (msgIdArray.includes(id)) continue;　//IDがD列に既に存在する場合はスルー


      const msgData = [[msg.getDate(), msg.getSubject(), msg.getPlainBody(), id]];
      sheet.getRange(lastRow, 1, 1, 4).setValues(msgData);　//まとめて書き込み


+     // スプレッドシートから必要なデータを読み取り、スラックに投稿する。
+     const staffErrorEmail = sheet.getRange(lastRow, 5).getValue(); //5＝E列（送付エラーアドレス）
+     const staffName = sheet.getRange(lastRow, 6).getValue();　//6＝F列（スタッフ名）
+     const temphireId = sheet.getRange(lastRow, 7).getValue(); //7＝G列（ID）
+     const slackId = sheet.getRange(lastRow, 9).getValue(); //9＝I列（SlackID）
+     const message = slackId + '\n' + staffName + '\n' + staffErrorEmail + temphireId;
+     postToSlack(message);

      lastRow++
    }


    const label = GmailApp.getUserLabelByName('処理済み');
    thread.addLabel(label);
  }


- // スプレッドシートから必要なデータを読み取り、スラックに投稿する。
- const staffErrorEmail = sh.getRange(writeRow, 5).getValue(); //5＝E列（送付エラーアドレス）
- const staffName = sh.getRange(writeRow, 6).getValue();　//6＝F列（スタッフ名）
- const temphireId = sh.getRange(writeRow, 7).getValue(); //7＝G列（ID）
- const slackId = sh.getRange(writeRow, 9).getValue(); //9＝I列（SlackID）

- const message = slackId + '\n' + staffName + '\n' + staffErrorEmail + temphireId;
- postToSlack(message);

}

function postToSlack(message) {
   ...略...
}

Comments

[k_k]

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

・質問に対する回答が不十分で失礼いたしました。
I列のセルについてもスプレッドシートの関数で入力されています。
SlackIDが出力されるようにしてあります。
=ARRAYFORMULA(IF(H3:H="","",iferror("<@"&VLOOKUP(H3:H,'SlackIDが入力されているシート'!$E$1:$G,3,FALSE)&">")))

・いただいたコードで、トリガーを「編集時」から「変更時」に変更して保存してみましたが、やはりSlack通知は飛びませんでした。

下記質問も拝見しました。Appsheet使ったことがないので少し調べてみましたが、Appsheetで入力する必要があり、上記のI列のセルの関数の機能をAppSheetでもたせることは難しいのでしょうか？
https://teratail.com/questions/371662

[退会済みユーザー]

繰り返しになりますが、監視対象が、数式が入っているセルである限り、そのセルの（計算結果の）変化は、どうやっても検知できないと思われます。
したがって数式が参照している大元（この場合I列に入っている数式が参照しているH列
　→仮にH列が数式ならばその数式が参照している先・・・以下同じ）を監視する必要があります。

そして、数式が参照している大元がどのようにして（＝どのようなツールやアプリ等を用いて）自動入力されているのか、という点をご教示いただけないことには、それがGASで検知可能か不可能かを調べることはできないと思われます。

>Appsheetで入力する必要があり、上記のI列のセルの関数の機能をAppSheetでもたせることは難しいのでしょうか？
→前述の通り、「現状、どういう方法で大元のスプレッドシートに自動入力されて、最終的にJ列に"SlackID生成済み"という表示がなされるようになっているのか」に関する情報が全く不明であるため、
現状の機能をAppSheetで置き換えられるかどうかについても、答えようがありません。

[k_k]

回答ありがとうございます。
理解力不足で失礼いたしました。

数式が参照している大元はD列で、GASでメールIDを自動入力させております。
送付エラーになったメールの内容を出力しているGASになります。

[退会済みユーザー]

追記しました。

[k_k]

追記ありがとうございます。
D列にはメールアドレスではなく、メールIDをGASで書き込んでおります。

理解力不足で申し訳ないのですが、下記内容を自分のプログラムで実装する際にどうすればよいのかがよくわかりませんでした。
質問文のプログラムを変更しましたので、可能であれば教えていただいてもよろしいでしょうか？

// 書き込むメールアドレス
const mailAddress = "[e-mail]";

// 書き込み先の行番号（ここでは簡便のため固定値（10）にしているが、forループを使っている場合はその変数に読み替えてもよい）
const writeRow = 10;

// メールアドレスを「writeRow行目、D列」に書き込む
sh.getRange(writeRow, 4).setValue(mailAddress);
}

[退会済みユーザー]

>D列にはメールアドレスではなく、メールIDをGASで書き込んでおります。
とのことですが、
それでは、その「メールIDを書き込む方のGAS」のコードを、省略せずに質問文に追記していただけないでしょうか？

[k_k]

返信ありがとうございます。
質問文を修正いたしました。

const msgData = [[msg.getDate(),msg.getSubject(),msg.getPlainBody(),id]];
sheet.getRange(lastRow,1,1,4).setValues(msgData);

こちらで書き込みを行っております。

[退会済みユーザー]

後半のコードを修正しました

[k_k]

ご回答ありがとうございます。
望んでいた形で実装することができました。
何から何までご丁寧に教えていただき大変助かりました。

まずは、記載いただいたコードをしっかり理解するところから勉強させていただきます。