【GAS】Google Apps Script 活用事例　制約条件があるグルーピングの自動化

アイスブレイクや全社的なイベントなどで組み分けを考える機会がありました。ただ乱数を使って事足りるのであれば、GASを使わず、RANDBETWEEN関数などを使う手もあります。前職でもそういう機会があったので、割とそういう会社少なくないのかなと思っています。

RANDBETWEEN 関数 - Microsoft サポート

制約条件は次の3つ

1.  グループ内に管理職が必ずいること
2. グループ内に女性が必ず一人いること
3. グループをならして配置する必要がある(例：3人, 3人, 2人, 2人など)

Webサービスを使用したり、上述のRANDBETWEEN関数を使用した場合、制約条件を全て満たすには、どこかで手作業が必要です。
男女比7:3ぐらいを想定した場合、偏りが生じて純粋に乱数のみの場合、女性がいなくなってしまうグループが生じます。

3番目の条件も今回の自動化の難易度を上げるポイントでした。
人数を動的に変える必要があります。

スプレッドシートのイメージ

上記のスクショは、なーんちゃって個人情報で作成しています。
アクティブ欄には、育休などでグルーピングに参加できない休職中などの社員を想定しています。退職しない限りは表に残しておくのが賢明かなと思っています。

データがこんな感じで取得されるので、男性か女性か、管理職かどうかなどを把握しながら処理を実装しています。

乱数を用いてグループを作成する一連の処理

/**
 * 従業員マスタから情報を取得して、成形する
 * 管理職は乱数を使わず固定
 * 管理職以外の男性と女性のみを乱数を使ってシャッフルしている
 * 
 * @return {Array.<object.<string>>}
 * 
 */
function generateObjectsInValues() {
  const sheet  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('従業員マスタ');
  const values = sheet.getDataRange().getValues();

  // 従業員マスタから休職者を取り除く
  const leaders = getTargetEmplpoyee_(values, 'アクティブ', '管理職'); // [{index: 0, name: nobita, role: '一般', gender: '男性'}]
  const members = getTargetEmplpoyee_(values, 'アクティブ', '一般');   // [{index: 0, name: shizuka...}]

  const groups    = createGroups_(leaders, members);
  const shuffled  = reformatArray_(groups, '女性', '一般');
  const shuffled2 = reformatArray_(shuffled, '男性', '一般');

  return shuffled2

}



/**
 * 
 * 残余引数で、男性、一般などのキーワードを指定して該当する行のみを乱数で入れ替える
 * 
 * @param  {Array.<Object.<string>>} groups - オブジェクトを含む配列
 * @param  {Array.<string>} queries - 残余引数
 * @return {Array.<Object.<string>>}
 * 
 */
function reformatArray_(groups, ...queries){
  // クエリに一致するメンバーを抽出
  let matchingMembers = groups.flat().filter(member => queries.includes(member.gender) && queries.includes(member.role));

  // メンバーをシャッフル
  const newArray = shuffle_(matchingMembers);

  // シャッフルされたメンバーを元の位置に戻す
  let index = 0;
  groups.forEach(group => {
    for (let i = 0; i < group.length; i++) {
      if (queries.includes(group[i].gender) && queries.includes(group[i].role)) {
        group[i] = newArray[index++];
      }
    }
  });

  console.warn(`シャッフル後のGroups 条件：${queries.join(',')}`);
  console.log(groups);
  return groups;
}



/**
 * 従業員マスタから、分かち合い参加対象者のみを取得する
 * 
 * @param {Array.<Object.<string>>} values - オブジェクトを含む配列
 * @param {Array.<string>} 
 * 
 */
function getTargetEmplpoyee_(values, ...params) {
  const keys = values[0];

  // 配列の中に、queryを含むもののみを残す
  const filtered = values.filter(row => params.every(param => row.join(',').includes(param)));
  console.log(`取得条件：${params}\n対象者:${filtered.length} 人`);

  // オブジェクトに変換する
  filtered.unshift(keys);
  return convertValuesToObjects_(filtered, 0, keys);

}



