👩‍💻ノンエンジニアがchatGPTを活用して休憩終了打刻リマインダーを作ってみた！✨実際の制作工程をステップバイステップで解説する📝

はじめに

こんにちは

エックスポイントワンのCSO（chief spreadsheet officer）の垣内です。

今回は、ノンエンジニアながらchatGPTを駆使して休憩終了打刻リマインダーを制作した経緯を、実際の工程をステップバイステップで詳細に解説していきます。

さて、本題に移る前に、このプロジェクトの背景と目的をご紹介いたします。

背景

• 当社では、休憩の開始と終了をSlackで打刻していますが、休憩終了の打刻を忘れることがしばしばありました。
• また、メンバーにより休憩開始の時刻が異なることがあります。

目的

• それぞれのメンバーが休憩を開始してから一定時間後、そのメンバーに対してSlackでリマインドを送信する機能を実装する。

これから、実際にchatGPTとのやり取りを通じて、上記の目的達成に向けた取り組みを時系列に沿って紹介してまいります。私の発言（プロンプト）、chatGPTの返答ともに必要な部分のみを抜粋して掲載する形を取ります。その点、ご理解いただけますと幸いです。

すごいな～と思った点（予告編）

本編がかなり長くなってしまったため、今回の取り組みで凄いなと思った点についていくつか例示いたします。

エラーメッセージを渡したら・・・

エラーについて教えてくれるのかなと思いますよね、普通そうだと思います。
今回、エラーメッセージを雑に渡して「これなに？」みたいな終わってる聞き方をしたシーンがあったのですが、その時に「これはこういうエラーで、おそらくこういう背景によって起こっているので、以下のような修正で直ると思います」というところまでノンストップで駆け抜けてくれました。ほんとに、気が利くエンジニアと喋ってる？っていう体験でした。

botの設定してないのに

incoming webhookをつかってslackにメッセージを送っていたのですが、このbotの表示をまだ触っていないにも関わらず適切なbot名・アイコン（アイコンまで！）になっていてめちゃくちゃ驚いたのですが、メッセージを送るメソッドの中でpayloadで適切に設定してくれているからでした。これはほんとに最初何が起こったのかわからなかったです。

本編

chatGPTとの対話は引用形式で示されます。

私：slack上での休憩打刻をoutgoing webhookでGASに飛ばして、x分後に休憩終了のリマインドをslack上で当人にメンションする動作のGAS上で動作するサンプルプログラムを出力してください

chatGPT

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var text = data.text;

  if(text === '休憩開始') {
    var userName = data.user_name;
    Utilities.sleep(1000 * 60 * X); // X is the time in minutes. Replace X with the time you want to wait before sending the reminder.
    postMessage(userName);
  }

  return ContentService.createTextOutput();
}

function postMessage(userName) {
  var url = 'https://slack.com/api/chat.postMessage';
  var payload = {
    'channel' : '@' + userName,
    'text' : '<@' + userName + '> 休憩時間が終了しました。作業を再開してください。',
    'as_user' : false,
  };

  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Bearer SLACK_API_TOKEN'
    },
    'payload' : JSON.stringify(payload)
  };

  UrlFetchApp.fetch(url, options);
}

よく見るとわかるのですが、sleepで実装してるのが笑えます。
一旦置いといて（この時点では気づいていなかった）次

私：スプレッドシートを疑似的なDBとして扱い、リマインダの時刻より前に休憩終了打刻があった場合にリマインダを解除するサンプルプログラムを示してください

chatGPT：

var SPREADSHEET_ID = "Your_Spreadsheet_ID";
var SHEET_NAME = "Your_Sheet_Name";
var SLACK_API_TOKEN = "Your_Slack_API_Token";
var REMINDER_TIME = 10; // 10 minutes for example

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var text = data.text;
  var userName = data.user_name;

  var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);

  if(text === '休憩開始') {
    var date = new Date();
    var startTime = date.getTime();
    sheet.appendRow([userName, date, startTime, '']);
    Utilities.sleep(REMINDER_TIME * 60 * 1000); // Reminder Time in ms

    var lastRow = sheet.getLastRow();
    var breakData = sheet.getRange(lastRow, 1, 1, 4).getValues()[0];

    if(breakData[0] == userName && breakData[3] == '') {
      postMessage(userName);
    }
  }

  if(text === '休憩終了') {
    var lastRow = sheet.getLastRow();
    var breakData = sheet.getRange(lastRow, 1, 1, 4).getValues()[0];

    if(breakData[0] == userName && breakData[3] == '') {
      var date = new Date();
      sheet.getRange(lastRow, 4).setValue(date);
    }
  }

  return ContentService.createTextOutput();
}

function postMessage(userName) {
  var url = 'https://slack.com/api/chat.postMessage';
  var payload = {
    'channel' : '@' + userName,
    'text' : '<@' + userName + '> 休憩時間が終了しました。作業を再開してください。',
    'as_user' : false,
  };

  var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Bearer ' + SLACK_API_TOKEN
    },
    'payload' : JSON.stringify(payload)
  };

  UrlFetchApp.fetch(url, options);
}

かなりツッコミどころ満載な感じではございますが、続けてまいります

私：休憩開始および完了は別のサービスに紐づいたbotにメンションし、"bi"を宣言し開始し、"bo"を宣言して完了します。つまり、biまたはboが文中に含まれているかで処理を分岐させる必要があります。
この点について修正をお願いいたします

