파이썬 gspread 라이브러리로 구글시트 다루기

참고 자료

https://www.youtube.com/watch?v=bu5wXjz2KvU&ab_channel=PrettyPrinted

https://docs.gspread.org/en/v5.1.1/

셋업

구글 스프레드 시트는 OAuth2 인증방식을 통해 사용할 수 있다. open API를 제공하는 서비스에서 인증 기능을 사용하는 방식이다.

 

아래와 같이 구글시트를 다루기 위한 파이썬 라이브러리 gspread와 인증을 위한 oath2client도 설치해주자.

pip install gspread

pip install –upgrade oauth2client 

구글 개발자 페이지로 가보자.

https://console.developers.google.com/?pli=1

 

프로젝트를 생성한다.

 

API라이브러리에서 1)구글 드라이브 API와, 2)구글시트 API를 Enable 해주자.

Credentials 메뉴

Credentials 메뉴를 누르고 ‘Manage service accounts’를 누른다

 

다음 페이지가 나타나면, 상단에 ‘+Create service account’를 클릭한다.

 

기본적인 정보를 넣어주자. 2,3 번째 정보 기입란은 모두 선택 사항(Optional)이므로 무시하고 Done을 누르자.

 

그럼 아래와 같이 Service Accounts가 생성된다.

 

생성된 데이터에서 E-mail 정보를 누르면 아래 페이지가 나타난다. 아래 E-mail 데이터를 복사해주자. E-mail 데이터는 추후 구글시트와 연동할 때 사용할 데이터다.

 

그리고 제어할 구글시트 페이지에 들어가 ‘Share’ 버튼을 누른 후 위에서 복사한 E-mail 데이터를 추가하여 권한을 부여해준다.

 

다시 구글 클라우드 플랫폼으로 돌아와보자.

 

우측편에 Actions 옵션을 선택해서 ‘Manage keys’를 선택한다.

그럼 Keys 관리 페이지가 나오는데 ‘Create new key’를 해주자.

Key 파일은 JSON 파일로 생성해주자.

 

 

그럼 JSON 파일의 키가 자동으로 다운받아진다.

 

자 이제, 본격적으로 파이썬으로 구글 시트를 제어해보자.

 

gspread 라이브러리 설치

아래 명령어를 터미널에 입력하여 구글시트를 제어할 수 있는 gspread 라이브러리를 설치해주자

 

pip install gspread

필요하다면 아까 받은 JSON 파일도 프로젝트 폴더에 이동시켜준다. 

mkdir %APPDATA%\gspread

move credentialsfile.json %APPDATA%\gspread\service_account.json

이동 방법은 아래 링크를 참고한다.

https://www.computerhope.com/issues/ch001476.htm#:~:text=the%20steps%20below.-,Highlight%20the%20files%20you%20want%20to%20move.,V%20to%20move%20the%20files.

 

파일 명은 ‘lets_sheet_with_python’이다.

아래와 같이 코드를 실행시켜주니 정상적으로 작동된다. 그럼 구글시트가 문제 없이 잘 연결되었다는 말이다.

 

 

파이썬으로 본격적으로 제어하기

셀 개수 확인하기

위와 같이 샘플 시트를 우선 만들어보았다. ‘list’ 시트의 컬럼과 로우 개수(행열 개수)를 알려달라고 해보자.

 

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

print('Rows: ', wks.row_count)

print('Cols: ', wks.col_count)

 

row_count, col_count 함수는 데이터 기입과 상관없이 시트의 배경 템플릿을 구성하고 있는 빈 행렬 개수까지도 세어주는 것으로 보인다.

 

 

불필요한 rows들을 지워주고 다시 코드를 실행해보자.

 

 

Rows 수가 9개다. 

 

하나의 셀 값 가져오기

acell().value

B2 셀의 값을 호출해보자.

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

print(wks.acell('B2').value)

 

잘 나온다.

 

Cell

다른 방식으로 cell 값을 호출해보자.

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

#print(wks.acell('B2').value)

print(wks.cell(1, 2))

 

 

wks.cell(1, 2)에서 행열의 좌표를 지칭한다.

행 번호 : 1번

열 번호 : 2번

 

print(wks.cell(1, 2).value)

 

value를 붙여주면 title 값만 얻을 수 있다.

 

acell 함수를 활용하면 셀 명을 직접 언급해 셀 값을 호출하고, cell 함수는 셀의 인덱스를 이용해 값을 호출한다.

여러 셀 값 가져오기

get 함수로 데이터의 range를 가져오보자.

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

#print(wks.acell('B2').value)

#print(wks.cell(1, 2).value)

 

print(wks.get('B2:D7'))

 

 

결과는 아래와 같이 리스트들의 리스트 형태로 나타난다.

 

[['sultan of swing', '', 'rock'], ['let it be', '', 'pop'], ['let it go', '', 'os'], ['enter sandman', '', 'rock'], ['six play', '', 'jazz'], ['nocturne', '', 'classic']]

 

한 행의 데이터들이 하나의 리스트가 된다. 다음 행으로 넘어가면서 리스트들이 반복 생성되어 최종적으로 하나의 리스트에 쌓인 형태다. 

 

모든 셀 데이터 가져오기

