Spread SheetとGoogle App Scriptで抽選システムを作った話

本記事はCraft Egg Advent Calendar 2021の12/5の記事です。
12/4の記事は@kai_yamamotoさんの「Beta版のUnity Gaming Servicesを導入して軽く触ってみる」でした。

はじめに

株式会社Craft EggでUnityクライアントエンジニアをしている鈴木です。
今回はUnityの話ではなく、社内のオンライン懇親会にて、クイズ大会と抽選会がしたい！という話があり、その中でSpread SheetとGoogle App Scriptで抽選システムを作成したので、システムの簡単な説明記事になります。

完成品の紹介

上記のように、点数が整理されているシートを元に当選ボーダーの点数と当選人数を設定して抽選を行うことができます。

完成コード

とりあえず作ったコードだけを見たい方はこちら
シートに作成したボタンを押すとmyFunctionが呼び出されます。

function myFunction() {
  // シートから各情報の取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetList = sheet.getSheetByName('回答データ');
  var sheetLots = sheet.getSheetByName('抽選シート');
  var lastRow = sheetList.getLastRow(); // 抽選対象リストの最終行
  var aptList = sheetList.getRange(2, 2, lastRow - 1, 4).getValues();
  var winCnt = sheetLots.getRange("E3").getValue();
  var borderPoint = sheetLots.getRange("E4").getValue();

  // シートから取得した多重リストを構造体のリストに変換
  var userList = aptList.map(function(apt){
    return {
      name : apt[0],
      point : apt[1],
      alradyLot : !(apt[3] == ""),
    }
  })

  // 抽選対象をポイントでフィルタリングして抽選
  var targetList = validateTargetByBorder(userList, borderPoint);
  winList = lot(targetList, winCnt);

  // 結果領域クリア
  sheetLots.getRange(3, 1, sheetLots.getLastRow(), 1).clear();

  // 抽選結果をセット
  for (i = 0; i <= winList.length - 1; i++) {
    sheetLots.getRange(3 + i, 1).setValue(winList[i]);
  }

  Browser.msgBox("抽選完了しました");
}

/**
 * 抽選する
 *
 * @param {Object} targetList 抽選対象の名前と重みのリスト
 * @param {int} winCnt 当選人数
 * @return {array} winList 当選対象の名前のリスト
 * @customfunction
 */
function lot(targetList, winCnt) {
  // 抽選対象が当選人数以下の場合
  if (targetList.length <= winCnt) {
    Browser.msgBox("抽選対象が当選人数以下です");
    return targetList.map(function(target){
      return target.name
    });
  }

  return lotWeighted(targetList, winCnt);
}

/**
 * 重み付けありの抽選をする
 *
 * @param {Object} targetList 抽選対象の名前と重みのリスト
 * @param {int} winCnt 当選人数
 * @return {array} winList 当選対象の名前のリスト
 * @customfunction
 */
function lotWeighted(targetList, winCnt){
  var winList = new Array();
  var hitCnt = 0;
  var totalWeight = 0;

  for (i = 0; i < targetList.length; i++){
    totalWeight += targetList[i].point;
  }


  while (hitCnt + 1 <= winCnt) {
    var weightedValue = Math.floor(Math.random() * totalWeight);
    var hitIdx = 0;

    for (var i = 0; i < targetList.length; i++){
      if(weightedValue < targetList[i].point){
        hitIdx = i;
        break;
      }

      weightedValue -= targetList[i].point;
    }

    // 既に当選した人でないかチェック
    if (winList.indexOf(String(targetList[hitIdx].name)) == -1) {
      winList.push(String(targetList[hitIdx].name));
      hitCnt++;
    }
  }

  return winList;
}

/**
 * 抽選対象の選別
 *
 * @param {Object} userList 抽選対象の名前とポイントのリスト
 * @param {int} borderPoint 当選人数
 * @return {array} targetList 抽選対象の名前とポイントのリスト
 * @customfunction
 */
