Google Drive+ Gemini로 주문서 입력 자동화하기

 

AI 실험도 해볼 겸 부모님이 매번 수작업/반복적으로 하던 서류 작업을 자동화해보았다.

매 주문마다 서류 4종(사진대지·품질보증서·품질보증및사후봉사증서·보조서류)에 똑같은 이름/주소/기종명을 손으로 채우는 게 너무 비효율이라, Apps Script 하나로 끝내봤다.

 

사실은 Claude Cowork로 하려고 했으나, 토큰이나 앞으로 관리 등을 생각했을 때 그냥 구글 내에서 수행하는 것으로 변경.

무엇을 자동화했나

• 주문자리스트 시트에서 완료여부가 비어있는 행을 찾고, 주문자정보를 확인한다.
• 같은 이름의 주문자 폴더에서 현장 사진을 읽어
• Gemini Vision으로 사진을 전경/기대로 분류 + 규격명·제조번호·제품번호 OCR
• 양식 폴더의 Google Docs 템플릿을 복사해 {{플레이스홀더}} 치환 + 사진 삽입
• 끝나면 시트의 완료여부를 완료로 업데이트

Drive 구조

Agent (루트)
├─ 주문자리스트 (Sheet)
├─ 양식/                ← 템플릿 4종 (Google Docs)
│   ├─ 사진대지_양식
│   ├─ 품질보증서_양식
│   ├─ 품질보증및사후봉사증서양식
│   └─ 보조서류_양식
└─ 강단/                ← 주문자 폴더 (이름 = 구매자명)
    ├─ IMG_전경.jpg
    ├─ IMG_기대.jpg
    └─ (스크립트가 완성본 4종을 여기에 생성)

템플릿 설계 규칙

템플릿 안 채워야 할 값은 {{필드명}} 형태. 필드명이 시트 컬럼명과 일치하면 바로 매핑, 이름이 다르면 CONFIG에 선언만 해주면 된다 사진에서 읽어야 하는 값(규격명·제조번호·제품번호)은 Vision이 담당.

사전 준비

1. Google AI Studio에서 Gemini API 키 발급
2. Apps Script 프로젝트 → 프로젝트 설정 → 스크립트 속성에 추가
   • Property: GEMINI_API_KEY
   • Value: AIza...
3. 실행 권한: Drive, Documents, Spreadsheet, UrlFetch

전체 코드

아래 코드를 Code.gs 하나에 그대로 붙여넣는다. CONFIG 상단의 ID 3개(시트/양식폴더/루트폴더)만 본인 환경에 맞게 수정.

/**
 * 농기계 주문자 서류 자동 생성 스크립트 (Google Apps Script)
 * AI: Google Gemini (Vision + 검수)
 */

// ============================================================
//  CONFIG
// ============================================================
const CONFIG = {
  SHEET_ID: '주문자리스트_시트_ID',
  TEMPLATE_FOLDER_ID: '양식_폴더_ID',
  ROOT_FOLDER_ID: '루트_폴더_ID',

  SHEET_TAB_NAME: '',
  NAME_COLUMN: '구매자명',
  STATUS_COLUMN: '완료여부',
  DONE_VALUE: '완료',

  // 503/과부하 시 앞에서부터 순서대로 폴백
  GEMINI_MODELS: ['gemini-2.5-flash', 'gemini-2.0-flash', 'gemini-1.5-flash-002'],
  GEMINI_API_BASE: 'https://generativelanguage.googleapis.com/v1beta/models',
  GEMINI_MAX_TOKENS: 2048,
  GEMINI_MAX_RETRIES: 4,
  GEMINI_BASE_DELAY_MS: 1500,

  USE_GEMINI_VISION: true,
  USE_GEMINI_VALIDATION: true,

  // {{플레이스홀더}} → 시트 컬럼
  SHEET_FIELD_MAPPING: {
    '구매자명': '구매자명',
    '주소': '주소',
    '연락처': '연락처',
    '생년월일': '생년월일',
    '계좌번호': '계좌번호',
    '단가': '단가',
    '기종명': '사업명'
  },
  PHOTO_EXTRACTED_FIELDS: ['규격명', '제조번호', '제품번호'],
  PHOTO_LABELS: { 전경: '전체전경', 기대: '기대번호' },
  PHOTO_WIDTH: 180
};

