(업무자동화) 이메일에서 송장번호 수집 및 구글시트에 기록하기

자동화의 필요성

구매대행의 경우 공급처에서 송장번호가 나오면 대부분 이메일을 통해 전달된다. 몇건 안되면 모르겠지만 매일 수많은 메일을 읽고 처리하는 것은 꽤나 번거로운 일이다. 더구나 사람이 확인하는 경우 오류가 발생할 여지가 있고 소모적인 경우가 많다. 송장번호를 담긴 이메일을 자동으로 읽어오고 구글 시트에 그 데이터를 기록하는 스크립트를 활용하면 많은 시간이 절약될 수 있다.

코드 소개

이번에 소개할 코드는 파이썬을 사용하여 NAVER 메일의 특정 이메일을 읽어와, 그 데이터를 구글 스프레드시트에 기록하는 작업을 수행하는 흐름이다. 파이썬으로 작성된 코드로 파이썬은 이 작업을 수행하기에 적합한 언어로, IMAP을 통해 이메일을 읽고 Google Sheets API를 연동하여 데이터를 기록할 수 있다.

 

1. 필요한 라이브러리와 설정

먼저, 이 작업에는 IMAP을 통한 이메일 접근과 Google Sheets API가 필요하다. 이를 설정하기 위해서는 NAVER 메일 서버에 접근할 수 있는 설정을 하고, Google Cloud Platform에서 프로젝트를 생성해 API를 활성화한 뒤 인증 파일을 다운로드 받아야 한다.

파이썬에서 Google Sheets API를 사용하려면 gspread와 oauth2client 같은 라이브러리가 필요합니다. 이들은 인증과 API 호출을 도와주는 도구들이다. 또한 imaplib, email, re 등의 라이브러리를 사용해 이메일을 읽어올 수 있다.

 

3. 구글 시트에 데이터 추가

이메일에서 추출한 데이터를 Google Sheets API를 사용해 구글 시트에 기록한다. 구글 스프레드시트는 협업과 데이터 관리에 매우 유용한 도구로, API를 통해 프로그래밍적으로 데이터를 추가, 수정, 삭제할 수 있다. 이 코드에서는 이메일에서 tracking URL을 추출하고 주문번호에 맞춰 구글 시트에 기록하게 됩니다. tracking URL 에는 송장번호가 포함되어 있고 주문번호에 맞게 구글시트에 기록하기 위해서는 이메일에서 주문번호와 tracking URL 만 추출하면 된다.

 

4. 코드의 전체 흐름

• IMAP을 통해 NAVER 메일 서버에 연결하고 특정 조건의 이메일을 검색
• 검색된 이메일의 내용을 파싱하여 필요한 정보를 추출
• Google Sheets API를 통해 해당 정보를 스프레드시트에 기록
• 추가적으로, 배송 추적 URL을 업데이트하기 위해 BeautifulSoup을 사용해 웹 페이지를 스크래핑하여 최신 정보를 기록

다음은 코드의 주요 부분과 주석을 포함한 설명이다.

import imaplib  # IMAP 서버에 연결하고 이메일을 가져오기 위한 라이브러리
import email  # 이메일 메시지를 처리하기 위한 라이브러리
from email.header import decode_header  # 이메일 헤더를 디코딩하기 위한 모듈
import re  # 정규 표현식을 사용하여 문자열을 검색하기 위한 라이브러리
import datetime  # 날짜와 시간을 처리하기 위한 라이브러리
import gspread  # Google Sheets API와의 연결을 위한 라이브러리
from oauth2client.service_account import ServiceAccountCredentials  # 서비스 계정 자격 증명을 사용하여 Google API에 접근하기 위한 모듈
import requests  # HTTP 요청을 보내기 위한 라이브러리
from bs4 import BeautifulSoup  # HTML과 XML 파일을 파싱하기 위한 라이브러리
import os  # 운영 체제와 상호작용하기 위한 라이브러리 (환경 변수 관리 등)
from dotenv import load_dotenv  # .env 파일에서 환경 변수를 로드하기 위한 라이브러리

# .env 파일에서 환경 변수를 로드합니다.
# 이메일 자격 증명과 같은 민감한 정보를 안전하게 유지하는 데 도움이 됩니다.
dotenv_path = '.env'
load_dotenv(dotenv_path)

# 환경 변수에서 이메일 ID, 비밀번호 및 Google 자격 증명 경로를 로드합니다.
email_id = os.getenv('email_id')  # 이메일 계정 ID
email_pw = os.getenv('email_pw')  # 이메일 계정 비밀번호
creds_path = os.getenv('GOOGLE_CREDENTIALS_PATH')  # Google API 자격 증명 파일 경로

