How We Normalized a 30-Person Daily Schedule Sheet with GAS and Visualized It in Power BI

2 days ago

5 min read

A client asked us to analyze, in Power BI, a daily schedule sheet used by about 30 staff members. Looking at the actual sheet, though, it was a wide, sprawling, Excel-style layout — far from anything Power BI could ingest directly. Here's a practical account of the process, from normalizing the data with Google Apps Script (GAS) through to connecting it to Power BI.

The task: one sheet per date, employees laid out horizontally

The original spreadsheet had a new date-named sheet added each day, like "20260421スケジュール." A single sheet looked roughly like this:

Employees are arranged horizontally, and for each employee, a 5-column block repeats: time, planned task, actual task, completed count, and notes. With 30 employees, that's a 150-column-wide sheet per row.

For the staff filling in the sheet day to day, this "side-by-side" layout is intuitive and easy to use. The problem was that this format couldn't be loaded into a BI tool like Power BI as-is. What BI tools handle well is "one row per record" — tall, normalized data.

The approach: normalize with GAS first, then hand off to Power BI

We considered handling the wide sheet directly in Power Query (Power BI's own data-transformation feature), but since the number of columns varies (employees get added or removed), that approach is fragile. So we went with a two-stage setup: convert to tall-format data on the Google Apps Script (GAS) side first, and only feed the result into Power BI.

GAS reads each daily sheet one at a time and writes it out to a "one row per record" summary sheet like this:

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

In practice we also carry columns for aggregation — year, month, week label, ISO week number, day of week. Each row represents a 30-minute time slot.

Pitfalls we hit implementing the GAS side

The normalization logic itself is simple, but once we actually ran it, several subtle pitfalls turned up.

1. Mixed up the date row and the name row

At first we assumed row 1 was the date and row 2 was the name, but for some sheets it was reversed — row 1 was the name, row 2 was the date. Building a debug-only function that just logs the sheet structure, and then questioning the user-managed Google Sheet itself, turned out to be the fastest way to sort this out.

2. Time cells come back as Date objects

Cells formatted to show time, like "9:00," don't come back as strings when read via getValues() in GAS — they come back as JavaScript Date objects. We hadn't accounted for that and had written logic treating time as a string, which caused values to turn into dates like "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; // 既に文字列の場合はそのまま
}

The root cause was not knowing that cells with a date/time format in Google Sheets are always handled as Date objects on the GAS side.

3. A log-output column got misread as an employee column

We'd mechanically assumed the wide sheet's columns broke into "employee blocks of 5 columns each," but a log-output column inserted partway through got misread as a nonexistent employee.

The fix was to stop judging purely by column position, and instead verify that the column after "time" is actually headed "planned" and the one after that "actual" before treating it as an employee block.

4. The 6-minute execution limit

GAS caps a single execution at 6 minutes. As the daily sheets grow into the dozens or hundreds, re-reading every sheet each time eventually times out. We switched to an incremental approach — only processing dates not yet reflected in the summary sheet — to work around this.

Most of the trouble came from "implementing based on assumptions, then discovering the reality only once real data was involved." Building a small function to verify the actual structure against a slice of production data (just one sheet) before the full implementation cuts down rework considerably.

Connecting to Power BI

We loaded the normalized summary sheet directly into Power BI via its Google Sheets connector. No extra setup like web publishing was needed — authenticating with the Google account was enough to connect.

On the Power Query side, we added columns to calculate actual hours worked from the time-slot string (30 minutes = 0.5 hours), and to classify the task category from the planned/actual task text by keyword matching.

Metrics built in DAX (overview only)

On the DAX side, we set up a separate date table linked to the summary data, and built metrics along these lines. We're skipping the exact formulas since they include client-specific logic, but here's the general direction:

・Total actual hours worked over a period

・Number of staff actively working, by time slot

・Completed count, and completed count per hour

・Labor cost (hourly rate data multiplied by actual hours)

When building a metric like labor cost, you need to decide upfront how to handle inconsistencies in employee-name spelling (differences in katakana notation, for instance) and how to treat employees missing from the hourly-rate data. Leaving this ambiguous just means chasing down mismatched numbers later.

What the finished dashboard makes possible

・A heatmap view of actual hours by employee and task category

・Seeing which time slots see the heaviest workload, by time of day and day of week

・Tracking monthly and weekly completed-count trends in a bar chart

・Getting a sense of cost per task category through labor-cost-based metrics

The biggest change was that workload — previously something you could only sense as "seems busy" — became visible in numbers and charts.

Summary

1. An Excel-style wide sheet can't be loaded into a BI tool as-is; it needs normalizing (converting to tall format) first

2. Normalizing with GAS has its share of subtle pitfalls: time data turning into Date objects, a cap on execution time, and more

3. Verifying the structure against a slice of production data before full implementation reduces rework

4. For metrics like labor cost, decide on rules for name inconsistencies and edge cases upfront

Visualizing spreadsheet-based operational data in Power BI is a request we expect to keep coming up. If you have a similar need, feel free to contact Robin Planning LLC.

Want to learn Power BI

If you want to systematically cover Power BI's basics through the DAX way of thinking, a book can be a good shortcut.

📚 Related book

▶ できる Power BI データ分析&可視化超入門 (Power BI Data Analysis & Visualization: A Complete Beginner's Guide)

Impress ／ A gentle walkthrough of Power BI's interface, data import, and basic visualization. Good for covering the fundamentals of a spreadsheet integration like this one.

* The link above is an Amazon Associate link. Revenue from this blog goes toward running costs.

#GAS #GoogleAppsScript #PowerBI #DAX #Productivity #DataAnalysis #SMB