배경: 공고를 놓치지 않으려면?

공공조달 입찰을 주요 영업 채널로 활용합니다. 나라장터(g2b.go.kr)에는 하루에도 수백 건의 공고가 올라오는데, 2명이 이걸 직접 들어가서 확인하기엔 한계가 있었습니다.

그래서 Express + TypeScript 서버가 매시간 나라장터 API를 크롤링해서 MySQL에 저장하고, 저장된 데이터를 Google Sheets에 자동으로 정리하는 시스템을 구축했습니다.

핵심 요구사항은 간단했습니다.

• "우리가 수주할 수 있는 공고"만 보여줄 것

• 담당자·연락처·첨부파일까지 한 줄에 보일 것

• 마감이 지난 공고는 자동으로 치울 것

• 사람이 직접 시트를 열 필요 없을 것

전체 구조: 두 개의 파이프라인

시스템은 같은 스프레드시트를 공유하지만 전혀 다른 방식으로 동작하는 두 파이프라인으로 나뉩니다.

나라장터 공고 DB (MySQL)
      │
      ├─ [파이프라인 A] 적격 공고 → 탭 복제 업로드 (매시 10분)
      │     BidSheetsExportService
      │     자격검증 "적격" 공고 1건 = 스프레드시트 탭 1개
      │
      └─ [파이프라인 B] 과업 검색 → 카테고리 탭 행 추가 (매시 50분)
            TaskSheetsExportService + TaskSearchService
            키워드 매칭 공고 → 카테고리별 탭에 행 삽입

두 파이프라인의 공통 기반은 GoogleSheetsService입니다. API 인증, 속도 제한, 메타데이터 캐싱 등 Sheets API 관련 복잡도를 이 레이어에 가뒀습니다.

파이프라인 A: 적격 공고 → 탭 복제 업로드

아이디어

자격검증을 통과한 공고는 중요도가 높습니다. 그래서 행 하나로 축약하는 대신, 서식 있는 TEMPLATE 탭을 복제해서 공고마다 독립된 탭을 만드는 방식을 선택했습니다.

BidQualificationSummary (overallStatus = '적격')
  → TEMPLATE 탭 복제
  → Named range 위치에 값 입력
  → bid_google_sheet_exports에 이력 저장

Named Range 활용

TEMPLATE 탭에는 미리 Named range를 설정해 둡니다. 코드에서는 Named range 이름으로만 셀 위치를 참조하므로, 스프레드시트 레이아웃이 바뀌어도 코드를 수정할 필요가 없습니다.

// googleSheetsTemplateMapping.ts
export const GOOGLE_SHEETS_NAMED_RANGES = {
  NOTICE_NO: 'NOTICE_NO',
  NOTICE_TITLE: 'NOTICE_TITLE',
  BASE_AMOUNT: 'BASE_AMOUNT',
  DEADLINE_DATE: 'DEADLINE_DATE',
  QUALIFICATIONS_LIST: 'QUALIFICATIONS_LIST',
  // ...
};

탭 이름에 특수문자나 예약어가 들어올 수 있어서, 복제 전에 시트 제목을 항상 정제합니다.

function sanitizeSheetTitle(input: string): string {
  const replaced = input.replace(/[:\\/?*\[\]]/g, ' ').replace(/\s+/g, ' ').trim();
  return replaced.slice(0, 100) || 'untitled';
}

파이프라인 B: 과업 검색 시트

파이프라인 A가 "중요한 공고를 깊게" 다룬다면, 파이프라인 B는 "관련 있는 공고를 넓게" 커버합니다.

이중 스코프 규칙 엔진

공고를 어느 탭에 넣을지 결정하는 규칙이 핵심입니다. 처음에는 단순한 키워드 배열로 시작했지만, 운영하면서 "전광판 카테고리에 교통 전광판은 빼야 해", "전시 카테고리에서도 이건 예외야" 같은 요구사항이 쌓였습니다.