chatGPT：指定したキーワードが含まれるかどうかで処理を分岐させるために、indexOf関数を使用してメッセージテキストを確認することができます。

（抜粋）

  if(text.indexOf('bi') !== -1) {
    var date = new Date();
    var startTime = date.getTime();
    sheet.appendRow([userName, date, startTime, '']);
    Utilities.sleep(REMINDER_TIME * 60 * 1000); // Reminder Time in ms

    var lastRow = sheet.getLastRow();
    var breakData = sheet.getRange(lastRow, 1, 1, 4).getValues()[0];

    if(breakData[0] == userName && breakData[3] == '') {
      postMessage(userName);
    }
  }

  if(text.indexOf('bo') !== -1) {
    var lastRow = sheet.getLastRow();
    var breakData = sheet.getRange(lastRow, 1, 1, 4).getValues()[0];

    if(breakData[0] == userName && breakData[3] == '') {
      var date = new Date();
      sheet.getRange(lastRow, 4).setValue(date);
    }
  }

  return ContentService.createTextOutput();
}

実はこの時点で、GASを実行しても失敗していましたので、原因を探るために

私：要所要所にconsole.log()で適切な値をコンソールに出力するように変更してください

こういう雑な指示ができるのがいいですね。chatGPTが本当にありがたいと思うのはこういう瞬間です。

chatGPT：Google Apps Scriptではconsole.log()の代わりにLogger.log()が利用可能です。

（抜粋）

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var text = data.text;
  var userName = data.user_name;
  
  Logger.log('Received text: ' + text + ', from user: ' + userName);

別にconsole.log()も使えるんですが、、、chatGPT大先生がこっちを採用するなら従いましょう。
しかし、これではエラーの調査ができませんでした。
調べましたら、外部からdoPost()を呼び出したときはログ出力されないとのこと。。。

外部からのHTTPリクエストで呼び出されるdoPost関数は、これらのログ出力メソッドを使ってもログが出力されません。

そうですか。。。ということで、動作重くなるのでやりたくないですが、都度スプレッドシートにログを書き出すために

私：Loggerのかわりに、DEBUGという名前のシートに都度ログを書き出すように変更お願いします

chatGPT：

（抜粋）

var SPREADSHEET_ID = "Your_Spreadsheet_ID";
var SHEET_NAME = "Your_Sheet_Name";
var DEBUG_SHEET_NAME = "DEBUG";
var SLACK_API_TOKEN = "Your_Slack_API_Token";
var REMINDER_TIME = 10; // 10 minutes for example

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var text = data.text;
  var userName = data.user_name;
  
  writeLog('Received text: ' + text + ', from user: ' + userName);

（中略）

function writeLog(message) {
  var date = new Date();
  var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(DEBUG_SHEET_NAME);
  sheet.appendRow([date, message]);
}

Logger.log()がwriteLog()に適切に置き換えられました。最高！
そうしまして、手元でちょっと試していましたらそもそも最初の

  var data = JSON.parse(e.postData.contents);
  var text = data.text;
  var userName = data.user_name;

この時点で何らかのエラーが起きていて処理が中断していることがわかりましたので、

私：以下の部分で何らかのエラーが起きているようなのでtryで囲って、catchしたエラーを同様にDEBUGシートにwrhteLogするようにしてください
ー
var data = JSON.parse(e.postData.contents);
var text = data.text;
var userName = data.user_name;

chatGPT：

（抜粋）

  try {
    data = JSON.parse(e.postData.contents);
    text = data.text;
    userName = data.user_name;
  } catch (error) {
    writeLog('Error occurred: ' + error.message);
    return ContentService.createTextOutput();
  }

おもいっきりタイポしていますが DEBUGシートにwrhteLogするように chatGPTさんは当たり前のように「はいはいwriteLog()すればいいのね」と確認もせずにやってくれてます。
すると
Error occurred: Unexpected token 'o', "token=Y6pvP"... is not valid JSON
というエラーが表示されましたので、雑にききます。
ここが今回開発していてchatGPTすげ～～～となった箇所です。

私：どゆ意味？
Error occurred: Unexpected token 'o', "token=Y6pvP"... is not valid JSON

chatGPT：このエラーメッセージは、JSONの解析に失敗したことを示しています。具体的には、JSON.parse()メソッドが呼び出された際に、無効なJSONデータが渡され、パース処理中に予期しないトークン 'o' が検出されたという内容です。

"token=Y6pvP"... is not valid JSON という部分が特に重要で、これは渡されたデータがJSON形式ではなく、おそらくクエリパラメータ形式（key=value）であることを示しています。

Google Apps ScriptでSlackのOutgoing Webhooksを使用している場合、送信されるデータはクエリパラメータ形式であることが一般的です。ですので、JSON.parse()を使用する代わりに、データを適切に処理する必要があります。

以下は、修正したサンプルコードの一部です。e.parameter を使用してクエリパラメータを受け取ります。

と、エラーメッセージを渡すと、エラーの意味だけでなく、そのエラーが起こった背景を類推し、それに対する修正コード案を提示してくれました。
この一行震えませんか？感動しました
"token=Y6pvP"... is not valid JSON という部分が特に重要で、これは渡されたデータがJSON形式ではなく、おそらくクエリパラメータ形式（key=value）であることを示しています。

