GoogleスプレッドシートでGASを使用して「メンバーをランダムに振り分けるマクロ」を作ってみた

概要
スプレッドシート
マクロ
1. コーディング画面
2. ソースコード
スプレッドシートとマクロ（GAS）を連携
あとがき

概要

Googleスプレッドシートでマクロが使用出来ると耳にしたので、試しに「メンバーをランダムに振り分けるマクロ」を作ってみました。
マクロと言えばExcelでVBAというイメージですが、スプレッドシートではGAS（GoogleAppsScript）というプラットフォーム上でJavaScriptなどを使用して処理を記述します。

スプレッドシート

まずは以下のようなスプレッドシートを作成します。
※題材はグループ分けで有名な魔法の帽子です！

＜スプレッドシート＞

以降のマクロでは、スプレッドシートのStudentに記載されているメンバーを各Roomへ振り分ける処理を作成します。
RoomへはCountの数までStudentのメンバーを順次配置します。
※Trip列に1が入力さているStudentのメンバーは処理対象外となります。（Trip=旅行中。邪魔しちゃダメ。）

マクロ

コーディング画面

１．「拡張機能」>「Apps Script」を選択して、コーディング画面へ遷移します。

※以下のような画面が表示された場合は、アクセス権を持つアカウントへ切り替えてください。

２．コーディング画面（AppsScript）が表示されればOKです。

ソースコード

// 組み分け帽子ボタンのマクロ
function sortingHat() {
  // メンバーの一覧を取得
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("work").activate();
  const activeSheet = SpreadsheetApp.getActiveSheet();
  console.log("activeSheet: " + activeSheet.getSheetName()); // ログ出力
  // メンバーの総数を取得
  let memberTargetCount = activeSheet.getLastRow() - 9
  console.log("memberTargetCount:" + memberTargetCount); // ログ出力
  // メンバーの入力値を取得
  const targetRange = activeSheet.getRange(10, 1, memberTargetCount, 2)
  const allMembers = targetRange.getValues()
 
  // Trip=１のメンバーを除外した配列（ディクショナリ）を作成
  let targetMembers = []
  allMembers.forEach(member => {
    if (member[1] == 1) {
      // 対象外のメンバーが存在する場合、メンバーの総数を減算
      memberTargetCount -= 1;
      return
    }
    targetMembers.push(
      {
        name: member[0],
      }
    )
  })
 
  // 対象のメンバーをランダムにソート
  const shuffledMembers = this.shuffleArray(targetMembers)

  // RoomへCountの数ごとにメンバーを振り分け
  let roomGryffindor = []
  let roomSlytherin = []
  let roomRavenclaw = []
  let roomHufflepuff = []
  const countGryffindor = activeSheet.getRange(3,2).getValue()
  const countSlytherin = activeSheet.getRange(3,4).getValue()
  const countRavenclaw = activeSheet.getRange(3,6).getValue()
  const countHufflepuff = activeSheet.getRange(3,8).getValue()

  // Gryffindor
  let baseCount = 0
  for (let i = 0; i < countGryffindor; i++) {
    if (i > memberTargetCount - 1){
      break
    }
    roomGryffindor.push(shuffledMembers[i + baseCount]['name'])
  }
  outputGryffindor = roomGryffindor.join(",")
  console.log("outputGryffindor:" + outputGryffindor); // ログ出力
  // 出力
  activeSheet.getRange(4,2).setValue(outputGryffindor)

  // Slytherin
  baseCount = countGryffindor
  for (let i = 0; i < countSlytherin; i++) {
    if (i + baseCount > memberTargetCount - 1){
      break
    }
    roomSlytherin.push(shuffledMembers[i + baseCount]['name'])
  }
  outputSlytherin = roomSlytherin.join(",")
  console.log("outputSlytherin:" + outputSlytherin); // ログ出力
  // 出力
  activeSheet.getRange(4,4).setValue(outputSlytherin) 

  // Ravenclaw
  baseCount = baseCount + countSlytherin
  for (let i = 0; i < countRavenclaw; i++) {
    if (i + baseCount > memberTargetCount - 1){
      break
    }
    roomRavenclaw.push(shuffledMembers[i + baseCount]['name'])
  }
  outputRavenclaw = roomRavenclaw.join(",")
  console.log("outputRavenclaw:" + outputRavenclaw); // ログ出力
  // 出力
  activeSheet.getRange(4,6).setValue(outputRavenclaw) 

  // Hufflepuff
  baseCount = baseCount + countRavenclaw
  for (let i = 0; i < countHufflepuff; i++) {
    if (i + baseCount > memberTargetCount - 1){
      break
    }
    roomHufflepuff.push(shuffledMembers[i + baseCount]['name'])
  }
  outputHufflepuff = roomHufflepuff.join(",")
  console.log("outputHufflepuff:" + outputHufflepuff); // ログ出力
  // 出力
  activeSheet.getRange(4,8).setValue(outputHufflepuff) 
}

// 配列をランダムにソート
function shuffleArray(inputs) {
 let array = inputs
 for(let i = array.length - 1; i > 0; i--) {
   const r = Math.floor(Math.random() * (i + 1));
   const tmp = array[i]
   array[i] = array[r]
   array[r] = tmp
 }
 return array
}

上記のソースコードを以下のように反映します。
ソースコード２行目の”function sortingHat”の”sortingHat“は、スプレッドシートのボタンとマクロを紐づけする際に使用する名称（関数名）となります。
マクロはコーディング画面から「実行」または「デバッグ」する事も可能です。