GAS + Lambda로 정산 데이터 자동 수집, 가공, 리포트 발송하기

🪙 정산 자동화 시리즈 #2

GAS + Lambda로 정산 데이터 자동 수집, 가공, 리포트 발송하기

---

1️⃣ 목표

이 단계에서는 다음을 자동화하여 정산 보고 프로세스를 구축한다:
✅ 매일/매주 거래 데이터 자동 수집
✅ 카테고리별 수수료율, 반품/환불 데이터 자동 가공
✅ 엑셀 리포트 생성 및 자동 이메일 발송
✅ Slack/메일 보고 자동화

---

2️⃣ 아키텍처 개요

구조 설명

• 사용자 → Google Sheets 메뉴 클릭
• GAS → Lambda URL로 action, params 전송
• Lambda:
  • DB 조회
  • Excel 리포트 생성
  • SES 이메일 발송
• 사용자 → Slack/메일로 보고 받음
• Google Apps Script (GAS): 시트 기반 유저 인터페이스, Lambda 호출, Slack 연동
• AWS Lambda + EventBridge: 주기 실행 및 자동 리포트 발송
• MySQL (또는 Firestore): 데이터 저장 및 분석
• xlsxwriter: 엑셀 보고서 생성
• AWS SES: 이메일 발송

---

3️⃣ 주요 Lambda 파이썬 코드

📄 엑셀 파일 생성 함수

def create_excel(data, sheet_name):
    output = BytesIO()
    workbook = xlsxwriter.Workbook(output)
    worksheet = workbook.add_worksheet(sheet_name)

    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#D9E1F2',
        'font_color': '#000000',
        'align': 'center',
        'valign': 'vcenter',
        'border': 1
    })

    row_format_even = workbook.add_format({'bg_color': '#F2F2F2', 'border': 1})
    row_format_odd = workbook.add_format({'bg_color': '#FFFFFF', 'border': 1})

    if data:
        headers = list(data[0].keys())
        for col_num, header in enumerate(headers):
            worksheet.write(0, col_num, header, header_format)
        for row_num, row in enumerate(data, 1):
            row_format = row_format_even if row_num % 2 == 0 else row_format_odd
            for col_num, value in enumerate(row.values()):
                worksheet.write(row_num, col_num, value, row_format)

    workbook.close()
    output.seek(0)
    return output

📧 이메일 발송 함수 (AWS SES)

def send_email_with_attachment(file_data, file_name):
    ses_client = boto3.client('ses', region_name='ap-northeast-2')
    sender_email = os.environ['SENDER_EMAIL']
    recipient_email = os.environ['RECIPIENT_EMAIL']

    to_list = [email.strip() for email in recipient_email.split(',') if email.strip()]

    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = ', '.join(to_list)
    msg['Subject'] = f'[더파이러츠] 정산 데이터 - {file_name}'

    attachment = MIMEBase('application', 'octet-stream')
    attachment.set_payload(file_data.getvalue())
    encoders.encode_base64(attachment)
    attachment.add_header('Content-Disposition', 'attachment', filename=f'{file_name}.xlsx')
    msg.attach(attachment)

    try:
        response = ses_client.send_raw_email(
            Source=sender_email,
            Destinations=to_list,
            RawMessage={'Data': msg.as_string()}
        )
        print(f"이메일 전송 성공: {response}")
    except Exception as e:
        print(f"이메일 전송 실패: {str(e)}")

🗓️ 리포트 생성 및 발송 메인 함수

• mode=daily, weekly, custom 옵션에 따라 기간을 자동 계산
• 정산 SQL 실행 → 엑셀 파일 생성 → 이메일 발송

def send_report(params):
    connection = create_db_connection(get_fishmarket_db_credentials())

    mode = params.get('mode')
    start_date = params.get('startDate')
    end_date = params.get('endDate')

    today = datetime.today()

    if mode == 'daily':
        yesterday = today - timedelta(days=1)
        start_date = end_date = yesterday.strftime('%Y-%m-%d')
    elif mode == 'weekly':
        last_monday = today - timedelta(days=today.weekday() + 7)
        last_sunday = last_monday + timedelta(days=6)
        start_date = last_monday.strftime('%Y-%m-%d')
        end_date = last_sunday.strftime('%Y-%m-%d')
    else:
        if not (start_date and end_date):
            raise ValueError("startDate와 endDate를 지정해야 합니다.")

    start_datetime = f"{start_date} 00:00:00"
    end_datetime = (datetime.strptime(end_date, "%Y-%m-%d") + timedelta(days=1)).strftime('%Y-%m-%d 00:00:00')

    try:
        with connection.cursor() as cursor:
            cursor.execute(query, (start_datetime, end_datetime, start_datetime, start_datetime))
            result = cursor.fetchall()
    finally:
        connection.close()

    try:
        excel_file = create_excel(result, f"정산_{start_date}_{end_date}")
        send_email_with_attachment(excel_file, f"정산리포트_{start_date}_{end_date}")
    except Exception as e:
        raise

    return f"{start_date} ~ {end_date} 리포트 전송 완료"

---

4️⃣ 주기 실행: Lambda + EventBridge 구성

Lambda 함수에

• 환경변수로 SENDER_EMAIL, RECIPIENT_EMAIL 설정

