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

AppSheetでアルバイトのシフト表生成できないか?という注文が来たので、これを実現する仕組みを用意しなければなくなった為、色々と思案。しかし、このバイトシフト表作成はメンバー個々人の都合や日々の制約要件というものがあり、なかなか上手く嵌め込むのが難しい。

さらに上位のシフト表となると医療機関の看護師配置基準であったり、工場の三交代制で尚且つ対象マシンのスキルの有無などをまで考慮する必要があり、制約が厳しくなると解がないなんてケースも(結果管理職が代わりにシフト入ったり・・・)。少しでもこれを生成AIで軽減できないか?ということで取り組んでみました。

今回利用するファイル等

過去に同様の事例において、Google Apps ScriptのLinearOptimizationServiceというクラスを使った、線形計画法に基づくシフト割り当てを作ったことがあります。しかし非常に数学的で扱いにくい上に動作も結構遅い

これを今回、生成AIであるGeminiで代用してなんとか生成できないかなぁというのが一番のテーマです。まだ現時点ではアルバイトシフト割り当ての最も簡単なパターンのみなので、時間帯別割り当てや看護師配置基準対応、三交代制且つスキル考慮などより制約要件の厳しい内容は今後の課題です。

しかし、今回の簡単なアルバイトシフト割り当てに関しては旨く生成できたのでこれを元にアプリを構築したいと思います。前回作成したタイムレコーダーアプリと合体させたら尚良いかもしれません(アルバイトの人は次週のバイトシフトをスマフォで確認できるようになる)。

Google Apps Scriptでソルバーを実現する【GAS】

AppSheetでタイムレコーダーを作成する

事前準備

Gemini 2.0 FlashのAPIキーを取得する

Gemini Advancedなどの後ろで動いてるAPIを直接利用します。以下の手順でGemini 2.0 FlashのAPIキーを今回は取得してみます。

  1. Googleアカウントにログインした状態にしておく
  2. こちらのウェブサイトにアクセスする
  3. APIキーを作成をクリックする
  4. 1個だけはそのままAPIキーを作成で続行できます。2つ目は既存のGCPプロジェクトを選ぶよう指示が出ます。GCPプロジェクトを指定しなかった場合には新規にGCPプロジェクトが生成されます。
  5. APIキーが発行されるのでコピーする

このAPIキーは無料ですが、流出することの無いように大切に保管する必要があります。このキーは後でGAS側で利用します。

図:APIキーを発行してる画面

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

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

  • geminikey : Gemini APIのAPIキー

 

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

スタンドアローンのGASの準備

AppSheetテンプレートをコピーした後はGASへの参照が切れています。故に自身でスタンドアローンコンテナのGASファイルを作成して繋げてあげる必要があります。ここではそのGASのファイルの作成手順です。

  1. Google Driveを開いて、左上の新規をクリック
  2. その他 => Google Apps Scriptをクリック
  3. スクリプトエディタが直接開いて警告文が出るのでそのまま作成を続行する
  4. ドライブにはスタンドアローンファイルが生成される
  5. スタンドアローンスクリプトをダブルクリックしてスクリプトエディタを開く
  6. 左上のプロジェクト名がファイル名になるので、無題のプロジェクトではなくきちんと名前を付けましょう。(今回は緯度経度変換スクリプトという名前にしています)。
  7. 後述のGoogle Apps Scriptをコピー&ペーストして保存する
  8. AppSheetのAutomation内のCall a scriptで参照してるGASを今回作ったものに差し替えます。

見つからない場合には、Chrome Webstoreから拡張機能インストールしましょう(Google Apps Scriptで検索すると出てくる)。

図:新規から作成することが出来る

データの準備

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

メンバーシート

所属してるアルバイトのメンバー表です。IDは連番とし、メンバーの名前を書くだけの簡単なシートです。このメンバーを元に指定期間のシフト表を作成することになります。

図:メンバー表をまずは整備する

制約要件

固定の制約要件です。通常はあまり書き換えをすることはありません。個別のメンバーのスポットの制約要件(この日は出勤できないといった内容)は、AppSheet上で各シフト表作成時に手動で指定することになります(個別制約要件シートに記載するが、AppSheet上で行う必要がある)。

制約内容は自然言語でOK。この内容のまま制約要件としてGemini問い合わせに利用します。

図:常に適用される固定の制約要件です

技術的ポイント

AppSheet側での実装

AppSheet側は今回はあまりUI的なロジックとしてこれまでもブログで紹介してきたようなリレーションくらいで、独特のロジックといったものはありません。更新フラグ列を用意してアクションで更新→GASを発動させて返り値を受け取るがしっかりできていればそこまで難しい実装ではないのではないかと思います。

