30名規模の日次スケジュール表をGASで正規化してPower BIで可視化

9月9日

読了時間: 6分

あるクライアント先で、30名規模のスタッフが使っている日次スケジュール表をPower BIで分析したい、という相談を受けました。ところが実際のシートを見てみると、Power BIに読み込める形とはほど遠い、Excelらしい「横に伸びる」構造になっていました。GASでデータを正規化してからPower BIに繋ぐまでの流れを、実務目線でまとめます。

お題：日付ごとに1シート、従業員は横に並ぶ

元のスプレッドシートは「20260421スケジュール」のような、日付名のシートが日々増えていく形式でした。1シートの中身はこんなイメージです。

横方向に従業員が並び、各従業員につき「時間・予定・実績・完了数・備考」という5列のブロックが繰り返される構造。30名分だと、1行あたり150列という横長シートになります。

現場で日々シートを埋めるスタッフにとっては、この「横並び」形式は直感的で使いやすいものです。問題は、この形式のままではPower BIのようなBIツールに読み込めない点でした。BIツールが扱いやすいのは「1行1レコード」の縦長データ（正規化されたデータ）です。

方針：GASで正規化してから、Power BIに渡す

横長のシートを直接Power Query（Power BI側のデータ変換機能）で捌く方法も検討しましたが、列数が可変（従業員の増減がある）で崩れやすいため、Google Apps Script（GAS）側で先に縦持ちのデータに変換し、その結果だけをPower BIに読ませる二段構えにしました。

GASで日次シートを1枚ずつ読み取り、次のような「1行1レコード」の集計データシートに書き出します。

日付	従業員	時間帯	予定タスク	実績タスク	完了数	備考
2026/04/21	田中	9:00	MTG準備	MTG準備	1
2026/04/21	田中	9:30	開発作業	開発作業	3
2026/04/21	佐藤	9:00	電話対応	電話対応	2

実際にはこれに加えて、年・月・週ラベル・ISO週番号・曜日といった集計用の列も持たせています。1行＝30分刻みのスロットとして扱う設計です。

GAS実装でハマったポイント

正規化のロジック自体は単純ですが、実際に動かしてみると細かい落とし穴がいくつもありました。

1. 日付行と氏名行を取り違えていた

最初、シートの1行目が日付、2行目が氏名だと思い込んでいましたが、逆になっている人もいました。1行目が氏名、2行目が日付。デバッグ用に「シート構造をログに出力するだけの関数」を作って、ユーザー側が管理しているGoogle Spreadsheetを疑ってかかるのが結局一番早い解決策でした。

2. 時間セルがDateオブジェクトとして返ってくる

スプレッドシート上で「9:00」のように時刻表示にしているセルは、GASの getValues() で取得すると文字列ではなくJavaScriptのDateオブジェクトとして返ってきます。これに気づかず時刻を文字列として扱うロジックを書いていたため、値が「1899/12/30」のような日付になってしまうバグが発生しました。

// 時刻セルをHH:mm形式の文字列に変換する
function cellToTimeStr(cell) {
  if (cell instanceof Date) {
    const h = cell.getHours();
    const m = cell.getMinutes();
    return Utilities.formatString('%02d:%02d', h, m);
  }
  return cell; // 既に文字列の場合はそのまま
}

Google Sheetsで日付・時刻フォーマットが設定されたセルは、GAS側では常にDateオブジェクトとして扱われる、という仕様を知らなかったのが原因でした。

3. ログ出力用の列を従業員の列と誤認識していた

横長シートの列を機械的に「5列ずつ従業員ブロック」と決め打ちしていたところ、途中に差し込まれていたログ出力用の列を、存在しない従業員として誤読してしまいました。

対策として、単純に列位置だけで判定するのをやめ、「時間列の次が『予定』、その次が『実績』という見出しになっているか」を実際に検証してから従業員ブロックと判定するロジックに変更しました。

4. 実行時間の6分制限

GASには1回の実行につき6分という上限があります。日次シートが数十枚〜数百枚に増えてくると、毎回全シートを読み直す処理では時間切れになります。「まだ集計データに反映されていない日付だけを追加処理する」という差分実行方式に変更し、この問題を回避しました。

トラブルの多くは「思い込みで実装してから、実データで初めて気づく」パターンでした。本番データのごく一部（1シート分）を使って、想定通りの構造になっているかをまず確認する関数を先に作っておくと、手戻りがかなり減ります。

Power BI側との繋ぎ方

正規化が済んだ集計データシートを、Power BIのGoogle Sheetsコネクタで直接読み込みます。Web公開のような追加設定は不要で、Googleアカウントでの認証だけで繋がりました。

Power Query側では、時間帯の文字列から実績時間（30分＝0.5時間）を計算する列や、予定・実績の内容からタスクカテゴリをキーワードで分類する列などを追加しています。

DAXで作った指標（概要のみ）

DAX側では、日付テーブルを別途用意して集計データと連携させたうえで、以下のような指標を用意しました。具体的な数式はクライアント固有のロジックを含むため割愛しますが、方向性だけご紹介します。

・期間内の総実績時間

・時間帯ごとの実働人数

・完了数と、時間あたりの完了数

・人件費（時給データと実績時間を掛け合わせたもの）

人件費のような指標を作る場合、従業員名の表記ゆれ（カタカナ表記の違いなど）や、時給データに存在しない従業員の扱いをどうするかを、事前にルールとして決めておく必要があります。ここを曖昧にすると、後から数字が合わない原因を探す羽目になります。

できあがったダッシュボードでできるようになったこと

・従業員ごと・タスクカテゴリごとの実績時間をヒートマップで一覧できる

・時間帯×曜日で、どの時間にどれだけ稼働が集中しているかが見える

・月次・週次での完了数の推移を棒グラフで追える

・人件費を絡めた指標で、タスクカテゴリごとのコスト感を把握できる

これまでは「なんとなく忙しそう」という感覚でしか把握できなかった業務量が、数字とグラフで見えるようになったのが一番の変化でした。

まとめ

1. Excelらしい横長シートは、そのままではBIツールに読み込めない。まず正規化（縦持ち化）が必要

2. GASでの正規化は、時刻データがDateオブジェクトになる、実行時間に上限があるなど、地味な落とし穴が多い

3. 本番データの一部で構造を検証してから本実装に進むと、手戻りが減る

4. 人件費のような指標を作る場合は、表記ゆれや例外データの扱いを先にルール化しておく

スプレッドシートの業務データをPower BIで可視化したい、という相談は今後も増えていきそうな分野です。同じようなお困りごとがあれば、ロビンプランニング合同会社までお気軽にご相談ください。

Power BIをこれから学びたい方へ

Power BIの基本操作からDAXの考え方まで、体系的に押さえたい場合は書籍で学ぶのも近道です。

▶ できる Power BI データ分析&可視化超入門

インプレス／ Power BIの画面操作から、データの取り込み、簡単な可視化までを丁寧に解説した入門書。今回のようなスプレッドシート連携の基礎を押さえたい方に。

※ 上記はAmazonアソシエイトのリンクを含みます。当ブログの収益は運営費に充てさせていただきます。

#GAS #GoogleAppsScript #PowerBI #DAX #業務効率化 #データ分析 #中小企業