// ============================================================
//  ENTRY POINTS
// ============================================================
function runAll() {
  const orders = getIncompleteOrders_();
  Logger.log('미완료 주문: ' + orders.length + '건');
  const summary = [];
  orders.forEach(order => {
    try {
      processOrder_(order);
      summary.push({ name: order.data[CONFIG.NAME_COLUMN], status: 'OK' });
    } catch (err) {
      Logger.log('[ERROR] ' + order.data[CONFIG.NAME_COLUMN] + ': ' + err);
      summary.push({ name: order.data[CONFIG.NAME_COLUMN], status: 'ERR: ' + err.message });
    }
  });
  summary.forEach(s => Logger.log('• ' + s.name + ' → ' + s.status));
  return summary;
}

function runOne(name) {
  const orders = getIncompleteOrders_();
  const target = orders.find(o => String(o.data[CONFIG.NAME_COLUMN]).trim() === String(name).trim());
  if (!target) throw new Error('미완료 주문에 해당 이름이 없습니다: ' + name);
  processOrder_(target);
}

function verifySetup() {
  const ss = SpreadsheetApp.openById(CONFIG.SHEET_ID);
  Logger.log('✓ 시트: ' + ss.getName());
  const tpl = DriveApp.getFolderById(CONFIG.TEMPLATE_FOLDER_ID);
  Logger.log('✓ 양식: ' + tpl.getName() + ' (템플릿 ' + getTemplates_().length + '개)');
  const root = DriveApp.getFolderById(CONFIG.ROOT_FOLDER_ID);
  Logger.log('✓ 루트: ' + root.getName());
  const key = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  Logger.log(key ? '✓ GEMINI_API_KEY 설정됨' : '✗ GEMINI_API_KEY 없음');
  Logger.log('✓ 미완료 주문 ' + getIncompleteOrders_().length + '건');
}

// ============================================================
//  CORE PIPELINE
// ============================================================
function processOrder_(order) {
  const name = String(order.data[CONFIG.NAME_COLUMN]).trim();
  Logger.log('\n=== 처리 시작: ' + name + ' ===');

  let step = '시작';
  try {
    step = '주문자 폴더';
    const ordererFolder = driveRetry_(() => findOrdererFolder_(name), step);
    if (!ordererFolder) throw new Error('주문자 폴더 없음: ' + name);

    step = '사진 수집';
    const photos = driveRetry_(() => collectPhotos_(ordererFolder), step);

    step = 'Vision';
    let visionResult = { extracted: {}, classified: { 전경: [], 기대: [] } };
    if (photos.length > 0 && CONFIG.USE_GEMINI_VISION) {
      try { visionResult = extractPhotoInfoWithGemini_(photos, order.data); }
      catch (e) {
        Logger.log('  ⚠ Vision 실패, 파일명 폴백: ' + e.message);
        visionResult.classified = classifyPhotosByName_(photos);
      }
    } else if (photos.length > 0) {
      visionResult.classified = classifyPhotosByName_(photos);
    }

    const placeholders = buildPlaceholderMap_(order.data, visionResult.extracted);

    step = '템플릿 처리';
    const templates = driveRetry_(() => getTemplates_(), step);
    const createdDocs = [];
    templates.forEach(template => {
      step = '복사(' + template.getName() + ')';
      const copy = driveRetry_(() => copyTemplateToOrdererFolder_(template, ordererFolder, name), step);
      step = '치환(' + copy.getName() + ')';
      driveRetry_(() => { replacePlaceholders_(copy, placeholders); return true; }, step);
      if (copy.getName().indexOf('사진대지') > -1) {
        step = '사진 삽입(' + copy.getName() + ')';
        driveRetry_(() => { insertPhotosIntoPhotoDoc_(copy, visionResult.classified); return true; }, step);
      }
      createdDocs.push(copy);
      Logger.log('  ✓ ' + copy.getName());
    });

    if (CONFIG.USE_GEMINI_VALIDATION) {
      createdDocs.forEach(doc => {
        try {
          const issues = validateFinalDocWithGemini_(doc);
          if (issues && issues.length) Logger.log('  ⚠ 이슈(' + doc.getName() + '): ' + issues);
        } catch (e) { Logger.log('  검수 건너뜀: ' + e.message); }
      });
    }

    markOrderComplete_(order.rowIndex);
    Logger.log('=== 완료: ' + name + ' ===');
  } catch (e) {
    throw new Error('[' + step + '] ' + e.message);
  }
}