function validateTargetByBorder(userList, borderPoint) {
  var targetList = new Array();
  for (i = 0; i <= userList.length - 1; i++) {
    if (userList[i].point >= borderPoint && userList[i].alradyLot == false) {
      targetList.push(userList[i]);
    }
  }

  return targetList;
}

使い方

概ね以下の通りです

• 参加者は事前に集計
• クイズをGoogle Formで回収する
• 点数を「回答データ」シートにまとめる
• 全員に配る基礎点を決める(全問不正解でも当選チャンスを作るかどうか)
• 当選人数とボーダー点数を決めて実行！

抽選元のデータの集約と抽選ボタンの作成

まず抽選システムの前に点数を集計したり、実際に操作するシート側の準備をします。
今回、抽選ボタンの用意などシート側のシステムは下記のサイトを参考に作成させていただきました。
本記事では「点数の集計」の部分をメインに記載させていただきます。
Googleフォーム と Googleスプレッドシートを使ったTwitter抽選ツールの作り方

各問題の正誤を1シート内にまとめる

Google Form の回答をSpread Sheetに出力します

別のSpreadSheetでも参照は可能ですが分ける必要もないので同Spread Sheet内に出力するのをおすすめします

出力したタブ内で正誤判定を行う

今回は下記の様にIFS関数を用いて
未記入(回答なし)なら「表示なし」
正解なら「○」
不正解(正解以外)なら「×」
を表示するようにしました(例はC6セルに回答がある場合)

=IFS(C6 = "", "", C6 = "hogehoge(正解)", "○", EXACT(C6 , C6), "×")

IFS関数は左から順番に判定を行なっていくので
Ⅰ. C6セルが空かどうか
Ⅱ. C6セルが正解と同じ文字列かどうか
Ⅲ. C6セルがC6セルと同一か(実質的なelse句)
となっています。IFやIFSではelseがないので苦肉の策のⅢです。
(もうちょっとスマートにできないものか。。。。。)

各クイズの回答シートにある正誤を「集計シート」に集約する

画像のように1人ごとに回答を集計して、点数を出します

例として、G2は一問目の正誤をIFERROR関数とVLOOKUP関数を使用して表示しています。
VLOOKUPは指定の値で検索をかけ、その結果を元に値を表示します。今回は名前の一致で検索することを想定しています。
詳しい解説はこちらを参照ください。
VLOOKUP関数は見つからなかった場合エラーを返してしまうのでIFERROR関数でラップして使用しています。

=IFERROR(VLOOKUP($B4,'一問目回答のシート名'!B:D,3, false), "-")

あとは丸の数をCOUNT関数で集計すれば完了です！全問不正解だと0になって困るなどあれば適当に1足しておきましょう。
(サンプルではD列が正解数でC列が抽選に使う数として正解数+1になっています)
また、すでに当選しているなどでなんらかの理由で抽選から外したい人が出る場合を考え、E列に何かしら入力すると抽選対象から外れる機能を今回は持たせていきます。
(ここもスマートにignoreみたいな指定の文字列にしたかったが断念。。。)

抽選コードの作成

ここからはGoogle App Scriptを使用した抽選ロジックの作成に入ります。

シートから情報を取得する

SpreadsheetAppを用いて、先程作成したシートから抽選対象や条件の情報を取得します。

  // シートから各情報の取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetList = sheet.getSheetByName('回答データ');
  var sheetLots = sheet.getSheetByName('抽選シート');
  var lastRow = sheetList.getLastRow(); // 抽選対象リストの最終行
  var aptList = sheetList.getRange(2, 2, lastRow - 1, 4).getValues();
  var winCnt = sheetLots.getRange("E3").getValue();
  var borderPoint = sheetLots.getRange("E4").getValue();

シートから取得した二次元配列を構造体のリストに変換