get_all_records()

get_all_records() 함수를 사용하면 모든 데이터들을 가져올 수 있다.

 

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

#print(wks.acell('B2').value)

#print(wks.cell(1, 2).value)

#print(wks.get('B2:D7'))

 

print(wks.get_all_records())

 

결과는 아래와 같다.

위와 달리 딕셔너리들의 리스트를 가져온다.  한 행 내 각 데이터들이 어떤 column의 데이터인지 파악할 수 있다.

[{'': 1, 'title': 'sultan of swing', 'length': '', 'genre': 'rock'}, {'': 2, 'title': 'let it be', 'length': '', 'genre': 'pop'}, {'': 3, 'title': 'let it go', 'length': '', 'genre': 'os'}, {'': 4, 'title': 'enter sandman', 'length': '', 'genre': 'rock'}, {'': 5, 'title': 'six play', 'length': '', 'genre': 'jazz'}, {'': 6, 'title': 'nocturne', 'length': '', 'genre': 'classic'}, {'': 7, 'title': 'last farewell', 'length': '', 'genre': 'k-pop'}, {'': 8, 'title': '', 'length': '', 'genre': ''}]

get_all_values()

값들만 가져올 수도 있다.

 

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

#print(wks.acell('B2').value)

#print(wks.cell(1, 2).value)

#print(wks.get('B2:D7'))

 

#print(wks.get_all_records())

print(wks.get_all_values())

 

결과는 아래와 같다. 리스트의 리스트 형태다.

[['', 'title', 'length', 'genre'], ['1', 'sultan of swing', '', 'rock'], ['2', 'let it be', '', 'pop'], ['3', 'let it go', '', 'os'], ['4', 'enter sandman', '', 'rock'], ['5', 'six play', '', 'jazz'], ['6', 'nocturne', '', 'classic'], ['7', 'last farewell', '', 'k-pop'], ['8', '', '', '']]

Update() 함수로 값 업데이트하기

단일 셀 업데이트

B2 셀에 적혀있는 곡 이름을 다른 곡으로 바꿔주자.

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

#print(wks.acell('B2').value)

#print(wks.cell(1, 2).value)

#print(wks.get('B2:D7'))

 

#print(wks.get_all_records())

#print(wks.get_all_values())

wks.update('B1', 'Hotel California')

이렇게 실시간으로 수정된다. 놀랍지 않은가?

 

여러 셀 동시에 업데이트

한 셀 말고 여러 셀을 동시에 업데이트 할 수는 없을까?

위 범위를 업데이트해보자.

 

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

#print(wks.acell('B2').value)

#print(wks.cell(1, 2).value)

#print(wks.get('B2:D7'))

 

#print(wks.get_all_records())

#print(wks.get_all_values())

#wks.update('B2', 'Hotel California')

wks.update('C2:D4', [['3:14', 'old pop'], ['2:50', 'old pop'], ['2:15', 'ost music']])

 

깔끔하게 업데이트 되었다. 셀의 개수가 맞게 리스트의 값들을 넣어주는 것을 주의해라.

 

구글시트 함수 적용해주기

파이썬 함수 말고도 구글시트 자체의 유용한 함수들이 많다. 그 함수들을 적용하는 방법을 알아보자.

 

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

#print(wks.acell('B2').value)

#print(wks.cell(1, 2).value)

#print(wks.get('B2:D7'))

 

#print(wks.get_all_records())

#print(wks.get_all_values())

#wks.update('B2', 'Hotel California')

#wks.update('C2:D4', [['3:14', 'old pop'], ['2:50', 'old pop'], ['2:15', 'ost music']])

 

wks.update('E2', '=UPPER(D2)', raw=False)

위 코드를 설명하자면 이러하다. E2 셀에 ‘=UPPER(D2)’ 데이터를 입력하라. 여기서 raw가 False로 지정되어있는데, 해당 데이터는 rawdata, 즉 그냥 문자로만 넣는 것이 아니라 함수를 적용해서 넣을 수 있도록 하라는 의미이다. 구글시트의 함수를 사용하려면 raw=False 값으로 해주면 된다.

 

 

만약, raw=True로 설정하면 아래와 같이 문자 그대로 기입된다.

셀 삭제하기

 

delete_rows()

행을 삭제하는 함수를 사용해보자.

import gspread

 

sa = gspread.service_account(r"C:\Users\yangs\Desktop\project\003.moviepy_musicmachine\%appdata%\gspread\service_account.json")

sh = sa.open("lets_sheet_with_python")

 

wks = sh.worksheet("list")

 

#print('Rows: ', wks.row_count)

#print('Cols: ', wks.col_count)

 

#print(wks.acell('B2').value)

#print(wks.cell(1, 2).value)

#print(wks.get('B2:D7'))

 

#print(wks.get_all_records())

#print(wks.get_all_values())

#wks.update('B2', 'Hotel California')

#wks.update('C2:D4', [['3:14', 'old pop'], ['2:50', 'old pop'], ['2:15', 'ost music']])

#wks.update('E2', '=UPPER(D2)', raw=True)

 

wks.delete_rows(5)

삭제 전

 

삭제 후

5번째 row가 삭제되어 아래에 있던 데이터들이 올라왔다.