직원 연말 평가 시스템 개발 기록 (2) 부서마다 다른 구글시트를 하나로 불러오기

부서마다 시트 모양이 달랐다 평가 시스템을 만들면서 가장 먼저 부딪힌 벽은 입력이었다. 각 부서는 이미 구글시트로 연간 OKR과 평가기준을 쓰고 있었다. 시스템에 다시 손으로 옮겨 적으라고 하면 처음부터 쓰지 않을 게 뻔했다. 시트를 그대로 가져오는 기능이 필요했다. 그런데 같은 "년간 OKR" 시트라도 부서마다 모양이 달랐다. 어떤 시트는 Objective가 열로 있었고, 어떤 시트는 "Objective | 내용" 형태의 구분 행 아래에 KR이 이어졌다. 평가기준은 한 탭 안에 정량지표와 정성지표 블록이 위아래로 붙어 있었고, 열 이름도 "평가기준", "내용", "추진내용"처럼 제각각이었다. 병합셀도 많았다. 그래서 처음부터 고정 파서는 쓰지 않기로 했다. 원본 표를 2차원 배열로 만들고, 헤더와 컬럼을 추정하고, 사용자가 매핑을 확정한 뒤 적재한다. 매핑만 바꾸면 어떤 양식이든 받아들이는 구조다. 설계: 추정하고, 사람이 확정한다 입력 — 구글시트 범위를 복사해 붙여넣기(TSV), CSV 업로드(UTF-8·EUC-KR), 구글시트 URL 세 가지. URL은 관리자만, 구글 도메인만 허용했다. 파싱 — 탭과 콤마 개수로 구분자를 판별하고 fgetcsv 로 읽는다. 따옴표로 감싼 셀 안의 줄바꿈이 깨지지 않는다. 헤더 추정 — 헤더 행을 자동으로 고르고, 사용자가 클릭으로 바꿀 수 있다. 컬럼 매핑 — 별칭 사전으로 열 이름을 필드에 연결한다. 결과는 미리보기로 보여 주고 사용자가 확정한다. 정리와 적재 — 구분 행 주입, 쓰레기 행 제거, 병합셀 상속을 거쳐 DB에 넣는다. 자동 추정을 믿고 바로 적재하지 않고 반드시 사람이 확인하게 한 이유는, 양식이 다양한 만큼 추정이 틀릴 수 있다는 걸 처음부터 받아들였기 때문이다. 그래도 추정이 자주 틀리면 쓸모가 없으니, 실제 시트를 넣어 보며 추정을 계속 다듬었다. 시행착오 주소창 주소를 넣으면 HTML이 돌아왔다 URL 불러오기는 처음에 "웹에 게시한 CSV 주소"를 넣는 방식이었다. 그런데 실제로 사람들이 넣는 건 브라우저 주소창에서 복사한 /edit?gid=...#gid=... 주소였다. 이 주소로 요청하면 구글은 CSV가 아니라 HTML 편집기 화면을 돌려준다. 파싱 결과는 당연히 엉망이었다. 사용자에게 주소 형식을 지키라고 하는 대신, 들어온 주소를 CSV 내려받기 주소로 바꾸게 했다. 문서 ID와 gid 를 뽑아 /export?format=csv&gid=... 로 다시 조립한다. 웹에 게시한 주소( /d/e/.../pub )와 드라이브에 올린 파일 주소도 처리했다. #gid= 같은 조각은 서버로 전달되지 않지만, 붙여넣은 문자열에는 남아 있으니 거기서 읽으면 된다. // 조각(#gid=…) 은 서버로 전달되지 않지만, 붙여넣은 문자열에는 남아 있다 $gid = null; if (preg_match('/[?&#]gid=(\d+)/', $url, $m)) $gid = $m[1]; $id = null; if (preg_match('#/spreadsheets/d/([A-Za-z0-9_-]{20,})#', $url, $m)) $id = $m[1]; elseif (preg_match('#/file/d/([A-Za-z0-9_-]{20,})#', $url, $m)) $id = $m[1]; $out = 'https://docs.google.com/spreadsheets/d/' . $id . '/export?format=csv'; return $gid === null ? $out : $out . '&gid=' . $gid; 요청 전에는 https 여부와 호스트가 구글 도메인인지 확인해, 이 기능이 임의 주소를 서버가 대신 요청하는 통로가 되지 않게 했다. 실패하면 원인별(공유 설정, 웹에 게시, 붙여넣기로 대체) 안내와 실제 요청한 주소를 함께 보여 줬다. Key Results가 번호 열에 붙었다 OKR 시트는 "Key Results" 제목이 병합셀로 번호 칸과 내용 칸 위에 걸쳐 있었다. CSV로 내보내면 제목은 첫 칸에만 남고 다음 칸은 비어 있다. 그 결과 Key Results 필드가 번호 열에 매핑됐다. 빈 제목 칸은 왼쪽 제목을 물려받게 했다. 그러면 같은 제목을 가진 열이 두 개가 되는데, 붙어 있는 열이면 데이터 모양(숫자만 있는지, 문장인지)으로 고르고, 떨어져 있으면 왼쪽부터 배정했다. 별칭 사전도 과하게 넓었다. 팀명 필드의 별칭 '부서'가 '유관부서' 열을, 당초계획의 '계획'이 '하반기 중점 추진 계획' 열을 가로채고 있었다. 별칭을 줄이고, 정규화할 때 괄호를 지워 '하반기(P)'나 '중점 추진사항(계획)' 같은 열도 맞게 잡히게 했다. 이 과정을 실제 시트 3종(년간 OKR 51행, 평가기준 40행, 부문별 추진사항 79행)으로 돌려 모든 컬럼이 제자리에 매핑되는 것을 확인했다. 다른 부서 시트를 넣자 쓰레기 행 20개가 생겼다 개발팀 시트로 맞춘 불러오기에 경영지원 쪽 평가기준 시트를 넣자 세 가지가 동시에 깨졌다. 평가기준 열 이름이 '내용'이라 필수 컬럼이 매칭되지 않았고, 적재 자체가 거부됐다. 빈 줄, 합계 줄, 섹션 제목 줄이 그럴듯한 데이터 행으로 바뀌어 20행이 만들어졌다. 한 탭에 정량지표_매출, 정성지표_프로세스, 정성지표_고객 세 블록이 있는데 화면에서 고른 구분 하나로만 적재됐다. 두 번째가 가장 흥미로웠다. 병합셀 대응으로 "라벨 열의 빈 칸은 윗 행 값을 물려받는다"는 상속 처리를 하고 있었다. 그런데 이 상속이 빈 줄과 합계 줄보다 먼저 실행되고 있었다. 빈 줄은 상속을 받는 순간 윗 행 값으로 채워져 더 이상 빈 줄이 아니게 되고, 그 뒤의 정리 단계에서는 진짜 데이터와 구분할 수 없었다. 순서를 바꿔 정리를 먼저 하고, 상속은 그 다음에 하도록 했다. // 상속을 먼저 하면 빈 줄·합계 줄까지 윗 값으로 채워져 그럴듯한 쓰레기 행이 된다. // - key 필드가 전부 비어 있으면 빈 줄이거나 서식 줄 // - 매핑된 칸에 '합계/소계/평균' 만 들어 있으면 집계 줄 $body = okr_import_clean_rows($body, $mapping, $targets[$target]['key'] ?? []); // ... $body = okr_import_fill_down($body, array_unique($fillCols), $resetCol); 필수 컬럼 지정은 아예 풀었다. 양식마다 열 이름이 달라 필수 지정이 오히려 방해가 됐다. 대신 적재 결과가 0건이면 명시적인 오류를 낸다. 별칭에는 '내용·평가내용·추진내용', 근거 필드에는 '근거·산출물'을 추가했다. 세 블록 문제는 "■ ... 정량지표/정성지표" 같은 구분 행을 읽어 평가구분 열을 자동으로 붙이는 방식으로 풀었다. 한 탭을 한 번에 올바른 구분으로 나눠 담을 수 있게 됐다. 그 과정에서 '구분'이라는 짧은 헤더가 '평가구분'을 가로채는 일이 생겨, 역포함 매칭은 4글자 이상 헤더에만 적용했다. 결과는 쓰레기 20행이 실데이터 6행으로 줄었고, 각 행이 정량·프로세스·고객 구분으로 자동 분류됐다. 같은 부문의 년간 OKR도 합계 행이 자동으로 빠져 비중 합계가 정확히 100%로 들어왔다. 앞 블록의 비중이 뒤 블록으로 흘러갔다 그래도 하나가 남았다. 정량 블록 다음에 정성 블록이 이어 붙은 시트에서, 정성 블록 첫 행의 비중 칸이 비어 있으면 앞 정량 블록의 비중(100%)을 물려받았다. 병합셀 상속이 블록 경계를 모르고 있었기 때문이다. 상속 함수에 "그룹 리셋 열" 인자를 추가했다. 지정한 열의 값이 바뀌면 그때까지 기억한 값을 버린다. 평가기준에서는 평가구분 열을 경계로 썼다. foreach ($rows as $ri => $row) { // 블록이 바뀌면 상속을 끊는다. if ($resetCol !== null) { $g = (string)($row[$resetCol] ?? ''); if ($prevGroup !== null && $g !== $prevGroup) $last = []; $prevGroup = $g; } foreach ($colIdxs as $ci) { if (!isset($row[$ci])) continue; if ($row[$ci] === '') { if (isset($last[$ci])) $rows[$ri][$ci] = $last[$ci]; } else { $last[$ci] = $row[$ci]; } } } 처음부터 상속은 라벨 열에만 적용하고 있었다. 금액 칸에까지 적용하면 빈 금액 칸이 윗 행 숫자로 채워지는 사고가 나기 때문이다. 블록 리셋은 그 원칙을 한 단계 더 좁힌 것이다. 결과 붙여넣기, CSV, 구글시트 주소 어느 쪽으로 넣어도 같은 미리보기와 매핑 화면을 거쳐 적재된다. 개발팀 시트에 맞춰 만든 불러오기가 경영지원 쪽 평가기준과 OKR 시트까지 받아들이게 됐다. 양식이 새로 나와도 파서를 고치는 대신 매핑 화면에서 열을 맞추면 된다. 회고 시트 데이터 정리는 "어떤 순서로" 하느냐가 결과를 바꾼다. 상속처럼 빈 칸을 채우는 처리는 반드시 빈 줄·집계 줄 제거 뒤에 와야 한다. 이건 테스트 케이스를 하나 더 넣어 봐야 보이는 문제였다. 별칭 사전은 넓게 잡을수록 편할 것 같지만 실제로는 엉뚱한 열을 가로챈다. 짧은 단어의 부분 일치는 특히 위험하다. 다시 만든다면 처음부터 서로 다른 부서 시트 서너 개를 테스트 입력으로 모아 두고 시작하겠다. 한 부서 시트로 맞춰 놓고 나중에 다른 양식을 넣을 때마다 고치는 방식은 같은 종류의 수정을 여러 번 하게 만들었다. 기간: 2026.08 ~ 2026.09 기술: PHP, MySQL, Google Sheets CSV export, fgetcsv 관련 프로젝트: 직원 연말 평가 시스템