function doPost(e) {
  var text = e.parameter.text;
  var userName = e.parameter.user_name;

  try {
    writeLog('Received text: ' + text + ', from user: ' + userName);
  } catch (error) {
    writeLog('Error occurred: ' + error.message);
    return ContentService.createTextOutput();
  }

  // ... 以下は以前と同様
}

この修正でいったんエラーで処理が失敗することはなくなったので、実際のロジックの実装にうつってまいります。

私：リマインダーまでの時間をsleepで実装するのはgasの実行時間の制限という問題と、実行中に休憩終了打刻があったときにその情報を受け取れないという問題があるので、scriptAppオブジェクトのtriggerクラスを用いて実装できますか？

chatGPT：はい、それは可能です。ただし、一つのスクリプトで多くのトリガーを作成する場合、Google Apps Scriptのトリガーには一定の制限がありますので注意が必要です。

以下に、休憩開始時にリマインダートリガーを作成し、休憩終了時にそのトリガーを削除するサンプルコードを示します。このコードでは、createReminderTrigger関数とdeleteReminderTrigger関数を新しく定義しています。

（抜粋）

  if(text.indexOf('bi') !== -1) {
    var date = new Date();
    var startTime = date.getTime();
    sheet.appendRow([userName, date, startTime, '']);
    writeLog('Break start logged for user: ' + userName);

    // Create a reminder trigger
    createReminderTrigger(userName);
  }

  if(text.indexOf('bo') !== -1) {
    var lastRow = sheet.getLastRow();
    var breakData = sheet.getRange(lastRow, 1, 1, 4).getValues()[0];

    if(breakData[0] == userName && breakData[3] == '') {
      var date = new Date();
      sheet.getRange(lastRow, 4).setValue(date);
      writeLog('Break end logged for user: ' + userName);

      // Delete the reminder trigger
      deleteReminderTrigger(userName);
    }
  }

（中略）

function createReminderTrigger(userName) {
  ScriptApp.newTrigger('postReminder')
    .timeBased()
    .after(10 * 60 * 1000) // 10 minutes
    .create();
  
  writeLog('Reminder trigger created for user: ' + userName);
}

function deleteReminderTrigger(userName) {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === 'postReminder') {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
  
  writeLog('Reminder trigger deleted for user: ' + userName);
}

function postReminder() {
  var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);
  var lastRow = sheet.getLastRow();
  var breakData = sheet.getRange(lastRow, 1, 1, 4).getValues()[0];
  var userName = breakData[0];

  if(breakData[3] == '') {
    postMessage(userName);
  }
}

受け取ったtextにbi（休憩開始の宣言）が含まれていたらcreateReminderTrigger()を、bo（休憩完了の宣言）が含まれていたらdeleteReminderTrigger()を呼ばれるように修正され、尚且つそれぞれの関数も適切に定義されていることがわかります。この修正でtriggerが発火したときに呼ばれるpostReminder()も必要になっているので定義されています。

しかし、ここからやや迷走します。具体的には、うえの修正案コードを返信されている最中に長文であるがゆえにchatGPTの発言が途切れたので、いつものように「つづき」をリクエストしたところ、

chatGPT：あ、すみませんが、それぞれのトリガーをユーザー名に紐づける方法を考えていなかったようです。

と言い、再度修正案のコードを送ってきました。
結論から言うとこれも複数ユーザーへの対応が不完全だったため、このあと

私：リマインダーの分は当初のあなたの提案通り
REMINDER_TIME
という定数で定義しているので、そのように直前のコードの修正をお願いいたします

という小変更を挟みつつ

私：今試験をしたのですが2名のユーザーが順番に休憩開始（bi）すると、それぞれのリマインダーのメンションがいずれも後から登録した方に対してなされました

私：Aさん、Bさんの順序でbiしたにも関わらず、リマインダーがBさん、Aさんの順にメンションされました

と複数回chatGPTとやり取りしましたが、chatGPTのコードがどんどんよくわからなくなっていき、正直ここでは一度複数人のtriggerを適切に管理することは難しそうだと感じ、心が折れかけていました。
ここで、スプレッドシートを疑似的なDBとして休憩中の方とその休憩開始時刻を管理し、triggerされるたびに休憩開始時刻を走査して必要な行にはリマインダーを送るというアイディアが浮かんだので、

私：トリガーが発火されたときの動作を、DB全体を確認し、作成日時からREMINDER_TIMEが経過するものすべてに適切な相手にpostMessageし、postMessageしたリマインダーにはDB上でそれを完了したことがわかるような列を設け、そこで管理する（もしくは当該行は削除する）という動作のアイディアについてはいかがでしょうか？コーディングする前にこのアイディアの長所と短所を提示してください

と、chatGPTに意見を求めました。
即座にコーディングされるといいも悪いもわからないため、このように一度chatGPTと議論することは結果的にはいいアプローチだったと思います。

chatGPT：あなたの提案はトリガーが発火された時に、Google スプレッドシート（DB）をスキャンし、休憩開始から REMINDER_TIME が経過したユーザーすべてに対して postMessage を行うというものです。そして、リマインダーが送信された後、その情報を記録または行を削除することで管理します。

