0. Motivation

Who’s Good에서는 ESG리서쳐와 분석가/개발자 간에 데이터를 주고 받는 일이 매우 빈번하다. 특히 기업 관련 뉴스 데이터와, 다양한 소스로부터 수집하는 ESG 관련 데이터에 대한 QC를 진행하고 결과를 DB에 적재하는 과정이 있다. 엑셀에서 작업한 데이터를 저장하고, 슬랙으로 전달하는 여러 단계와 여러 사람들을 거치다 보니 주고받은 파일명이 뒤죽박죽인 아주 원초(?)적인 문제부터, 데이터가 언제 업데이트 되었는지 추적이 불가능한 상황도 발생하면서 마음 한 켠에 찝찝함이 남아있는 나날이 계속 되었다. 언제 어디선가 불시에 문제가 생기지는 않을까 하는 두려움. 하지만 아무도 두려워하지 않는 듯해 보여서 더 두려운 고요한 두려움.

“대체 언제까지 슬랙으로 엑셀 파일을 주고 받아야 하는가?“에 대한 해답을 찾던 중, 구글 스프레드시트(Google Spreadsheet)와 연동하는 것으로 몇가지 고민을 해결할 수 있게 되었다. gspread라는 라이브러리를 찾게 되었다. 별 거 아닌데, 왜 여태 사용해보지 않았을까. 이거다 싶었다.

바로 적용한 아주 간단한 예시를 들자면, 리서쳐들이 구글 스프레드시트에서 작업한 것들을 DB에 적재하는 과정을 자동화할 수 있었고, 데이터를 누가 작업했고, 업데이트가 되었는지에 대한 여부 또한 스프레드시트를 기반으로 작업하게 되니 해결 되었다. Python과 gspread 통해서 구글 스프레드시트와 연동하는 과정을 다룬 사내 튜토리얼을 다시 정리해보았다.

 

1. 튜토리얼

1.1. GCP에서 사용자 인증 설정

Python으로 스프레드시트를 연동하기 위해서는, GCP(Google Cloud Platform)에서 사용자 인증과 API 사용 인증이 준비되어야 한다.

 

1.1.1. 구글 클라우드 플랫폼에 로그인

  • 회사에서는 팀원들과 드라이브를 공유하기에 Gsuite 계정으로 로그인했다.

 

1.1.2. 새로운 프로젝트 생성

  • 구글 드라이브, 구글 스프레드시트와 연동할 새로운 프로젝트를 새로 생성한다.

 

1.1.3. 구글 드라이브 API 사용 설정

  • 새로 생성한 프로젝트를 선택하고, API 개요 이동하여 구글 드라이브 API 사용을 설정한다.

 

1.1.4. 구글 드라이브 API에 대한 인증정보 생성.

  • 구글 드라이브 API를 사용함에 있어서 필요한 사용자 인증정보를 추가한다.
  • 새 서비스 계정을 만들면, 서비스 계정 및 Key가 JSON 형태의 파일이 받아지게 된다.
    • 특히 추후 파이썬 코드 내에서 Key가 필요하게 되니, 적절한 디렉토리에 저장하도록 한다.

 

1.1.5. 구글 스프레드시트 API 사용 설정

  • 구글 드라이브 API 사용 설정한 방법과 동일하게, 구글 스프레드시트 API 사용설정을 활성화 한다.

   

1.2. 파이썬 패키지 설치

  • 파이썬에서는 gspread와 oauth2client가 필요하다.
    • gspread: 파이썬을 통해 구글 스프레드시트와 연동하고, 제어할 수 있게 하는 패키지.
    • oauth2client: OAuth2을 통해 사용자 인증을 하기 위해 설치함.
  • 터미널 또는 CMD에서 pip를 통해 설치하도록 한다.
pip install gspread
pip install --upgrade oauth2client

   

1.3. 구글 스프레드시트 파일 설정

  • 파이썬을 통해서 접근하고 연동하고자 하는 해당 구글 시트에서 사용자 인증 정보를 설정하다.
  • 앞서 내려 받은 JSON파일에서 client_email의 값을 복사한 뒤, 해당 스프레드시트 우측 상단 공유를 클릭한 뒤 입력하여 권한을 부여한다..

  • 복잡하지는 않지만, 스프레드시트마다 이 작업을 해주어야 한다는 점이 약간 번거로운 부분이다.
  • 공유 권한부여까지 완료되었다면, 이제 파이썬으로 스프레드시트의 데이터를 읽어와보자!

   

