퍼스트파티 DB → 파이썬 → 구글 시트 → 슬랙 연결 자동화 방법

이 글에서는 퍼스트파티 DB, 즉 회사 내부 데이터를 SQL로 추출해서 구글 시트에 적재하고, 슬랙에 자동으로 시트의 데이터를 전송하는 과정을 알아볼 것입니다. 이 과정을 파이썬과 Apps Script를 활용하여 전부 자동화시키는 것이 목적입니다.

 

 

목차

1. 퍼스트파티 DB, 파이썬 연결

2. 파이썬, 구글 시트 연결

3. 구글 시트, 슬랙 연결

4. 파이썬 자동 실행 방법

 

 

1. 퍼스트파티 DB, 파이썬 연결 

이 부분은 이전에 포스팅했던 적이 있다. 참고하도록 하자.

파이썬에서 MySQL 쿼리로 DB 데이터 끌어오는 방법

 

 

2. 파이썬, 구글 스프레드시트 연결

파이썬과 구글 스프레드시트를 연동하기 위해서는 구글 사용자 인증 설정이 필요하다. 이 작업을 하기 위해서 구글 클라우드 플랫폼을 먼저 접속해야 한다.  

 

2-1. 구글 클라우드 플랫폼에서 해야 하는 작업

1) [API 및 서비스 정보 > 사용자 인증 정보 > 프로젝트 만들기]에서 프로젝트 생성

2) 생성한 프로젝트 하에서 [API 및 서비스 정보 > 사용자 인증 정보 > 서비스 인증정보 만들기 > 서비스 계정] 클릭

3) '서비스 계정 이름' 작성 후, '만들고 계속하기' 클릭

4) [역할 선택 > Basic > 편집자] 선택 후 완료

5) [API 및 서비스 정보 > 사용자 인증 정보 > 서비스 계정 > 생성된 이메일] 클릭

6) [상단메뉴 '키' > 키 추가 > 새 키 만들기] 클릭

7) josn 파일이 컴퓨터에 저장 됨(파이썬을 실행시킬 경로에 파일 저장)

8) [상단메뉴 '세부정보' > 이메일] 복사

9) 연결할 구글 시트에서 '공유' 클릭 후, 이메일 붙여넣고 완료

10) [API 및 서비스 정보 > 라이브러리] 클릭

11) Google Sheets API와 Google Drive API 검색 후, 사용 클릭

 

여기까지 했으면 구글 사용자 인증 설정 절차는 끝났습니다. 이제 파이썬에서 구글 시트에 입력할 코드를 짜서 실행하면, 자동으로 구글 스프레드시트에 데이터가 입력됩니다.

 

2-2. 패키지 설치

파이썬과 실제 연동을 위한 환경을 구성하기 위해서 아래 3가지 패키지가 필요하므로, 터미널을 이용해 설치해 줍니다. 

 

pip install --upgrade oauth2client
pip install gspread
pip install PyOpenSSL

 

2-3. 파이썬 코드

1) 환경 셋업

from oauth2client.service_account import ServiceAccountCredentials
import gspread

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
        '{your JSON key file path}', scope)
gc = gspread.authorize(credentials)

 

2) 구글 시트 데이터 읽어오기

gc1 = gc.open("sample").worksheet('시트1') # open()부분에 시트 제목을 넣어줘야한다.
gc2 = gc1.get_all_values()
print(gc2) #시트1에 입력되어있는 데이터 출력 ex. [[1,2,3],[2,3,4]]

 

3) 구글 시트에 데이터 입력하기

gc1.update_acell('B2', 'Hello World!')

 

한꺼번에 여러셀에 입력하고 싶다면, 아래와 같이 array형태로 입력하면 된다.

gc1.update('A1', [[1 2,3], [3, 4], [5,6]])

 

4) (전체 코드) 1,2번 코드를 합치면, 회사 내부 DB 데이터를 파이썬으로 구글 시트에 바로 옮길 수 있다.

import pymysql
from oauth2client.service_account import ServiceAccountCredentials
import gspread

plt.rcParams['font.family'] = 'Malgun Gothic'

conn = pymysql.connect(host='localhost', user='root', password='0000', db='main_db', charset='utf8')
cursor = conn.cursor()

sql = """
select * 
from user 
limit 5
"""

cursor.excute(sql)
data = cursor.fetchall() #튜플 형태 데이터
array_data = [list(t) for t in data] # 튜플을 배열로 변환

conn.close()


scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
        '{your JSON key file path}', scope)
gc = gspread.authorize(credentials)

gc1 = gc.open("sample").worksheet('시트1')
gc1.update('A1', array_data)

 

 

