파이썬으로 구글드라이브/스프레드시트 API 활용하기 (예제)

파이썬으로 구글 드라이브, 특히 구글 스프레드시트 다루는 방법에 대해 정리했다. API를 활용해보자.

준비

구글 스프레드시트를 다룰 때 사용할 파이썬 라이브러리는 gspread다.

https://gspread.readthedocs.io/en/latest/index.html

설치는 pip install gspread 로 끝낼 수 있다. (라이브러리 설치 위치를 명시하려면 pip install --target="C:\python_lib" gspread 로 지정하면 된다.)


그리고 구글 스프레드시트는 OAuth2를 이용한 인증 방식을 제공한다. (OAuth2란 open API 를 제공하는 서비스에서 인증을 위해 사용하는 도구라는 정도만 알고 넘어가자. 깊이 들어가면 사실 나도 잘 모른다.)

그래서 인증에 필요한 oauth2client도 설치를 해주어야 한다. pip install --upgrade oauth2client로 설치하면 된다. (마찬가지로 라이브러리 설치 위치를 명시하려면 pip install --target="C:\python_lib" --upgrade oauth2client로 지정하면 된다.)

인증 과정

일단 구글 개발자 콘솔에 접속하자.

https://console.developers.google.com/

여기서 프로젝트를 하나 생성한다. 이름은 상관 없다. 난 그냥 googlespreadsheet로 했다.

그러면 이렇게 뜰 거다.

이제 이 프로젝트에서 사용할 API를 신청해야 한다는 뜻이다.

라이브러리로 들어가서 검색을 하면 Google Drive API도 있고 그냥 Google Sheets API도 있는데 사실 스프레드시트도 구글 드라이브에 속한 기능을 제공하는 것이므로, 그리고 향후에 어떻게 활용될지 모르니 더 범용적인 Google Drive API를 설치해서 활용해보자.

사용 설정을 클릭하면 아래와 같이 뜰 텐데 여기서 우측 상단의 사용자 인증 정보 만들기를 클릭한다.

그리고 여기서 쉽게 인증할 수 있도록 우측의 서비스 계정을 클릭,

상단의 서비스 계정 만들기를 클릭하자.

1단계. 서비스 계정 이름을 입력하고,

2단계. 권한을 일일이 알아보기 귀찮다. 그냥 프로젝트의 탐색자, 편집자, 소유자, 뷰어 권한을 다 주자. 어차피 내 건데.

3단계. 키 만들기를 클릭한다.

클릭하면 이렇게 뜨는데, JSON을 선택하고 만들기를 누르면 파일 하나를 다운로드 하게 된다. 잘 저장해놓자.

파일을 열어 보면 이렇게 텍스트가 적혀 있는 걸 볼 수 있다.

여기서 client_email만 잘 저장해놓자.

이제 준비는 끝났다.

아, 참고로 API를 사용하는 데에 할당량이 있다. 하루에 10억 번 밖에(?) 못 한다.


1. 데이터 가져오기 (예제)

아래와 같이 미리 스프레스 시트를 하나 만들어봤다.

일단 공유를 눌러 아까 json 파일에서 확인했던 client_email 값을 넣어 사용자를 추가하자.

그리고 이 문서의 url을 기록해두자. 문서에 접근할 때 url을 활용하기 때문이다.

이제부터 본격적인 스크립트 작성이 필요하다.

인증 후 문서에 접속하고, 시트를 선택하는 코드는 아래와 같다.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

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

json_file_name = 'vast-ethos-251302-b8a92651b359.json'

credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file_name, scope)
gc = gspread.authorize(credentials)


spreadsheet_url = 'https://docs.google.com/spreadsheets/d/1j83Zex9AdiHad-LkmnndHdvZdu25KupZCmMdtqwCjCY/edit#gid=0'

# 스프레스시트 문서 가져오기 
doc = gc.open_by_url(spreadsheet_url)

# 시트 선택하기
worksheet = doc.worksheet('시트1')

1) 특정 셀 데이터 가져오기

특정 셀을 선택해서 데이터를 가져올 수 있다. 예를 들어 B1 셀에 있는 값을 가져오고 싶다면 acell('B1').value를 사용한다.

cell_data = worksheet.acell('B1').value
print(cell_data)

미리 입력해둔 값 b1이 잘 출력되는 걸 확인할 수 있다.

2) 행 데이터 가져오기

특정 행 데이터 전체를 가져오는 것도 가능하다. 예를 들어 1행에 있는 값을 가져오고 싶다면 row_values(1)을 사용한다.

