본문으로 건너뛰기

구글 시트에서 앱스 스크립트를 활용해 구글 드라이브 이미지 취합 자동화하기

희원

2024.11.19

구글 시트에서 앱스 스크립트를 활용해 구글 드라이브 이미지 취합 자동화하기 본문 이미지

소개

회사에서 매주 수기로 상품별 이미지를 취합하는 업무가 있어, 이 취합 과정을 자동화 해보았습니다.

이 업무는 아래와 같이 진행되는 업무입니다.

  • 스프레드 시트에 상품들이 리스트업 되어 있습니다.

  • 각 상품에 맞는 이미지를 각 담당자들이 구글 드라이브에 업로드 합니다.

    • 이미지를 업로드 할 때는 파일명을 제품명과 동일하게 업로드 하고 있습니다.

  • 취합 여부를 확인할 때는 스프레드 시트에 있는 파일명을 구글 드라이브에 복붙하며, 하나하나 수기로 체크하고 있었습니다.

이 업무를 통해서 아래와 같은 자동화 시나리오를 짰습니다.

  • 스프레드 시트에 기입된 상품명을 바탕으로 구글 드라이브에서 이미지를 자동으로 검색한다.

  • 동일한 이미지가 있을 경우, 동그라미를 입력하고 옆 셀에 링크를 생성한다.

  • 동일한 이미지가 없을 경우, 엑스를 입력한다.

진행 방법

  1. 먼저 클로드에게 이 방법이 가능한지부터 물어보았습니다.

  2. 파일의 구조를 알려준 뒤 스크립트를 짜달라고 했더니 뚝딱 스크립트를 만들어서 보내옵니다.

  3. 스크립트를 실행해보니 이미지를 잘 찾지 못합니다. 몇 가지 문제가 있었는데요.

    • 이미지 드라이브는 상위 폴더 안에 하위 폴더 여러 개가 있는 구조인데, 하위 폴더까지 찾아보지 않아서 이미지를 잘 찾지 못했습니다.

    • 이미지 확장자는 jpg, png 등 다양한데 jpg 파일만 찾고 있었습니다.

    • 이미지를 올려주는 담당자들이 제각기 달라서 파일명 규칙도 중구난방인데, 이런 경우 이미지를 잘 찾지 못했습니다.

  4. 이후에는 이 문제를 해결하기 위해 클로드에게 계속 물어가며 스크립트를 수정하고 또 수정했습니다.

    검정색 배경에 한국어 텍스트

  1. 그 결과, 자동으로 이미지를 뚝딱 취합해주는 앱스 스크립트를 완성할 수 있었습니다.

function checkProductImages() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  if (sheet.getRange('D1').getValue() == '') {
    sheet.getRange('D1').setValue('이미지 링크');
  }
  
  var parentFolderId = '19pbP3GfiIqSatg-Vn1YghlUJPo6CYpTl';
  var parentFolder = DriveApp.getFolderById(parentFolderId);
  
  var fileMap = new Map();
  
  // 텍스트 정제 함수
  function cleanText(text) {
    return text
      .toLowerCase() // 대소문자 구분 없애기
      .replace(/\([^)]*\)/g, '') // 일반 괄호와 내용 제거
      .replace(/([^)]*)/g, '') // 전각 괄호와 내용 제거
      .replace(/\[[^\]]*\]/g, '') // 대괄호와 내용 제거
      .replace(/【[^】]*】/g, '') // 다른 괄호 제거
      .replace(/\//g, ' ') // 슬래시를 공백으로
      .replace(/&/g, ' and ') // & 처리
      .replace(/_상품정보고시$/, '') // _상품정보고시 제거
      .replace(/^상정고_/, '') // 상정고_ 제거
      .replace(/\s+/g, ' ') // 연속된 공백을 하나로
      .trim(); // 앞뒤 공백 제거
  }
  
  // 재귀적으로 모든 하위 폴더 검색
  function searchFolderRecursively(folder) {
    // 현재 폴더의 파일들 처리
    var files = folder.getFiles();
    while (files.hasNext()) {
      var file = files.next();
      var fileName = file.getName();
      if (fileName.endsWith('.jpg') || fileName.endsWith('.png')) {
        var nameWithoutExt = fileName.replace(/\.(jpg|png)$/i, '');
        var cleanFileName = cleanText(nameWithoutExt);
        
        fileMap.set(cleanFileName, {
          exists: true,
          url: file.getUrl(),
          originalName: fileName
        });
      }
    }
    
    // 하위 폴더 재귀적 처리
    var subFolders = folder.getFolders();
    while (subFolders.hasNext()) {
      searchFolderRecursively(subFolders.next());
    }
  }
  
  // 검색 시작
  try {
    searchFolderRecursively(parentFolder);
  } catch (error) {
    console.error('폴더 검색 중 오류:', error);
    SpreadsheetApp.getUi().alert('폴더 검색 중 오류가 발생했습니다: ' + error.toString());
    return;
  }
  
  // 스프레드시트 데이터 처리
  var lastRow = sheet.getLastRow();
  var data = sheet.getRange(2, 1, lastRow-1, 3).getValues();
  
  data.forEach(function(row, index) {
    var productName = row[0];
    var cleanProductName = cleanText(productName);
    var found = false;
    var fileUrl = '';
    
    // 디버깅용 로그 (필요시 주석 해제)
    // console.log('Checking product:', productName);
    // console.log('Cleaned product name:', cleanProductName);
    
    for (let [mapFileName, fileInfo] of fileMap) {
      if (cleanProductName === mapFileName || 
          mapFileName.includes(cleanProductName) || 
          cleanProductName.includes(mapFileName)) {
        found = true;
        fileUrl = fileInfo.url;
        break;
      }
    }
    
    // C열에 존재 여부 표시
    sheet.getRange(index + 2, 3).setValue(found ? 'ㅇ' : 'x');
    
    // D열에 링크 추가
    if (found) {
      var cell = sheet.getRange(index + 2, 4);
      cell.setFormula('=HYPERLINK("' + fileUrl + '", "이미지 보기")');
    } else {
      sheet.getRange(index + 2, 4).setValue('');
    }
  });
  
  SpreadsheetApp.getUi().alert('이미지 체크가 완료되었습니다.');
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('이미지 체크')
    .addItem('이미지 취합 현황 체크', 'checkProductImages')
    .addToUi();
}

결과와 배운 점

‎다음에는 미취합 담당자에게 슬랙 메시지를 자동으로 발송하게 하는 것까지 해 볼 예정입니다.

함께 이야기해요 0

로그인하고 댓글을 남겨 주세요

필요한 AI 정보만
메일함에 쏙쏙 넣어드려요.

지금 핫한 AI 소식부터 직접 해 본 사례까지, 월요일마다 무료로 받아보세요

이런 사례도 있어요

사례 전체 보기