// ============================================================
//  SHEET I/O
// ============================================================
function getIncompleteOrders_() {
  const ss = SpreadsheetApp.openById(CONFIG.SHEET_ID);
  const sheet = CONFIG.SHEET_TAB_NAME ? ss.getSheetByName(CONFIG.SHEET_TAB_NAME) : ss.getSheets()[0];
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return [];
  const headers = values[0].map(h => String(h).trim());
  const statusIdx = headers.indexOf(CONFIG.STATUS_COLUMN);
  const orders = [];
  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    if (String(row[statusIdx] || '').trim() === CONFIG.DONE_VALUE) continue;
    const data = {};
    headers.forEach((h, j) => { data[h] = row[j]; });
    if (!String(data[CONFIG.NAME_COLUMN] || '').trim()) continue;
    orders.push({ rowIndex: i + 1, headers, data });
  }
  return orders;
}

function markOrderComplete_(rowIndex) {
  const ss = SpreadsheetApp.openById(CONFIG.SHEET_ID);
  const sheet = CONFIG.SHEET_TAB_NAME ? ss.getSheetByName(CONFIG.SHEET_TAB_NAME) : ss.getSheets()[0];
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const statusIdx = headers.indexOf(CONFIG.STATUS_COLUMN);
  sheet.getRange(rowIndex, statusIdx + 1).setValue(CONFIG.DONE_VALUE);
}

// ============================================================
//  DRIVE HELPERS
// ============================================================
function findOrdererFolder_(name) {
  const root = DriveApp.getFolderById(CONFIG.ROOT_FOLDER_ID);
  const it = root.getFoldersByName(name);
  return it.hasNext() ? it.next() : null;
}

function collectPhotos_(folder) {
  const photos = [];
  [MimeType.JPEG, MimeType.PNG, 'image/jpg', 'image/webp', 'image/heic'].forEach(mime => {
    const it = folder.getFilesByType(mime);
    while (it.hasNext()) photos.push(it.next());
  });
  return photos;
}

function getTemplates_() {
  const folder = DriveApp.getFolderById(CONFIG.TEMPLATE_FOLDER_ID);
  const it = folder.getFilesByType(MimeType.GOOGLE_DOCS);
  const docs = [];
  while (it.hasNext()) docs.push(it.next());
  return docs;
}

function copyTemplateToOrdererFolder_(template, ordererFolder, name) {
  const copyName = name + '_' + template.getName().replace(/_양식$/, '') + '_양식';
  const existing = ordererFolder.getFilesByName(copyName);
  while (existing.hasNext()) { try { existing.next().setTrashed(true); } catch(e){} }
  const copy = template.makeCopy(copyName, ordererFolder);
  Utilities.sleep(600); // Drive 인덱싱 대기
  return copy;
}

function driveRetry_(fn, stepLabel) {
  const maxRetries = 4;
  const baseMs = 800;
  let lastErr;
  for (let i = 0; i <= maxRetries; i++) {
    try { return fn(); } catch (e) {
      lastErr = e;
      const msg = String(e.message || e);
      const retryable = /Service error|rateLimit|backendError|timeout|Internal error|temporarily unavailable/i.test(msg);
      if (!retryable || i === maxRetries) break;
      const wait = baseMs * Math.pow(2, i) + Math.floor(Math.random() * 300);
      Logger.log('  ⟳ Drive 재시도(' + stepLabel + ') ' + wait + 'ms');
      Utilities.sleep(wait);
    }
  }
  throw lastErr;
}

// ============================================================
//  PLACEHOLDER MAPPING
// ============================================================
function buildPlaceholderMap_(rowData, extracted) {
  const map = {};
  Object.keys(CONFIG.SHEET_FIELD_MAPPING).forEach(ph => {
    map[ph] = formatValue_(ph, rowData[CONFIG.SHEET_FIELD_MAPPING[ph]]);
  });
  CONFIG.PHOTO_EXTRACTED_FIELDS.forEach(f => { map[f] = (extracted && extracted[f]) ? String(extracted[f]) : ''; });
  map['날짜'] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
  return map;
}