row_data = worksheet.row_values(1)
print(row_data)

그런데 결과 값을 찍어보면 아래와 같이 나온다.

['a1', 'b1', 'c1', 'd1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']

이게 뭔고 하니 스프레드시트는 애초에 크기가 정해져 있어, 빈 데이터라도 그 크기만큼 다 가져오는 거다. 시트를 생성하면 기본적으로 A부터 Z까지 총 26개의 열이 있다. 위 결과는 이 26개 열에 입력된 값을 다 가져오고 있는 거다. 데이터 입력된 범위까지만 가져오려면 애초에 시트에서 필요한 열만 남기고 삭제해야 한다.

3) 열 데이터 가져오기

당연히 특정 열 데이터 전체를 가져오는 것도 가능하다. 예를 들어 1열에 있는 값을 가져오고 싶다면 col_values(1)을 사용한다.

column_data = worksheet.col_values(1)
print(column_data)

이것도 마찬가지로 결과를 찍어보면 ['a1', 'a2', '', '', '', ... , '', '', '']와 같은 식으로 생긴 1000개짜리 리스트가 나타난다. 위에서 설명한 것처럼 정해진 사이즈만큼 다 가져왔기 때문이다. 구글 스프레드시트는 1000개 행이 기본 사이즈고 당연히 추가/삭제할 수 있다.

4) 특정 영역 선택하여 데이터 가져오기

엑셀에서 영역을 마우스로 드래그하듯이 range('A1:D2')와 같은 방식으로 행과 열의 범위를 사각으로 지정해서 값을 가져올 수도 있다. 단, 범위를 지정하면 그 범위에 속한 셀들의 위치가 리스트로 생성되므로, 그 후에 각 셀마다 값에 접근해서 값을 가져와야 한다.

# 범위(셀 위치 리스트) 가져오기
range_list = worksheet.range('A1:D2')
print(range_list)
# 범위에서 각 셀 값 가져오기
for cell in range_list:
    print(cell.value)

범위를 지정하면 이런 리스트가 생성되고

[<Cell R1C1 'a1'>, <Cell R1C2 'b1'>, <Cell R1C3 'c1'>, <Cell R1C4 'd1'>, <Cell R2C1 'a2'>, <Cell R2C2 'b2'>, <Cell R2C3 'c2'>, <Cell R2C4 'd2'>]

여기에 .value를 사용해서 값을 가져온다.

a1
b1
c1
d1
a2
b2
c2
d2

2. 데이터 쓰기 (예제)

1) 특정 셀에 값 쓰기

스프레드시트에서는 셀 값을 업데이트, 갱신하는 방식으로 작성한다. 예를 들어 B1셀에 ‘b1 updated’라고 쓰고 싶다면 이렇게 하면 된다.

worksheet.update_acell('B1', 'b1 updated')

2) 행으로 데이터 추가하기

역시 셀 하나씩 값을 입력하는 것말고, 한 행을 통째로 추가할 일이 있다.

append를 활용해서 맨 아래에 한 행을 추가할 수 있다. 이렇게.

worksheet.append_row(['new1', 'new2', 'new3', 'new4'])

그런데 이렇게 하면 아래와 같이 1001번째 행에 추가가 된다.

값이 있든 없든 화면에 있는 행과 열이 이 시트의 활성화 범위로 보고, 그 아래에 추가한 거다.

만약 특정 위치에 한 행을 추가하고 싶다면 insert_row를 쓰고 행 번호를 넣어주어야 한다. 예를 들어 4번 행에 추가하고 싶다면 이렇게.

worksheet.insert_row(['new1', 'new2', 'new3', 'new4'], 4)

3) 시트 크기 조정하기

위에서 언급한 것처럼 스프레드시트는 크기가 화면에 보이는 것만큼 딱 정해져 있다. 기본적으로 시트를 생성하면 A부터 Z까지 총 26개 열과 1000개 행이 있다.

만약 이 시트 사이즈를 조정하고 싶다면 resize를 활용한다. 예를 들어 10행, 4열로 만들고 싶다면 이렇게.

worksheet.resize(10,4)

3. 스프레드시트 생성/공유하기 (예제)

어쨌든 API로 문서 생성, 시트 공유, 권한 부여 등을 할 일이 별로 없긴 하지만 그래도 기능이 있으니 간략히 남겨본다.

1) 스프레드시트 생성하기

create를 사용하고, 문서 이름을 넣어주면 된다.

gs = gc.create('새로운 테스트')

시트를 새로 생성하고 싶다면 add_worksheet를 사용해서 시트 이름과 사이즈를 지정해줄 수도 있다.

