2025年6月24日 / 最終更新日時 : 2025年6月24日 officeの杜 AI

Google Apps ScriptとGeminiで条件付きグループ分け【GAS】

Gemini API とGoogle Apps Scriptの組み合わせで、自身でロジックを組むのではなく課題解決できるフロントエンドを構築シリーズのネタを次々挑戦中です。これはSaaS is Deadと言われても納得というレベルで日々、生成AIのみで課題が解決できるプログラムが出来ています。

そんなうちの１つであるメンバーのグループ分けを条件つけて出来ないか？ということで、前回アルバイトのシフト組みがGeminiで実現出来たので、同じような制約要件があるこのテーマを今度は班分けで使えないか？ということで挑戦してみました。

今回利用するツール

以前、当時勤めていた会社にて、グループ企業向けの研修用に集めたメンバーのグループ分けプログラムを作っていました。頻繁に利用する上に結構条件がシビアであったので、毎回頭を悩ませていた班分けをAccessのプログラムの力技でこなしていました。

今回はそのGeminiバージョン。線形計画法に基づき制約要件を満たす班分けをスマートに実現出来るのではないかと考えています。

利点として作り手も使い手も、難しいソルバーなど理解できずとも、またロジックを自前で構築しなくても自然語で同様のことがライブラリを使っているかのように使えるので、スキルが無くてもたどり着けるのが生成AIを使ったプログラムの良い点です。

またこうして用意しておくことで、リモート研修時のMeetのブレイクアウトルームの班分けもスムーズに行えるので、この部分でパワーを使わなくて済みます。

Accessで条件付きグループ分けを作ってみた

AppSheetとGeminiを使ってアルバイトシフト表を生成する

事前準備

Gemini APIキーを取得する

GeminiのAPIキーが必要です。以下のエントリーに独立してまとめています。課金されますので利用のしすぎには要注意。また課金されていない場合、学習に利用されてしまう恐れがあるので、きちんとGoogle Cloud上で課金アカウントとの紐付けなどをしておきましょう。

GeminiのAPIキーの取得と学習の可否

スクリプトプロパティに値を格納

前述までに取得しておいたGeminiのAPIキーについて、GASのスクリプトプロパティに値をセットします。

  • apikey : Gemini APIのAPIキー

図：スクリプトプロパティに格納する

データシートについて

スプレッドシートやAppSheet上からいくつか事前にデータの準備をする必要があります。

userlistシート

社員番号やユーザの姓名、年齢や性別などをまとめたユーザのリストです。この元データを取得し、Geminiに問い合わせることで班分けを行うのが今回の目的です。

このデータ自身もGemini for Google Workspaceのサイドパネルからダミーデータとして生成しています。以前は10件程度しか出来なかったのが、現在は100件程度なら簡単に生成し、そのままスプレッドシートに差し込むことが可能です。

ダミーデータの準備も簡単な時代になりましたね。

図：簡単な研修参加者リストになります

groupシート

ここはGoogle Apps ScriptでGeminiにてグループ分けした配列データを書き出す場所になります。常に洗替えで生成されるようになっている為、毎回クリアされます。

事項のグループ理由条件に従い、A列はグループ名が付くようになっています（A〜Zまでのアルファベット）。ダミーに1とあるのはグループ表シートでQuery関数を使ってクロス集計する為に敢えて用意してる列になっており、ここまで含めてGeminiにて生成を依頼しています。

図：Gemini問い合わせの結果出力用シート

グループ表シート

前述のgroupシートの中身をQuery関数を持ってしてクロス集計してるページになります。列にグループ名を使い、行側にメンバー名、値はダミーの値のカウントでピボットしてます。数式としては以下のようになっています。

=QUERY(group!A2:E, "SELECT B, COUNT(E) GROUP BY B PIVOT A LABEL B 'メンバー'")

この数式も、groupのシートをスクショを撮ってGeminiに問い合わせし、縦横カウントにどの列を使うか？を指示した結果の数式を出してもらいました。

Query関数は構築が難しい関数であるので、このようにスクショを撮って問い合わせをするとスムーズに数式を生成してくれます。

図：Query関数でクロス集計表にしてみた

図：数式生成もGeminiにやってもらった

Query関数を使ってデータを集計しよう - 其の壱

ソースコード

グループ分け条件

今回のグループ分けは少し厳し目の設定になっていますが、解答が出せなくはない条件にしています。

  • 解答データは配列で出力を指定（列やその値についても細かく仕様を指定）
  • 解答がない場合の出力についてもあらかじめ定義しています。
  • メンバーデータはスプシデータをそのまま渡しています。
  • 制約要件は以下の通り
    • 各班は5名ずつが原則（余った場合は偏らないように振り分けを指定）
    • 同姓同名は別の班に分けるように指定
    • 班ごとにメンバーの年齢構成が偏らないように考慮しての班分けを指定
    • 同じ部門の人が偏らないようにバラけるように指定
    • 性別についても偏らないようにバラけるように指定
    • 出力内容のグループ名について、A〜Zまでのアルファベットを振る
    • 出力内容の氏名は姓名を半角スペースで結合したものを使うように指定
    • ダミー列には1を固定値として出力指定

