虎の穴開発室ブログ

虎の穴ラボ株式会社所属のエンジニアが書く技術ブログです

MENU

GoogleスプレッドシートとGASでイベント出欠管理ツールを作ってみた話

技術エントリ JavaScript

こんにちは、お久しぶりです。虎の穴ラボのA.M.です。

虎の穴ラボ Advent Calendar 2020 - Qiita 4日目の記事になります。

今までは全文検索エンジンについての記事を書いてたりしましたが、今回は趣向を変えまして、趣味でプレイしているMMORPGのギルドイベント出欠管理用に作ったツールをご紹介したいと思います。

※本記事の内容は、以前の記事「Googleスプレッドシートに書き込まれたらSlackに通知する」を応用したものになります。

toranoana-lab.hatenablog.com

最近のMMORPG（特にスマートフォン向け）では毎週決まった曜日・時間にギルド向けのイベント（対人戦、レイドボスなど）が開催されていますね。
イベントを効率よく進めたり、勝ち進めるには、事前にパーティ編成を決めておく必要があります。 そのためには、まずギルドに所属しているメンバーがイベントに参加できるかどうかの出欠管理を行う必要があります。
この出欠管理をどうやって行うかについては皆、頭を悩ませるところだと思いますが、今回は私がイベントの出欠管理用に作成したものをご紹介します。
出欠管理のやり方について悩んでいる方の一助になれば幸いです。

目次

用語

単語 意味
MMORPG Massively Multiplayer Online Role-Playing Gameの略。大規模多人数同時参加型オンラインRPGを指す。いわゆるネトゲ。
ギルド ゲーム内で設立された団体・コミュニティ。ゲームによってはクラン、サークル等と称されることもある。
パーティ 共に冒険する仲間。ゲームによって人数下限や上限が異なるが、基本的には3~8人程度。
レイド 多数のプレイヤーが協力し、少人数ではクリアが困難な高難易度要素を攻略すること。ゲームのシステム上では、複数のパーティを束ねたレイドパーティを指すこともある。
その他の関連用語：レイドボス（≒高難易度ボス）、レイドダンジョン（≒高難易度ダンジョン）
戦力 ゲーム内のキャラクターの強さ（レベル、スキル、装備やその他の強化要素）を総合的に評価した数値。ゲームによってはスコア、戦闘力等と称されることもある。

要件

  1. ログインなしで手軽に利用できるもの（登録が面倒だと出欠の提出率が下がるため）
  2. 出欠表はギルドメンバー全員が見れること
  3. 出欠の記入はメンバーが各々で行う
  4. 出欠が登録・変更された場合は検知できるようにする（出欠提出期限の締切後の変更や、急用等でイベント直前に出欠変更される場合があるため）
  5. レイドパーティを事前編成するために必要な指標である戦力も入力してもらう（ゲームによっては非公開ステータスでプレイヤーの自己申告が必要）

※1~3はスプレッドシートだけで実現可能

※4はGoogle Apps Scriptで変更を検知してDiscordに通知することで対応

※5は出欠表のフォーマットを工夫すればOK

使用するもの

  • Googleスプレッドシート
  • Google Apps Script （以降、GAS）
  • Discord
    ※ゲーム界隈でよく使用されているので、今回はSlackではなくDiscordを使用します

前提

  • Googleアカウントを作成し、ログイン済みであること
  • Discordインストール済みであること
  • Discordのサーバは作成済みであること

実際に作ってみる

STEP1 スプレッドシートで出欠表を作る

まずはスプレッドシートで出欠表のフォーマットを作ります。

以下の項目が最低限あれば良いでしょう。

  • キャラクター名
  • 職業
  • 戦力
  • 出欠入力（イベントの回数分）
  • コメント
  • 戦力の最終更新日時

f:id:toranoana-lab:20201203175740p:plain
イベント出欠管理表サンプル

実際に作ったものがこちらになります。コピーして使っていただいてもOKです。

イベント出欠管理表 - Google スプレッドシート

STEP2 DiscordでWebhookURLを発行する

出欠変更通知を受け取るためのチャンネル作成

まずは通知を受け取るためのテキストチャンネルを作成します。

テキストチャンネルの右側にある「+」をクリックします。
f:id:toranoana-lab:20201203130857p:plain

テストチャンネルを選択し、チャンネル名に任意の名前（今回は「通知用」）を入力して、「チャンネルを作成」ボタンを押します。
f:id:toranoana-lab:20201203131607p:plain

※パブリックチャンネルとして作成しても問題ないですが、通知が結構流れてくるのと、ギルド運営メンバー以外には不要な情報なので、プライベートチャンネルとして作成することをお勧めします。