このアプローチの長所と短所は以下の通りです。

長所:

1. 単純さ: 特定のユーザーを特定のトリガーに関連付ける必要がなく、コードが単純になります。
2. 順序の正確さ: すべてのリマインダーは、ユーザーが休憩を開始した順に正確に送信されます。
3. 複数のリマインダーの同時管理: このアプローチでは、リマインダーが経過した全てのユーザーに対して一度にリマインダーを送信します。これにより、複数のユーザーがほぼ同時に休憩を開始した場合でも、正確にリマインダーを送信することが可能です。

短所:

1. 効率: トリガーが発火するたびに、DB全体をスキャンする必要があります。これにより、DBのサイズが大きくなるとパフォーマンスが低下する可能性があります。
2. リマインダーのタイミングの精度: トリガーが定期的に発火する設定（例えば毎分）に依存するため、リマインダーのタイミングは完全に正確ではない可能性があります。例えば、REMINDER_TIMEが10分であっても、トリガーが9分59秒または10分1秒で発火する可能性があります。
3. トリガーの制限: Google Apps Scriptにはトリガーの作成と削除の回数に制限があります。トリガーを頻繁に作成・削除すると、これらの制限に達する可能性があります。

長所はさておき、短所については真剣に検討する必要があると感じました。
この中で1については都度用済みの行を削除することでカバーできると感じました。2については社内ツールなので1分程度の誤差は許容することにしました。
しかし、問題は3で、これは看過できない問題だったため、まず以下のようにして懸念点について確認しました。

私：短所の1については、リマインド済の行を都度削除することでカバーできると思います。
短所の3について、以下のような資料がありました
-
トリガー制限
処理の自動化のために時間等でトリガーを設定することも多いかと思いますが、このトリガーにも制限があります。

対象 無料アカウント Google Workspace
トリガーの総実行時間 90 分 / 日 6 時間 / 日
トリガー数 20 / ユーザー / スクリプト 20 / ユーザー / スクリプト
-
このトリガー数制限（20/ユーザー/スクリプト）には発火済で無効になったトリガーは含まないのですよね？

chatGPT：はい、その通りです。Google Apps Scriptのトリガー数制限はアクティブな（有効な）トリガーに対してのみ適用されます。すでに発火済みで無効化されたトリガーや、手動またはスクリプトにより削除されたトリガーは制限数には含まれません。

したがって、各ユーザーの休憩開始時にトリガーを作成し、リマインダー送信後にそのトリガーを削除するというアプローチを取る場合でも、1つのスクリプト内で同時にアクティブなトリガーが20個を超えない限り、トリガー数制限には達しないということになります。

ただし、トリガーの作成と削除を頻繁に行うと、その操作自体がエラーを引き起こす可能性もあります。そのため、可能な限りトリガーの作成と削除の回数を最小限に抑えることが望ましいと言えます。

今この瞬間の社内では、トリガーが同時に20個まで使えるのであれば恐らく問題なく稼働するのですが、将来明らかに量的に不足するようになる選択は明らかにアンチパターンですので、以下のようなアイディアを考えました。

私：若干ロジックが複雑になりますが、アクティブなトリガーは常に1つという実装のアイディアが浮かびました、以下でそれを記述するので、評価をお願いします。

1. bi宣言をうけとり、doPostが実行される
2. DBをスキャンする
3. a. DB上のデータから現在アクティブなトリガーがないと判断される場合（都度削除方式であれば1行も有効なデータがない場合、フラグ式削除であれば有効な行がない場合）：REMINDER_TIME 後のトリガーが設定される
   b. アクティブなトリガーがあると判断される場合：トリガーは設定しない

ここまでの動作によって少なくとも1つ、恐らく最大でも1つの有効なトリガーが設定されています。それは最大でREMINDER_TIME 後であり、3bの場合はそれよりも短い時間後のトリガーである可能性があります。

4. トリガーによってリマインダーが実行される
5. 上のアイディアによりDBをスキャンして有効なリマインダーが全て実行され、論理/物理的にDBの行が削除される
6. a. DBスキャン時に「今回リマインダーを送る必要はないが、未来にリマインダーを送る必要がある」行があった場合にはそのリマインダーまでの時間（つまり設定時刻＋REMINDER_TIME 　と　現在時刻との差）をDBの各行について保持し、最も直近のリマインダーまでのトリガーを再度設定する
   b. DBスキャン時に今回リマインダーを送るもの以外に有効なデータ（未来にリマインダーを送るべきデータ）がない場合、トリガーを設定しない。

これを受けてchatGPT

chatGPT：あなたが提案しているロジックは、必要なときにのみトリガーを動的に作成し、アクティブなトリガーは常に1つだけに制限するというものです。これには以下のような長所と短所があります。

長所:

効率: 同時にアクティブなトリガーが1つだけであるため、Google Apps Scriptのトリガー制限を気にする必要がなくなります。

リソースの最小化: 休憩のリマインダーが必要なときだけトリガーが作成されるため、不必要なトリガーを作成・削除する手間がなくなります。

短所:

コードの複雑性: このアプローチでは、休憩開始時とリマインダー送信時の2つの異なるタイミングでトリガーを管理する必要があります。また、どのトリガーが次に必要かを決定するために、全ての行をスキャンして最も早いリマインダーを見つけるロジックが必要になります。