getRangeで取得した情報は二次元配列っぽいもの(正確にはRangeクラス)として保持されます。
そのままでも良かったのですが、とっても見づらかった(大事)ので
行は構造体にして一次元配列にしました。
あわせて、E列の当選からの除外情報も扱いやすいようにboolに変換します。

  var userList = aptList.map(function(apt){
    return {
      name : apt[0],
      point : apt[1],
      alradyLot : !(apt[3] == ""),
    }

抽選対象をポイントでフィルタリングして抽選

抽選ボーダーのポイントを設定できる様にしているので、それ以下の点数の参加者情報を弾きます。
今回は加えて抽選対象外の人もここで弾いています。

var targetList = validateTargetByBorder(userList, borderPoint);

/**
 * 抽選対象の選別
 *
 * @param {Object} userList 抽選対象の名前とポイントのリスト
 * @param {int} borderPoint 当選人数
 * @return {array} targetList 抽選対象の名前とポイントのリスト
 * @customfunction
 */
function validateTargetByBorder(userList, borderPoint) {
  var targetList = new Array();
  for (i = 0; i <= userList.length - 1; i++) {
    if (userList[i].point >= borderPoint && userList[i].alradyLot == false) {
      targetList.push(userList[i]);
    }
  }

  return targetList;
}

実際に抽選する

抽選対象外の参加者を弾いた結果、抽選人数を下回った場合などを考慮し、ここでチェックを行います。
問題なければ実際に抽選を行います！

/**
 * 抽選する
 *
 * @param {Object} targetList 抽選対象の名前と重みのリスト
 * @param {int} winCnt 当選人数
 * @return {array} winList 当選対象の名前のリスト
 * @customfunction
 */
function lot(targetList, winCnt) {
  // 抽選対象が当選人数以下の場合
  if (targetList.length <= winCnt) {
    Browser.msgBox("抽選対象が当選人数以下です");
    return targetList.map(function(target){
      return target.name
    });
  }

  return lotWeighted(targetList, winCnt);
}

重み付けありの抽選をする

今回は、正解数が多いほど当たりやすいという同様に確からしくない抽選を行うので、ただランダム関数で引っ張ってくるだけではできません。
別言語にはなりますが、下記のサイトを参考に重み付けありの抽選を実装しました。
重み付けの抽選を行うアルゴリズム

/**
 * 重み付けありの抽選をする
 *
 * @param {Object} targetList 抽選対象の名前と重みのリスト
 * @param {int} winCnt 当選人数
 * @return {array} winList 当選対象の名前のリスト
 * @customfunction
 */
function lotWeighted(targetList, winCnt){
  var winList = new Array();
  var hitCnt = 0;
  var totalWeight = 0;

  for (i = 0; i < targetList.length; i++){
    totalWeight += targetList[i].point;
  }


  while (hitCnt + 1 <= winCnt) {
    var weightedValue = Math.floor(Math.random() * totalWeight);
    var hitIdx = 0;

    for (var i = 0; i < targetList.length; i++){
      if(weightedValue < targetList[i].point){
        hitIdx = i;
        break;
      }

      weightedValue -= targetList[i].point;
    }

    // 既に当選した人でないかチェック
    if (winList.indexOf(String(targetList[hitIdx].name)) == -1) {
      winList.push(String(targetList[hitIdx].name));
      hitCnt++;
    }
  }
  return winList;
}

抽選結果を表示して終了

前の結果が残っているかもしれないのでそちらを一度クリアしてから抽選結果を表示させます。
最後に完了の旨のメッセージボックスを表示して終了です。

  // 結果領域クリア
  sheetLots.getRange(3, 1, sheetLots.getLastRow(), 1).clear();

  // 抽選結果をセット
  for (i = 0; i <= winList.length - 1; i++) {
    sheetLots.getRange(3 + i, 1).setValue(winList[i]);
  }

  Browser.msgBox("抽選完了しました");

参考サイト

Googleフォーム と Googleスプレッドシートを使ったTwitter抽選ツールの作り方
重み付けの抽選を行うアルゴリズム

終わりに

今回は抽選システムをSpread SheetとGoogle App Scriptで作成しました。
Spread Sheetの関数とGoogle App Scriptをしっかり使ってみるのは初めてでしたが、思いのほか簡単に色々できることを知れました。
オンラインでプレゼント交換会を！みたいな時にも応用できると思いますのでぜひ使ってみてください。

明日は@Tomy_0331 さんの「Toggl Trackを使ってみた」です。