2025年3月18日 / 最終更新日時 : 2025年4月12日 officeの杜 AI

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

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

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

1 今回利用するファイル等
2 事前準備
3 技術的ポイント
4 使ってみる
5 関連動画
6 関連リンク

今回利用するファイル等

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

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

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

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

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

事前準備

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

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

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

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

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

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

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

geminikey : Gemini APIのAPIキー

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

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

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

Google Driveを開いて、左上の新規をクリック
その他 => Google Apps Scriptをクリック
スクリプトエディタが直接開いて警告文が出るのでそのまま作成を続行する
ドライブにはスタンドアローンファイルが生成される
スタンドアローンスクリプトをダブルクリックしてスクリプトエディタを開く
左上のプロジェクト名がファイル名になるので、無題のプロジェクトではなくきちんと名前を付けましょう。（今回は緯度経度変換スクリプトという名前にしています）。
後述のGoogle Apps Scriptをコピー&ペーストして保存する
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クラスを使って頭悩ませながらよりもかなり柔軟にデータ生成が行えているのではないかと思います。

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

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

//Gemini 2.0エンドポイントURL
var endpoint = "https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash-exp:generateContent?key=";

//読み書き先スプシのID
var ssid = "ここに読み書き先のスプシのIDを入れる";

//ファイル名から特定しGeminiに読み取らせる
function shiftGenerator(id,start,end) {
  //プロパティの値を取得する
  let prop = PropertiesService.getScriptProperties()
  let apikey = prop.getProperty("geminikey");

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

  //スプレッドシートのデータを取得
  let ss = SpreadsheetApp.openById(ssid);
  let users = ss.getSheetByName("メンバー").getRange("A2:B").getValues()
  let seiyaku = ss.getSheetByName("制約要件").getRange("A2:B").getValues();
  let kobetsu = ss.getSheetByName("個別制約要件").getRange("A2:D").getValues();
  let shift = ss.getSheetByName("シフト");

  //シフトシートから該当idのレコードを削除しておく
  //最終行を取得する
  let lastRow = shift.getLastRow() - 2;

  //シフトデータを取得する
  let range = shift.getRange("A2:E").getValues();

  //1行ずつ精査して
  for (let i = lastRow; i >= 0; i--) {
    //レコードを一個取り出す
    let rec = range[i];

    //IDが一致するかどうか？
    console.log(i)
    if(rec[1] == id){
      //削除ポイント
      let point = i + 2;

      //カウンタの値が1の場合は行を消すのみ
      if(i == 0){
        shift.getRange("A" + point + ":E" + point).clearContent();
      }else{
        //行削除する
        shift.deleteRow(point)
      }
    }
  }

  //メンバーデータを構築する
  let members = [];
  for(let i = 0;i<users.length;i++){
    //空の場合はスルーする
    if(users[i][0] == "" || users[i][0] == undefined){
      continue;
    }

    members.push(users[i][1]);
  }
  members = members.join(",");

  //制約要件データを取り込む
  let seiyakuarr = "";
  for(let i = 0;i<seiyaku.length;i++){
    //空の場合はスルーする
    if(seiyaku[i][0] == "" || seiyaku[i][0] == undefined){
      continue;
    }

    seiyakuarr = seiyakuarr + "・" + seiyaku[i][1] + "\n"
  }

  //個別の制約要件を取り込む
  let kobetsuarr = "";
  for(let i = 0;i<kobetsu.length;i++){
    //レコードを一個取り出す
    let rec = kobetsu[i];

    //空の場合はスルーする
    if(rec[0] == "" || rec[0] == undefined){
      continue;
    }

    //シフトIDが一致するか？
    if(rec[1] == id){
      //そのまま処理を継続する
    }else{
      continue;
    }

    //userのIDを取り出す
    let uid = rec[2];

    //usersから名前を持ってくる
    let uname = "";
    for(let j = 0;j<users.length;j++){
      if(uid == users[j][0]){
        uname = users[j][1];
        break;
      }
    }

    //構築する
    kobetsuarr = kobetsuarr + "・" + uname + "は" + rec[3] + "\n"
  }

  //プロンプトを構築する
  let prompt = `あなたはお店のアルバイトの管理者です。毎週アルバイトのシフト表を作成する必要があります。以下の要件に合うアルバイトの人員配置をしてください。線形計画法に基づき、制約要件を満たすように配置が必要です。出力結果はJSONの配列でお願いします。

    #重要次項
    - 生成値はダブルコーテーションで括ってください
    - 回答は配列に格納して返してください
    - 出力事例に従ったデータだけを返してください。余計な解説や説明は一切不要です。
    - 対象の人物が出勤する日は1、そうでない日は0を出力してください。
    - 出勤できない日であってもデータは0にて出力してください。
   
    #シフト期間
    ${start}〜${end}
    
    #アルバイトのメンバー
    ${members}
    
    #制約要件
    ${seiyakuarr}

    #個別の制約要件
    ${kobetsuarr}
   
    #出力する内容
    出力事例に従った配列形式でデータは返してください。クロス集計する必要はありません。

    #出力事例
    [
      ["日付","メンバー名","出勤"],
      ["2025/3/17", "アリーナ", "1"],
      ["2025/3/18", "アリーナ", "0"],
    ]
  `;

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

  //リクエストオプション
  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;
    
    //余計な文字を除外する
    let ret = result.replace("```json","");
    ret = ret.replace("```","");
    ret = ret.replace("\n","");

    //配列の値として変換
    let array = JSON.parse(ret);

    //先頭のタイトル行を削除しておく
    array.shift();
    
    //配列データを加工してスプシの最終行に追記
    let editarr = []
    for(let i = 0;i<array.length;i++){
      //レコードを一個取り出す
      let rec = array[i];

      //一時配列を構築する
      let temparr =[
        Utilities.getUuid(),
        id,
        rec[0],
        rec[1],
        rec[2]
      ]

       //配列にpushする
       editarr.push(temparr);
    }

    //シートのラストに一括書き出し
    let endrow = Number(shift.getLastRow()) + 1;
    let lastColumn = editarr[0].length; //カラムの数を取得する
    let lastRow = editarr.length;       //行の数を取得する
    shift.getRange(endrow,1,lastRow,lastColumn).setValues(editarr);

    //filterシートに値を書き込む
    ss.getSheetByName("filter").getRange("A2").setValue(id);

    //処理完了
    return true;
  } else {
    return false;
  }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