이를 해결하기 위해 전역 규칙 + 카테고리별 규칙 두 레이어로 분리했습니다.

export const GLOBAL_RULES: GlobalRules = {
  searchInclude: ['전시', '체험', '미디어', '콘텐츠', '전광판', ...],
  whitelist: ['체험관', '박물관', '기념관', '과학관'],   // 제외 면제
  priorityExclude: ['지명경쟁', '수의계약'],             // 화이트리스트도 무시
  excludeTitle: ['환경', '정비', '임차', '유니폼', ...], // 전역 제외
  institutionExclude: ['재단법인', '대학교', ...],
  agencyHardExclude: ['제주'],                          // 지역 하드 제외
};

export const CATEGORY_RULES: CategoryRule[] = [
  {
    sheetName: '전광판 및 조명',
    searchInclude: ['안내전광판', 'LED전광판', '경관조명', ...],
    excludeTitle: ['도로전광판', '교통안내전광판', 'VMS', ...], // 이 카테고리에만 적용
  },
  // ...
];

평가 로직은 전역 규칙 → 카테고리 규칙 순으로 적용되며, whitelist는 전역 제외를 면제하지만 priorityExclude는 넘지 못합니다.

탭 배정

카테고리가 결정되면 공고 타입(입찰공고 / 사전규격)에 따라 접미사를 붙입니다.

function resolveTargetSheetName(params: {
  title: string;
  status: string;
  availableSheetTitles: string[];
}): string | null {
  const baseName = classifyTitle(params.title); // 이중 스코프 규칙 평가
  if (!baseName) return null;

  const suffix = params.status === '사전규격' ? ' 사전규격' : ' 입찰공고';
  const desired = baseName + suffix;

  // 탭이 스프레드시트에 없으면 null → 스킵
  if (available.has(desired)) return desired;
  return null;
}

탭을 스프레드시트에 직접 만들기만 하면 자동으로 사용됩니다. 코드 변경이 필요 없습니다.

시트 유지보수: 가장 까다로운 부분

새 행을 추가하는 것보다 기존 시트를 올바른 상태로 유지하는 게 훨씬 복잡했습니다.

문제 1: 마감 지난 행 처리

입찰 마감일이 지난 공고는 자동으로 삭제해야 합니다. 문제는 날짜 형식이 제각각이라는 것입니다.

• 사람이 입력한 6월 4일

• ISO 형식 2025-06-04

• Google Sheets 시리얼 넘버 46045

• 범위 형식 6월 1일 ~ 6월 15일

이 모든 형식을 처리하는 파서를 만들고, KST 기준 오늘 날짜와 비교합니다. 서버 로컬타임이 UTC일 수도 있어서 Intl.DateTimeFormat으로 KST를 명시합니다.

function getKstTodayParts(now: Date = new Date()): DateParts {
  const parts = new Intl.DateTimeFormat('en-CA', {
    timeZone: 'Asia/Seoul',
    year: 'numeric', month: '2-digit', day: '2-digit',
  }).formatToParts(now);
  // ...
}

6월 1일처럼 연도가 없는 날짜는 "183일 초과 미래면 작년"이라는 규칙으로 연도를 추정합니다. 1월 3일에 12월 30일을 보면 작년으로 판정하는 방식입니다.

문제 2: Nuke-and-Pave 전략

만료된 행, 중복 행, 깨진 행(과업명 없이 다른 데이터만 있는 행)을 정리할 때, 처음에는 행별로 deleteRows를 N번 호출했습니다. 시트에 행이 많아지자 API 할당량이 금세 소진됐습니다.

해결책은 한 번에 정리하는 "Nuke-and-Pave" 입니다.

// 메모리에서 정상 행만 필터링
const nukeGoodRows: (string | number)[][] = [];
for (const row of existingValues) {
  if (isHeaderRow(row))  { nukeHeaderRow = row; continue; }
  if (isEmptyRow(row))   { continue; }
  if (isBrokenDataRow(row)) { nukePhase1BrokenCount++; continue; }
  if (isDeadlinePassedRow(row)) { expiredCount++; continue; }
  // ...
  nukeGoodRows.push(row);
}