例示も含めてかなり長文のプロンプトになっています。しかしこれが肝であり、ここまで深く調整をしてあげないと、制約要件を満たしていない結果が返ってくる可能性があります。

ユーザーはスプシにデータを入れて、関数を実行するだけなので、お手軽に動かせるようにしています。

グループ分けのコード

モデルとしてはGemini 2.5 Proを利用しています。特徴としては

  • スプシのメンバーデータは配列のままGeminiに渡しています。
  • プロンプトにて前述の制約条件を詳しく列挙し、出力内容も細かく指定しています。
  • 出力結果は配列で返ってくるので、特に無加工のままスプシにそのまま一括書き出ししています。

時々上手く班分けが難しいケースもありますが、再度実行することで今回の条件の場合はだいたい生成出来ています。必ずしも解があるとは限らないのが、制約条件に基づく線形計画法によるグルーピングなので、条件を厳しくしすぎないように注意が必要です。

//モデル
const model = "gemini-2.5-pro";

//エンドポイントURL
const endpoint = `https://generativelanguage.googleapis.com/v1beta/models/${model}:generateContent?key=`;

//班分けをGeminiに依頼
function groupDivision(){
  //プロパティの値を取得する
  let prop = PropertiesService.getScriptProperties()
  let apikey = prop.getProperty("apikey");

  //リクエストURLを構築する
  let url = endpoint + apikey;

  //スプレッドシートのデータを取得
  let ui = SpreadsheetApp.getUi();
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let users = ss.getSheetByName("userlist").getDataRange().getValues();
  let group = ss.getSheetByName("group");

  //groupデータをクリアする
  group.getRange("A2:E").clearContent();

  //プロンプトを構築する
  let prompt = `あなたは研修を行う講師です。研修参加者をいくつかのグループに分ける必要があります。線形計画法に基づき、制約要件を満たすようにグループを分けてください。出力結果はJSONの配列でお願いします。

    #重要事項
    - 生成値はダブルコーテーションで括ってください
    - 回答は配列に格納して返してください
    - 出力事例に従ったデータだけを返してください。余計な解説や説明は一切不要です。
    - 研修のメンバーは2次元配列データです。このデータを元にグループ分けを行います。1行目はヘッダです。
    - グループ分けが出来なかった場合には「グループ分け困難」とだけ答えを返してください。無理やり回答せずに。
    
    #研修のメンバー
    ${users}
    
    #制約要件
    - 各グループは原則5名ずつ配置が必要です。
    - 同姓同名の人は別々のグループに配置するようにしてください。
    - 例外として5名ずつ配置後に余った人数は既存のグループのどこかに加えてください（同じグループにまとめて追加しないでください）
    - 全メンバーの年齢の中央値を考慮し、なるべくどのグループに年齢層がバラけて配置するようにしてください。特定のグループにだけ年齢が若い人が集中しないように。
    - 同じグループに同じ部門の人をなるべく配置しないでバラけるようにしてください。
    - 性別のなるべくバラけるようにしてください。特定グループに極端に片方の性別が集まらないように。
    - 各グループはA〜Zまでのグループ名をつけ、出力する内容に従って出力してください。
   
    #出力する内容
    出力事例に従った配列形式でデータは返してください。クロス集計する必要はありません。
    出力する列は以下の通り
    - グループ名列 : A〜Zまでのグループ名
    - 名前 : 姓の漢字と名の漢字を半角スペースで結合したもの
    - 年齢 : 対象者の年齢
    - 性別 : 対象者の性別
    - ダミー : 値は1固定

    #出力事例
    [
      ["A", "山本 太郎", "26", "男", "1"],
      ["B", "篠山 京子", "30", "女", "1"],
    ]
  `;

  //payloadを構築する
  let payload = {
    'contents': {
      'parts': [
        {
          'text': prompt
        }
      ]
    },
    'generation_config': {
      'temperature': 0,
      'topP': 0,
      'maxOutputTokens': 12000
    }
  };

  //リクエストオプション
  let options = {
    'method': 'POST',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  };

  //Geminiにリクエスト
  let response = UrlFetchApp.fetch(url, options);
  let json = JSON.parse(response.getContentText());

  //結果を受け取る
  if (json && json.candidates && json.candidates.length > 0) {
    let result = json.candidates[0].content.parts[0].text;

    //エラーかどうか？
    if(ret == "グループ分け困難"){
      ui.alert(ret)
      return false;
    }
    
    //余計な文字を除外する
    let ret = result.replace("```json","");
    ret = ret.replace("```","");
    ret = ret.replace("\n","");

    //配列データをパースする
    let jsondata = JSON.parse(ret)

    //データの一括出力
    let lastColumn = jsondata[0].length; //カラムの数を取得する
    let lastRow = jsondata.length;       //行の数を取得する
    group.getRange(2,1,lastRow,lastColumn).setValues(jsondata);

    //処理完了
    ui.alert("完了しました。")
    return true;
  } else {
    return false;
  }
}

関連リンク

カテゴリー
AI、Google、JavaScript
タグ

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）

Google

前の記事

Exchange OnlineからGmailへ移行するツールを検証してみた新着!!
2025年6月24日