Google Sheets API 를 통해 스프레드시트 데이터 읽기/쓰기

회사 업무 특성상 구글 스프레드시트에 데이터를 기록하고 주기적으로 관리해야 하는 문서들이 많다.

예를들면 매월 첫째주가 되면 현재 운영중인 스토리지의 디스크 사용량을 기록하고, AWS S3 버킷의 용량이 어느정도인지, DB서버의 데이터들은 어느정도인지 등을 기록하여 추이를 확인하고 용량이 부족한 것들은 용량 증설 계획을 세우기도 한다. 

 

시트에 데이터를 기록하기 위해 매번 서버에 접속하여 df 명령어를 통해 직접 확인하고 AWS S3 콘솔에 접속하여 각 버킷의 지표를 확인해 기록하곤 한다.

 

사람이 직접 하나하나 확인해서 기록하는 일이다 보니 누락이 되거나 잘못 입력할 가능성이 있어 더 효율적으로 관리할 수 있는 방법을 알아보다가 Sheets API 라는 것을 알게되었다.

 

---

Google Cloud Platform API Setting

---

구글 API를 사용하기 위해선 GCP에 가입하여 여러 설정을 진행해야 한다.

 

1. GCP Project 생성 및 Sheets API 활성

- [API 및 서비스] => [사용 설정된 API 및 서비스] => [API 및 서비스 사용 설정] 
 : "Google Sheet" 검색해서 해당 API를 "사용" 으로 변경한다.

 

2. Service Account 생성

- [IAM 및 관리자] => [서비스 계정] => [서비스 계정 만들기]
 : 계정이름 입력 및 Basic - Editor 권한 설정 후 완료

3. 키 생성

위에서 만든 서비스계정의 키를 생성한다.

JSON 형식으로 된 키가 자신의 PC에 다운로드 된다.

(해당 키를 통해 API 서비스 이용 인증을 해야하니 잘 보관해야 함)

 

---

구글 스프레드시트 사용 권한 부여

---

[파이썬 테스트] 라는 스프레드 시트를 하나 생성했다.

[공유] 버튼을 클릭하여 위에서 생성한 서비스계정에 권한을 부여해준다.

이러면 이제 GCP의 서비스 계정이 해당 시트의 [편집자] 권한을 갖게 된다.

 

---

파이썬 코드

---

import os
import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds'] # Google API 요청 시 필요한 권한 유형
json_file_name = 'D:\sheets api\sojinhwan_python.json' # 서비스계정의 KEY. JSON Key File 경로
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file_name, scope) #사용자 계정의 자격증명
gc = gspread.authorize(credentials) #Google API에 로그인

spreadsheet_url = 'https://docs.google.com/spreadsheets/d/1EeZg1mnIzjWX8uSrMwtMiRdISHH8mBXCokryFQgXx-o/edit#gid=0' # 구글 스프레드시트 URL
doc = gc.open_by_url(spreadsheet_url) # 스프레드 시트 Open
worksheet = doc.worksheet('시트1') # 스프레드 시트의 워크시트 선택


cell_data = worksheet.acell('A1').value # 위 워크시트의 A1 셀 번호 값
#print('A1 cell data is ' + cell_data)
print('A1 cell data is ',cell_data)

# 시트에 쓰기
worksheet.update_acell('A1', "python test") # 위 워크시트의 A1 셀에 s3의 0번째 배열 값 업데이트

cell_data = worksheet.acell('A1').value # 위 워크시트의 A1 셀 번호 값
print('A1 cell data is ' + cell_data)

설명은 모두 주석에 달아놓았다.

이제 위 프로그램을 실행해 본다.

A1 셀에는 데이터가 아무것도 없으므로 None 으로 나오며,

그 이후 A1 셀에 python test 라는 데이터를 update 했다.

GCP 서비스계정이 해당 셀을 업데이트 했음을 알 수 있다.

 

이로써 Sheets API를 사용해 스프레드시트에 데이터를 읽고 쓰는 기본적인 방법을 정리했다.

이제 프로그램을 잘 짜서 서버의 스케줄러를 통해 자동화하는 작업을 진행해야겠다.