// deleteRows 1회 + insertRows 1회 + updateValues 1회
await this.sheets.deleteRows({ sheetId, startRowIndex0: 1, rowCount: dataRowCount });
await this.sheets.insertRows({ sheetId, startRowIndex0: 1, rowCount: nukeGoodRows.length });
await this.sheets.updateValues({ range: `...!A2:L${1 + nukeGoodRows.length}`, values: nukeGoodRows });

API 호출 횟수가 O(N) → O(1)로 줄었습니다.

문제 3: richText-only 깨진 행

첨부파일 하이퍼링크를 셀에 Rich Text로 저장하는데, getValues(UNFORMATTED_VALUE)로 읽으면 텍스트 내용이 없는 hyperlink-only 셀은 빈 값으로 반환됩니다. 결과적으로 existingValues.length가 실제 시트 행 수보다 작아집니다.

이를 감지하기 위해 getSheetInfo로 실제 gridProperties.rowCount를 가져와서 비교합니다.

const { sheetId, rowCount: sheetRowCount } = await this.sheets.getSheetInfo(spreadsheetId, sheetName);
const hasHiddenRichTextRows = sheetRowCount > existingValues.length;

// deleteRows 시 실제 행 수 기준으로 삭제
const dataRowCount = Math.max(sheetRowCount - 1, existingValues.length - 1);

문제 4: 금액 셀 포맷

금액을 USER_ENTERED로 쓰면 Google Sheets가 숫자로 파싱해서 콤마를 제거합니다. 255,232,800이 255232800으로 바뀌는 문제입니다.

// 앞에 '를 붙이면 텍스트로 강제 저장됨
function formatMoneyCell(amount: number | null): string {
  const formatted = formatMoney(amount);
  if (!formatted) return '';
  return "'" + formatted; // '255,232,800
}

문제 5: 마감일 기준 정렬

API의 sortRange로 정렬하면 되지만, 날짜가 6월 4일 같은 문자열이라 알파벳 순 정렬이 됩니다. 임시 컬럼 M에 MMDD 형식의 숫자 정렬 키를 채운 뒤 정렬하고, 정렬 후 임시 컬럼을 지웁니다.

// M열에 정렬 키 숫자 기록 → sortRange(M열 기준) → M열 값 삭제
const sortKeys = dataRows.map(row => parseDateToSortNum(row[2]));
await batchFillA1({ values: sortKeys.map((k, i) => ({ range: `M${i+2}`, values: [[k]] })) });
await sortRange({ sortColumnIndex0: 12, ascending: true });
await batchFillA1({ values: sortKeys.map((_, i) => ({ range: `M${i+2}`, values: [['']] })) });

Google Sheets API 안정성 확보

속도 제한 (Rate Limiting)

Google Sheets API는 분당 60회 제한이 있습니다. GoogleSheetsService에 두 가지 보호 장치를 넣었습니다.

const READ_THROTTLE_MS = 1_100;  // 읽기 요청 간격 1.1초 (분당 ~54회)
const RETRY_BASE_MS = 2_000;     // 429 에러 시 지수 백오프: 2s → 4s → 8s

private async withRetry<T>(fn: () => Promise<T>): Promise<T> {
  for (let attempt = 0; attempt <= RETRY_MAX; attempt++) {
    try {
      return await fn();
    } catch (err) {
      if ((err as any)?.code !== 429 || attempt === RETRY_MAX) throw err;
      const delay = RETRY_BASE_MS * Math.pow(2, attempt);
      await new Promise(resolve => setTimeout(resolve, delay));
    }
  }
}

메타데이터 캐싱

탭 목록, 시트 ID, 행 수 등의 메타데이터는 API를 자주 호출해야 하지만 자주 바뀌지 않습니다. 30초 TTL 캐시를 두어 중복 호출을 줄였습니다.

const METADATA_CACHE_TTL_MS = 30_000;