//Gemini 2.0エンドポイントURL

var endpoint = "https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash-exp:generateContent?key=";

//読み書き先スプシのID

var ssid = "ここに読み書き先のスプシのIDを入れる";

//ファイル名から特定しGeminiに読み取らせる

function shiftGenerator(id,start,end) {

//プロパティの値を取得する

let prop = PropertiesService.getScriptProperties()

let apikey = prop.getProperty("geminikey");

//リクエストURLを構築する

let url = endpoint + apikey;

//スプレッドシートのデータを取得

let ss = SpreadsheetApp.openById(ssid);

let users = ss.getSheetByName("メンバー").getRange("A2:B").getValues()

let seiyaku = ss.getSheetByName("制約要件").getRange("A2:B").getValues();

let kobetsu = ss.getSheetByName("個別制約要件").getRange("A2:D").getValues();

let shift = ss.getSheetByName("シフト");

//シフトシートから該当idのレコードを削除しておく

//最終行を取得する

let lastRow = shift.getLastRow() - 2;

//シフトデータを取得する

let range = shift.getRange("A2:E").getValues();

//1行ずつ精査して

for (let i = lastRow; i >= 0; i--) {

//レコードを一個取り出す

let rec = range[i];

//IDが一致するかどうか？

console.log(i)

if(rec[1] == id){

//削除ポイント

let point = i + 2;

//カウンタの値が1の場合は行を消すのみ

if(i == 0){

shift.getRange("A" + point + ":E" + point).clearContent();

}else{

//行削除する

shift.deleteRow(point)

}

//メンバーデータを構築する

let members = [];

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

//空の場合はスルーする

if(users[i][0] == "" || users[i][0] == undefined){

continue;

}

members.push(users[i][1]);

}

members = members.join(",");

//制約要件データを取り込む

let seiyakuarr = "";

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

//空の場合はスルーする

if(seiyaku[i][0] == "" || seiyaku[i][0] == undefined){

continue;

}

seiyakuarr = seiyakuarr + "・" + seiyaku[i][1] + "\n"

}

//個別の制約要件を取り込む

let kobetsuarr = "";

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

//レコードを一個取り出す

let rec = kobetsu[i];

//空の場合はスルーする

if(rec[0] == "" || rec[0] == undefined){

continue;

}

//シフトIDが一致するか？

if(rec[1] == id){

//そのまま処理を継続する

}else{

continue;

}

//userのIDを取り出す

let uid = rec[2];

//usersから名前を持ってくる

let uname = "";

for(let j = 0;j<users.length;j++){

if(uid == users[j][0]){

uname = users[j][1];

break;

}

//構築する

kobetsuarr = kobetsuarr + "・" + uname + "は" + rec[3] + "\n"

}

//プロンプトを構築する

let prompt = `あなたはお店のアルバイトの管理者です。毎週アルバイトのシフト表を作成する必要があります。以下の要件に合うアルバイトの人員配置をしてください。線形計画法に基づき、制約要件を満たすように配置が必要です。出力結果はJSONの配列でお願いします。

#重要次項

- 生成値はダブルコーテーションで括ってください

- 回答は配列に格納して返してください

- 出力事例に従ったデータだけを返してください。余計な解説や説明は一切不要です。

- 対象の人物が出勤する日は1、そうでない日は0を出力してください。

- 出勤できない日であってもデータは0にて出力してください。

#シフト期間

${start}〜${end}

#アルバイトのメンバー

${members}

#制約要件

${seiyakuarr}

#個別の制約要件

${kobetsuarr}

#出力する内容

出力事例に従った配列形式でデータは返してください。クロス集計する必要はありません。

#出力事例

[

["日付","メンバー名","出勤"],

["2025/3/17", "アリーナ", "1"],

["2025/3/18", "アリーナ", "0"],

]

//payloadを構築する

let payload = {

'contents': {

'parts': [

{

'text': prompt

}

]

'generation_config': {

'temperature': 0,

'topP': 0,

'maxOutputTokens': 8192

}

};

//リクエストオプション

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;

//余計な文字を除外する

let ret = result.replace("```json","");

ret = ret.replace("```","");

ret = ret.replace("\n","");

//配列の値として変換

let array = JSON.parse(ret);

//先頭のタイトル行を削除しておく

array.shift();

//配列データを加工してスプシの最終行に追記

let editarr = []

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

//レコードを一個取り出す

let rec = array[i];

//一時配列を構築する

let temparr =[

Utilities.getUuid(),

id,

rec[0],

rec[1],

rec[2]

]

//配列にpushする

editarr.push(temparr);

}

