미국 물류 고객사 한 곳의 정산용 구글 시트에서 “담당자가 입력한 금액이 사라진다”는 제보를 받았다. 원인 규명·복구·스크립트 재작성까지가 이번 작업이다.
시트는 약 7,500행 × 80열이다. 왼쪽 27열은 외부 시스템에서 동기화되는 미러고, 오른쪽 53열은 담당자가 손으로 넣는 청구·지급 금액이다. 사라진 건 오른쪽뿐이었다. 약 4,000행의 수기 금액과 최근 한 달치 지급 데이터.
이 사고에서 제일 고약한 부분은 규모가 아니다. 시트가 사고 후에도 계속 멀쩡해 보였다는 것이다. 행 수도 그대로고 시스템 열도 다 차 있었다. 그래서 두 달간 아무도 몰랐다.
소실은 한 번의 실행으로 완성되지 않았다
스크립트 구조는 이랬다.
시트 전체 읽기 → 계산 → clearContents() → 7,500 × 80 통째로 setValues()
원본에 있는 행은 갱신하고, 원본에 없는 행은 결과 배열에서 빼서 삭제 효과를 냈다. 의도는 “원본의 미러를 유지한다”였고 그 자체는 틀리지 않았다.
문제는 실패했을 때다. 실행 로그를 보니 최근 7회 중 1회가 94초에서 죽었다. clearContents()는 이미 돌았고 setValues()는 완주하지 못했다. 시트에는 앞쪽 250행만 남았다.
진짜 문제는 그다음 날이었다. 정상 실행이 시트를 읽고 “원본엔 있는데 여기 없네 = 신규 행”으로 판단해서 나머지 7,000여 행을 다시 만들었다. 시스템 열은 원본에서 완벽히 복구됐고 자동계산 열도 다시 채워졌다. 사람이 넣은 금액만 영구 공란이 됐다.
정리하면 이렇다. 1회차 실패가 데이터를 날렸고, 2회차 성공이 그 흔적을 지웠다. 사고를 감지할 수 있는 유일한 신호(빈 시트)를 다음 정상 실행이 덮어버린 구조였다.
이미 있던 가드는 작동한 적이 없다
원본이 비었을 때를 대비한 가드가 코드에 있었다.
if (rawData.length < 1) return;
이 조건은 절대 참이 되지 않는다. getDataRange().getValues()는 완전히 빈 시트에서도 최소 1행을 반환하기 때문이다. 가드가 있으니 안전하다고 생각하고 있었는데, 실은 없는 것과 같았다.
가드는 작성한 시점에 한 번은 실제로 발동시켜 봐야 한다. 발동한 적 없는 방어 코드는 방어가 아니라 안심용 주석이다.
나도 두 번 틀렸다
현재 상태만 보고 원인을 지어냈다. 데이터를 보니 특정 구간만 비어 있길래 “몇 달 전에 별도 사고가 있었다”고 추정했다. 편집 이력에 그 시기 대량 붙여넣기 흔적도 있어 그럴듯했다. 그런데 사고 직전 백업본을 열어보니 그 구간이 꽉 차 있었다. 사고는 한 번이었고, 서사는 내가 만든 것이었다. 추정하기 전에 과거 스냅샷부터 열었어야 했다.
컬럼 하나만 보고 결론을 냈다. “트럭킹 비용”에 해당하는 열이 사실 셋이었다. 하나만 보고 “12월 이후로 안 쓰는구나” 했는데, 셋을 겹쳐 보니 중간이 비고 끝쪽에 값이 있었다. 담당자가 시기별로 다른 열을 쓰고 있었던 것이다. 스프레드시트에서 “이 열이 비었다”는 관찰은 생각보다 신뢰도가 낮다.
재설계 — 안전은 쓰기 범위에서 나온다
고치면서 얻은 결론은 한 줄이다.
안전은 “얼마나 조금 쓰느냐”가 아니라 “무엇을 쓰기 범위에 넣느냐”에서 나온다.
원래 설계 의도는 유지했다. 틀린 건 두 가지뿐이었다 — 덮어쓰는 범위에 사람 소유 열까지 넣은 것, 그리고 먼저 비운 것.
- 쓰기 범위를 시스템 열로 고정. 매핑 테이블에서 시스템 소유 열의 마지막 인덱스를 계산해 그 앞까지만
getRange로 잡는다. 사람 열은 코드가 아예 손에 쥐지 않는다. 어떤 버그가 나도 그 값은 사라질 수 없는 구조가 된다. clearContents()제거. 바뀐 행만 갱신하고 신규만 append 한다.- 삭제를 유예. “원본에 없다”에는 진짜 삭제와 일시적 부재(동기화 중, API 부분 실패, 키 흔들림)가 섞여 있다. 비용이 비대칭이다 — 오판 삭제는 영구 소실이고, 하루 늦게 삭제하는 비용은 0이다. 그래서 3회 연속 결번일 때만 아카이브 탭으로 옮긴다.
- 원본 건전성 가드. 원본 행 수가 임계 미만이거나 직전 대비 급감하면 아무것도 하지 않고 중단한다. 이번엔 실제로 발동시켜 확인했다.
LockService. 트리거와 수동 실행이 겹치면 서로의 스냅샷으로 덮어쓴다.- 실행 로그 탭. 없으면 “어제 실패했는데 아무도 몰랐다”가 그대로 반복된다.
부분 쓰기가 느릴 거라는 직관은 틀렸다
“바뀐 행만 쓰려면 전부 비교해야 하는데 그게 더 느리지 않나?” 싶어 실제로 재봤다.
| 작업 | 실측 |
|---|---|
| 20만 셀 정규화 비교 | 116ms |
| 시트 API 호출 1회 | 50~100ms |
비교는 사실상 공짜고 비싼 건 API 호출이다. 헤더 인덱스를 처음에 한 번 만들고 키를 Map으로 찾으면(전수 대조가 아니라) 그 뒤는 배열 인덱스 접근일 뿐이다.
다만 함정이 하나 더 있다. 바뀐 행이 시트 전체에 흩어져 있으면 setValues 호출이 잘게 쪼개진다. 150행이 50행 간격으로 흩어지면 150번 호출이라 오히려 느려진다. 그래서 덩어리 수가 임계를 넘으면 시스템 열 범위만 통째로 쓰는 폴백을 넣었다. 어느 쪽이든 사람 열은 range 밖이라 안전은 유지된다.
그리고 이 사고에는 배포가 없었다. 실행 시간이 31초에서 96.9초로 늘고 있었다. 행이 수백에서 수천으로 커지면서 전체 재작성 방식이 선형으로 취약해진 것이다. 바뀐 건 코드가 아니라 데이터 규모였다. “같은 코드인데 요즘 갑자기 터진다”의 전형이다.
복구는 “버전 복원” 버튼이 아니었다
구글 시트 버전 기록에서 복원을 누르면 사고 이후 입력분까지 통째로 되돌아간다. 대신 그 버전을 사본으로 만들고, 고유 키로 매칭해서 현재 비어 있는 셀만 채우는 스크립트를 따로 짰다. 빈 셀만 보므로 멱등이라 중간에 실패해도 다시 돌리면 이어서 채운다.
previewRestore() 매칭 7,482행 / 채울 셀 73,457개
restoreUserColumns() 1분 37초
previewRestore() 채울 셀 0개 ← 재확인
복구 후에도 스크립트 자신의 재확인만 믿지 않고, API로 따로 읽어서 특정 컬럼의 값 개수와 월별 분포를 백업본과 대조했다. 완전히 일치했다.
편집자 추적에는 함정이 있다. 구글 계정 로그인 없이 링크로 들어와 편집하면 버전 기록에 “익명 사용자”로 뭉뚱그려져 누가 했는지 알 수 없다. 반면 Apps Script는 절대 익명으로 안 찍힌다 — 승인한 계정 이름으로 남는다. 이 차이로 사람이 한 편집과 스크립트가 한 편집을 가를 수 있었다. (onEdit 트리거의 e.user.getEmail()도 로그인 상태가 아니면 빈 값이라 같은 함정이 있다.)
부수적으로 나온 것 — 그 값은 입력할 값이 아니었다
복구하면서 과거 데이터 6,900건을 훑었더니, 담당자가 매번 손으로 채우던 운송료가 “노선 × 요율 유효기간” 기준으로 96.8%가 단일 값으로 설명됐다. 요율 개정 시점도 데이터에 그대로 찍혀 있었다.
즉 그건 입력할 값이 아니라 계산될 값이었다. 담당자가 필터를 걸고 드래그로 같은 값을 채우던 건 UI가 불편해서가 아니라 요율 마스터가 없어서 사람이 계산기 노릇을 하던 것이었다. 입력 화면을 개선하려다가 입력 자체를 없애는 쪽이 답이라는 걸 데이터가 알려준 셈이다.
한계와 다음
- 재발 감지는 아직 사후적이다. 실행 로그 탭은 붙였지만 실패했을 때 사람에게 가는 알림이 없다. 로그는 보는 사람이 있을 때만 로그다.
- 복구 스크립트를 상시로 두지 않았다. 이번 건은 사고 직전 백업본이 남아 있어서 가능했다. 수기 열만 주기적으로 스냅샷하는 장치가 없으면 다음엔 복구할 원본 자체가 없다.
- 요율 마스터는 아직 없다. 96.8%라는 숫자는 “자동화 가능성”이지 규칙이 아니다. 나머지 3.2%가 예외인지 오입력인지 구분하는 게 먼저다.
- 시트를 웹 앱으로 옮기는 작업이 다음인데, 설계를 절반쯤 하고 나서 사내 SaaS 제품에 같은 기능이 이미 있다는 걸 발견했다. 새로 설계하기 전에 우리 것부터 봤어야 했다. 이건 따로 쓴다.
이번 건에서 남기고 싶은 한 줄은 이거다. 사람이 소유한 데이터는 스크립트의 쓰기 범위 밖에 두면, 코드가 아무리 틀려도 지워지지 않는다. 조심하는 코드보다 못 건드리는 구조가 낫다.
자주 묻는 질문
Q. clearContents() 후 전체 재작성은 왜 위험한가요?
비우기와 다시 쓰기 사이에 실패 구간이 생기기 때문이다. Apps Script는 실행 시간 제한이 있고 시트가 커질수록 그 구간에 걸릴 확률이 올라간다. 이번엔 7회 중 1회가 94초에서 죽었는데, 그 시점엔 이미 비어 있었다. 게다가 남은 시트가 다음 실행의 입력이 되므로 두 번째 정상 실행이 “여긴 없으니 신규”로 판단해 소실을 확정한다.
Q. 바뀐 행만 골라 쓰면 비교 비용 때문에 더 느리지 않나요?
아니다. 실측으로 20만 셀 정규화 비교가 116ms인 반면 시트 API 호출 한 번이 50~100ms다. 병목은 계산이 아니라 호출 횟수다. 다만 바뀐 행이 시트에 흩어져 있으면 호출이 잘게 쪼개져 역전되므로, 덩어리 수가 임계를 넘으면 시스템 열 범위만 통째로 쓰는 폴백을 두는 게 좋다.
Q. “원본에 없는 행”은 바로 지우면 안 되나요?
비용이 비대칭이라 안 된다. 원본이 없다는 신호에는 진짜 삭제와 일시적 부재(동기화 중, API 부분 실패, 키 값 흔들림)가 섞여 있다. 오판 삭제는 사람이 넣은 값의 영구 소실이고, 하루 늦게 지우는 비용은 사실상 0이다. 3회 연속 결번일 때만 아카이브 탭으로 옮기는 식으로 유예를 두면 대부분의 오판이 걸러진다.
Q. 시트에서 누가 값을 지웠는지 어떻게 구분하나요?
버전 기록만으로는 어렵다. 로그인 없이 링크로 들어와 편집하면 “익명 사용자”로 뭉뚱그려지기 때문이다. 대신 Apps Script는 승인한 계정 이름으로 기록되므로, 익명 편집인지 스크립트 편집인지는 확실히 가를 수 있다. onEdit 트리거의 e.user.getEmail()도 로그인 상태가 아니면 빈 값이라 같은 한계를 갖는다.