1.4. 파이썬에서 테스트

1.4.1. 인증과 연동

  • 사용자 인증 파일(JSON)을 통해 연동을 한다.
from oauth2client.service_account import ServiceAccountCredentials

scope = ["https://spreadsheets.google.com/feeds",
         "https://www.googleapis.com/auth/spreadsheets",
         "https://www.googleapis.com/auth/drive.file",
         "https://www.googleapis.com/auth/drive"]

creds = ServiceAccountCredentials.from_json_keyfile_name("{your_JSON_filename}.json", scope)

 

1.4.2. 스프레드 시트 선택

  • 테스트를 위해서 샘플 스프레드시트를 생성한 상태이다.
    • 빈 시트인 시트1, 그리고 상장기업종목코드와 기업명이 담긴 sample_data 시트가 있다.
  • gspread 패키지를 통해 인증 후, 접근하고자 하는 시트의 이름을 패스한다.
import gspread

spreadsheet_name = "{your target spreadsheet name}"
client = gspread.authorize(creds)
spreadsheet = client.open(spreadsheet_name)

 

1.4.3. 시트 불러오기

for sheet in spreadsheet.worksheets():
    print(sheet)

<Worksheet '시트1' id:1966713574>
<Worksheet 'sample_data' id:0>

 

1.4.4. 시트 선택하기

  • 이름 또는 인덱스를 통해 시트를 선택할 수 있다.
## by name
sheet = spreadsheet.worksheet("sample_data")

## OR by index
sheet = spreadsheet.get_worksheet(1)
print(sheet)

<Worksheet 'sample_data' id:0>

 

1.4.5. 시트 내 데이터 읽어오기

  • get_all_values() 함수는 시트 내 데이터를 모두 출력한다.
sheet.get_all_records()[:3]

[{'CompanyStockCode': 'S030190',
  'PA_CompanyID': 1,
  'IA_CompanyID': 'ID00001',
  'DelistStatus': '',
  'CompanyKorName': 'NICE평가정보'},
 {'CompanyStockCode': 'S038620',
  'PA_CompanyID': 2,
  'IA_CompanyID': 'ID00002',
  'DelistStatus': '',
  'CompanyKorName': '위즈코프'},
 {'CompanyStockCode': 'S039020',
  'PA_CompanyID': 3,
  'IA_CompanyID': 'ID00003',
  'DelistStatus': '',
  'CompanyKorName': '이건홀딩스'}]

 

1.4.6. Pandas DataFrame 형태로 변환

  • 간단한 함수를 작성하여 Pandas의 DataFrame형태로 데이터를 불러올 수 있다.
def gsheet2df(sheet):
    df = pd.DataFrame(columns=list(sheet.get_all_records()[0].keys()))
    for item in sheet.get_all_records():
        df.loc[len(df)] = item
    return df

df_sample_data = gsheet2df(sheet)
df_sample_data.head()

   

2. 마무리하며

리서쳐와 데이터를 주고 받으며 일하던 와중에, gspread의 적용은 매우 빛과 소금 같은 시원한, 그리고 매우 신속했던 해결책이었다. 내 업무영역에 적용한 뒤, 바로 다른 분석가 동료들에게 튜토리얼을 통해 공유하는 세션을 가지기도 했던 만큼 적용되는 범위가 컸다.

누구든 일을 하다 잠재적인 기술 부채를 맞닥뜨리면, 미래의 나를 위해서 어떻게든 털어내버리고 싶을 것이다. 하지만, 함께 해결해나갈 인력이 없는 환경에서 비개발자인 동료들도 불편해하지 않게끔 적절한 방법을 찾고 도입하는 것은 생각보다 쉽지 않았다. 답답할 때가 없지는 않지만, 이번 케이스처럼 또 하나하나씩 도입해나가면 더 매끄러워지지 않을까라는 생각을 해보며 또 다른 기술 부채를 맞닥뜨릴 마음의 준비를 해본다.

 

3. Reference