スプレッドシート上の指定列が入力された際、入力列の情報をチャットワークへ送信するスクリプト

Question

前提

GAS(GoogleAppsScript)について質問です。
社内で共有している受付シートです。
項目を入力していき、受付まで埋まりきったところでチャットワークへ送信される
スクリプトを作りたいと考えてます。

表がすべて埋まったら次のシートへ移って行き、5まで行ったら
シート1へ戻り、中身を削除してまた利用するというサイクルで運用しています。
単純にチャットワークへ送信できるところまでは確認できました。
細かい条件式などでご教示いただければ幸いです。

実現したいこと

1．指定の列（受付）が入力されたら、入力列の情報をチャットワークへ送信したい。
2．日付から内容（B～F)まで入力されている状態で受付（G)が入力されたら送信したい。

発生している問題

1．どのセルを編集しても情報が送信されてしまう。
2．セルを削除すると編集扱いになり空データが送信される。
3．日付表記がごちゃごちゃしてしまい分かりづらい。

該当のソースコード

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var lastRow = sheet.getLastRow();
  var token = PropertiesService.getScriptProperties().getProperty('CW_TOKEN');
   
  for(var i = 2; i <= lastRow; i++) {
    if(!sheet.getRange(i, 8).getValue()){ 
 
      var values = sheet.getRange(i, 1, 1, 7).getValues();     
      var body = '[info]'
      body += '日付：' + values[0][1] + '[hr]'; //day
      body += '時間：' + values[0][2] + '\n'; //time
      body += '氏名：' + values[0][3] + '\n'; //person
      body += '番号：' + values[0][4] + '\n'; //number
      body += '内容：' + values[0][5] + '\n'; //naiyou
      body += '受付：' + values[0][6] + '[/info]'; //tantou
     
      sendMessage(token, body);
      sheet.getRange(i, 8).setValue(true);
      break;
    }
  }
  
  if(i >= lastRow) {
    sheet.getRange(2, 4, lastRow - 1).clearContent();
  }
}
 
function sendMessage(token, body){
  var cw = ChatWorkClient.factory({token: token});
  cw.sendMessageToMyChat(body);
}

スクリプトについてはネットからの見様見真似です。
運用上、もっと別の式を構築すべきなのでしょうか。
どなたかご教示いただけると幸いです。
どうぞ宜しくお願いいたします。

Answer 1

• 前提

myFunctionに編集時トリガーでかけられている(他の人が編集したときどうすんの？)
運用ルールは守られている(具体的には、B列には日付が入っている。少なくとも受付は最後に記入される。送信済みのものは必ずTrueになっている)

function myFunction(e) {
  if(e["value"] === undefined) { return; } //削除時は何もしない
  const c = e.range.getColumn();
  if(c !== 7) { return; } //G列でなければ何もしない
  const r = e.range.getRow();
  const sheet = e.range.getSheet();
  const data = sheet.getRange(r,1,1,8).getValues()[0];
  if(data[7]) { return; } //H列がTRUEだったら何もしない。true以外のtruelyな文字列が入っていると誤動作する
  var message = '[info]';
//  message += '日付：' + prettyPrintDate(data[1]) + '[hr]'; //day
  message += '日付：' + data[1] + '[hr]'; //day
  message += '時間：' + data[2] + '\n'; //time
  message += '氏名：' + data[3] + '\n'; //person
  message += '番号：' + data[4] + '\n'; //number
  message += '内容：' + data[5] + '\n'; //naiyou
  message += '受付：' + data[6] + '[/info]'; //tantou

  const token = PropertiesService.getScriptProperties().getProperty('CW_TOKEN');
  sendMessage(token, message);
  sheet.getRange(r,8).setValue(true);
}
function prettyPrintDate(d) { //B列が空白とか日付っぽく見えるただの文字列だったらエラーになる
  return (d.getMonth() + 1) + "/" + d.getDay();
}

//質問からコピー
function sendMessage(token, body){
  var cw = ChatWorkClient.factory({token: token});
  cw.sendMessageToMyChat(body);
}

ここでclearContentをプログラム的にやるのは筋違いだと思うので、外しました。
(5行目を書くやいなや、送信して消すことは可能ですが、それだったらわざわざシートを5個も用意してヒストリ残している運用がただのアホなムダ手間ってことになっちゃうんで)

ここまで大幅な書き換えをするのは面倒なんですが、今のコードを直すほうが面倒なんで。あと、分かっていると思いますが、スクリプトエディタから「関数の実行」しても動かないので、シートで編集してくださいね。
最初にも書きましたが、他の人が書いたときにはトリガ発動しないから送信されない気がするよ。

Comments

[hbc]

papinianus様、早速のご回答、誠にありがとうございます。

複数人で共有し記入していく受付スプレッドシートですので、私だけでなく共有ユーザーの編集によるトリガー発動が目的です。どのように宣言すれば宜しいのでしょうか。。

プログラムの書き換え、ありがとうございました。
こちらをコピペしてテスト編集してみたのですが、TRUEが出ずエラーが起きてしまいました。
何か私の方で見落としている箇所がございますでしょうか。

更なるご回答を頂けると幸いです。

[papinianus]

どういうエラーか伺えますか？

他人にトリガーかける方法は私が知りたいくらいです。確実なのは全員が同じ関数をトリガー登録することです。

[hbc]

トリガーの件、承知致しました。では共有したスプレッドシートにそれぞれコードを貼り付けてトリガー登録させて試してみようと思います。

下記、エラーについてです。
Stackdriver のログ
エラー
ReferenceError: 「sendMessage」が定義されていません。 at myFunction(コード:18)

エラー
TypeError: undefined のメソッド「getMonth」を呼び出せません。 at prettyPrintDate(コード:22)

[papinianus]

sendMessageは質問にあるコードをそのまま再利用してください。
分かりにくいので回答にも足します。

prettyPrintDateでそういうエラーにはならないと思うのですが、B列は日付が入っていますよね？
ちょっとここキリがなくなるので、日付が望ましい表示にはならないかもしれませんが、回答をかえます。

[hbc]

書き換えありがとうございます。
申し訳ないですがA~Fまで入力された状態でGに入力してもTRUE確認できず、実行エラーとなってしまいました...。どこか私のほうで根本的な何かを間違えているのでしょうか。

エラー
ReferenceError: 「body」が定義されていません。 at myFunction(コード:19)

エラー
TypeError: undefined からプロパティ「value」を読み取れません。 at myFunction(コード:2)

エラー
TypeError: undefined のメソッド「getMonth」を呼び出せません。 at prettyPrintDate(コード:23)

エラー
指定された属性の値が無効です: Header:null at [unknown function](client:78) at [unknown function](client:120) at [unknown function](client:33) at sendMessage(コード:29)

[papinianus]

分かってるはずだと思ってたのですがもしかして関数の実行やデバッグをしていませんか？
編集時トリガーで動くものもprettyPrintもsendMessageもデバッグや関数の実行では必ずエラーになります。
最初のエラー文のみ対応しました。今のところ動かない原因としてbodyしか考えにくいので。

[hbc]

ありがとうございます。
チャットワークに送られてきたのですが
"[info]"
のみで他の情報が送信されませんでした。
あともう少しの所で申し訳ございません。

[papinianus]

constにしてました。varに変えました

[hbc]

ありがとうございます。
お陰様で求めていたものができました。
日付、時間表記については試行錯誤してみます！

[hbc]

日付、時間表記については、スプレッドシート側で表示形式を”書式なしテキスト”を選択することで運用することにしました。ありがとうございました。