WebhookURLを発行する

通知用のテキストチャンネルができたら、チャンネル名の右側にある歯車アイコンをクリックし、チャンネルの編集画面を開きます。
f:id:toranoana-lab:20201203164322p:plain

左側のメニューから連携サービスを選択し、「ウェブフックを作成」ボタンを押します。
f:id:toranoana-lab:20201203131648p:plain

アイコンと名前は任意で設定し、「ウェブフックURLをコピー」ボタンを押して、URLをコピーしておきます。
f:id:toranoana-lab:20201203131702p:plain

STEP3 出欠変更があったらGASでDiscordに通知

通知用のスクリプトを書く

作成した出欠表のスプレッドシートに戻り、スクリプト エディタを開きます。
ツール > スクリプト エディタ
f:id:toranoana-lab:20201203164717p:plain

スクリプト エディタを開いたら、ここにスクリプトを書いていきます。
myFunctionは名前をonEditに変更しておきます。
f:id:toranoana-lab:20201203131845p:plain

まずは不必要な通知が飛ばないように、変更通知対象の範囲外のセルが変更された場合は処理を中断するように実装します。

function onEdit(e) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシート
  var activeSheet = activeSpreadsheet.getActiveSheet(); // アクティブシート
  
  if(activeSheet.getName() != "出欠表"){
    return;
  }
  
  var activeCell = activeSheet.getActiveCell(); // アクティブセル
  var nowInputRow = activeCell.getRow(); // 入力のあった行番号
  var nowInputColumn = activeCell.getColumn(); // 入力のあった列番号
  
  if(nowInputRow <= 3 || nowInputColumn < 2 || nowInputColumn == 10){
    // 変更通知対象の範囲外のセルを変更した場合は何もしない
    return;
  }
}

f:id:toranoana-lab:20201203175506p:plain

次に、通知メッセージを作成する処理を追加します。

function onEdit(e) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシート
  var activeSheet = activeSpreadsheet.getActiveSheet(); // アクティブシート
  
  if(activeSheet.getName() != "出欠表"){
    return;
  }
  
  var activeCell = activeSheet.getActiveCell(); // アクティブセル
  var nowInputRow = activeCell.getRow(); // 入力のあった行番号
  var nowInputColumn = activeCell.getColumn(); // 入力のあった列番号
  
  if(nowInputRow <= 3 || nowInputColumn < 2 || nowInputColumn == 10){
    // 変更通知対象の範囲外のセルを変更した場合は何もしない
    return;
  }
  
  // 変更した列名
  var columnName = activeSheet.getRange(3, nowInputColumn).getValues();
  // キャラクター名取得
  var charaName = activeSheet.getRange(nowInputRow, 2).getValues();
  
  var textMessage = "";
  if(columnName == 'キャラクター名' || columnName == '職業' || columnName == '戦力' || columnName == 'コメント'){
    activeCellValue = activeCell.getValues();
    if(columnName == 'キャラクター名'){
      // キャラクター名が変更された場合は変更前の名前をセット
      charaName = e.oldValue;
    }else if(columnName == '戦力'){
      // 戦力の場合は更新日時を記録 
      activeSheet.getRange(nowInputRow, 10).setValue(Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss'));
      activeCellValue = activeCell.getValues() + "万";
    }
    // 送信するテキスト
    textMessage = charaName + "さんが" + columnName + "を「" + activeCellValue +"」に変更したよ！ヨシ！";
  } else if(nowInputColumn > 4){
    // イベント出欠が変更された場合
    var eventDate = activeSheet.getRange(1, nowInputColumn).getValues(); // イベントの曜日・時間
    var eventName = activeSheet.getRange(2, nowInputColumn).getValues(); // イベント名
    // 送信するテキスト
    textMessage = charaName + "さんが " + eventName + "（" + eventDate + "）" + "の出欠を「" + activeCell.getValues() +"」に変更したよ！ヨシ！";
  }
  sendDiscord(textMessage);
}

キャラクター名、職業、戦力、コメントが変更された場合と、イベント出欠（◯、✕）が変更された場合で通知メッセージを変えています。
前者の場合、変更された行のキャラクター名、変更した項目名、変更後の値を通知します。 また、以下の特定の項目については個別処理も実装しています。

  • キャラクター名が変更された場合は変更前の名前（e.oldValueで取得）で通知する
  • 戦闘力が変更された場合は、J列に最終更新日時を記録する

後者の場合、イベント出欠の変更なので、キャラクター名と変更されたイベント名、イベント曜日時間、変更後の出欠を通知します。

