JavaScript：スプレッドシートの特定列に書き込みがあった場合に Google Chat にそのセルの書き込みを飛ばしたい

Question

実現したいこと

タイトルにも記載させていただいたのですが、スプレッドシートの特定列に書き込みがあった場合に Google Chat にそのセルの書き込みを飛ばしたいと考えております。（A3:A と B3:B に書き込みがあった場合に A3:A と B3:B の書き込み内容を Google Chat に飛ばしたい）
右も左もわかっていない初心者には高望みかもしれませんが、どうかお知恵をお借りできたらと思っております。

発生している問題・分からないこと

下記の Link にて紹介されていたコードを元にトライしているのですが、何点か問題にぶちあったってしまっております。（職場にマクロを組めるひともおらず、だれにも質問ができず困っております）
<問題点>
❶そもそもエラーコードがでてしまう（下にエラーコードを掲載しました）
❷シートの特定の列ではなく、どこが編集されても通知が送られてしまう可能性あり
❸シートの列 2か所が埋まった場合に通知がとばされない
❹通知文章が固定のものしか送られない可能性あり

❶のエラーコード
Exception: Request failed for https://chat.googleapis.com returned code 400. Truncated server response: {
"error": {
"code": 400,
"message": "Invalid JSON payload received. Unknown name "手上げあり" at 'message': Cannot find field.",
"statu... (use muteHttpExceptions option to examine full response)

該当のソースコード

function sheetsToHangout() {

/* Spreadsheet */
  let sheetName = '〇〇〇';
  let sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  // データ取得範囲指定
  const row = 2;
  const column = 1;  
  const LastRow = sheet.getDataRange().getLastRow();
  const LastColumn = sheet.getDataRange().getLastColumn();
  const numRows = LastRow - row + 1;
  const numColumns = LastColumn - column + 1;

  // データ取得
  let data = sheet.getRange(row, column, numRows, numColumns).getValues();


/* Hangout Chat */  
  // 投稿先指定
  const url = '〇〇〇'; // 手順1で取得したURLをセット

  for (i=0; i < data.length; i++) {
    // 送信内容作成
    let text = data[i][0];
    let message = {'〇〇〇' : text}
    let params = {
      'method': 'POST',
      'headers' : {
        'Content-Type': 'application/json; charset=UTF-8'
      },
      'payload':JSON.stringify(message)
    };

    // 送信
    UrlFetchApp.fetch(url, params);
  }
}

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

下記の Link を参照にトライしてみております。
https://qiita.com/Shin/items/72eaa5fa24b608c58b93

補足

特になし

Comments

[jimbe]

javascript の質問でしたら java のタグで無く javascript のタグにしてください。

[TN8001]

JavaとJavaScriptは全く別の言語です。

「Java」タグは外してください。
「Google Apps Script」や「Google スプレッドシート」タグを付けてください。

[タグ一覧｜Google Apps Script](https://teratail.com/tags/Google%20Apps%20Script)
[タグ一覧｜Google スプレッドシート](https://teratail.com/tags/Google%20%E3%82%B9%E3%83%97%E3%83%AC%E3%83%83%E3%83%89%E3%82%B7%E3%83%BC%E3%83%88)

[tsubugai.taka]

ご教示をいただきありがとうございます💦
とてもありがたいです💦
ご指摘にあわせてタグを変更させていただきました💦

[jimbe]

編集ありがとうございます

[YAmaGNZ]

let message = {'〇〇〇' : text}
この部分の'〇〇〇'は'text'固定であるべきですが伏字にしているということは変更しているのでしょうか？

[Lhankor_Mhy]

let message = {'〇〇〇'

の 〇〇〇 は、具体的には何が入っていますか？

[tsubugai.taka]

let message = {'〇〇〇'
の部分には「報告あり」に変えておりました💦
この部分は正しくは下記のどちらかというイメージでしょうか💦
❶ let message = {'text' : text}
❷ let message = {'text' : 報告あり}

[Lhankor_Mhy]

同じような質問を過去にされているようです。
https://teratail.com/questions/376065#reply-508390

[tsubugai.taka]

ご返信ありがとうございます！たしかにこの「let message = {'〇〇〇'」の部分同じ内容で躓いておりました💦お恥ずかしいかぎりです。。
ですが、実は前のときも解決できずにおりまして、希望の動作がかなっておりません。
その間に、私的な問題ではありますが、部署移動がおこり当分必要がなくなっていたのですが、あらためていま必要になり問題を解決したいと思いここにすがらせていただいた所存でございます。

[Lhankor_Mhy]

❶はご解決ですか？

[tsubugai.taka]

はい！ただ❶を修正すると、いま現在 A列に入力されているセルの情報がすべて 1つずつチャットに送られてしまい、その数が膨大すぎて途中でエラーが発生するような形になってしまいました💦

[Lhankor_Mhy]

このページが参考になると思います。
https://note.com/manabibashi/n/n679edc719696

[tsubugai.taka]

ありがとうございます！参考にさせていただきます💦

Answer 1

特定のシートの特定の二つの列に書き込みがあったら、
書き込みがあった行のA列のセルとB列のセルの値をGoogleChatに通知するのであれば、
次のようなコードを書けると思います。

/*
*　この関数をスプレッドシートの「編集時」のトリガーに設定します。
*　この関数はスクリプトエディタで「実行」をクリックしても
*　編集されたセルを取得できないのでエラーになります。
*/
function sheetToGoogleChat(e) {
  if(!e) {
    console.log('このスクリプトは、セルが編集された時に起動するようにトリガーを設定するものです\nエディタからは実行できませんのでトリガーの設定後にセルを編集してください');
    return;
  }
  // シート名と二つの列を指定
  const sheetName = '手上げ'; // 確認するシート名
  const column1 = 1; // A列
  const column2 = 2; // B列

  // 編集されたセルが対象のシートなら対象の列か確認
  const range = e.range;
  const sheet = range.getSheet();
  const column = range.getColumn();
  if (sheet.getName() === sheetName && [column1, column2].includes(column)) {
    // 両方の列のセルに表示されている文字列を取得して確認
    const value1 = range.offset(0, column1 - column).getDisplayValue();
    const value2 = range.offset(0, column2 - column).getDisplayValue();
    if (value1 && value2) { // どちらも書き込みがあるなら
      // スクリプトエディタの「実行数」から確認できるログに記録
      console.log('A列の値: ' + value1 + ' B列の値: ' + value2);
      notifyGoogleChat(value1, value2);
    }
  }
}

/* Google Chat */
function notifyGoogleChat(value1, value2) {
  const url = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; // 手順1で取得したURLをセット
  // 送信内容作成
  let text = 'A列の値: ' + value1 + ' B列の値: ' + value2;
  let message = { text }
  let params = {
    'method': 'POST',
    'headers': {
      'Content-Type': 'application/json; charset=UTF-8'
    },
    'payload': JSON.stringify(message)
  }
  // 送信
  UrlFetchApp.fetch(url, params);
}

セルを編集したときの通知の画像

Comments

[atlanticSalmon]

何行目に書き込みがあったかを通知するならもう少しコードが増えます。

[tsubugai.taka]

丁寧に回答をいただきありがとうございます💦
また言われて私の言葉が足りていないことに気づきました、そちらも教えていただきありがとうございます💦

実行したかったことは、A列からV列まで項目があるシートがありまして、「A列が名前 / B列が作業項目」となっております。（シートのページもいくつかありまして、その中のひとつのページのみ必要となります）
シートを管理者が常に開いた状態にしてなくても、シートに報告を書きこんださいに気づくことができるように常時使用しているチャットに「誰が・どの作業について」を届けたいと考えておりました💦
そのため、A列とB列の書き込み内容を書き込み時にチャットに送信できたらと考えました💦
----------------------------------------------------------------------------------------------
先ほどいただいたものを実行しましたが下記のエラーが出てしまいました💦（※そこで他にもページがあることに気づき、自分なりにどうやったらページを指定できるかな？と思い、少しだけ手を加えてみてトライしてみましたが、同じ結果でした💦）

<エラーコード>
TypeError: Cannot read properties of undefined (reading 'range')

<試したもの>
function sheetToGoogleChat(e) {
// スプレッドシートの指定の二つの列に値が入力されたかを確認
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('手上げ');
const column1 = sheet.getRange("A:A") // A列
const column2 = sheet.getRange("B:B") // B列

// 編集されたセルを取得し対象の列か確認
const range = e.range;
const column = range.getColumn();
if ([column1, column2].includes(column)) {
// 両方の列のセルに表示されている文字列を取得して確認
const value1 = range.offset(0, column1 - column).getDisplayValue();
const value2 = range.offset(0, column2 - column).getDisplayValue();
if (value1 && value2) { // どちらも書き込みがあるなら
// スクリプトエディタの「実行数」から確認できるログに記録
console.log('A列の値: ' + value1 + ' B列の値: ' + value2);
notifyGoogleChat(value1, value2);
}
}
}

/* Google Chat */
function notifyGoogleChat(value1, value2) {
const url = 'ここには Google Chat の Webhook URL をそのまま貼り付けました'; // 手順1で取得したURLをセット
// 送信内容作成
let text = 'A列の値: ' + value1 + ' B列の値: ' + value2;
let message = { text }
let params = {
'method': 'POST',
'headers': {
'Content-Type': 'application/json; charset=UTF-8'
},
'payload': JSON.stringify(message)
}
// 送信
UrlFetchApp.fetch(url, params);
}

[atlanticSalmon]

エラーの原因は、他にシードがあることではなく、スクリプトエディタから実行したことによるものです。
エラーにならないようにしました。

また、指定したシートのときだけに通知するようにしました。

[tsubugai.taka]

ありがとうございます！すごいです！エラーはでなくなりました！
ですがA列とB列にあわせて何かを書き込んでもチャットが何も送られてきませんでした💦
もしかして Webhook の URL を全部はってはダメなどのルールはありましたでしょうか？💦

<新しく試したもの（Webhook の URL 以外は使用を試した通りに張り付けました💦）>
function sheetToGoogleChat(e) {
if(!e) {
console.log('このスクリプトは、セルが編集された時に起動するようにトリガーを設定するものです\nエディタからは実行できませんのでトリガーの設定後にセルを編集してください');
return;
}
// シート名と二つの列を指定
const sheetName = '手上げ'; // 確認するシート名
const column1 = 1; // A列
const column2 = 2; // B列

// 編集されたセルが対象のシートなら対象の列か確認
const range = e.range;
const sheet = range.getSheet();
const column = range.getColumn();
if (sheet.getName() === sheetName && [column1, column2].includes(column)) {
// 両方の列のセルに表示されている文字列を取得して確認
const value1 = range.offset(0, column1 - column).getDisplayValue();
const value2 = range.offset(0, column2 - column).getDisplayValue();
if (value1 && value2) { // どちらも書き込みがあるなら
// スクリプトエディタの「実行数」から確認できるログに記録
console.log('A列の値: ' + value1 + ' B列の値: ' + value2);
notifyGoogleChat(value1, value2);
}
}
}

/* Google Chat */
function notifyGoogleChat(value1, value2) {
const url = 'https://chat.googleapis.com/v1/spaces/AAAA1lEGLDE/messages?key=〇〇〇&token=〇〇〇'; // 手順1で取得したURLをセット
// 送信内容作成
let text = 'A列の値: ' + value1 + ' B列の値: ' + value2;
let message = { text }
let params = {
'method': 'POST',
'headers': {
'Content-Type': 'application/json; charset=UTF-8'
},
'payload': JSON.stringify(message)
}
// 送信
UrlFetchApp.fetch(url, params);
}

[atlanticSalmon]

私が試した限りでは、
A列にXXXと記入し、その後B列にYYYと書き込んでから数秒後に
A列の値: XXX B列の値: YYY
と通知が来ました。↑画像を添付しました。

/*
*　この関数をスプレッドシートの「編集時」のトリガーに設定します。
*　この関数はスクリプトエディタで「実行」をクリックしても
*　編集されたセルを取得できないのでエラーになります。
*/

スプレッドシート「編集時」のトリガーを設定しないと
A列とB列に書き込んでも通知されませんよ。

[tsubugai.taka]

ありがとうございます！！！！！！！
トリガーを設定するのを忘れてました！！！！！！通知送られるようになりました！！！！
最後まで見捨てないでくださり本当にありがとうございました！！！！！！
ちなみになんですが、通知が 2つ届いてしまうのですが、1つだけにするのは条件を2列にしてるので仕方ないでしょうか？💦

[atlanticSalmon]

本来の動作は、

A列とB列を順に記入した時は、
A列への記入で起動したスクリプトは、A列は記入は確認できるがB列の記入がないので
通知はしないで終了します。
その後、B列への記入で起動したスクリプトは、A列とB列の記入が確認できるので通知をする。

という動作になるのですが、

A列の記入後直ちに（1秒未満で）B列に記入がなされると、

A列への記入で起動したスクリプトがB列を確認した時点で既にB列に記入がなされているため、
A列への記入で起動したスクリプトも通知をしてしまうので、
その後B列への記入で起動したスクリプトの通知と重複して通知されてしまいます。

テストで短時間に短い文字を入力するとそうなります。

[tsubugai.taka]

そういうことだったんですね！！！
A列とB列の項目がデータ規則を選択するだけの項目だったので 2つになったんだとわかりました！！
atlanticSalmon さんは何で？を説明いただけるのでとてもわかりやすいです！
本当にありがとうございました！！！😭