/**
 * 2次元配列内の1次元配列を全てオブジェクトに変換するスクリプト
 * FIXME: 見出しが日本語だとオブジェクトの各keyも日本語になってしまう
 * 
 * @param  {Array.<Array.<string|number>>} values - 2次元配列
 * @param  {number} columnIndex - 空白をチェックする列のインデックス（0から始まる）
 * @param  {Array.<string>} keys - 省略可、オブジェクトのkeyを格納した1次元配列 (例)　['name', 'url']
 * @return {Array.<Object.<string|number>>}
 * 
 */
function convertValuesToObjects_(values, columnIndex, keys) {

  // headers にvalues[0], recordsに、valuesの内容をコピーする　（スプレッド構文）
  const [headers, ...records] = values; 
  console.log(values);

  // keysが指定されていればそれをヘッダーとして使用し、そうでなければvaluesの1行目をヘッダーとする
  const customHeaders   = keys || headers;
  const filteredRecords = records.filter(record => record[columnIndex]);

  // 2次元配列内の1次元配列をオブジェクトに置き換える
  // customHeaders[0] = name;
  // [Bob, 20, ramen] -> [name, Bob] -> {name: Bob}
  const objects = filteredRecords.map(record => Object.fromEntries(
    record.map((value, i) => [customHeaders[i], value])
  ));

  console.log(objects);
  console.log(`検算：${objects.length}人`);
  return objects
}



/**
 * グループ分けを行う関数
 * 
 * @param  {Array.<Object.<string>>} leaders - 管理職のみ
 * @param  {Array.<Object.<string>>} members - 管理職以外（男女一緒）
 * @return {Array.<Object.<string>>}
 * 
 */
function createGroups_(leaders, members) {
  
  let groups = [];
  const alphabetArray = generateAlphabetArray_();

  for (let i = 0; i < leaders.length; i++) {
    let femaleCount = 0;
    let eachGroup   = [leaders[i]];
    let groupSize   = Math.ceil((members.length + leaders.length - i) / (leaders.length - i));
    console.log(`groupSize: ${groupSize}`);

    // 女性管理職の場合の処理
    femaleCount = eachGroup[0].gender === '女性' ?  1 : 0;

    // メンバーをグループに追加（管理職を含めて人になるまで）
    while (eachGroup.length < groupSize && members.length) {

      // @param {name: 'nobita', gender: '男性'}
      let currentMember = members.shift(); 

      // 女性メンバー、またはすでに女性がいる場合、または管理職しかいない場合に追加
      if (currentMember.gender === '女性' || femaleCount > 0 || eachGroup.length === 1) {
        eachGroup.push(currentMember);

        if (currentMember.gender === '女性') {
          femaleCount++;
        }
      } else {
        // 女性がまだいない場合は、男性メンバーを一時的に保留
        members.push(currentMember);
      }
    }
    // 完成したグループをgroups配列に追加
    console.warn(`グループ${alphabetArray[i]}　${eachGroup.length}人`);
    console.log(eachGroup);

    // 女性がいるかどうかを判定する
    const hasFemaleInArray = eachGroup.some(obj => obj.gender === '女性');
    hasFemaleInArray ? console.log(`配列に少なくとも1人は女性が含まれています`) : console.warn(`配列に女性が1人も含まれていません`);
    groups.push(eachGroup);

  }

  console.log(groups);
  return groups;
}



/**
 * アルファベットの配列を作成する関数
 * 
 * 
 */
function generateAlphabetArray_(){
  let doubleAlphabetArray = [];
  const alphabetArray = Array.from({length: 26}, (_, i) => String.fromCharCode('A'.charCodeAt(0) + i));
  for(let i = 65; i <= 90; i++) {
    for(let j = 65; j <= 90; j++) {
      doubleAlphabetArray.push(String.fromCharCode(i) + String.fromCharCode(j));
    }
  }
  const merged = alphabetArray.concat(doubleAlphabetArray);
  // console.log(merged);

  return merged
}