スキャンの頻度: ユーザーが休憩を開始したり終了したりするたび、そしてリマインダーが送信されるたびに全ての行をスキャンする必要があります。これは大きなデータベースの場合、パフォーマンスに影響を与える可能性があります。

全体として、提案されたアプローチは効果的であり、あなたの必要性によく合っていると思われます。ただし、上記のような短所については注意して設計と実装を進めてください。

と、悪くなさそうなのでこの方針で行くことにしました。
しかし、この後の進め方についてもやや課題がありました。

私：動作可能なロジックを設計してください。量が多くなると思われるので、先ずは定数の宣言部分のコードのみと、必要な関数のリストを提示してください

実際には先ほどの自分の発言を再度掲載し「～これが動作可能な～」と続けて上の発言になっています。
これを受けたchatGPTは以下のように返事をしてきます。

chatGPT：

// Slackにメッセージを投稿するためのURL
var SLACK_WEBHOOK_URL = 'YOUR_SLACK_WEBHOOK_URL';

// リマインダーまでの時間（単位: ミリ秒）
var REMINDER_TIME = 15 * 60 * 1000;

// スプレッドシートのID
var SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID';

// スプレッドシートのシート名
var SHEET_NAME = 'Break';

// ログを出力するシート名
var LOG_SHEET_NAME = 'DEBUG';

// スプレッドシートのアクセス
var sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(SHEET_NAME);

// ログ出力用シートのアクセス
var logSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(LOG_SHEET_NAME);

// 休憩開始のキーワード
var BREAK_START_KEYWORD = 'bi';

// 休憩終了のキーワード
var BREAK_END_KEYWORD = 'bo';

これに続き、以下の関数を実装します：

doPost(e) - Slackからのリクエストを受け取り、それに対応するアクションを行います。

setReminderTrigger(userName, timeToTrigger) - 指定された時間後にリマインダーのトリガーを設定します。

deleteTriggers() - すべてのリマインダートリガーを削除します。

scanDatabase() - データベースをスキャンして、未来のリマインダーが必要な行があるかチェックし、その情報を返します。

sendReminder() - トリガーの発火により実行され、リマインダーを送り、データベースを更新します。

writeLog(message) - DEBUGシートにログを書き込みます。

各関数の詳細な実装は具体的な要件とロジックによりますが、上記の骨子をもとに各関数を詳細に設計し、それを組み合わせて全体のシステムを作り上げることができます。

なるほどね、まあなんか悪くない気がしますと。
REMINDER_TIMEの設定がわかりづらかったので、あくまで設定は分で、ミリ秒への変換は機械的にやってもらうようにしました。

私：まず定数部について、REMINDER_TIMEは分で入力できるようにし、システム的にミリ秒の定数を他に準備し、これは計算によって求めるようにしましょう

chatGPT：

（抜粋）

// リマインダーまでの時間（単位: 分）
var REMINDER_TIME_MINUTES = 15;

// リマインダーまでの時間をミリ秒単位で計算（1分 = 60,000ミリ秒）
var REMINDER_TIME = REMINDER_TIME_MINUTES * 60 * 1000;

続いてシート名などの管理のために

私：DBのシートに関する変数名はDBシートに関するものだと変数名から理解できるようにDB_やdbを変数名のルールに則り合理的に付記してください。また実際のシート名はDBですのでこれも変更願います

chatGPT：

（抜粋）

// スプレッドシートのDBシート名
var DB_SHEET_NAME = 'DB';

// ログを出力するシート名
var LOG_SHEET_NAME = 'DEBUG';

// スプレッドシートのDBシートのアクセス
var dbSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(DB_SHEET_NAME);

// ログ出力用シートのアクセス
var logSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName(LOG_SHEET_NAME);

さて、ここから実際に必要な関数を実装していこうと考えていました。具体的には、先ほどchatGPTがリスト化してくれた以下の関数

これに続き、以下の関数を実装します：

doPost(e) - Slackからのリクエストを受け取り、それに対応するアクションを行います。

setReminderTrigger(userName, timeToTrigger) - 指定された時間後にリマインダーのトリガーを設定します。

deleteTriggers() - すべてのリマインダートリガーを削除します。

scanDatabase() - データベースをスキャンして、未来のリマインダーが必要な行があるかチェックし、その情報を返します。

sendReminder() - トリガーの発火により実行され、リマインダーを送り、データベースを更新します。

writeLog(message) - DEBUGシートにログを書き込みます。

各関数の詳細な実装は具体的な要件とロジックによりますが、上記の骨子をもとに各関数を詳細に設計し、それを組み合わせて全体のシステムを作り上げることができます。

これらを順に記述してもらうと思ったのですが、「続きを書いてください」と続けていると、この時提示した関数とは違う関数で実装し始めたので、ここらへんから今現在のコードを見せて、"これをこうしてほしい"というふうにリクエストする形のプロンプトの投げ方に変更しました。これはかなり有効だったと思います。

私：以下のような動作が達成されるコードの記述をお願いいたします。