続いて、Discordに通知を送る処理を実装します。

function sendDiscord(textMessage){
  if(textMessage == ""){
    // 通知内容が空の場合は何もしない
    return;
  }

  //Webhook URLを設定
  var webHookUrl = "https://discord.com/api/webhooks/***************";
  
  var jsonData =
      {
        "content" : textMessage
      };
  
  var payload = JSON.stringify(jsonData);
  
  var options =
      {
        "method" : "post",
        "contentType" : "application/json",
        "payload" : payload,
      };
  
  // リクエスト
  UrlFetchApp.fetch(webHookUrl, options);
}

トリガーの設定

最後に、スプレッドシートが変更されたら、実装したスクリプトが実行されるように、トリガーの設定を行います。

スクリプトエディタの時計マークを押して、トリガーの設定を開きます。
f:id:toranoana-lab:20201203132000p:plain

「トリガーを追加」で新規にトリガーを追加します。
f:id:toranoana-lab:20201203132021p:plain

スプレッドシートに変更があった場合にonEdit関数を実行して通知を送りたいので、

  • 実行する関数: onEdit
  • イベントソースの選択: スプレッドシートから
  • イベントの種類を選択: 編集時
    ※「変更時」ではe.oldValueで変更前の値が取れないので注意

を指定して、トリガーを保存します。
f:id:toranoana-lab:20201203173212p:plain

これでDiscordに通知が届くようになっているはずなので、出欠表の内容を変更してみます。
f:id:toranoana-lab:20201203170414p:plain

値を変更した時に、通知が送られていますね。

おまけ レイドパーティ編成用に職業と戦力でソートした一覧を作ってみる

実際にパーティ編成を考えるとき、職業と戦力のバランスを見て組むので、見やすいようにソートしたシートを作成します。 出欠表のスプレットシートに、新しいシートを作成し、出欠表の内容を自動ソートした形で表示できるようにします。

まずは新しいシートを作成します。
f:id:toranoana-lab:20201203134022p:plain

次に、以下のQUERY関数を新しく作成したシートのB3セル辺りに記述します。

=QUERY({'出欠表'!B3:D,ARRAYFORMULA(REGEXREPLACE('出欠表'!E3:E,"^$","×")),ARRAYFORMULA(REGEXREPLACE('出欠表'!F3:F,"^$","×")),ARRAYFORMULA(REGEXREPLACE('出欠表'!G3:G,"^$","×")),ARRAYFORMULA(REGEXREPLACE('出欠表'!H3:H,"^$","×"))}, "select Col1, Col2, Col3, Col4, Col5, Col6, Col7 where Col1!='' order by Col2, Col3 desc")

これはスプレッドシートのQUERY関数を使用し、「出欠表」シートの内容を職業と戦力で並び替えたものを表示するようにしています。
また、出欠の値（出欠表のE列からH列）については、空欄の場合は欠席（✕）として扱うようにARRAYFORMULA関数とREGEXREPLACE関数を組み合わせて使用しています。

あとは、ソートされた表が見やすいように背景色を追加したり、フィルタを追加したりすれば完成です。
f:id:toranoana-lab:20201203193739p:plain

あとがき

今回作成したイベント出欠管理表のスプレッドシートはGASと共に公開していますので、もし出欠管理に困っているという場合はぜひ使ってみてください。

イベント出欠管理表 - Google スプレッドシート

※上記スプレッドシートを開き、「ファイル＞コピーを作成」でマイドライブなどに保存してご使用ください。
※スプレッドシートをコピーすれば、通知用のGASも一緒に付いてきますので、あとはWebhookURLの記述とトリガー設定だけ行えば通知が届くようになります。

この出欠管理表を実際に使用開始したところ、「スマートフォンから入力しづらい」または「入力できない」という声が上がりました。
次の記事では、その解決策として今回作成したイベント出欠管理表を拡張し、Googleフォームでも出欠登録・変更（Discordにも通知）ができるようにしようと思います。

続きはこちら👇 toranoana-lab.hatenablog.com

P.S.

アイコンで使用しているメイドちゃんはとらラボ素材集に収録されています。
無料で利用可能なので、発表資料などでぜひ使ってみてください。 yumenosora.co.jp

採用情報

■募集職種
yumenosora.co.jp

カジュアル面談も随時開催中です

■お申し込みはこちら！
news.toranoana.jp

■ToraLab.fmスタートしました！

メンバーによるPodcastを配信中！
是非スキマ時間に聞いて頂けると嬉しいです。
anchor.fm

■Twitterもフォローしてくださいね！

ツイッターでも随時情報発信をしています
twitter.com