private async getSpreadsheetMetadata(spreadsheetId: string): Promise<CachedSheetMeta[]> {
  const now = Date.now();
  if (this.metadataCache && now - this.metadataCache.timestamp < METADATA_CACHE_TTL_MS) {
    return this.metadataCache.sheets;
  }
  // API 호출 후 캐시 갱신
}

행 삽입·삭제 시에는 invalidateMetadataCache()를 호출해서 다음 요청에서 최신 상태를 가져오게 합니다.

배치 쓰기

개별 셀을 하나씩 쓰는 대신, batchUpdate와 batchFillA1으로 한 번에 묶어 씁니다. 200개 단위로 청크를 나눠 요청 크기를 제한합니다.

const chunkSize = 200;
for (let i = 0; i < updates.length; i += chunkSize) {
  await this.sheets.batchFillA1({
    spreadsheetId,
    values: updates.slice(i, i + chunkSize),
  });
}

중복 방지: SHA1 해시 기반 행 키

시트에 같은 공고가 두 번 들어가지 않도록 (과업명 + 공고기관) 조합으로 키를 만들어 DB와 시트 양쪽에서 중복을 확인합니다.

function buildKeyFromRow(title: string, institution: string): string {
  const raw = `${title.replace(/\s+/g, '').toLowerCase()}::${institution.replace(/\s+/g, '').toLowerCase()}`;
  return `sha1:${crypto.createHash('sha1').update(raw).digest('hex')}`;
}

공백 차이, 대소문자 차이 때문에 동일 공고가 중복 취급되는 걸 방지하기 위해 정규화 후 해시합니다.

진단 API

시트에 데이터가 안 들어갈 때 원인 파악을 위해 GET /api/sheets/task-search-debug 엔드포인트를 만들었습니다.

{
  "typeCounts": { "bid": 45, "pre_standard": 12, "total": 57 },
  "noCategory": 8,
  "sheetBreakdown": {
    "전광판 및 조명 입찰공고": 12,
    "콘텐츠 입찰공고": 9
  },
  "categoryTabStatus": [
    { "name": "전광판 및 조명 입찰공고", "found": true },
    { "name": "전광판 및 조명 사전규격", "found": false }
  ],
  "routingDetails": [
    { "title": "LED 안내전광판 설치", "sheetName": "전광판 및 조명 입찰공고", "reason": "..." }
  ]
}

noCategory가 크면 키워드 규칙을 점검하고, found: false이면 스프레드시트에 탭이 없는 것입니다.

결과

• 일주일에 수십 시간 나라장터를 수작업으로 확인하던 것이 하루 한두 번 스프레드시트 확인으로 줄었습니다.

• 마감일 기준 자동 정렬 덕분에 가장 급한 공고가 항상 상단에 보입니다.

• 담당자·연락처·첨부파일이 한 행에 모여서 개별 공고 페이지에 들어갈 필요가 없습니다.

• L열(피드백)은 자동화가 절대 건드리지 않으므로, 영업팀이 직접 메모를 남길 수 있습니다.

회고

처음에 운영측에서 요청 받았을 때는 "그냥 행 추가하면 되겠지"라고 생각했습니다. 실제로는 날짜 파싱, richText 셀, 속도 제한, 중복 감지, 깨진 행 정리까지 생각보다 많은 엣지케이스가 있었습니다.

가장 큰 교훈: 쓰기(write)보다 유지보수(maintenance)가 훨씬 복잡합니다. 처음부터 "시트를 항상 올바른 상태로 유지"하는 관점으로 설계했다면 리팩토링을 덜 했을 것 같습니다.

두 번째 교훈: 규칙을 코드에 하드코딩하지 않고 데이터로 분리(taskSearchRules.ts)한 덕분에, 운영팀이 "이 키워드 추가해줘"라고 하면 한 파일만 수정하면 됩니다.

소규모 팀에서 공공조달 업무를 자동화하려는 분들에게 도움이 되길 바랍니다.