# NAVER 메일 서버 주소 정의
server = 'imap.naver.com'  # NAVER의 IMAP 서버 주소

# Google Sheets 연결 구성
# Google Drive 및 Sheets API 액세스를 위한 범위를 정의합니다.
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
# 서비스 계정 자격 증명을 사용하여 Google Sheets에 액세스합니다.
creds = ServiceAccountCredentials.from_json_keyfile_name(creds_path, scope)
client = gspread.authorize(creds)  # Google Sheets API 클라이언트를 인증합니다.
# "Order Management"라는 Google 시트와 "주문관리" 워크시트를 엽니다.
ordering_sheet = client.open("Order Management").worksheet("주문관리")

# 이메일 본문에서 주문번호와 추적 URL을 추출하는 함수
def extract_order_info(email_body):
    # 이메일 본문에서 주문번호와 추적 URL을 찾기 위한 정규 표현식 패턴 정의
    order_number_pattern = r"주문번호\s+(\d+)"  # "주문번호" 뒤에 숫자를 매칭합니다.
    tracking_url_pattern = r"배송출적\s+\(\s+(https?://[^\s]+)\s+\)"  # "배송추적" 뒤에 URL을 매칭합니다.
    # 이메일 본문에서 주문번호와 추적 URL을 검색합니다.
    order_number_match = re.search(order_number_pattern, email_body)
    tracking_url_match = re.search(tracking_url_pattern, email_body)
    # 매칭된 주문번호와 추적 URL을 추출합니다.
    order_number = order_number_match.group(1) if order_number_match else None
    tracking_url = tracking_url_match.group(1) if tracking_url_match else None

    # 추적 URL을 찾지 못한 경우 경고 메시지를 출력합니다.
    if tracking_url is None:
        print("Tracking URL not found in email body.")
    return order_number, tracking_url

# 특정 날짜 이후의 이메일을 검색하기 위해 메일 서버에 연결하는 함수
def search_emails_by_date(server, email_id, email_pw, start_date):
    print("서버에 연결 중...")
    # IMAP 서버에 연결하고 이메일 자격 증명을 사용하여 로그인합니다.
    mail = imaplib.IMAP4_SSL(server)  # IMAP4 SSL을 사용하여 서버에 연결합니다.
    mail.login(email_id, email_pw)  # 이메일 계정에 로그인합니다.
    # 특정 메일함 폴더(이 경우 "CostcoTrackingNumber")를 선택합니다.
    mail.select("CostcoTrackingNumber")

    # 특정 날짜 이후에 수신된 이메일을 검색합니다.
    print(f"{email_id}로 로그인되었습니다. {start_date.strftime('%Y-%m-%d')} 이후의 이메일을 검색 중...")
    date_str = start_date.strftime("%d-%b-%Y")  # 시작 날짜 형식 지정
    status, messages = mail.search(None, f'(SINCE "{date_str}")')  # 특정 날짜 이후의 이메일 검색
    messages = messages[0].split()  # 이메일 ID 목록을 가져옵니다.

    print(f"{len(messages)}개의 메시지를 발견했습니다.")
    emails = []
    # 발견된 각 이메일을 반복 처리합니다.
    for msg_num in messages:
        print(f"메시지 {msg_num.decode('utf-8')} 처리 중...")
        typ, msg_data = mail.fetch(msg_num, '(RFC822)')  # 전체 이메일 메시지를 가져옵니다.
        for response_part in msg_data:
            if isinstance(response_part, tuple):
                # 가져온 데이터에서 이메일 메시지 객체를 생성합니다.
                msg = email.message_from_bytes(response_part[1])
                # 이메일 제목을 디코딩합니다.
                subject = decode_header(msg['subject'])[0]
                subject = subject[0].decode() if isinstance(subject[0], bytes) else subject[0]
                # 발신자의 이메일 주소를 가져옵니다.
                from_ = msg.get('from')
                body = ""
                # 이메일이 멀티파트인지 확인합니다 (즉, 여러 부분으로 구성되어 있는지).
                if msg.is_multipart():
                    # 이메일의 각 부분을 반복하여 text/plain 콘텐츠를 찾습니다.
                    for part in msg.walk():
                        if part.get_content_type() == "text/plain" and "attachment" not in str(part.get("Content-Disposition")):
                            body_part = part.get_payload(decode=True)  # 부분 콘텐츠를 디코딩합니다.
                            body_part = body_part.decode(part.get_content_charset('utf-8')) if part.get_content_charset() else body_part.decode('utf-8', errors='replace')
                            body += body_part  # 이메일 본문에 콘텐츠를 추가합니다.
                else:
                    # 이메일이 멀티파트가 아닌 경우 직접 페이로드를 디코딩합니다.
                    body = msg.get_payload(decode=True).decode('utf-8', errors='replace')
                # 이메일 본문에서 주문번호와 추적 URL을 추출합니다.
                order_number, tracking_url = extract_order_info(body)
                # 이메일 정보를 리스트에 추가합니다.
                emails.append({'from': from_, 'subject': subject, 'body': body,
                              'order_number': order_number, 'tracking_url': tracking_url})
    # 메일 서버 연결을 종료합니다.
    mail.close()  # 메일함 닫기
    mail.logout()  # 서버 로그아웃
    print("완료되었습니다. 서버에서 연결이 해제되었습니다.")
    return emails

