[python] 구글 스프레드 시트 연동 및 관리(gspread)

gspread는 파이썬에서 구글 스프레드 시트와 연동하기 위한 라이브러리 입니다.

 

gspread를 이용하면 구글 시트에서 읽기 쓰기 등을 자동화할 수 있습니다.

 

가장먼저 구글 로그인을 합니다.

 

1. API_KEY 생성

먼저 아래사이트에 들어가서 콘솔을 클릭합니다.

 

https://cloud.google.com/?hl=ko

클라우드 컴퓨팅 서비스 | Google Cloud

 

 

콘솔 버튼을 클릭하면 아래 화면이 나타날겁니다.  '새 프로젝트 생성'이라는 버튼을 찾아서 클릭합니다.

 

 

아래화면에서 프로젝트이름을 입력하고 만들기를 클릭합니다.

프로젝트가 생성되었다면 다시 클라우드의 콘솔을 클릭하면 아래와 같은 화면이 나타납니다.

 

좌측 위의 API 및 서비스를 클릭합니다.

API 라이브러리로 이동하여 spread를 검색합니다.

 

 

검색하면 나타나는 스프레드시트 아이콘을 클릭하면 아래와 같은 화면이 나타납니다.

 

사용 버튼을 클릭합니다.

 

다시 API 및 서비스에 접속하여 사용자인증정보 → 사용자 인증 정보 만들기 → 서비스계정을 클릭합니다.

 

아래 순서대로 진행하여 계정을 생성합니다.

역할은 쓰기도 할 것이기 때문에 편집자가 좋습니다.

 

 

 

아래처럼 계정이 생성되며 생성된 계정을 클릭합니다.

상부의 키를 클릭 → 키추가 → 새 키 만들기를 클릭합니다.

 

저는 json 형식으로 만들었습니다.

 

만들기 버튼을 클릭하면 json형식의 파일이 다운로드됩니다. 이 파일을 사용하여 API를 실행하기 때문에 잃어버리면 안됩니다.

 

2. 스프레드 시트 권한 얻기

위의 방법을 통해서 key를 만들어도 바로 읽기, 쓰기를 할 수는 없습니다. 먼저 스프레드 시트를 읽고, 수정할 수 있는 권한을 얻어야 합니다.

 

먼저 위의 서비스 계정에서 이메일을 복사합니다.

 

마우스를 가져가면 복사 아이콘이 나타납니다.

 

아래화면에 복사된 이메일을 붙여넣습니다.

 

 

권한은 편집자로 부여합니다. 이메일 알림보내기는 체크해도되고 해제해도 됩니다.

체크되어 있을 때

 

체크해제 되어 있을 때

 

전송 또는 공유를 클릭하면 API를 사용할 수 있는 권한을 얻게 됩니다.

 

3. 기본설정

먼저 아래 명령어를 터미널에 입력하여 gspread 모듈을 설치합니다.

pip install gspread

기본적으로 아래 코드를 사용하여 시트를 호출합니다.

json_key ="다운받은json파일.json"
gc = gspread.service_account(json_key)
spreadsheet_url = "구글스프레드 시트 url"
doc = gc.open_by_url(spreadsheet_url)

 

4. 시트 생성, 선택, 삭제

1) 시트 추가 생성

# 워크시트 생성
worksheet = doc.add_worksheet(title="두번째 시트",rows=50,cols=40)

 

2) 시트 선택 및 삭제

# 워크 시트 선택 및 삭제
worksheet = doc.worksheet("두번째 시트") # 시트명으로 선택
# worksheet = doc.get_worksheet(1) # 인덱스로 선택
doc.del_worksheet(worksheet) #시트 선택을 하지않으면 삭제불가

 

5. 읽기

1) 단일값 읽기

worksheet = doc.worksheet("테스트")

cell_value = worksheet.cell(2,3).value # C2 셀 값
print(cell_value) #출력:3-1 데이터

cell_value = worksheet.acell("B2").value
print(cell_value) #출력:2-1 데이터

cell_value = worksheet.get("B2")
print(cell_value) # 출력: [['2-1 데이터']] -> 2차원 배열로 출력되기 때문에 범위 읽기도 가능

2) 범위값 읽기

worksheet = doc.worksheet("테스트")

row_value_list = worksheet.row_values(1) # 첫번째 행값을 모두 출력
print(row_value_list) #출력: ['1번째 열', '2번째 열', '3번째 열', '4번째 열']

column_value_list = worksheet.col_values(1) # 첫번째 열값을 모두 출력
print(column_value_list) #출력: ['1번째 열', '1-1 데이터', '1-2 데이터']

all_values = worksheet.get_all_values() #모든 값 출력
print(all_values) # 출력: [['1번째 열', '2번째 열', '3번째 열', '4번째 열'], ['1-1 데이터', '2-1 데이터', '3-1 데이터', ''], ['1-2 데이터', '2-2데이터', '3-2 데이터', '']]
all_values = worksheet.get_all_values()[1:] #2행부터 가져오고 싶을때
print(all_values) # 출력: [['1-1 데이터', '2-1 데이터', '3-1 데이터', ''], ['1-2 데이터', '2-2 데이터', '2-2 데이터', '']]

ainfos = [row[0] for row in all_values] # 모든 값중에서 첫번째 열의 값만 담아서 리스트로 출력
print(ainfos) #출력: ['1-1 데이터', '1-2 데이터']

all_values = worksheet.get_all_records() #모든 값을 딕셔너리가 담긴 리스트의 형태로 출력, key값은 첫번째 열의 값이 됨
print(all_values) #출력: [{'1번째 열': '1-1 데이터', '2번째 열': '2-1 데이터', '3번째 열': '3-1 데이터', '4번째 열': ''}, {'1번째 열': '1-2 데이터', '2번째 열': '2-2데이터', '3번째 열': '3-2 데이터', '4번째 열': ''}]

from gspread.utils import GridRangeType

list_of_lists = worksheet.get(return_type=GridRangeType.ListOfLists) # 행별로 리스트에 담기도록 출력
print(list_of_lists) #출력: [['1번째 열', '2번째 열', '3번째 열', '4번째 열'], ['1-1 데이터', '2-1 데이터', '3-1 데이터'], ['1-2 데이터', '2-2데이터', '3-2 데이터']]

6. 쓰기(업데이트)

worksheet = doc.worksheet("테스트")

worksheet.update_cell(2,3,"update") # C2 업데이트
worksheet.update([[1111]], "B2") #B2 업데이트
worksheet.update([[222,"333"]],"B3:C3") # B3:C3 범위 업데이트
worksheet.update([['aaaa','bbbb'],['cccc','dddd']],"B4:C5") #B4:C5 범위 업데이트

worksheet.format("A1:D1",{ #색상은 RGB로 변경
    "backgroundColor": { # 배경색 변경
      "red": 0.0,
      "green": 0.0,
      "blue": 0.2
    },
    "horizontalAlignment": "CENTER", #글자 위치 중앙
    "textFormat": {
      "foregroundColor": { # 글자색 변경
        "red": 1.0,
        "green": 0.0,
        "blue": 0.0
      },
      "fontSize": 15, #폰트 크기 변경
      "bold": True # 굵기 설정
    }
})

worksheet.batch_update([{ # 다중 범위 쓰기
    'range':'A6:D7',
    'values':[['A66','B66','C66','D66'],['A77','B77','C77','D77']]
},{
    'range':'A13:D14',
    'values':[['A-','B-','C-','D-'],['A--','B--','C--','D--']]
}])

 

결과