1. bi宣言をうけとり、doPostが実行される
2. DBをスキャンする
   3a. DB上のデータから現在アクティブなトリガーがないと判断される場合（都度削除方式であれば1行も有効なデータがない場合、フラグ式削除であれば有効な行がない場合）：REMINDER_TIME 後のトリガーが設定される
   3b. アクティブなトリガーがあると判断される場合：トリガーは設定しない
   ーここまでの動作によって少なくとも1つ、恐らく最大でも1つの有効なトリガーが設定されています。それは最大でREMINDER_TIME 後であり、3bの場合はそれよりも短い時間後のトリガーである可能性があります。
3. トリガーによってリマインダーが実行される
4. 上のアイディアによりDBをスキャンして有効なリマインダーが全て実行され、論理/物理的にDBの行が削除される
   6a. DBスキャン時に「今回リマインダーを送る必要はないが、未来にリマインダーを送る必要がある」行があった場合にはそのリマインダーまでの時間（つまり設定時刻＋REMINDER_TIME 　と　現在時刻との差）をDBの各行について保持し、最も直近のリマインダーまでのトリガーを再度設定する
   6b. DBスキャン時に今回リマインダーを送るもの以外に有効なデータ（未来にリマインダーを送るべきデータ）がない場合、トリガーを設定しない。

次の、定数宣言とdoPostに続けて記述してください

// Slackにメッセージを投稿するためのURL
var SLACK_WEBHOOK_URL = 'https://hooks.slack.com/services/xxx';

// リマインダーまでの時間（単位: 分）
var REMINDER_TIME_MINUTES = 1;

// リマインダーまでの時間をミリ秒単位で計算（1分

（略）

chatGPT：

（抜粋）

      if (now - startTime >= REMINDER_TIME) {
        // リマインダーを送信する処理をここに書く
        dbSheet.deleteRow(i + 1);
      } else {
        var timeLeft = startTime + REMINDER_TIME - now;
        if (nextReminderTime === null || timeLeft < nextReminderTime) {
          nextReminderTime = timeLeft;
        }
      }

それを書くのもあなたなんだよ

私：リマインダを送信する処理はpostMessageで外だししてください

chatGPT：

// Slackにメッセージを投稿する
function postMessage(userName) {
  writeLog("postMessage started");
  try {
    var payload = {
      "text": "リマインダー： " + userName + "さん、休憩終了の時間です。",
      "username": "reminder-bot",
      "icon_emoji": ":alarm_clock:"
    };

    var options = {
      "method" : "post",
      "payload" : JSON.stringify(payload)
    };

    UrlFetchApp.fetch(SLACK_WEBHOOK_URL, options);
  } catch (error) {
    writeLog(`Error occurred in postMessage: ${error}`);
  }
}

からの

私：先ほどの「//リマインダーを送信する処理をここに書く」の部分をpostmessage関数を用いて適切な形にリライトしてください、前後3行ほどの表示で結構です

これの返事がまたちょっとおかしかった（既存のコードと齟齬ってた）ので改めて

私：ちょっと記述がさきほどと違うので確認です。さきほどあなたが提案してくださった以下のsendReminderをもとにpostmessageを用いた記述に修正してください
ー

// リマインダーを送信し、次のリマインダーのトリガーを設定する
function sendReminder() {
  writeLog("sendReminder started");
  try {
    var now = new Date().getTime();
    var data = dbSheet.getDataRange().getValues();
    var nextReminderTime = null;

    for (var i = 0; i < data.length; i++) {
      var userName = data[i][0];
      var startTime = new Date(data[i][1]).getTime();

      if (now - startTime >= REMINDER_TIME) {
        // リマインダーを送信する処理をここに書く
        dbSheet.deleteRow(i + 1);
      } else {
        var timeLeft = startTime + REMINDER_TIME - now;
        if (nextReminderTime === null || timeLeft < nextReminderTime) {
          nextReminderTime = timeLeft;
        }
      }
    }

    if (nextReminderTime !== null) {
      ScriptApp.newTrigger("sendReminder")
        .timeBased()
        .after(nextReminderTime)
        .create();
    }

  } catch (error) {
    writeLog(`Error occurred in sendReminder: ${error}`);
  }
}

と、やはり実際のコードを提示してそれを修正してもらうようにしました。
またここで、以下の箇所で、forの中で各行毎に行を削除していたので

私：各行をforでスキャンしている最中に直接行を削除するとforのスキャンが破綻しませんか？

chatGPT：その通りで、各行をスキャンしている途中で行を削除すると、削除後の行のインデックスが変わり、それが次のループ処理に影響を与えます。したがって、通常は行を削除するときは、最後の行から逆順に削除するか、または削除する行を別の配列に格納してからループ終了後に削除するのが一般的です。

以下のように修正します。

// リマインダーを送信し、次のリマインダーのトリガーを設定する
function sendReminder() {
  writeLog("sendReminder started");
  try {
    var now = new Date().getTime();
    var data = dbSheet.getDataRange().getValues();
    var nextReminderTime = null;
    var rowsToDelete = [];

    for (var i = 0; i < data.length; i++) {
      var userName = data[i][0];
      var startTime = new Date(data[i][1]).getTime();

      if (now - startTime >= REMINDER_TIME) {
        // リマインダーを送信
        postMessage(userName);
        // 削除する行番号を記録
        rowsToDelete.push(i + 1);
      } else {
        var timeLeft = startTime + REMINDER_TIME - now;
        if (nextReminderTime === null || timeLeft < nextReminderTime) {
          nextReminderTime = timeLeft;
        }
      }
    }

    // 記録した行を削除
    for (var i = rowsToDelete.length - 1; i >= 0; i--) {
      dbSheet.deleteRow(rowsToDelete[i]);
    }

    if (nextReminderTime !== null) {
      ScriptApp.newTrigger("sendReminder")
        .timeBased()
        .after(nextReminderTime)
        .create();
    }

  } catch (error) {
    writeLog(`Error occurred in sendReminder: ${error}`);
  }
}

