GAS(GoogleAppScript）でシフト表をガントチャート風に変換してみた

はじめに

日ごとの勤務時間をビジュアル的にわかりやすく表示する方法を紹介します。

シフト管理ツールには、CSV形式などで月ごとのシフト表を出力できるものがあります。

スプレッドシートやExcelといった表計算ツールには関数が備わっており、自由自在にデータを変形できます。

とはいえ、複雑さが一定のラインを越えた辺りで、重たかったり書きづらかったりして、ストレスが溜まってきます。

そこで、プログラム（GAS）を書いて処理する方法を検討してみました。コードを見慣れない人には若干怖いかもしれないですが、

やることとしてはChatGPTに発注するだけなので、ご心配なく。

こんな方を想定しています

• プログラミングはできないが、抵抗感はない方
• バイトのマネージャーをしている方
• スマレジなどのツールを利用している方
• シフト表をガントチャート風にしてみたい方

GASとは

GASは、Googleが開発したJavaScript風のプログラム実行環境です。

Googleドライブ上で動くので環境構築が要らず、単発または定期実行の｢ちょい複雑な処理｣に強いというメリットがあります。

Pythonなどと比較されることもありますが、いわゆるプログラミング言語とは求めるものが違うので、これといった弱みはない気がします。

準備するもの

• Googleスプレッドシート（編集権限があるもの）
• ChatGPT（ほかの生成AIでもよい）

手順

①スプシを開く

Googleドライブからスプシを開きます。Excelにある場合は、適当なファイル名で作成してください。

シート名は”2024.8”にし、変換後のデータを載せるシートとして”shiftchart”をつくっておきます。

この際、”2024.8”は以下のようになっていると仮定します。

	2024-08-01	2024-08-02	2024-08-03	2024-08-04	2024-08-05	2024-08-06	2024-08-07
中 あかり				16:00 ~ 22:00		09:00 ~ 17:00
青葉 ひろみ
磯子 たかし
港南 さやか		16:00 ~ 22:00
鶴見 ゆうた
南 まこと			09:00 ~ 17:00	09:00 ~ 15:30	09:00 ~ 15:30
金沢 みさき
緑 あおい
保土ヶ谷 しんじ	16:00 ~ 22:00	09:00 ~ 17:00	09:00 ~ 17:00		09:00 ~ 17:00	09:00 ~ 17:00
西 れいこ
栄 かなめ		16:00 ~ 22:00	16:00 ~ 22:00	16:00 ~ 22:00		16:00 ~ 22:00
旭 まなみ		09:00 ~ 17:00	09:00 ~ 17:00			09:00 ~ 15:00

シフト表 = A列に従業員氏名があり、行１に日付（YYMM/DD)が記載されています。

②ChatGPTに相談する

GASを使うこと、データがスプシに載っていることを伝え、背景情報やデータの形式を詳しく説明します。たとえば、

色分けされたガントチャート風のシフト表をつくりたい。

シフトのデータはスプレッドシートにの"2024.8" というシートに記載されており、これをGASによって処理し、同じファイルの"shiftchart"に色分けした日ごとのシフトを表示したい。

"2024.8"について解説する。縦軸（A列）に名前、横軸（1行目）に日付があり、B2セルより内側のエリアに"HH:MM-HH:MM"（ただし、HHは時刻、MMは分を表す）の形で勤務時間帯が記載されている。

これを、1日24時間に区切り、5時から24時の間で各従業員の就業時間が色分けによって判別できる"shiftchart"に変換する。縦軸（A列）はshiftdataそのままの従業員氏名、横軸（1行目）は日付、横軸（2列目）が時刻で、その内側に色分けされたエリアを設ける。就業時間はピンク、それ以外は白くするコードを書いてほしい。

みたいに発注するとよいでしょう。コツは、表の何列目に何があって… という情報を明記することです。

ただ、このプロンプトそのままだと、うまくできませんでした。

そこで、改めて表のフォーマットをそのまま送ってあげることにしました。まず、これが変換前のシート。

従業員名	2024-08-01	2024-08-02	2024-08-03
中 あかり
青葉 ひろみ
磯子 たかし
港南 さやか		16:00 ~ 22:00
鶴見 ゆうた
南 まこと			09:00 ~ 17:00
金沢 みさき
緑 あおい
保土ヶ谷 しんじ	16:00 ~ 22:00	09:00 ~ 17:00	09:00 ~ 17:00
西 れいこ
栄 かなめ		16:00 ~ 22:00	16:00 ~ 22:00
旭 まなみ		09:00 ~ 17:00	09:00 ~ 17:00

次に、これが変換後のシート。

従業員名	2024/08/01
時間帯	6:00	7:00	8:00
中 あかり
青葉 ひろみ
磯子 たかし
港南 さやか
鶴見 ゆうた
南 まこと
金沢 みさき
緑 あおい
保土ヶ谷 しんじ
西 れいこ
栄 かなめ
旭 まなみ

Markdownでは色が塗れないのでこういう書き方になってしまいますが、うまく意味を汲み取ってくれました。