/**
 * 
 * Fisher-Yates (Knuth)
 * 1次元配列内の項目を入れ替える
 * 
 * @param  {Array.<Object.<string>>}
 * @return {Array.<Object.<string>>}
 * 
 */ 
function shuffle_(array) {
  for (let i = array.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [array[i], array[j]] = [array[j], array[i]];
  }
  return array
}

男女別に色分けをして転記するスクリプト

別記事に分けることも考えたのですが、別の関数で処理を切り分けているので、これだけ見ても分かんないよな….と思い直し一緒に載せることにしました。

男性は薄い青で、女性は薄い赤でスプレッドシートに書き出します。
あと、垂直水平中央に変更する処理も加えています。

function convertObjectsToValues() {
  const values = generateObjectsInValues();
  const alphabetArray = generateAlphabetArray_(); // ['A', 'B', 'C',....]
  let colors = [], temp = [], newValues = [['index', 'name', 'group', 'mailAddress']];

  // スプレッドシートに転記できる形に変換する
  // [[{}, {}], [{}, {}]]
  // 各オブジェクトに対して処理を行う
  values.forEach((array, index) => {
    array.forEach(obj => {
      // 性別に基づいて色を決定
      let color = (obj.gender === '男性') ? '#c9daf8' : '#f4cccc';
      console.log(`${obj.name} ${obj.gender}: ${color}`);

      temp.push([color]);
      console.log(temp);

      // colors配列に追加
      newValues.push([obj.index, obj.name, alphabetArray[index], obj.mailAddress]);
    });

    colors = colors.concat(temp);
    temp = [];

  });
  console.log(colors);
  console.log(newValues);

  // 値の貼り付けと着色
  setupNewSheet_(colors, newValues);
}



function setupNewSheet_(colors, newValues){
  const newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
　　　　const date = Utilities.formatDate(new Date(), 'JST', 'yyyy_MM');
  try{
    newSheet.setName(`${date}`); // yyyy_MM形式でシート名を命名

    // シート名が存在する場合は、エラーになるtry内で実行
    // [1, '野比のび太', 'A', '****@samaple.co.jp']
    const targetRange = newSheet.getRange(1, 1, newValues.length, newValues[0].length);
    targetRange.setValues(newValues);

    // 男女別に色分けする・ 見出し行を除くため2行目から開始
    newSheet.getRange(2, 2, colors.length).setBackgrounds(colors);
    newSheet.activate();

    // infoは４列
    const info  = targetRange.getValues().map(row => row.map(value => 'middle'));
    const info2 = targetRange.getValues().map(row => ['center']);

    console.log(`info`);
    console.log(info);

    // 1列目、3列目水平中央
    const columns = [ 1, 3 ];
    columns.forEach(column => {
      newSheet.getRange(1, column, info2.length).setHorizontalAlignments(info2);

    });
  }catch(error){

    console.warn(`${error}`);
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`${date}`); 
    sheet.getRange(1, 1, newValues.length, newValues[0].length).setValues(newValues);

    // 男女別に色分けする・ 見出し行を除くため2行目から開始
    sheet.getRange(2, 2, colors.length).setBackgrounds(colors);
    sheet.activate();
　　　　　　　　Utilities.sleep(5000); // 5秒間スリープ
    const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

    // 不要なシートを削除
    sheets.forEach(sheet => {
      if(sheet.getName().match(/シート.*/g) !== null){
        console.log(sheet.getName());
        SpreadsheetApp.getActiveSpreadsheet().deleteSheet(sheet);
      }
    });
  }
}

自分用の備忘録

