30명 규모의 일일 스케줄표를 GAS로 정규화해 Power BI로 시각화한 이야기
어느 클라이언트사에서, 30명 규모의 스태프가 사용하고 있는 일일 스케줄표를 Power BI로 분석하고 싶다는 상담을 받았습니다. 그런데 실제 시트를 보니, Power BI에 읽어들일 수 있는 형태와는 거리가 먼, Excel스러운 「가로로 긴」 구조였습니다. GAS로 데이터를 정규화한 후 Power BI에 연결하기까지의 흐름을, 실무 관점에서 정리합니다.
과제: 날짜별로 1시트, 직원은 가로로 나열
원래 스프레드시트는 「20260421스케줄」 같은, 날짜 이름의 시트가 매일 늘어나는 형식이었습니다. 시트 하나의 내용은 이런 이미지입니다.
가로 방향으로 직원이 나열되고, 각 직원마다 「시간·예정·실적·완료수·비고」라는 5열 블록이 반복되는 구조. 30명분이면, 한 행당 150열이라는 가로로 긴 시트가 됩니다.
현장에서 매일 시트를 채우는 스태프에게는, 이 「가로 나열」 형식이 직관적이고 사용하기 쉽습니다. 문제는, 이 형식 그대로는 Power BI 같은 BI 도구에 읽어들일 수 없다는 점이었습니다. BI 도구가 다루기 쉬운 것은 「1행 1레코드」의 세로로 긴 데이터(정규화된 데이터)입니다.
방침: GAS로 정규화한 후, Power BI에 넘긴다
가로로 긴 시트를 직접 Power Query(Power BI 측의 데이터 변환 기능)로 처리하는 방법도 검토했지만, 열 수가 가변적(직원의 증감이 있음)이라 무너지기 쉬우므로, Google Apps Script(GAS) 측에서 먼저 세로 형태의 데이터로 변환하고, 그 결과만 Power BI에 읽히는 2단계 구성으로 했습니다.
GAS로 일일 시트를 한 장씩 읽어들여, 다음과 같은 「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 스프레드시트를 의심해 보는 것이 결국 가장 빠른 해결책이었습니다.
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 커넥터로 직접 읽어들입니다. 웹 공개 같은 추가 설정은 불필요하며, Google 계정 인증만으로 연결되었습니다.
Power Query 측에서는, 시간대 문자열로부터 실적 시간(30분＝0.5시간)을 계산하는 열이나, 예정·실적 내용으로부터 태스크 카테고리를 키워드로 분류하는 열 등을 추가하고 있습니다.
DAX로 만든 지표(개요만)
DAX 측에서는, 날짜 테이블을 별도로 준비해 집계 데이터와 연계시킨 후, 다음과 같은 지표를 준비했습니다. 구체적인 수식은 클라이언트 고유의 로직을 포함하므로 생략하지만, 방향성만 소개합니다.
・기간 내 총 실적 시간
・시간대별 실동 인원수
・완료 수와, 시간당 완료 수
・인건비(시급 데이터와 실적 시간을 곱한 것)
인건비 같은 지표를 만들 때는, 직원명의 표기 흔들림(가타카나 표기의 차이 등)이나, 시급 데이터에 존재하지 않는 직원의 취급을 어떻게 할지를, 사전에 규칙으로 정해 둘 필요가 있습니다. 여기를 애매하게 하면, 나중에 숫자가 맞지 않는 원인을 찾는 처지가 됩니다.
완성된 대시보드로 가능해진 것
・직원별·태스크 카테고리별 실적 시간을 히트맵으로 한눈에 볼 수 있다
・시간대×요일로, 어느 시간에 얼마나 가동이 집중되는지가 보인다
・월별·주별 완료 수 추이를 막대그래프로 추적할 수 있다
・인건비를 엮은 지표로, 태스크 카테고리별 비용감을 파악할 수 있다
지금까지는 「왠지 바빠 보인다」는 감각으로밖에 파악할 수 없었던 업무량이, 숫자와 그래프로 보이게 된 것이 가장 큰 변화였습니다.
정리
1. Excel스러운 가로로 긴 시트는, 그대로는 BI 도구에 읽어들일 수 없다. 먼저 정규화(세로화)가 필요
2. GAS로의 정규화는, 시각 데이터가 Date 객체가 되는, 실행 시간에 상한이 있는 등, 소소한 함정이 많다
3. 실제 데이터의 일부로 구조를 검증한 후 본 구현으로 진행하면, 재작업이 줄어든다
4. 인건비 같은 지표를 만들 때는 표기 흔들림이나 예외 데이터의 취급을 먼저 규칙화해 둔다
스프레드시트의 업무 데이터를 Power BI로 시각화하고 싶다는 상담은 앞으로도 늘어날 것 같은 분야입니다. 비슷한 어려움이 있다면, 로빈 플래닝 합동회사로 부담 없이 상담해 주세요.
Power BI를 앞으로 배우고 싶은 분께
Power BI의 기본 조작부터 DAX의 사고방식까지, 체계적으로 익히고 싶다면 책으로 배우는 것도 지름길입니다.
📚 관련 서적
Impress ／ Power BI의 화면 조작부터, 데이터 취합, 간단한 시각화까지를 꼼꼼하게 해설한 입문서. 이번 같은 스프레드시트 연계의 기초를 익히고 싶은 분께.
※ 위 링크는 Amazon 어소시에이트 링크를 포함합니다. 당 블로그의 수익은 운영비에 충당하겠습니다.
댓글