개요
구글 계정과 구글 스프레드시트를 잘 이용하면 이를 데이터베이스로 활용하거나, 해당 시트에 저장되어 있는 것을 이메일로 보낼 수 있다.
심지어 메일서버가 연동되어 있지 않은 정적페이지에서도 Contact Us와 같은 기능을 넣을 수 있다는 것은 정말 즐거운 일이다.
원문 사이트
https://github.com/mtinet/learn-to-send-email-via-google-script-html-no-server
번역 사이트
https://kutar37.tistory.com/entry/%EC%A0%95%EC%A0%81-HTML-form%ED%83%9C%EA%B7%B8%EC%97%90%EC%84%9C-%EB%A9%94%EC%9D%BC%EB%B3%B4%EB%82%B4%EA%B8%B0-Google-Apps-Mail
이번에는 python을 이용해서 구글 스프레드시트에 데이터를 업로드 하는 방법을 시연해보고자 한다.
설치
우선 파이썬이 설치되어 있다는 가정 하에 윈도우에서는 cmd를 열고, 맥에서는 터미널을 연다.
그 다음 구글 스프레드 시트를 사용하기 위해 인증을 할 수 있는 파이썬 라이브러리 oauth2client를 설치한다.
pip install oauth2client
다음으로 구글 스프레드시트를 사용할 수 있게 해주는 라이브러리 gspread를 설치한다.
pip install gspread
다음으로 데이터 가공을 위한 라이브러리 pandas를 설치한다.
pip install pandas
구글 개발자 콘솔
여기까지 진행이 되었다면 구글 개발자 콘솔에서 프로젝트를 만들고 해당 프로젝트의 권한을 부여해주는 작업을 해야 한다. 아래 링크로 들어가보자.
https://console.developers.google.com/
왼쪽 상단에 보이는 Google APIs 옆의 드롭다운 버튼을 누르면 새로운 프로젝트를 만들거나 기존의 프로젝트를 선택할 수 있는 화면이 나온다.
‘새 프로젝트'를 만들기 위해 오른쪽 상단의 버튼을 누르고, 원하는 이름을 지어주자.
적당한 이름이 만들어지면 ‘만들기’ 버튼을 누른다.
이제 자연스럽게 ‘사용자 인증 정보'만들기 페이지로 넘어가게 되는데, 페이지가 다 열리면 가운데 위쪽의 ‘사용자 인증 정보 만들기'버튼을 누르고 ‘서비스 계정'을 선택한다.
서비스 계정 만들기 페이지가 열리면 적당한 서비스 계정의 이름을 지정해준다. 이름 지정만으로 서비스 계정 ID가 자동으로 생성되는 것을 볼 수 있다.
만들기 버튼을 눌러보자.
다음으로 나오는 서비스 계정 권한 페이지에서는 역할과 권한을 부여할 수 있는데, 우리는 그냥 ‘프로젝트’-‘소유자'로 선택하자.
우측 상단의 ‘정보 패널 표시'버튼을 누르면 다음과 같이 본인의 계정과 권한을 부여한 ID가 함께 보여지는 것을 확인할 수 있다.
다음으로 가운데 하단의 ‘키만들기’ 버튼을 눌러보자.
이 버튼을 누르면 오른편에 JSON과 P12를 선택할 수 있는 화면이 나오는데, 일반적으로 JSON을 사용하므로 이것을 선택하고 ‘만들기'버튼을 누른다.
그러면 우리가 만든 서비스 계정 ID의 접속권한을 인증할 수 있는 비공개 키가 들어있는 JSON 파일이 다운로드 될 것이다. 다음 안내사항처럼 이 비공개키를 이용해 사용자의 시트에 접근이 가능하므로 안전하게 저장하여야 한다.
만약 이 키가 들어 있는 파일을 github 같은 곳의 공개 리포지토리에 올려놓게 되면 구글로부터 다음과 같은 메일을 받게 될 것이다.
요약하자면, 네 구글 스프레드 시트 비공개 키가 github의 공개된 공간에 노출되어 있으니 주의하라는 내용이다. 이 메일을 받고 리포지토리를 바로 비공개로 전환하였다.
우리는 이제 이렇게 만들어진 비공개 키를 이용해 구글 스프레드시트에 접속할 것이다.
다운로드 된 파일을 파이썬 파일을 만들 폴더로 이동시키자. 다운로드 된 파일의 형태는 다음과 같다.
서비스 계정을 다 만들고 나면 아래와 같이 이메일 형태로 된 서비스 계정 ID를 확인할 수 있다.
마지막으로 사용하고자 하는 라이브러리를 선택해보자. 왼쪽 메뉴의 상단에 있는 라이브러리를 클릭하면 아래와 같은 화면이 나온다. 우리가 받은 서비스 계정 ID를 이용해 어떤 API서비스를 이용할 것인지를 물어보는 것인데, 그냥 큰 범위로 google derive API와 google sheets API를 모두 선택해주어야 한다.
구글 드라이브 API와 구글 스프레드시트 API 모두 ‘사용설정’ 버튼을 누르면 사용할 할 수 있도록 세팅을 할 수 있고, API사용해보기 버튼을 누르면 사용할 수 있는 명령어에 대한 안내가 나온다.
사용설정이 완료된 화면과 API 안내 화면은 아래와 같다.
파이썬 API라이브러리를 살펴보고 싶다면 왼편 하단의 python 버튼을 누르고 API library 버튼을 눌러보자.
구글 스프레드 시트
개발자 페이지에 만들어진 사용자 계정 ID를 카피하고, 새로 만든 스프레드 시트의 ‘공유'버튼을 눌러 사용자 추가를 해준다.
그 다음 편집 화면 링크의 아이디에 해당하는 부분(~/d/*****/edit~ 의 ‘*’ 부분)이나 스프레드 시트 문서의 제목부분(여기서는pythonToSheet)을 카피한다. 소스코드를 짤 때 이 두 부분 중 하나를 통해서 문서를 찾아내기 때문이다. 우리는 여기서 문서의 제목을 사용하는 방법을 시연해 볼 것이다.
우리는 이 링크를 통해 편집화면으로 접근하게 되고, 인증된 사용자 계정 ID를 통해 권한을 부여받은 다음 시트의 셀들의 값을 가져오거나, 삭제하거나, 수정하거나, 추가할 수 있게 된다.
다만 스프레드 시트를 만든 다음에는 ‘헤더'가 되는 부분을 작성해주는 것이 좋다.
예를들면 연번, 품목, 단가, 수량, 금액이라던가….. 연번, 이름, 전화번호라던가 하는 등의 것들을 말하며, 이 부분이 있어야 파이썬을 이용해 데이터를 호출하거나 추가하거나 삭제할 때 유용하게 사용할 수 있다.
파이썬
다음으로 아래와 같은 형태로 파이썬 파일을 만들어보자.
from oauth2client.service_account import ServiceAccountCredentials
import gspread
import pandas as pd
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'spreadsheet-******-************.json', scope)
gc = gspread.authorize(credentials)
위 코드의 8열은 자신의 사용자 계정 ID의 비공개 키가 들어있는 JSON 파일의 이름을 적어주면 된다.
그 다음에는 위의 9행에 있는 것처럼 credentials에 의해 구글 스프레드 시트에 인증한 것을 gc라는 변수에 넣었으므로, 이를 이용한 메소드를 사용하면 된다. 예를들면 다음과 같다.
pythonToSheet문서의 시트1을 열고 모든 값을 가져와 프린트 하기
gc1 = gc.open("pythonToSheet").worksheet('시트1')
gc2 = gc1.get_all_values()
print(gc2)
특정 셀의 값을 업데이트 하고 다시 모든 값을 가져와 프린트하기
gc1.update_acell('B2', 'novels')
gc2 = gc1.get_all_values()
print(gc2)
리스트의 형태로 나타나는 값은 보기가 불편하니 pandas를 활용해 표로 정리해서 보여주기(drop는 첫번째 행이 다시 인쇄되는 것을 막기 위해 제거해주는 작업)
gc2 = pd.DataFrame(gc2, columns=gc2[0])
gc2 = gc2.reindex(gc2.index.drop(0))
print(gc2)
등의 활용법이 있다.
아래 사이트를 이용하면 보다 더 다양한 사용법을 익힐 수 있다.
https://github.com/burnash/gspread
https://gspread.readthedocs.io/en/latest/
참조
https://yurimkoo.github.io/python/2019/07/20/link-with-googlesheets-for-Python.html
http://hleecaster.com/python-google-drive-spreadsheet-api/