ただし、個別制約要件シートではユーザ名ではなくIDが書き込まれてるので、個別制約要件追加時のドロップダウンの部分は注意が必要です。

またデータ生成上出勤を1、出勤なしを0として表現していたのでこれを☁️と⭕️で表現しなおす為に、出勤項目というテーブルを用意して置き換えて表現してる部分があります。このほうがユーザとしてはわかりやすいのでその為のロジックです。シフトテーブルに仮想列を追加して表示させているので、この部分はUI表現の為だけですが注意が必要です。

図:数値をアイコンに置き換えてます

AppSheetで他のアプリと横連携させる【GAS】

Google Apps Script側での実装

今回のアプリはGeminiに必要なデータを付け加えた上で問い合わせを行い、アルバイトのシフト表データを制約条件をクリアする形で返してもらい、スプシに書き込むという作業になります。本来はこのような処理も外部のサブスクでいくつか支援サービスがありますが有償であり、手が出しづらいなぁという時にAppSheetで作れるならまずそれを使ってみようというワンステップを試せるのが良いポイントです。

ポイントとしては、

  • コード内のssid変数内に読み書き先のスプシのIDを必ず入力しておくこと
  • プロンプトでしっかりと期間、渡すデータ、制約要件を列挙すること
  • ユーザデータや制約要件はシートで整備したりAppSheet上で追加したものを動的に付け加えること
  • 出力形式は配列形式で返すようにしてもらう
  • 最後にスプシに合うようにデータを整形して一括書き込み
  • 出勤表自体は生成データをシフトのIDを元にフィルタ(filterシートに書き出しています)、Query関数でしっかりとクロス集計させています(次項参照)

という割とシンプルな流れになっています。プロンプトがとっても重要で、今回Gemini Advancedで使ったプロンプトをそのまま投げたら、なぜかPythonのコード生成がされて返ってきました・・・・故に、しっかりとアルバイトのシフト表作成のデータを生成して欲しいというのを伝えないといけません。

正直、期待していなかったもののLinearOptimizationServiceクラスを使って頭悩ませながらよりもかなり柔軟にデータ生成が行えているのではないかと思います。

図:なかなか良さげにデータ生成してくれる

図:スプシに書き出してみた様子

シフト表の生成

シフトシートの内容を元に、尚且つAppSheet上でPDF生成時においてシフト作成シートの対象のレコードのIDをfilterシートに書き込んでいます。この値を元にQuery関数にてクロス集計を掛けたものをPDF生成対象としています。データは出勤表に出力しています。

数式としては以下のような数式がA1に入っています。filterのA2の値はUUIDなのでテキスト型である点に注意。よってシングルコーテーションで括らないと、Where条件が動きません。

図:Query関数でクロス集計を掛けています

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

使ってみる

使い方はシンプルで簡単です。以下の手順でAppSheetアプリ上で適当に指示を出すだけ。注意ポイントは「個別制約要件」の指示の仕方になります。

  1. シフト作成タブを開く
  2. Addボタンをクリックする
  3. シフトの名称を入力し、開始日と終了日を入力してSAVE(保存)をクリックする
  4. 再度作られたレコードをクリックする
  5. 個別制約要件の追加をクリックする
  6. 個別制約要件としては以下のようなスタイルで入れていく。ユーザを選択したら、制約要件に以下のように入れていく。
  7. 保存をして複数の個人の制約要件を自然言語で入れていく。あまりここを厳しくしすぎると解無しになってしまう。
  8. また、ビューとしては用意していませんが、制約要件テーブルの常に適用される要件も生成時には参照してるのでそちらも意識して入れましょう。
  9. 最後にシフト生成ボタンを押す
  10. 生成シフトデータにデータが生成される。
  11. スプシのfilterシートにIDが書き込まれるので、出勤表シートをみるとシフトデータが生成されてるので内容が制約要件を満たしてるかどうかをよく確認する

データは同じシフトIDのものは削除されての洗い替え方式で生成しています(別のシフトを作って生成した場合IDが変わるので古いものが消えるということはありません)。よって再度シフト生成をクリックすると直前に生成されたデータはスプシからは消えてしまいます。

出勤表シートは1が出勤、0が出勤無しです。アプリ側でも生成シフトデータに同様に出てきます

図:シフトを作成する

図:個別制約要件を登録していく

図:出勤表が生成されました。

関連動画

【Gemini】今日から使える活用術2選

関連リンク

コメントを残す

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

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