function formatValue_(ph, val) {
  if (val == null || val === '') return '';
  const s = String(val).trim();
  if (ph === '생년월일' && /^\d{8}$/.test(s)) return s.slice(0,4)+'-'+s.slice(4,6)+'-'+s.slice(6,8);
  if (ph === '단가' && /^\d+$/.test(s)) return Math.round(Number(s)/1000).toLocaleString('ko-KR');
  return s;
}

function replacePlaceholders_(file, map) {
  const doc = DocumentApp.openById(file.getId());
  const body = doc.getBody();
  Object.keys(map).forEach(k => {
    body.replaceText('\\{\\{' + escapeRegex_(k) + '\\}\\}', map[k] || '');
  });
  doc.saveAndClose();
}

function escapeRegex_(s) { return String(s).replace(/[.*+?^${}()|[\]\\]/g, '\\$&'); }

// ============================================================
//  PHOTO INSERTION
// ============================================================
function insertPhotosIntoPhotoDoc_(file, classified) {
  const doc = DocumentApp.openById(file.getId());
  doc.getBody().getTables().forEach(table => {
    for (let r = 0; r < table.getNumRows(); r++) {
      const row = table.getRow(r);
      if (row.getNumCells() === 0) continue;
      const label = row.getCell(0).getText().trim();
      if (label === CONFIG.PHOTO_LABELS.전경) insertPhotosIntoRow_(row, classified.전경);
      else if (label === CONFIG.PHOTO_LABELS.기대) insertPhotosIntoRow_(row, classified.기대);
    }
  });
  doc.saveAndClose();
}

function insertPhotosIntoRow_(row, photos) {
  for (let c = 1; c < row.getNumCells(); c++) {
    const idx = c - 1;
    if (idx >= photos.length) break;
    const cell = row.getCell(c);
    cell.clear();
    const para = cell.appendParagraph('');
    try {
      driveRetry_(() => {
        const img = para.appendInlineImage(photos[idx].getBlob());
        const w = CONFIG.PHOTO_WIDTH;
        const ratio = img.getHeight() / img.getWidth();
        img.setWidth(w); img.setHeight(Math.round(w * ratio));
        return true;
      }, '이미지(' + photos[idx].getName() + ')');
    } catch (e) {
      para.appendText('[사진 삽입 실패: ' + photos[idx].getName() + ']');
    }
  }
}

function classifyPhotosByName_(photos) {
  const out = { 전경: [], 기대: [] };
  photos.forEach(p => {
    if (/전경|전체|panorama/i.test(p.getName())) out.전경.push(p);
    else out.기대.push(p);
  });
  return out;
}

// ============================================================
//  GEMINI API
// ============================================================
function getApiKey_() {
  const k = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  if (!k) throw new Error('GEMINI_API_KEY 가 스크립트 속성에 없습니다.');
  return k;
}

function extractPhotoInfoWithGemini_(photos, rowData) {
  const parts = [];
  photos.forEach((file, i) => {
    const blob = file.getBlob();
    parts.push({ text: '[IMAGE ' + i + '] ' + file.getName() });
    parts.push({ inline_data: {
      mime_type: blob.getContentType() || 'image/jpeg',
      data: Utilities.base64Encode(blob.getBytes())
    }});
  });
  parts.push({ text:
    '[주문정보] 구매자=' + (rowData['구매자명']||'') + ', 기종=' + (rowData['사업명']||'') + '\n' +
    '[요청]\n1) 각 이미지를 "전경"(설치된 장소 전체) 또는 "기대"(기계 본체/명판)로 분류\n' +
    '2) "기대" 사진에서 규격명/제조번호/제품번호 OCR (없으면 빈 문자열)\n' +
    '[출력] {"classifications":[{"index":0,"category":"전경|기대"}], "extracted":{"규격명":"","제조번호":"","제품번호":""}}'
  });

  const resp = callGemini_({
    contents: [{ parts }],
    generationConfig: { maxOutputTokens: CONFIG.GEMINI_MAX_TOKENS, temperature: 0.2, responseMimeType: 'application/json' }
  });
  const parsed = parseJsonLoose_(extractGeminiText_(resp));
  const classified = { 전경: [], 기대: [] };
  if (parsed && Array.isArray(parsed.classifications)) {
    parsed.classifications.forEach(c => {
      const cat = c.category === '전경' ? '전경' : '기대';
      if (photos[c.index]) classified[cat].push(photos[c.index]);
    });
  } else {
    const fb = classifyPhotosByName_(photos);
    classified.전경 = fb.전경; classified.기대 = fb.기대;
  }
  return { extracted: (parsed && parsed.extracted) || {}, classified };
}