function test_code001() {
  const sheet = SpreadsheetApp.getActiveSheet();
  console.log(`${sheet.getName()}`);
  const values   = sheet.getDataRange().getValues();
  const selected = getTargetEmplpoyee_(values, '男', '一般');　// @return Array.<Object> オブジェクトを含んだ1次元配列
  const newObj   = {index:21, name: '野比のび太', gender: '男', isActive: 'アクティブ', mailAddress: '******@sample.co.jp'}
  selected.push(newObj);

  console.log(selected.shift()); //　オブジェクトで取得される
  console.log(selected); // のび太が配列の最後に追加される WhileがTrueになるまで繰り返される

}

shift()によって切り出されるのは、オブジェクト
push()も配列にオブジェクトが追加されます。

createGroups_()関数が一連の処理が肝で、While文と併用することにより、条件式がfalseになるまで処理を続けます。
条件式が間違っていると、無限ループが発生してしまいます。

for文を使うと、男性しかグループにいない状況で、continueなどでスキップすると人数が揃わなくなってしまします。

function decideGroupSize() {
  const members = [
  "佐藤", "鈴木", "田中", "高橋", "木村", 
  "林", "佐々木", "斎藤", "阿部", "野口", 
  "前田", "根本", "岡本", "島田", "渡辺", 
  ];

  console.log(`一般社員の人数:${members.length}`);

  const leaders       = ['サトウカズマ', 'アクア', 'めぐみん', 'ダクネス', 'アイリス'];
  const groupAlphabet = ['A', 'B', 'C', 'D', 'E'];

  for (let i = 0; i < leaders.length; i++) {

    let newGroup = [leaders[i]];
    const groupSize = Math.ceil((members.length + leaders.length - i) / (leaders.length - i));

      console.log(`Math.ceil((${members.length} + ${leaders.length} - ${i}) / (${leaders.length} - ${i}))`);
      console.log(`グループ　${groupAlphabet[i]}：　${groupSize}名`);

    while(newGroup.length < groupSize && members.length){
      // console.log(`newGroup.length: ${newGroup.length} < groupSize: ${groupSize} && members.length: ${members.length}`)
      newGroup.push(members.shift()); //各メンバーをグループに追加

    }
  }
}

while - JavaScript | MDN

while 文は、テスト条件が真と評価されている間、指定された文を実行するループを作成します。

while (eachGroup.length < groupSize && members.length) 
メンバーが15名、リーダーが5名、計20名の場合、1グループあたり4名
実行すると流れが分かると思います。

membersをshift()によって切り出して、eachGroupに追加されたあとは、membersが徐々に減っていくため、groupSizeが大きくなることはなく、条件式が成立するようになります。

サンプルデータの作成をするときのプロンプト

GoogleのGemini（旧BARD）に依頼すると、スプレッドシートにエクスポートが出来るので、ぜひお試しあれ
まだ、なーんちゃって個人情報の方が優秀ですね….。

下記の条件を満たす管理表をスプレッドシートで作成したいと考えています。 手伝ってくれますか？

- 5つのカラム
- カラムはそれぞれ、index, name, gender, role, maleAddress
- roleは役職で、管理職かそれ以外か2つのフラグだけで十分です。
- 男女比は、7:3
- 計算を簡略化するために、管理職は5人、女性管理職も最低1人、
- サンプルデータは、20人

あとがき

今回の一連の処理を効率化するのにChat GPTの力を借りました。
壁打ちしながら、もっとスマートに書けない？とかプロンプトが曖昧でも、割と良い回答を出してくれるようになりました。

でも、まだハルシネーション（間違った答え、エラーの出るスクリプト）が返されますね。でも時折、スマホのSSRガチャのように思っても見なかった頭の良い処理を返してくれる時があって、びっくりもしつつ、よしなに取り入れて、ようやく難題をクリアする事ができました！！

エンジニア界隈でもGitHub Copilotを使い始めたりしている会社が少なくないので、自分もAIを上手く活用していきたいなと思っています。