用GAS对30人规模的日常排班表进行标准化并接入Power BI可视化的记录
某客户委托我们，把约30名员工使用的日常排班表用Power BI进行分析。可实际打开表格一看，是典型的Excel式「横向铺开」结构，离能直接导入Power BI的形态相差甚远。本文从实务角度，记录用Google Apps Script（GAS）把数据标准化、再接入Power BI的整个过程。
任务：每个日期一个工作表，员工横向排列
原来的电子表格是每天新增一个以日期命名的工作表，比如「20260421排班」这样。单张工作表的内容大致是这样的：
员工横向排列，每位员工对应「时间·计划·实绩·完成数·备注」这5列一组的区块，反复排列。若有30名员工，每行就会变成150列的超宽表格。
对于每天在现场填表的员工来说，这种「横向排列」的形式直观易用。问题在于，这种格式无法直接导入Power BI这类BI工具。BI工具擅长处理的是「一行一条记录」的纵向长表（即已标准化的数据）。
方针：先用GAS标准化，再交给Power BI
我们也考虑过直接用Power Query（Power BI自带的数据转换功能）处理这种超宽表，但由于列数是可变的（员工会有增减），这种做法很容易崩溃，因此改为两段式方案：先在Google Apps Script（GAS）端把数据转换成纵向格式，再只把结果交给Power BI读取。
GAS逐张读取日常表，将其写入如下这种「一行一条记录」的汇总数据表：
日付 | 従業員 | 時間帯 | 予定タスク | 実績タスク | 完了数 | 備考 |
2026/04/21 | 田中 | 9:00 | MTG準備 | MTG準備 | 1 | |
2026/04/21 | 田中 | 9:30 | 開発作業 | 開発作業 | 3 | |
2026/04/21 | 佐藤 | 9:00 | 電話対応 | 電話対応 | 2 |
实际实现时，我们还额外附加了年、月、周标签、ISO周数、星期几等用于汇总的列。设计上以「一行＝30分钟一个时段」来处理。
实现GAS时踩过的坑
标准化的逻辑本身很简单，但实际运行后，还是遇到了不少细节上的坑。
1. 把日期行和姓名行搞反了
一开始以为表格第1行是日期、第2行是姓名，结果有的表格反过来：第1行是姓名，第2行是日期。为了调试，写了一个「只把表格结构输出到日志」的函数，最终发现，对用户自己管理的Google表格本身抱有怀疑，反而是最快的解决办法。
2. 时间单元格会以Date对象的形式返回
在电子表格中设置成时间显示的单元格（比如「9:00」），用GAS的getValues()读取时，返回的不是字符串，而是JavaScript的Date对象。我们没注意到这一点，写了把时间当字符串处理的逻辑，结果导致值变成了「1899/12/30」这样的日期，出现了bug。
// 時刻セルを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表格中设置了日期·时间格式的单元格，在GAS一侧一律会被当作Date对象来处理。
3. 把日志输出用的列误判为员工列
我们机械地把超宽表格的列按「每5列一个员工区块」来判定，结果把中途插入的一个日志输出用列，误读成了一个不存在的员工。
作为对策，我们不再单纯依据列的位置来判断，而是改为先验证「时间列的下一列是否是『计划』标题、再下一列是否是『实绩』标题」，确认后才把它判定为员工区块。
4. 6分钟的执行时间限制
GAS单次执行有6分钟的上限。当日常表增加到数十张甚至数百张时，每次都重新读取全部工作表的处理方式就会超时。我们改为「只对尚未反映到汇总数据中的日期进行追加处理」的增量执行方式，规避了这个问题。
大多数问题都属于「先凭假设去实现，实际接触真实数据后才发现问题」这种模式。用一小部分生产数据（哪怕只是一张表）事先构建一个验证结构是否符合预期的函数，能大幅减少返工。
与Power BI的连接方式
我们把标准化完成的汇总数据表，通过Power BI的Google表格连接器直接读取。不需要像网页发布那样的额外设置，只需用Google账号完成认证就能连接。
在Power Query一侧，我们添加了从时间段字符串计算实绩工时（30分钟＝0.5小时）的列，以及根据计划·实绩内容按关键词分类任务类别的列。
用DAX制作的指标（仅概览）
在DAX一侧，我们另外准备了一张日期表并与汇总数据关联，然后构建了以下这类指标。由于具体公式包含客户专属的业务逻辑，这里省略细节，只介绍大致方向。
・期间内的总实绩工时
・各时间段的实际在岗人数
・完成数，以及每小时完成数
・人力成本（时薪数据与实绩工时相乘得出）
制作人力成本这类指标时，需要事先制定规则，明确如何处理员工姓名的书写差异（例如片假名表记不一致），以及时薪数据中不存在的员工该如何处理。如果这部分含糊不清，后续就要花时间去排查数字对不上的原因。
完成的仪表盘能做到什么
・以热力图一览各员工·各任务类别的实绩工时
・通过时间段×星期几，看出哪个时段工作量最集中
・用柱状图追踪月度·周度的完成数变化趋势
・结合人力成本的指标，把握各任务类别的成本感
以往只能凭「感觉挺忙」来把握的工作量，如今变成了可以用数字和图表看到的东西，这是最大的变化。
小结
1. Excel式的超宽表格无法直接导入BI工具，需要先进行标准化（转为纵向格式）
2. 用GAS进行标准化时，会遇到时间数据变成Date对象、执行时间有上限等不少细节上的坑
3. 先用一部分生产数据验证结构，再进入正式实现，可以减少返工
4. 制作人力成本这类指标时，要提前把书写差异与异常数据的处理方式规则化
把电子表格中的业务数据用Power BI可视化，这类需求今后应该还会继续增多。如有类似困扰，欢迎随时咨询Robin Planning合同会社。
想学习Power BI的读者
若想系统掌握Power BI的基本操作乃至DAX的思路，读一本书也是一条捷径。
📚 相关书籍
Impress ／ 细致讲解了Power BI的界面操作、数据导入到简单可视化的入门书。适合想掌握本文这类电子表格对接基础的读者。
※ 以上链接包含Amazon联盟链接。本博客的收益将用于运营费用。
留言