서론: 시간 절약! Google Sheets 자동화
Google Sheets는 의심할 여지 없이 훌륭한 협업 도구입니다.
하지만 매일 아침, 매주 월요일, 반복되는 데이터를 복붙 하고 있다면,
우리는 가장 중요한 자원인 '시간'을 낭비하고 있는 것입니다.
파이썬을 다룰 줄 아는 우리에게는 더 스마트한 방법이 필요합니다.
바로 gspread 라이브러리를 이용한 완전 자동화!
하지만 막상 검색 해보면 "서비스 계정은 뭐지?", "OAuth2 인증은 왜 이렇게 복잡해?",
"Pandas DataFrame은 어떻게 한 번에 넣지?" 등 수많은 질문이 꼬리를 물죠.
저 또한 google_sheets_bridge.py를 개발하며 똑같은 문제들로 몇 시간씩 헤맨 경험이 있어요.
이 글은 단순한 라이브러리 기능 소개가 아닌 제가 직접 부딪히고 해결하며 얻은 실전 경험을 모두 녹여내,
여러분이 시간을 낭비하지 않도록 안내 하는 거에요.
1단계: Google Cloud 서비스 계정 생성 및 설정
우선, 우리 파이썬 스크립트가 Google Sheets에 접근할 수 있도록 '신분증'을 발급받아야 합니다.
이 신분증이 바로 '서비스 계정'입니다.
- Google Cloud Console 접속: Google Cloud Console에 접속하여 새 프로젝트를 생성하거나 기존 프로젝트를 선택합니다.
- API 및 서비스 활성화:
API 및 서비스>라이브러리로 이동합니다.Google Drive API와Google Sheets API를 검색하여 각각 '사용 설정'합니다. (매우 중요!)
- 서비스 계정 생성:
API 및 서비스>사용자 인증 정보로 이동합니다.+ 사용자 인증 정보 만들기>서비스 계정을 선택합니다.- 서비스 계정 이름(예:
sheets-automator)을 입력하고 '만들기 및 계속'을 클릭합니다. - 역할은
편집자(Editor)를 부여하고 '완료'를 클릭합니다.
- JSON 키 다운로드:
- 방금 만든 서비스 계정의 이메일 주소를 클릭합니다.
키탭 >키 추가>새 키 만들기를 선택합니다.- 키 유형은
JSON으로 선택하고 '만들기'를 클릭하면, 인증 정보가 담긴 JSON 파일이 다운로드됩니다. 이 파일은 절대로 외부에 노출해서는 안 됩니다.
- Google Sheet 공유:
- 자동화하려는 Google Sheet를 엽니다.
- 오른쪽 위
공유버튼을 클릭합니다. - 다운로드한 JSON 파일 안에 있는
client_email(예:sheets-automator@...iam.gserviceaccount.com)을 '사용자 및 그룹 추가'에 입력하고,편집자권한을 부여한 후 '공유'합니다.
이제 모든 준비 끝!
2단계: gspread 설치 및 인증 설정
이제 파이썬 환경에서 gspread와 관련 라이브러리들을 설치해 보겠습니다.
Pandas DataFrame을 쉽게 다루기 위해 gspread-dataframe도 함께 설치 강추!
pip install gspread gspread-dataframe pandas oauth2client이제 아까 다운로드한 JSON 키를 사용하여 Google Sheets에 연결합니다. Streamlit을 사용한다면, secrets.toml 파일을 이용해 안전하게 키를 관리하는 것이 좋습니다.
.streamlit/secrets.toml 파일 예시:
[google_sheets]
type = "service_account"
project_id = "your-project-id"
private_key_id = "your-private-key-id"
private_key = "-----BEGIN PRIVATE KEY-----\n ... \n-----END PRIVATE KEY-----\n"
client_email = "your-client-email@...iam.gserviceaccount.com"
client_id = "your-client-id"
auth_uri = "https://accounts.google.com/o/oauth2/auth"
token_uri = "https://oauth2.googleapis.com/token"
auth_provider_x509_cert_url = "https://www.googleapis.com/oauth2/v1/certs"
client_x509_cert_url = "..."주의: private_key의 개행문자는 \n으로 이스케이프 처리해야 합니다.
Python 인증 코드:
import gspread
import streamlit as st
import pandas as pd
# Streamlit secrets에서 인증 정보 로드
creds = st.secrets["google_sheets"]
gc = gspread.service_account_from_dict(creds)
# Google Sheet 열기 (URL 또는 이름으로)
spreadsheet = gc.open_by_url("YOUR_GOOGLE_SHEET_URL")
# spreadsheet = gc.open("YourSheetName")
# 특정 워크시트 선택
worksheet = spreadsheet.worksheet("Sheet1") # 또는 spreadsheet.get_worksheet(0)3단계: 기본 CRUD (생성, 읽기, 수정, 삭제) 작업
인증이 완료되었으니, 이제 자유자재로 데이터를 다뤄봅시다.
데이터 읽기 (Read):
# 모든 데이터 가져오기
all_data = worksheet.get_all_records()
df = pd.DataFrame(all_data)
print(df)
# 특정 범위의 데이터 가져오기
cell_value = worksheet.acell('A1').value
print(f"A1 셀의 값: {cell_value}")데이터 추가 (Create/Append):
# 한 줄 추가
new_row = ["Cline", "Software Engineer", 30]
worksheet.append_row(new_row)데이터 수정 (Update):
# 특정 셀 업데이트
worksheet.update('B2', 'Senior Engineer')
# 여러 셀을 한번에 효율적으로 업데이트 (Batch Update)
cell_list = worksheet.range('A3:C3')
cell_values = ['Newbie', 'Intern', 22]
for i, val in enumerate(cell_values):
cell_list[i].value = val
worksheet.update_cells(cell_list)데이터 삭제 (Delete):
# 특정 행 삭제
worksheet.delete_rows(3) # 3번째 행 삭제4단계: Pandas DataFrame ↔ Google Sheets 완벽 변환 (feat. gspread-dataframe)
데이터 분석가와 엔지니어에게 가장 필요한 기능입니다.
gspread-dataframe을 사용하면 이 과정이 엄청 간편 해져요.
DataFrame을 Google Sheets에 저장하기:
from gspread_dataframe import set_with_dataframe
# 새로운 DataFrame 생성
data = {'Name': ['John', 'Jane', 'Mike'], 'Role': ['Manager', 'CTO', 'Data Scientist'], 'Age': [40, 45, 28]}
df_to_upload = pd.DataFrame(data)
# 기존 시트 내용을 지우고 DataFrame으로 덮어쓰기
# resize=True 옵션은 시트 크기를 DataFrame에 맞게 자동으로 조절해줍니다.
set_with_dataframe(worksheet, df_to_upload, resize=True)
print("DataFrame이 성공적으로 업로드되었습니다.")set_with_dataframe 함수는 NaN 값을 빈 문자열로 자동 변환해주고,
시트 크기까지 조절해주어 훨씬 안정적이고 편리합니다.
5단계: 실전 팁 및 에러 처리
실제 프로젝트에서는 이론상으로 나오지 않는 문제 발생이 부지기수 입니다. 제가 직접 겪었던 문제와 해결책을 공유할께요.
API 할당량 초과 (Rate Limit): 단시간에 너무 많은 요청을 보내면 gspread.exceptions.APIError (HTTP 429) 오류가 발생합니다. 특히 반복문 안에서 셀을 하나씩 업데이트할 때 자주 발생합니다.
- 해결책 1 (Batch Update): 여러 업데이트 작업을
update_cells와 같은 배치 작업으로 묶어서 API 호출 횟수 자체를 줄이세요. - 해결책 2 (Exponential Backoff): 요청 실패 시 무작정 재시도하는 대신, 대기 시간을 점차 늘려가며 재시도하는 전략입니다.
import time
def update_with_backoff(worksheet, cell, value, retries=5, delay=1):
for i in range(retries): try: worksheet.update(cell, value) return True except gspread.exceptions.APIError as e: if e.response.status_code == 429: print(f"Rate limit exceeded. Retrying in {delay} seconds...") time.sleep(delay) delay *= 2 # 대기 시간을 2배씩 늘립니다. else: raise e raise Exception("Failed to update after multiple retries.")- 타임아웃 및 네트워크 오류:
- 해결책:
gspread는 내부적으로requests라이브러리를 사용합니다. gc.session.timeout을 설정하여 기본 타임아웃을 늘릴 수 있습니다.gc.session.timeout = 60 # 타임아웃을 60초로 설정
- 해결책:
- 자격증명 관리:
- 해결책: JSON 키 파일을 코드와 함께 Git에 올리는 것은 절대 금지!
Streamlit의secrets.toml, 환경변수, 또는 AWS Secrets Manager 같은 전문 도구를 사용하세요.
- 해결책: JSON 키 파일을 코드와 함께 Git에 올리는 것은 절대 금지!
- 프로젝트별 폴더 및 시트 관리:
- 팁:
gc.create()메서드로 새로운 스프레드시트를 만들고,spreadsheet.duplicate_sheet()로 템플릿 시트를 복제하여 프로젝트별로 자동화된 보고서를 생성할 수 있습니다.
- 팁:
결론: 이제 여유 시간은 가족과 함께 보내시기 바랍니다.
이제 여러분은 파이썬으로 Google Sheets를 자유자재로 다루는 강력한 무기를 습득했어요.
처음 1~2시간의 설정 과정이 조금 번거롭게 느껴질 수 있지만, 이 작은 투자가 여러분에게 굉장한 도움을 줄껍니다.
더 이상 단순 반복 업무에 소중한 시간을 낭비하지 마시길 바래요.
이 포스팅이 여러분의 업무 효율을 극대화하고, 더 창의적이고 중요한 문제에 집중하는 데 작은 도움이 되기를 진심으로 바랍니다.
시간 괜찮으시면 놀러오세요! [여기를 클릭]