見出し画像
Photo by snowdingo

Discrod+スプレッドシートで予定通知Botを作ってみた

crow

前回作ったDiscord用Botを使って、予定を自動的に通知させてみます。

作ったもの

スプレッドシートと連携し、以下の処理を自動で行うBotです。

  • メンバー全員が参加可能な時間の割り出し

  • Discordへのメンション付き通知

  • 活動がない日の通知スキップ

下準備

上記のスプレッドシートを開きコピーを作成。

拡張機能からApps Scriptを選択。

ウェブフックURLを下図1行目の部分に貼り付け。
（ウェブフックURLの取得は前回記事をご覧ください）

Discordの開発者タブ内にある開発者モードにチェックを入れる。

使い方

B1、B2セルには基本的な開始・終了時間を入力します。
これは〇と入力した場合に、基本時間として認識されます。
3行目D列以降にはユーザー名を入力しておきますが、Botの動作には無関係です。（人間用）
4行目D列以降にはUIDを入力します、UIDは各ユーザーへメンションを投げるときに使います。
UIDの取得はメンバーを右クリックしてユーザーのIDをコピーしてください。（上記開発者モードのチェックが入っていない場合、表示されません。）

C5セル以降には後述する関数を入力しています、内容としてはメンバー全員が参加可能な時間の割り出しを行っていてDiscordに出力される内容となります。
赤枠内は各メンバーに〇、×、参加時間を入力してもらうことでC列に実施時間として返ってきます。

=ARRAYFORMULA(
  IF(COUNTIF(INDIRECT("D" & ROW() & ":" & $C$1 & ROW()), "×")>0, "×",
    IF(COUNTBLANK(INDIRECT("D" & ROW() & ":" & $C$1 & ROW()))=COLUMNS(INDIRECT("D" & ROW() & ":" & $C$1 & ROW())), "",
      TEXT(
        MAX(
          IF(INDIRECT("D" & ROW() & ":" & $C$1 & ROW())="〇", $B$1, IF(OR(INDIRECT("D" & ROW() & ":" & $C$1 & ROW())="×", INDIRECT("D" & ROW() & ":" & $C$1 & ROW())=""), 0, VALUE(LEFT(INDIRECT("D" & ROW() & ":" & $C$1 & ROW()), FIND("-", INDIRECT("D" & ROW() & ":" & $C$1 & ROW()))-1))))
        ),
        "H:MM"
      ) & "-" & 
      TEXT(
        MIN(
          IF(INDIRECT("D" & ROW() & ":" & $C$1 & ROW())="〇", $B$2, IF(OR(INDIRECT("D" & ROW() & ":" & $C$1 & ROW())="×", INDIRECT("D" & ROW() & ":" & $C$1 & ROW())=""), 1, VALUE(MID(INDIRECT("D" & ROW() & ":" & $C$1 & ROW()), FIND("-", INDIRECT("D" & ROW() & ":" & $C$1 & ROW()))+1, 5))))
        ),
        "H:MM"
      )
    )
  )
)

トリガーの設定

Apps Scriptでトリガーを選択しトリガーを追加

追加時間主導型、日付ベース、時刻を選択

実行結果

上記トリガーのスケジュールに合わせて、メンション付きチャットと活動時間が投げかけられます。
活動の無い日（×の時）は自動的にスキップされます。

GASのコード

const WEBHOOK_URL="ウェブフックURLをコピーして貼り付け"

function sendDiscordMessage() {
  // 1. アクティブなスプレッドシートから情報を取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  
  const spreadsheetUrl = ss.getUrl();
 
  // 【追加】A列の日付チェックとC列の値取得
  const lastRow = sheet.getLastRow();
  let matchedCValues = [];
  
  if (lastRow >= 1) {
    // A列とC列のデータを一括で取得（1行目から最終行まで）
    const rangeValues = sheet.getRange(1, 1, lastRow, 3).getValues(); 
    
    // 今日の日付の「年・月・日」を取得（時間の違いによる不一致を防ぐため）
    const today = new Date();
    const todayStr = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy-MM-dd");
    console.log(todayStr)

    for (let i = 0; i < rangeValues.length; i++) {
      let cell0A = rangeValues[i][0]; // A列の値
      const cellC = rangeValues[i][2]; // C列の値
      const cellA = new Date(cell0A);
      if (cellA instanceof Date) {
        const cellAStr = Utilities.formatDate(cellA, Session.getScriptTimeZone(), "yyyy-MM-dd");
        
        // 日付が一致し、かつC列が空ではない場合
        if (cellAStr === todayStr && cellC !== "") {
          if (cellC =="×"){
            return
          }
          matchedCValues.push(cellC.toString().trim());
        }
      }
    }
  }
  if(matchedCValues[0] == undefined){
    return
  }
  matchedCValues[0]="**`"+matchedCValues[0]+"`**"
  console.log(matchedCValues[0])

  // 2. 4行目のD列（4列目）から最終列までのデータを取得
  const startRow = 4;
  const startColumn = 4; // D列は4列目
  const lastColumn = sheet.getLastColumn();
  
  let mentionsString = "";
  
  if (lastColumn >= startColumn) {
    const numColumns = lastColumn - startColumn + 1;
    const row4Values = sheet.getRange(startRow, startColumn, 1, numColumns).getValues()[0];
    
    const mentionsArray = row4Values
      .map(id => id.toString().trim())
      .filter(id => id !== "")
      .map(id => "<@" + id + ">");
    
    mentionsString = mentionsArray.join(" ");
  } else {
    console.log("4行目のD列以降にデータが見つかりませんでした。");
  }
  
  // 3. メッセージの組み立て
  // スプレッドシートURL、A1の値
  let message = spreadsheetUrl ;

    // 4行目のメンションを追加
  if (mentionsString) {
    message += "\n" + mentionsString;
  }
  
  // 【追加】一致したC列の値をメッセージに追加
  if (matchedCValues.length > 0) {
    message += "\n" + matchedCValues.join("\n");
  }
  

  
  // 4. Discordに送信するデータ（JSON形式）の作成
  const payload = {
    "content": message
  };
  
  // 5. HTTPリクエストのオプション設定
  const options = {
    "method": "post",
    "contentType": "application/json",
    "payload": JSON.stringify(payload),
    "muteHttpExceptions": true
  };
  
  // 6. Discordへデータを送信
  try {
    const response = UrlFetchApp.fetch(WEBHOOK_URL, options);
    const responseCode = response.getResponseCode();
    
    if (responseCode === 200 || responseCode === 204) {
      console.log("送信成功:\n" + message);
    } else {
      console.error("送信失敗。ステータスコード: " + responseCode + " レスポンス: " + response.getContentText());
    }
  } catch (e) {
    console.error("エラーが発生しました: " + e.toString());
  }
}

余談＆調整さんやデイコードとの相違点

毎日決まった時間や、1時間遅く開始するなど融通の利いた日程調整シートが出来ないかな、と思い作ってみました。
まだまだ色々と使い勝手が微妙なところもあるとは思いますが。

また、スプレッドシートとGASで組んでいるので各ユーザーに合わせて内容の改変もしやすいかと。
うまく組み合わせれば、前日の〇時までに入力されていないメンバーに対して入力を促すメンション付きアナウンスなども組み込めると思います。

あとウェブフックURLはスクリプトプロパティに入れたほうが良いかもしれません。


いいなと思ったら応援しよう！