LoginSignup
9
0

More than 1 year has passed since last update.

ABEJAAdvent Calendar 2021Day 5abeja
@Z-me(Hajime Saito)inABEJA, Inc.

GSS(Google SpreadSheet)とGAS(Google AppScript)でランダムメンバー選択通知システム作った話

Last updated at Posted at 2021-12-04
1 / 52

はじめに

こんにちは。
Z-meこと、株式会社ABEJAの齋藤 創（さいとう はじめ）です。

この記事は
ABEJA Advent Calendar 2021の
5日目の記事です。

本記事は
Qiitaのスライドモード前提で記載していますので、
読む際はスライドモードをご利用ください。
※コード部分は下の本文の方を確認していただいたほうが見やすいかと思います。

作成した経緯

開催機会

様々なミーティングの際
そのミーティングを回すファシリテーターを
選び出す必要がある

開催機会

  1. 毎日の朝会
    • 進捗報告 / 今日やること / 困りごとの共有 / 等
  2. スプリントプランニング
    • レトロスペクティブ(振り返り) / プランニング

コレまでの選出方法(やりかた)

  1. Web会議のときの名前リストから適当に
    • 例)Google Meetの参加者ギャラリー表示で真ん中の人
  2. その場のノリ

課題

  1. 同じ人が選ばれる可能性が高い
  2. 結局その場の気分で選ばれる
  3. 人為的に操作できてしまう(ランダム性がない）

完成要件

1. 朝会のタイミングでSlackに通知が飛ぶ

image.png

祝祭日は通知が飛ばないように設計

2. 連続で同じ人が選ばれない

3. 当日お休みの人とかにも対応

４. 非稼働日を設定可能

5. Slackでの通知ではなくても手動で選出

Let's 作成

【Ⅰ】 Slackで通知するためのSlack Appの準備

①Slack Appの準備

  1. slack api の Your Apps(https://api.slack.com/apps) からCreate New App
  2. From scratch から、アプリ名と作成するワークスペースを選択&作成

②Slack Appの準備

  1. Basic InformationのDisplay Informationを選択し、色々いじる image.png

③Slack　Appの準備

  1. Incoming WebhookをOnに変更
  2. 一番下にある Add New Webfook to Workspace を選択
  3. 投稿するチャンネルを選択
  4. 出力されたWebhook URLを控えておく

④Slack Appの準備

  1. Install Appから、該当のワークスペースに追加

【Ⅱ】 設定・実行画面としてのGSS(Google SpreadSheet)の準備

①GSSの入力設計

  • GSSでは何がしたい？
    • メンバーリストがある
    • 各種設定ができる
    • 手動で実行できる

②GSSの入力設計

シート設定画面
image.png

③GSSの入力設計 - A列:メンバーリスト

image.png

④GSSの入力設計 - B列:非稼働日

image.png

⑤GSSの入力設計 - C列:直近指名者

image.png

⑥GSSの入力設計 - D列:当日不参加

image.png

⑦GSSの入力設計 - ツールバー:手動実行

image.png

⑧GSSの入力設計 - 休日の取得

Holidays JP API( https://holidays-jp.github.io/ )というAPIから休日を取得
image.png
※CSV形式で取得できるので、事前に IMPORTDATA関数を用いて事前に読み込んでおきます。

シート全体（次のGASの説明はこちらをそのまま使います）

memberシート
セル番号 入力規則
C2:C A2:Aのリスト選択
D２:D A2:Aのリスト選択

image.png

holidayシート
セル番号 関数
A1 IMPORTDATA("https://holidays-jp.github.io/api/v1/date.csv", ", ")
C1:C TEXT(A1:A,"YYYY/MM/DD")

image.png

【Ⅲ】処理機能としてのGAS(Google App Script)の準備

※コードの全体は記事の最後に記載しておきます

トリガーの作成

①トリガーに関して
  • 課題
    • GASでは日次のトリガー(毎日〇〇する)設定では、時間単位でしか指定ができない
  • 解決策
    • ScriptApp.newTriggerで明確に指定する
②トリガーに関して

例)毎日１０時丁度に発報するトリガーの設定フロー

  1. 事前にトリガーを作成する関数を日次のトリガーとしてCall
  2. その日が休日かどうかを判断し、１０時丁度に発報するトリガーを設定
③トリガーに関して

毎日１０時丁度に発報するトリガーのイメージ
image.png

④トリガーに関して -code-
const DAYS = ['日', '月', '火', '水', '木', '金', '土'];

/**
 * 詳細な時間に実行するように設定
 */
function SetPostTimeTrigger() {
  if(isHoliday()) return
  const next  = new Date(),
        today = next.getDate();

  next.setHours(10);
  next.setMinutes(0);
  next.setSeconds(0);
  ScriptApp.newTrigger('setAsakaiFacilitator').timeBased().at(next).create();
}

/**
 * 実行した日が日本の祝日か否かを返す
 * @return boolean
 */
function isHoliday() {
  const today    = new Date().toDateString(),
        sheet    = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('holiday'),
        days     = sheet.getRange('A:A').getValues(),
        daysList = days.map((arr) => {
          if (!!arr[0]) return new Date(arr[0]).toDateString()
        }).filter((arr) => !!arr)
  return daysList.includes(today)
}

対象メンバーの抽出

①対象メンバーのフィルタリング
  1. メンバーリストからメンバーを取得
  2. 非稼働日を取得し、対象メンバーをフィルタリング
  3. 直近で指名されたメンバーを除外
  4. 当日不参加メンバーを除外
②対象メンバーのフィルタリング -code-
/**
 * シートからメンバーリストと、その他除外要因を取得し、対象メンバーのリストを返却する
 * @pram sheet
 * @return Array
 */
function getMemberList(sheet) {
  let menberValue    = sheet.getRange('A2:B').getValues(),  // ※1
      prevMembers    = sheet.getRange('C2:C').getValues(),
      notBeTheres    = sheet.getRange('D2:D').getValues(),
      list           = memberFilter(menberValue),
      prevMember     = prevMembers.map((arr) => arr[0]).filter((arr) => arr !== ''),
      notBeThere     = notBeTheres.map((arr) => arr[0]).filter((arr) => arr !== ''),
      today          = new Date().getDay();
  if (DAYS[today] === '日' || DAYS[today] === '土') return false
  return list.filter((arr) => {
    if (prevMember.includes(arr.name)) return false
    if (notBeThere.includes(arr.name)) return false
    if (arr.dayOff.includes(DAYS[today])) return false
    return true
  })
}

/**
 * メンバーリストから空の配列を除外し、名前と非稼働日の配列を返却する
 * @pram array[][]
 * @return Array({})
 */
function memberFilter(array) {
  let new_array = new Array();
  array.map((value) => {
    if(value[0] != null && value[0] != "") {
      new_array.push({
        name: value[0].toString(),
        dayOff: value[1].split(',')
      });
    }
  })
  return new_array;
}

※1: sheetオブジェクトの関数は処理速度的に重いので、本来ならば、sheet.getRange('A2:D').getValues()として、1回で呼び出して、各種項目に応じて算出したほうがいいですが、今回対象メンバーがそんなに多くないので、この処理にしています。

メイン処理

①Slackへ通知

UrlFetchApp.fetchを使って、作成したSlack AppのWebhookのURLにテキストをPOST

①Slackへ通知 - code
/**
 * テキストをSlackで通知する
 * @pram text
 * @return void
 */
function postSlack(text){
  let url     = "https://hooks.slack.com/services/<<SlackのURL>>",
      options = {
        "method"  : "POST",
        "headers" : {"Content-type":"application/json"},
        "payload" : '{"text":"' + text + '"}'
      };
  UrlFetchApp.fetch(url, options);
}
②対象メンバーをランダム選択
  • メンバーリストから Math.random()を使って選出
    • Math.random() * (対象メンバーの数) でランダムに数値を出力
    • ↑で出力された値を小数点以下を切り捨てて、対象を選出
②対象メンバーをランダム選択 - code
const allMemberNum = MemberObj.length, // 対象メンバーの数を利用
      randomNumber = Math.floor(Math.random()* allMemberNum),
      selected     = MemberObj[randomNumber].name
      resultText   = `本日の朝会担当は、 ${selected} さんです。 \nMeet: https://meet.google.com/<<google meet ID>>`
③直近指名者を更新 - code
// 書き込み処理
let prevMembers   = sheet.getRange(2, 3, prevNumber, 1).getValues() // 既存の直近指名者を取得
                      .filter((_, i) => i !== prevNumber - 1),  // 一番古い指名者だけを除外
    newPevMembers = [
      [selected], // 指名された人を追加
      ...prevMembers
    ];
sheet.getRange(2, 3, prevNumber, 1).setValues(newPevMembers);
④メニューバーへの追加 - code
  • メンバーリストからランダムにメンバーを取得して、メッセージボックスを表示する
  • ↑コレを発報させるメニューを追加
④メニューバーへの追加 - code
/**
 * 手動実行し、メッセージウィンドウでランダムに選択されたMemberを取得する
 */
function getRandomMember() {
  const sheet     = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('member');
  const MemberObj = getMemberList(sheet);
  if (!MemberObj) return false
  const allMemberNum = MemberObj.length,
        randomNumber = Math.floor(Math.random()* allMemberNum),
        selected     = MemberObj[randomNumber].name;
  Browser.msgBox(`選ばれたのは、「${selected}」さんです!!`, Browser.Buttons.OK)
}

/**
 * メニューへの追加
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi()
  var menu = ui.createMenu('ランダム実行メニュー')
  menu.addItem('Memberランダム選択', 'getRandomMember')
  menu.addToUi()
}

おわりに

後半はすべてGASのコードを垂れ流す形になってしまいましたが、今回紹介したメンバーのランダム抽選機は、GSSとGASとSlack Appだけで実現できます。
今回用いた実装や手法が、業務改善の一助となれば幸いです。

実装を用いてできること

  • 適当な名言をSlackに垂れ流したい
  • 毎日特定の時間に定期的にメッセージを流したい
    • もしくは処理を実行したい
  • メニューバーにボタンを追加して実行したい
  • 日本の祝祭日の判定をしたい
  • 等

お知らせ：ABEJA awaits your joining!

IMG
現在ＡＢＥＪＡでは一緒にＡＩの社会実装を進める仲間を募集しています！
【募集職種一覧はこちら！】

備考 -GASの全文-

main.gs
main.js
const DAYS = ['日', '月', '火', '水', '木', '金', '土'];

/**
 * 詳細な時間に実行するように設定
 */
function SetPostTimeTrigger() {
  if(isHoliday()) return
  const next  = new Date(),
        today = next.getDate();
  if (DAYS[today] === '金') return setSprintPlanningTrigger()
  next.setHours(10);
  next.setMinutes(0);
  next.setSeconds(0);
  ScriptApp.newTrigger('setAsakaiFacilitator').timeBased().at(next).create();
}

/**
 * 実行した日が日本の祝日か否かを返す
 * @return boolean
 */
function isHoliday() {
  const today    = new Date().toDateString(),
        sheet    = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('holiday'),
        days     = sheet.getRange('A:A').getValues(),
        daysList = days.map((arr) => {
          if (!!arr[0]) return new Date(arr[0]).toDateString()
        }).filter((arr) => !!arr)
  return daysList.includes(today)
}

/**
 * SprintPlanningのトリガーを設定する
 * @return void
 */
function setSprintPlanningTrigger() {
  const next = new Date();
  next.setHours(15);
  next.setMinutes(50);
  next.setSeconds(0);
  ScriptApp.newTrigger('setSprintPlanningFacilitator').timeBased().at(next).create();
}

/**
 * シートからメンバーリストと、その他除外要因を取得し、対象メンバーのリストを返却する
 * @pram sheet
 * @return Array
 */
function getMemberList(sheet) {
  let menberValue    = sheet.getRange('A2:B').getValues(),
      prevMembers    = sheet.getRange('C2:C').getValues(),
      notBeTheres    = sheet.getRange('D2:D').getValues(),
      list           = memberFilter(menberValue),
      prevMember     = prevMembers.map((arr) => arr[0]).filter((arr) => arr !== ''),
      notBeThere     = notBeTheres.map((arr) => arr[0]).filter((arr) => arr !== ''),
      today          = new Date().getDay();
  if (DAYS[today] === '日' || DAYS[today] === '土') return false
  return list.filter((arr) => {
    if (prevMember.includes(arr.name)) return false
    if (notBeThere.includes(arr.name)) return false
    if (arr.dayOff.includes(DAYS[today])) return false
    return true
  })
}

/**
 * メンバーリストから空の配列を除外し、名前と非稼働日の配列を返却する
 * @pram array[][]
 * @return Array({})
 */
function memberFilter(array) {
  let new_array = new Array();
  array.map((value) => {
    if(value[0] != null && value[0] != "") {
      new_array.push({
        name: value[0].toString(),
        dayOff: value[1].split(',')
      });
    }
  })
  return new_array;
}

/**
 * テキストをSlackで通知する
 * @pram text
 * @return void
 */
function postSlack(text){
  let url     = "https://hooks.slack.com/services/<<Slack App Webhook URL>>",
      options = {
        "method"  : "POST",
        "headers" : {"Content-type":"application/json"},
        "payload" : '{"text":"' + text + '"}'
      };
  UrlFetchApp.fetch(url, options);
}

function setAsakaiFacilitator(){
  const sheet      = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('member');
  const prevNumber = 2;
  const MemberObj  = getMemberList(sheet);
  if (!MemberObj) return false
  const allMemberNum = MemberObj.length,
        randomNumber = Math.floor(Math.random()* allMemberNum),
        selected     = MemberObj[randomNumber].name
        resultText   = `本日の朝会担当は、 ${selected} さんです。 \nMeet: https://meet.google.com/<<google meet ID>>`
  // slack送信処理
  postSlack(resultText)
  // 書き込み処理
  let prevMembers = sheet.getRange(2, 3, prevNumber, 1).getValues().filter((_, i) => i !== prevNumber - 1),
      newPevMembers = [
        [selected],
        ...prevMembers
      ];
  sheet.getRange(2, 3, prevNumber, 1).setValues(newPevMembers);
}

function setSprintPlanningFacilitator(){
  const sheet      = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('member');
  const prevNumber = 2;
  const MemberObj  = getMemberList(sheet);
  if (!MemberObj) return false
  const allMemberNum = MemberObj.length,
        randomNumber = Math.floor(Math.random()* allMemberNum),
        selected     = MemberObj[randomNumber].name
        resultText   = `本日のスプリントプランニングのFacilitatorは\n ${selected} さんです。 \nMeet: https://meet.google.com/<<google meet ID>>`
  // slack送信処理
  postSlack(resultText)
  // 書き込み処理
  let prevMembers   = sheet.getRange(2, 3, prevNumber, 1).getValues().filter((_, i) => i !== prevNumber - 1),
      newPevMembers = [
        [selected],
        ...prevMembers
      ];
  sheet.getRange(2, 3, prevNumber, 1).setValues(newPevMembers);
}

/**
 * 手動実行し、メッセージウィンドウでランダムに選択されたMemberを取得する
 */
function getRandomMember() {
  const sheet     = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('member');
  const MemberObj = getMemberList(sheet);
  if (!MemberObj) return false
  const allMemberNum = MemberObj.length,
        randomNumber = Math.floor(Math.random()* allMemberNum),
        selected     = MemberObj[randomNumber].name;
  Browser.msgBox(`選ばれたのは、「${selected}」さんです!!`, Browser.Buttons.OK)
}

/**
 * メニューへの追加
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi()
  var menu = ui.createMenu('ランダム実行メニュー')
  menu.addItem('Memberランダム選択', 'getRandomMember')
  menu.addToUi()
}
9
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
9
0