//シートのラストに一括書き出し

let endrow = Number(shift.getLastRow()) + 1;

let lastColumn = editarr[0].length; //カラムの数を取得する

let lastRow = editarr.length; //行の数を取得する

shift.getRange(endrow,1,lastRow,lastColumn).setValues(editarr);

//filterシートに値を書き込む

ss.getSheetByName("filter").getRange("A2").setValue(id);

//処理完了

return true;

} else {

return false;

}

シフト表の生成

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

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

=QUERY('シフト'!A2:E, "SELECT D, SUM(E) WHERE B='" & filter!A2 &"' GROUP BY D PIVOT C LABEL D 'メンバー'")

1	=QUERY('シフト'!A2:E, "SELECT D, SUM(E) WHERE B='" & filter!A2 &"' GROUP BY D PIVOT C LABEL D 'メンバー'")

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

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

使ってみる

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

シフト作成タブを開く
Addボタンをクリックする
シフトの名称を入力し、開始日と終了日を入力してSAVE（保存）をクリックする
再度作られたレコードをクリックする
個別制約要件の追加をクリックする
個別制約要件としては以下のようなスタイルで入れていく。ユーザを選択したら、制約要件に以下のように入れていく。

ライアン 2025/3/17, 2025/3/18は出勤できないロザリー 2025/3/23のみ出勤可能ピサロ 2025/3/22は出勤できない

1
2
3

ライアン 2025/3/17, 2025/3/18は出勤できない
ロザリー 2025/3/23のみ出勤可能
ピサロ 2025/3/22は出勤できない
保存をして複数の個人の制約要件を自然言語で入れていく。あまりここを厳しくしすぎると解無しになってしまう。
また、ビューとしては用意していませんが、制約要件テーブルの常に適用される要件も生成時には参照してるのでそちらも意識して入れましょう。
最後にシフト生成ボタンを押す
生成シフトデータにデータが生成される。
スプシのfilterシートにIDが書き込まれるので、出勤表シートをみるとシフトデータが生成されてるので内容が制約要件を満たしてるかどうかをよく確認する