worksheet = gs.add_worksheet(title='시트1', rows='1', cols='1')

인증을 쉽게 하기 위해 API 서비스 계정을 사용했기 때문에 문서를 생성해도 그냥 평소 본인 계정으로 들어가면 보이지 않는 문제가 있다. 이걸 해결하고 싶으면 다른 인증방법을 활용해야 한다. 아니면 이렇게 서비스 계정으로 생성한 문서를 다른 사람에게 공유하고 소유권을 이전해버리면 된다.

2) 스프레드시트 공유, 소유권 부여하기

아래와 같이 하면 다른 사람에게 그 문서를 공유하고 소유권을 이전한다.

gs.share('hleecaster@gmail.com', perm_type='user', role='owner')

스프레드시트를 만들어서 공유하면 동료나 고객과 매번 현황을 요청하고 전달하는 귀찮은 프로세스를 생략할 수 있어서 개인적으로 매우 자주 쓰곤 한다. 틀이 정해져 있고 반복적으로 돌아가는 업무가 있다면 자동화를 해보자.

추천 글

“파이썬으로 구글드라이브/스프레드시트 API 활용하기 (예제)”의 15개의 댓글

  1. 안녕하세요. 잘 보고있습니다.

    그런데 에러가 생겨 여쭙니다.
    Unable to import ‘oauth2client.service_account’
    라고 뜨는데
    어찌하면 좋을까요?

  2. 안녕하세요 좋은 정보 너무 잘 참고했습니다.

    하나 질문이 있는데요 저 같은 경우에는 시트에서 계정정보 같은걸 관리하고 이 프로그램을 사용자들에게 배포하는데요.
    이 경우 시트에는 관리자 외에는 접근을 하면 안되는데, 프로그램 동작을 보니 이 과정에서 생성되는 json 파일도 사용자들에게 배포를 해야 하더라구요.

    이 경우에는 서비스 계정 만들기에서 각 권한을 어떻게 주는것이 좋을까요?

    1. 아 혹은 그냥 예시처럼 다 허용한다음에, 생성한 시트를 잠그거나 관리자 외에 못보게 하여도 프로그램 동작에는 문제가 없을까요?

    2. 제가 댓글만으로 내용을 명확히 이해하긴 어렵지만, 어차피 스프레드시트에서 특정 사용자를 초대하는 방식으로 접근 권한을 관리할 수 있기 때문에 쉽게 설정할 수 있을 거예요. (사실 API 권한 설정은 어려워서 저도 잘 ㅜㅜ)

  3. 안녕하세요 글 잘 읽고 있습니다!
    도중에에러가 생겨서요ㅠㅠ

    scope = [
    ‘https://spreadsheets.google.com/feeds’,
    ‘https://www.googleapis.com/auth/drive’,
    ]
    json_file_name = ‘vast-ethos-251302-b8a92651b359.json’

    이 부분에서 제 제이슨 파일이름을 적는게 맞는것 같은데

    credentials = ServiceAccountCredentials.from_json_keyfile_name(json_file_name, scope)

    이부분으로 넘어가면
    Traceback (most recent call last):
    File “”, line 1, in
    File “/anaconda3/lib/python3.6/site-packages/oauth2client/service_account.py”, line 219, in from_json_keyfile_name
    with open(filename, ‘r’) as file_obj:
    FileNotFoundError: [Errno 2] No such file or directory: ‘prime-heuristic-274402-8b754b9c8d34.json’

    이런 오류가 떠서요ㅠㅠ 구글링 해도 안나와서.. 혹시 어떻게 하는지 아실까요?
    gc = gspread.authorize(credentials)

    1. 제가 인증을 받기 위해 oauth2client 라이브러리를 사용했는데
      지금 사이트 들어가보니까 “oauth2client is now deprecated.”라고 써있네요. ㅜㅜ

      google-auth 또는 oauthlib를 추천해주는 것 같은데,
      그냥 쓱~ 봤을 땐 google-auth가 사용하기 쉬워보이긴 하네요.
      이걸로 사용하시면 아마 될 거예요 🙂

    2. 감사해요! 그런데 oauth2client library는 지금도 잘 작동되는 듯 해요ㅜㅜ 제가 json파일 경로를 잘 못쳐서 그랬더라구요ㅠㅠ 댓글 정말 감사드립니다~ 글 많이 도움이 되었어요

  4. 핑백: Presto를 이용하여 Google Sheets와 mysql 등 DB 데이터 JOIN 하기 | Popit

댓글 남기기