function validateFinalDocWithGemini_(docFile) {
  const text = DocumentApp.openById(docFile.getId()).getBody().getText();
  const resp = callGemini_({
    contents: [{ parts: [{ text:
      '아래 서류에 문제가 있는지 검수: 치환 안된 {{...}}/필수값 누락/포맷 이상.\n' +
      '출력: {"issues":["..."]}\n=== 본문 ===\n' + text
    }]}],
    generationConfig: { maxOutputTokens: 512, temperature: 0.1, responseMimeType: 'application/json' }
  });
  const parsed = parseJsonLoose_(extractGeminiText_(resp));
  return (parsed && Array.isArray(parsed.issues)) ? parsed.issues : [];
}

function callGemini_(payload) {
  const apiKey = getApiKey_();
  let lastErr;
  for (let m = 0; m < CONFIG.GEMINI_MODELS.length; m++) {
    const model = CONFIG.GEMINI_MODELS[m];
    for (let attempt = 0; attempt <= CONFIG.GEMINI_MAX_RETRIES; attempt++) {
      const res = UrlFetchApp.fetch(
        CONFIG.GEMINI_API_BASE + '/' + model + ':generateContent?key=' + encodeURIComponent(apiKey),
        { method: 'post', contentType: 'application/json', payload: JSON.stringify(payload), muteHttpExceptions: true }
      );
      const code = res.getResponseCode();
      if (code >= 200 && code < 300) return JSON.parse(res.getContentText());
      lastErr = new Error('Gemini ' + code + ' (' + model + '): ' + res.getContentText());
      const retryable = [429, 500, 502, 503, 504].includes(code);
      if (!retryable) break;
      if (attempt < CONFIG.GEMINI_MAX_RETRIES) {
        Utilities.sleep(CONFIG.GEMINI_BASE_DELAY_MS * Math.pow(2, attempt) + Math.random() * 500);
      }
    }
  }
  throw lastErr;
}

function extractGeminiText_(resp) {
  const p = resp && resp.candidates && resp.candidates[0] && resp.candidates[0].content && resp.candidates[0].content.parts;
  return (p || []).map(x => x.text || '').join('');
}

function parseJsonLoose_(text) {
  if (!text) return null;
  let s = String(text).trim().replace(/^```(?:json)?\s*/i, '').replace(/```\s*$/i, '');
  const a = s.indexOf('{'), b = s.lastIndexOf('}');
  if (a < 0 || b < 0) { try { return JSON.parse(s); } catch(e) { return null; } }
  try { return JSON.parse(s.slice(a, b + 1)); } catch(e) { return null; }
}

 

우선 

필요할때마다 구글 시트에서 스크립트만 돌리면 되고, 

구글 내에서 작업이 이뤄지기 때문에 조금 더 저렴하다는 것......

 

위 코드는 Claude의 도움을 받았다.

오히려 Gemini가 주는 코드는 오류가 나고...Claude는 잘돌아가는 이상한 현상;;;

 

Gemini반성하자.

 

삽질 포인트 3가지

디버깅을 하며 오류는 클로드의 도움을 받았음.

 

SyntaxError: Identifier 'CONFIG' has already been declared Apps Script는 .gs 파일 전역 스코프가 공유된다. Code.gs와 따로 만든 파일 양쪽에 코드를 붙여넣으면 터짐. 파일 하나만 남길 것.

Gemini API 오류 503: UNAVAILABLE Gemini Flash가 가끔 과부하. GEMINI_MODELS 배열에 폴백 모델을 줄세워 놓고 지수 백오프로 자동 재시도 하게 했다. 보통 자동 복구됨.

Exception: Service error: Drive makeCopy 직후 openById 하면 가끔 인덱싱이 덜 끝나서 터진다. (1) makeCopy 뒤 600ms 대기, (2) 모든 Drive 작업을 driveRetry_()로 감싸고 Service error 패턴을 재시도 대상으로 지정해서 해결.

마치며

템플릿 측에서 {{필드명}}을 시트 컬럼명과 맞춰두기만 하면, 서류 종류가 늘어도 스크립트 수정 없이 양식 폴더에 파일만 추가하면 자동으로 처리된다. Vision이 필요한 필드만 CONFIG에 선언해두면 끝.