メタ的なことを言うと、色を塗る処理は｢色を塗らせるセル｣の設定ができればなんとでもなります。

GPT4から返ってきたコードがこちら。

function createShiftChart() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const shiftDataSheet = spreadsheet.getSheetByName("2024.8");
  const shiftChartSheet = spreadsheet.getSheetByName("shiftchart");

  // 既存のshiftchartをクリア
  shiftChartSheet.clear();

  // 2024.8シートからデータを取得
  const data = shiftDataSheet.getDataRange().getValues();
  const names = data.map(row => row[0]); // A列の名前
  const dates = data[0].slice(1); // 1行目の日付
  const timeSlots = generateTimeSlots();

  // shiftchartシートにヘッダーを設定
  shiftChartSheet.getRange(1, 2, 1, timeSlots.length * dates.length).setValues([dates.flatMap(date => Array(timeSlots.length).fill(formatDate(date)))]);
  shiftChartSheet.getRange(2, 2, 1, timeSlots.length * dates.length).setValues([Array(dates.length).fill(timeSlots).flat()]);
  shiftChartSheet.getRange(3, 1, names.length, 1).setValues(names.map(name => [name]));

  // シフトデータを色分けしてシフトチャートに設定
  for (let i = 1; i < data.length; i++) {
    for (let j = 1; j < data[i].length; j++) {
      const shift = data[i][j];
      if (shift) {
        const [start, end] = shift.split('~').map(time => parseTime(time.trim()));
        applyShiftColors(shiftChartSheet, i + 2, (j - 1) * timeSlots.length + 2, start, end);
      }
    }
  }
}

// 6時から24時までの1時間単位の時間スロットを生成
function generateTimeSlots() {
  const slots = [];
  for (let h = 6; h <= 24; h++) {
    slots.push(`${h % 24}:00`);
  }
  return slots;
}

// "HH:MM"形式の時間を分に変換
function parseTime(time) {
  const [hours, minutes] = time.split(':').map(Number);
  return hours * 60 + minutes;
}

// 日付をYYYY/MM/DD形式に変換
function formatDate(date) {
  const dateObj = new Date(date);
  const year = dateObj.getFullYear();
  const month = String(dateObj.getMonth() + 1).padStart(2, '0');
  const day = String(dateObj.getDate()).padStart(2, '0');
  return `${year}/${month}/${day}`;
}

// シフト時間に応じて色を設定
function applyShiftColors(sheet, row, col, startMinutes, endMinutes) {
  const timeSlots = generateTimeSlots();
  const pinkColor = "#FFC0CB"; // ピンク
  const whiteColor = "#FFFFFF"; // 白

  timeSlots.forEach((slot, index) => {
    const slotStartMinutes = parseTime(slot);
    const cell = sheet.getRange(row, col + index);

    if (slotStartMinutes >= startMinutes && slotStartMinutes < endMinutes) {
      cell.setBackground(pinkColor);
    } else {
      cell.setBackground(whiteColor);
    }
  });
}

生成AIらしいですが、特に問題なさそうなのでこのままいきます。

③GASを実行してみる

GASは、スプレッドシートに紐づかせることができます。Excelやスプシの関数と本質的にはあまり変わりません。まず、「拡張機能」から「AppScript」をひらきます。「無題のプロジェクト」が開くと思います。ここで、真ん中に「Function myFunction(){}」みたいな呪文が書いてあるので、これを消してChatGPTにもらってきた上のコードを貼ります。貼ると、以下のような感じになります。

Ctrl+V（Command+V）で貼ったら、Ctrl+S（Command+S）で保存します。そして、「実行」を押すと、コードが実行されます。初回実行時、「承認が必要です」というポップアップがでます。

ポップアップが出たら「権限を確認」をクリックして…

アカウントを選択（スプシが載っているアカウントです）。

「このアプリはGoogleで確認されていません」と出ますが、自分でつくったGASにリスクも何もないので、「詳細」→「無題（安全ではないページ」を押します。このあとも続きますが、承認しておけば大丈夫です。

④実行結果を確認する

実行すると、こんな感じになりました。右にスクロールしていくと、月末までシフトを見ることができます。日付がぜんぶのセルに載っているのがちょとあれですが、概ねやりたかったことはできているのではないでしょうか。

もっと便利にする

今回、”2024.8”というシートを”shiftchart”というシートに変換し、シフト表をガントチャート風にすることができました。今のままでも使えますが、あとひと手間加えることによって、来月からの作業が圧倒的に楽になります。ここに、こうしたらよさそうという案と、方向性を示しておきます。

• 枠外に情報を記載する：今は従業員氏名と日付、時刻しか書いていませんが、もっと情報を載せたい可能性があります。その場合には、コードの行や列を指定する部分をいじって、行を開けることもできます。
• 毎月自動処理するようにする：”2024.8”にしているのには理由があります。{YYYY.MM}のフォーマットに当てはまる名前のシートについて、YYYYとMMを自動生成して検索し、当月のシートに毎月色付けを行うことが可能です。
• 休憩時間や営業時間外、深夜賃金になる時間帯を色付けする：曜日や日付を指定するだけ。