# 제공된 링크를 방문하여 추적 URL을 업데이트하는 함수
def update_tracking_url(tracking_url):
    # 추적 URL이 Costco Korea의 URL인 경우에만 진행합니다.
    if tracking_url.startswith("https://www.costco.co.kr/"):
        try:
            print(f"{tracking_url}에 접속하여 새로운 추적 URL을 가져오는 중...")
            response = requests.get(tracking_url)  # HTTP GET 요청을 사용하여 페이지를 가져옵니다.
            if response.status_code == 200:
                # BeautifulSoup을 사용하여 페이지를 파싱합니다.
                soup = BeautifulSoup(response.content, 'html.parser')  # HTML 콘텐츠 파싱
                # 배송 정보를 포함하는 div를 찾습니다.
                delivery_info = soup.find('div', class_='delivery-info')
                last_row = delivery_info.find_all('span', class_='row')[-1]
                tracking_link = last_row.find('a')
                # 새로운 추적 링크가 발견되면 반환합니다.
                if tracking_link and tracking_link.has_attr('href'):
                    print(f"새로운 추적 URL 발견: {tracking_link['href']}")
                    return tracking_link['href']
                else:
                    print("새로운 추적 URL을 찾을 수 없습니다.")
                return None
            else:
                print(f"페이지를 가져오는 데 실패했습니다. 상태 코드: {response.status_code}")
        except Exception as e:
            print(f"추적 URL 업데이트 중 오류 발생: {e}")
    return tracking_url

# Google 시트에 추적 정보를 업데이트하는 함수
def update_spreadsheet_with_tracking_info(order_number, tracking_url, worksheet):
    try:
        # 주어진 주문번호를 포함하는 셀을 찾습니다.
        cell = worksheet.find(order_number)  # 주문번호가 있는 셀 찾기
        if cell:
            # 주문번호 옆의 셀에 추적 URL을 업데이트합니다.
            worksheet.update_cell(cell.row, cell.col + 8, tracking_url)  # 추적 URL 업데이트
    except Exception as e:
        print("스프레드시트 업데이트 중 오류 발생:", e)

# 이메일 검색을 시작할 날짜와 시간을 사용자로부터 입력받습니다.
datetime_input = input("날짜와 시간을 YYYY-MM-DD HH:MM 형식으로 입력하세요 (예: 2024-05-08 14:30): ")
start_datetime = datetime.datetime.strptime(datetime_input, "%Y-%m-%d %H:%M")  # 입력된 날짜와 시간을 datetime 객체로 변환

# 지정된 날짜 이후의 이메일을 검색하고 Google 시트를 업데이트합니다.
target_emails = search_emails_by_date(server, email_id, email_pw, start_datetime)

# 각 이메일을 반복하여 추적 정보를 업데이트합니다.
for email in target_emails:
    if email['tracking_url']:
        updated_url = update_tracking_url(email['tracking_url'])  # 추적 URL 업데이트
        update_spreadsheet_with_tracking_info(email['order_number'], updated_url, ordering_sheet)  # Google 시트 업데이트
    else:
        print("업데이트할 추적 URL이 없습니다.")

 

마무리

이렇게 설정해 두면 매일 수작업으로 이메일을 확인하고 데이터를 기록하는 대신, 자동으로 구글 시트에 기록된 내용을 확인할 수 있다. 이를 통해 반복적인 작업을 줄이고, 보다 중요한 일에 집중할 수 있게 된다.

이메일을 구글 시트에 자동으로 기록하는 것은 매우 유용한 자동화 작업이다. 이를 통해 시간과 노력을 절약할 수 있을 뿐만 아니라, 데이터의 정확성도 높일 수 있다.