참고 블로그

• 파이썬을 이용한 구글 스프레드시트 연동 및 자동화 방법
• gspread를 이용하여 Python에서 구글 시트 연동하기

 

3. 구글 스프레드시트, 슬랙 연결

 

3-1. 슬랙봇 만들기

1) 슬랙 api페이지에서 'Create New App' 버튼 클릭

2) 'From screatch' 클릭

3) 'App Name' 설정, 해당 알림을 받을 채널이 속한 workspace 선택 후, 'Create App' 클릭하여 슬랙봇 생성

4) [Basic Information > Add features and functionality > Bots] 클릭하여 슬랙봇 기본 설정

5) [App Display Name > Edit] 클릭 하여, 슬랙봇 이름 설정

6) [Basic Information > Add features and functionality > Incoming Webhooks] 클릭

7) [Incoming Webhooks > Activate Incoming Webhooks] ON으로 변경

8) 하단 'Webhooks URLs for Your Workspace'에서 'Add New Webhook to Workspace' 클릭

9) 메세지가 전송되어야 할 채널 선택 후, 허용 버튼 클릭

10) 'Webhooks URLs for Your Workspace'에서 'Webhook URL' Copy 클릭(시트연결에 사용할 URL)

11) 'Basic Information' 가장 하단 부분 'Display Information' 입력하여 슬랫봇 꾸미기

 

 

3-2. Apps Script 코드 입력

위에서 파이썬과 연결했던 구글 시트에서 [확장 프로그램 > Apps Script] 클릭 후, 아래 코드를 입력해 준다.

function sendMessage() { 
  var googleSheet = SpreadsheetApp.getActiveSheet(); //구글시트 변수를 생성한다
  var data = googleSheet.getRange("A:B").getValues(); // 구글시트에서 원하는 범위의 셀을 불러온다
  const webhook = "{Webhook URLs for Your Workspace URLG}";  //슬랙 앱의 Webhook URL 복붙
  let message = {         //메시지를 만들어주는 부분
    "text" : "안녕하세요 슬랙봇입니다! 데이터를 전송해드릴게요 :)\n" +
    "1번 :" + data[1][0] + "\n" +     //2번째 행의 1번째 열에 해당하는 값
    "2번 :" + data[2][0] + "\n" +     //3번째 행의 1번째 열에 해당하는 값
    "3번 :" + data[3][0] + "\n" +     //4번째 행의 1번째 열에 해당하는 값 
    "4번 :" + data[4][0] + "\n" +     //5번째 행의 1번째 열에 해당하는 값 
    "오늘 하루도 화이팅!"
  }
  var options = {       //슬랙으로 메시지를 발송할때 설정을 해주는 부분
    "method" : "post",
    "contentType" : "application/json",
    "muteHttpExceptions" : true,
    "payload" : JSON.stringify(message)
  };

  try {       //슬랙으로 메시지를 발송해주는 부분
    UrlFetchApp.fetch(webhook, options);
  } catch(e) {
    Logger.log(e);
  }
}

 

3-3. 앱스크립트 트리거 자동 전송 설정

마지막으로 트리거까지 설정해 주면, 구글 시트의 데이터 일정 주기마다 자동으로 슬랙에 전송된다!

 

 

참고 블로그

• 자동화 끝판왕 슬랙봇 만들기(입문)

 

4. 파이썬 자동 실행 방법

1) 윈도우의 경우, '작업 스케줄러'를 검색하여 프로그램을 실행시킨다.

 

2) '작업 만들기' 클릭

3) 이름 입력

4) 상단  '트리거' 클릭 후, '새로 만들기' 클릭

5) 언제 작업을 실행할지 설정

6) 상단 '동작' 클릭 후,  '새로 만들기' 클릭

7) pythonw.exe가 설치되어 있는 경로를 찾아서 '프로그램/스크립트'에 입력

8) '인수 추가(옵션)'에 실행시킬 파일의 경로 + 파일 이름을 입력(ex. C:\Users\coduking\slack_test.py)후, 확인

9) 작업이 생성된 걸 확인했면, 우클릭 후 실행을 눌러서 작동하는지 확인

 

 

여기까지 진행을 완료했다면, 퍼스트파트 데이터 -> 파이썬 -> 구글 스프레드시트 -> 슬랙 자동화가 완성됩니다! 한 가지 주의할 점은 Apps script 트리거보다 파이썬 파일을 실행시키는 작업스케줄러의 트리거가 먼저 작동되게끔 설정하셔야 하고, 작업 스케줄러는 컴퓨터가 켜져있어야 작동한다는 점입니다 :)

 

 

 

 

참고 유튜브