EventBridge Rule로 매일/매주 원하는 시간에 Lambda 호출
필요 시 Lambda URL로 Slack/수동 호출도 가능

---

5️⃣ GAS 연동: Lambda 호출 및 시트 기반 관리

✅ 핵심 역할
Google Sheets onOpen() → 사용자 친화 메뉴 생성

• callLambdaAction()으로 Lambda REST 호출

사용자는 시트에서 메뉴 클릭만으로:

• 신규 데이터 조회 및 메일 발송
• 신규 데이터 카테고리 업데이트
• 어제/지난주 데이터 메일 발송
• 사업자번호 업데이트
  를 진행 가능

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('📈 Data Automation')
    .addItem('🔄 Fetch New Data', 'fetchNewData')
    .addItem('🗂️ Update Data Categories', 'updateDataCategories')
    .addSeparator()
    .addItem('📆 Send Yesterday Report', 'sendYesterdayReport')
    .addItem('📅 Send Weekly Report', 'sendWeeklyReport')
    .addItem('🗓️ Send Custom Date Report', 'openCustomDateDialog')
    .addItem('🔄 Sync Business Numbers', 'syncBusinessNumbers')
    .addToUi();
}

// Replace with your actual Lambda URL
const LAMBDA_URL = "https://your-lambda-url";

function buildLambdaOptions(payload) {
  return {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload),
    muteHttpExceptions: true
  };
}

function callLambdaAction(action, params = {}) {
  const payload = { action, params };
  const options = buildLambdaOptions(payload);

  try {
    const response = UrlFetchApp.fetch(LAMBDA_URL, options);
    Logger.log(`[Lambda Response] ${response.getContentText()}`);
    return response.getContentText();
  } catch (e) {
    Logger.log(`[Lambda Error] ${e.message}`);
    throw new Error(`Lambda call failed: ${e.message}`);
  }
}

// Fetch new data from Lambda and notify user
function fetchNewData() {
  try {
    callLambdaAction("fetchNewData");
    SpreadsheetApp.getUi().alert("New data fetch request has been sent.");
  } catch (e) {
    SpreadsheetApp.getUi().alert("Error: " + e.message);
  }
}

// Update data categories in Lambda based on sheet data
function updateDataCategories() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NewData");
  if (!sheet) {
    SpreadsheetApp.getUi().alert("Sheet 'NewData' not found.");
    return;
  }

  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const idIdx = headers.indexOf("id");
  const categoryIdx = headers.indexOf("category");
  const updateIdx = headers.indexOf("update");

  const rowsToUpdate = [];

  for (let i = 1; i < data.length; i++) {
    if (data[i][updateIdx] === 1) {
      rowsToUpdate.push({
        rowIndex: i + 1,
        id: data[i][idIdx],
        category: data[i][categoryIdx]
      });
    }
  }

  const batches = chunkArray(rowsToUpdate, 20);

  for (const batch of batches) {
    const payload = batch.map(({ id, category }) => ({ id, category }));
    try {
      callLambdaAction("updateDataCategories", payload);
      for (const row of batch) {
        sheet.getRange(row.rowIndex, updateIdx + 1).setValue(0);
      }
    } catch (e) {
      Logger.log(`[Batch Update Error] ${e.message}`);
    }
  }
}

// Utility to chunk array into batches
function chunkArray(array, size) {
  const result = [];
  for (let i = 0; i < array.length; i += size) {
    result.push(array.slice(i, i + size));
  }
  return result;
}

// Send yesterday's report
function sendYesterdayReport() {
  try {
    callLambdaAction("sendReport", { mode: "daily" });
    SpreadsheetApp.getUi().alert("Yesterday's report request sent.");
  } catch (e) {
    SpreadsheetApp.getUi().alert("Error: " + e.message);
  }
}

// Send weekly report
function sendWeeklyReport() {
  try {
    callLambdaAction("sendReport", { mode: "weekly" });
    SpreadsheetApp.getUi().alert("Weekly report request sent.");
  } catch (e) {
    SpreadsheetApp.getUi().alert("Error: " + e.message);
  }
}

// Sync business numbers with external system
function syncBusinessNumbers() {
  try {
    callLambdaAction("syncBusinessNumbers");
    SpreadsheetApp.getUi().alert("Business numbers sync request sent.");
  } catch (e) {
    SpreadsheetApp.getUi().alert("Error: " + e.message);
  }
}

// Placeholder for a custom date picker dialog
function openCustomDateDialog() {
  SpreadsheetApp.getUi().alert("This feature is under development. Use Lambda with custom date parameters directly for now.");
}

6️⃣ 마치며: 자동화는 '팀 역량 확보'를 위한 도구

이제 반복되는 정산 보고 프로세스를 자동화하여:
✅ 팀원들이 데이터 수집/가공 대신 분석과 전략 수립에 집중
✅ 매출/수익/반품률 등 핵심 지표 기반 의사결정 강화
✅ 지속 가능한 자동화 운영 구조 확보
를 실현할 수 있다.

---

📌 다음 편 예고
3편에서는 정산 데이터를 시각화하여 카테고리별 손익 분석 및 개선 전략에 바로 활용하는 방법(Google Sheets 및 Data Studio 연동)을 다룬다.
📚 참고 자료

• AWS Lambda + EventBridge 공식
• AWS SES 메일 발송