これで、forループが破綻することなく、正確に行を削除することができます。

ありがとうございます。
というわけで続いて

私：ありがとうございます、それではwriteLog関数を記述してください

chatGPT：

// ログを出力する関数
function writeLog(logText) {
  try {
    var currentTime = new Date();
    var logOutput = currentTime + ': ' + logText;
    // スプレッドシートの指定されたシートにログを追加
    logSheet.appendRow([logOutput]);
  } catch (error) {
    // 何らかの理由でログの書き込みに失敗した場合
    Logger.log('Failed to write log: ' + error);
  }
}

として、当初のようにスプレッドシートにログを出力するようにしました。
ここまででとりあえず処理は一通り記述できたので

私：現在以下のようになっています。これで、希望した動作が達成されるかリファクタリングをお願いします

（現状のコードをchatGPTに渡す）

chatGPT：このコードは非常に良くできており、基本的な機能は正常に動作すると思います。ただし、以下の点を少し改良することでよりロバストなコードにできます：

• postMessage関数で投稿の成功・失敗をログ出力するようにする。
• isActiveTriggerExists関数で、プロジェクトにある全てのトリガーをスキャンしていますが、getProjectTriggersは一定量の処理時間を消費します。トリガーの存在をチェックするのは、新たにトリガーを設定する際だけで良いので、startBreak関数でのみ実行するようにする。
• 現在、doPost内で例外が発生すると、それがログに記録されるだけで、Slackへの応答は行われません。このままでは、エラーが発生した場合にユーザーは反応を見ることができません。したがって、エラーが発生した場合にも何らかの応答を返すようにdoPostを修正すると良いでしょう。

以下に改良版のコードを示します：

// ...

function doPost(e) {
  writeLog("doPost started");
  try {
    // ...
  } catch (error) {
    writeLog(`Error occurred in doPost: ${error}`);
    return ContentService.createTextOutput(JSON.stringify({ 'text': 'エラーが発生しました: ' + error }));
  }
}

// ...

function sendReminder() {
  writeLog("sendReminder started");
  try {
    // ...
    // 記録した行を削除
    for (var i = rowsToDelete.length - 1; i >= 0; i--) {
      dbSheet.deleteRow(rowsToDelete[i]);
      writeLog(`Deleted row ${rowsToDelete[i]} in sendReminder`);
    }
    // ...
  } catch (error) {
    writeLog(`Error occurred in sendReminder: ${error}`);
  }
}

// ...

// Slackにメッセージを投稿する
function postMessage(userName) {
  writeLog("postMessage started");
  try {
    // ...
    var response = UrlFetchApp.fetch(SLACK_WEBHOOK_URL, options);
    if (response.getResponseCode() === 200) {
      writeLog(`Successfully sent message to ${userName}`);
    } else {
      writeLog(`Failed to send message to ${userName}. Response code: ${response.getResponseCode()}`);
    }
  } catch (error) {
    writeLog(`Error occurred in postMessage: ${error}`);
  }
}

chatGPT先生からの熱い自画自賛を受けつつ、こちらのコードは、doPost(e)の引数'e'をJSON形式とみなしてパースしようとしています。しかし、すでにこの'e'がJSON形式ではないということは結論づけられています。その点を指摘したのですが、chatGPTは思うようにコードを修正してくれませんでした。結果的に、この部分は手作業で修正することにしました。

また、現在のコードでは、特定の文言が必要な箇所ごとに直接記述されており、これでは保守性が損なわれます。そこで、これらの文言を定数化することを考えました。しかし、一旦その方向で思考を進めると、もしかしたらスプレッドシートで直接設定できる方が便利かもしれないと考えるようになりました。

私：これらのスクリプトの中にはslackで返却する文字列などがベタで記述されている箇所が多くあります。
最終的にそれらの定義はスプレッドシート上でできるようにしたいです。
そのために以下の順で処理もしくはスプレッドシート上での作業を定義してください

1. 文言定義するシート名を考えてください 私はそれを採用します
2. A列に文言の見出し（人間が見てどんな文言を考えればいいのかわかるように）を考え、B列に現状の文言をテーブル形式で示してください
3. それらの文言（2で示す現状の文言）は動的に定義される箇所が多くあるので、そういった箇所は変数化したうえでスクリプトの中で適切に置換するような処理を挿入してください。どこの処理をどのように変更するか示してください。

chat GPT：
このシナリオでは、スプレッドシートを用いて外部からSlackに送信するメッセージの文言を管理します。以下に要件に基づいてステップを定義します。

文言を定義するシート名を"MESSAGE_DEFINITIONS"とします。

"MESSAGE_DEFINITIONS"シートのA列には文言の見出しを、B列には現状の文言を設定します。具体的なテーブルは以下のようになります：

A (見出し)	B (現状の文言)
Reminder Message	<@{username}> 休憩終了の時間です。
Error Message	エラーが発生しました: {error}
Bot Username	reminder-bot
Bot Icon	:alarm_clock:

上記の文言をスクリプト内で動的に読み込んで使用するようにします。これには、以下のような手順が必要です：

スプレッドシートからの文言の読み込みに対応する新たな関数を作成します。例えば、getDefinedMessage(key)という関数を作ります。この関数は、引数として見出し（上記のテーブルのA列）を取り、対応する文言（上記のテーブルのB列）を返します。

postMessage関数内でSlackに送信するメッセージの文言をgetDefinedMessage関数を用いて読み込むように変更します。また、ユーザー名とエラーメッセージの部分はgetDefinedMessage関数から取得した文言に対して文字列置換を行うことで埋め込みます。

以下には、これらの手順を反映したgetDefinedMessage関数と修正後のpostMessage関数の例を示します：

（抜粋）

// 文言定義シートのアクセス
var messageSheet = SpreadsheetApp.openById(SPREADSHEET_ID).getSheetByName("MESSAGE_DEFINITIONS");

// 文言定義シートから文言を取得する関数
function getDefinedMessage(key) {
  var data = messageSheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === key) {
      return data[i][1];
    }
  }
  return null; // 見出しに対応する文言が見つからない場合はnullを返す
}

// Slackにメッセージを投稿する
function postMessage(userName) {
  writeLog("postMessage started");
  try {
    var messageTemplate = getDefinedMessage("Reminder Message");
    var messageText = messageTemplate.replace("{username}", userName);

この後も細々と変更を加えましたが、かなり細かい点ですので割愛いたします。

学び

かなり冗長なブログエントリになっていて恐縮です。
全体を通じて色々と学びがありました。

基本的な知識が必要な場面も多い

今回のプロジェクトではリマインダー機能の実装に際しsleep()の使用を試みたり、データ形式がJSONでないと一度自己解決したものの、その知識をなぜか忘れ、二度目に問題が出た際には修正リクエストがJSONをパースしようとするものでした。また、各行を上からforループで回しながら直接行を削除するようなケースもありました。

これらの問題は、問題が発生した際やエラーが発生した際に適切な修正を依頼すれば、徐々に解決していくことが可能です。しかし、これらの問題を解決するための基本的なプログラミングの知識があることは、開発の効率性を高める上で強い武器になると感じました。

逆に驚くほどスマートに回答・修正・記述してもらえるケースもある

これは何度でもいいたいのですが、以下のコミュニケーションは本当にまるで優れた人間のエンジニアと会話しているかのように「汲み取られ・シームレスに解決まで導いてもらえた」感覚を得られました。

私：どゆ意味？
Error occurred: Unexpected token 'o', "token=Y6pvP"... is not valid JSON

chatGPT：このエラーメッセージは、JSONの解析に失敗したことを示しています。具体的には、JSON.parse()メソッドが呼び出された際に、無効なJSONデータが渡され、パース処理中に予期しないトークン 'o' が検出されたという内容です。

"token=Y6pvP"... is not valid JSON という部分が特に重要で、これは渡されたデータがJSON形式ではなく、おそらくクエリパラメータ形式（key=value）であることを示しています。

Google Apps ScriptでSlackのOutgoing Webhooksを使用している場合、送信されるデータはクエリパラメータ形式であることが一般的です。ですので、JSON.parse()を使用する代わりに、データを適切に処理する必要があります。

以下は、修正したサンプルコードの一部です。e.parameter を使用してクエリパラメータを受け取ります。

また、incoming webhookの設定でチャンネル内に表示されるbotの表示名やアイコンを設定できるのですが、今回特にそれをしていないにも関わらず適切な表示名とアイコンになっていて非常に驚きましたが、これもchatGPTがslackへのメッセージ送信のところで

      "username": "reminder-bot",
      "icon_emoji": ":alarm_clock:"

としれっと設定してくれていました。すごい

「過去のやりとりをもとに整合性をもって回答してくれる」ことを期待しない

長いスクリプトを書く際には、細かく関数に分割して作成することが必要となります。しかし、その際にはいくつかの課題が浮上します。例えば、一度必要な関数をリストアップしても、それぞれの関数を記述する過程で、元々リストには含まれていなかった関数が新たに現れることがあります。さらに、一度定義した関数がまるで未定義であるかのように再定義され、しかもその実装内容が前のものと異なる、という事態も発生します。これらは注意深く対処を行う必要がある問題点です。

こちらから渡したコードのリファクタリングや修正は強力

です。
基本的には都度こちらからコードを渡して、渡したコードについてどう修正してほしいかを指示していくやり方がもっとも効率がいいと感じました。

まとめ

色々と突っ込みどころはあったものの、最終的にはchatGPTの力を借りてデプロイしたツールが現在も問題なく稼働しています。そのことから、ツールの構築が合理的に行われれば、予期せぬバグの発生も少なくなるのではないかと感じています。

わずか1日弱で完成させることができたことは、単純に驚くべきことでした。確かに、優秀なエンジニアなら同じ期間で完成させることは可能だと思います。しかし、予期しない動作の修正が必要になったり、合理的でない構造が残ってしまったりすることは、現実の開発ではよくあることです。そういった点で、chatGPTの可能性を感じました。

このエントリが誰かのツール作成に一役買えれば、大変嬉しいです。