파이썬으로 구글 스프레드시트 다운받고, Json으로 변경하기

 보통 게임 밸런스 데이터를 엑셀로 관리하는 경우가 많다. [미니난투 온라인]의 경우 구글 스프레드 시트에서 밸러스 데이터를 작성하고 관리한다. 새 밸런스 데이터로 적용하려고 할 때, 구글 스프레트 시트를  엑셀로 변경해서 다운받고, 이걸 다시 json으로 변경 후, 서버를 빌드한다. 

미니난투 온라인 - Google Play 앱

 

위에 [다운로드], [json으로 변경] 과정을 좀 더 편하게 하기 위해, 파이썬으로 프로그램을 만들게 됐다.

 

작업환경

- 맥북

- 파이썬 3

 

1. 파이썬 구글 드라이브 api 설정

 구글 드라이브 파이썬 라이브러리를 사용하기 위해서는, 해당 컴퓨터에 라이브러리를 설치해야 한다. 

https://github.com/googleapis/google-api-python-client

 위의 링크를 들어가보면, pip를 이용한 설치 방법이 자세히 나와 있다.

pip install virtualenv
virtualenv <your-env>
source <your-env>/bin/activate
<your-env>/bin/pip install google-api-python-client

 

2. 구글 프로젝트 만들기(권한 만들기)

 구글 드라이브에 접근하기 위해서는 권한이 있어야 한다. 

https://console.cloud.google.com/apis/

 위 링크에 들어가서, [사용자 인증 정보], [사용자 인증 정보 만들기]를 클릭한다. 나머지는 아래 이미지를 순서대로 실행하자.

1,2

3,4

 

위에 다운 받은 json파일을 client_secret.json으로 변경 후, 구글 스프레드 시트를 다운받고 변경 할 폴더로 옮겨준다.

 

3. 다운로드 받기

 우선 다운받는 파이썬 코드를 작성한다. 아래에 구글 스프레드 시트 값을 변경하는 부분이 있는데, 수정 할 필요가 없다면 무시해도 된다.

#!/usr/local/bin/python3.7
from __future__ import print_function
from googleapiclient.discovery import build
from httplib2 import Http
from oauth2client import file, client, tools
from apiclient.http import MediaIoBaseDownload
from apiclient import discovery
import io
import datetime
import httplib2

# If modifying these scopes, delete the file token.json.
# 다운 받을 권한 범위
SCOPES = ['https://www.googleapis.com/auth/drive',
          'https://www.googleapis.com/auth/spreadsheets']
SCOPES_SHEET = 'https://www.googleapis.com/auth/spreadsheets'

# SCOPES = 'https://www.googleapis.com/auth/drive.file'

def main():
    """Shows basic usage of the Drive v3 API.
    Prints the names and ids of the first 10 files the user has access to.
    """
    # The file token.json stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.



    #google drive service
    #구글 로그인 페이지 창일 뜰 것이다. 로그인 완료하면 token.json이라는 파일이 생긴다.
    store = file.Storage('token.json')
    creds = store.get()
    
    #아까 다운 받은 client_secret.json파일
    if not creds or creds.invalid:
        flow = client.flow_from_clientsecrets('client_secret.json', SCOPES)
        creds = tools.run_flow(flow, store)
    service = build('drive', 'v3', http=creds.authorize(Http()))




	#스프레트 시트를 접근/수정 할 수 있는 객체를 생성한다.
    #spread sheet service
    store_sheet = file.Storage('token.json')
    creds_sheet = store_sheet.get()
    service_sheet = build('sheets', 'v4', http=creds_sheet.authorize(Http()))








    # Call the Drive v3 API
    page_token = None
    while True:

        #오늘 변경 된 파일만 다운 받도록 설정했다.
        dt = datetime.datetime.utcnow()
        dt2 = datetime.datetime(dt.year, dt.month, dt.day-1 if dt.day-1 != 0 else 1 , 0, 0, 0)


        dt_str = "\'" + dt2.strftime("%Y-%m-%dT%H:00:00") + "\'"
        # print(u'{0}'.format(dt))
        # print(u'{0}'.format(dt_str))
        response = service.files().list(q="parents='{구글 드라이브 링크에 맨 마지막에 있는 키값}' "
                                          "and mimeType='application/vnd.google-apps.spreadsheet' "
                                          "and trashed=false "
                                          "and modifiedTime > "+dt_str).execute()




        #다운 받을 목록
        for item in response.get('files', []):
            # Process change
            print(u'{0} ({1})'.format(item['name'], item['id']))




            #이부분은 무시해도 된다.
            #스프레드 시트에 version이라는 탭이 있고,  거기서 id 값을 하나씩 올려준다.
            # read version
            read_result = service_sheet.spreadsheets().values().get(spreadsheetId=item['id'],
                                                              range='version!A2').execute()
            print(u'{0}'.format(read_result))
            version = int(read_result.get('values')[0][0])

            new_version = version + 1

            #이부분은 무시해도 된다.
            #version 탭에 A2위치 값을 변경해 준다.
            # write version
            values = [
                [
                    new_version
                ]
            ]
            body = {
                'values': values
            }
            write_result = service_sheet.spreadsheets().values().update(spreadsheetId=item['id'],
                                                                  range='version!A2',
                                                                  valueInputOption='USER_ENTERED',
                                                                  body=body).execute()



            #다운받는 코드
            #download files
            request = service.files().export_media(fileId=item['id'],
                                                   mimeType='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            write_file = open("./"+item['name']+".xlsx", 'wb')
            downloader = MediaIoBaseDownload(write_file, request)

            done = False
            while done is False:
                status, done = downloader.next_chunk()

            write_file.close()


        page_token = response.get('nextPageToken', None)
        if page_token is None:
            break

if __name__ == '__main__':
    main()

 위에 [구글 드라이브 링크에 맨 마지막 키 값]은 아래 이미지처럼 링크 맨 마지막 부분을 말한다.

 

 

4. 엑셀을 json으로 변경하기

 다운받은 엑셀 파일을 json으로 바꿔주는 코드이다. 문제는 정해진 양식대로 구글 스프레트 시트를 작정해야 한다. 

'test' 라는 시트 이름에 아래와 같이 데이터가 입력 돼 있다면,

id	name	etc1	etc2	etc3		etc4
1	abc	1	2	3		옆 column이 비어서 해당 안됨
2	def	4	5	6		옆 column이 비어서 해당 안됨
3	ghi	7	8	9		위 row가 비어서 해당 안됨

 

위의 양식 처럼, 맨 윗줄이 키 값이 되고, 그 다음 행 부터 값이 된다. 첫 row의 column이 비게 되면, 거기까지만 json으로 변경한다. 비슷하게 row가 비게 되면, 그 다음 row부터는 무시된다.

{
    "test": [
        {
            "id": 1.0,
            "name": "abc"
            "etc1": 1.0,
            "etc2": 2.0,
            "etc3": 3.0,
        },
        {
            "id": 2.0,
            "name": "def"
            "etc1": 4.0,
            "etc2": 5.0,
            "etc3": 6.0,
        }
    ]
}

 

 

 혹시 다른 양식으로 엑셀데이터를 관리한다면, 다른 json변경 프로그램을 쓰길 권한다. 아래는 json으로 만드는 코드이다.

 

#!/usr/local/bin/python3.7
# -*- coding: utf-8 -*-
import xlrd
import collections
import json
import os
import sys

def make_row(sheet, row, column_names):
    row_dic = collections.OrderedDict()

    for i in range(len(column_names)):
        row_dic[column_names[i]] = sheet.row_values(row)[i]

    return row_dic

def make_sheet(sheet):
    sheet_dic = collections.OrderedDict()
    sheet_list = []
    column_names = []
    row_num = sheet.nrows


    #시트 이름을 소문자로 변경
    for n in sheet.row_values(0):
        if not n:
            break
        else:
            column_names.append(n.replace(" ", "").lower())

    #빈 시트의 경우, 아무것도 없다면, 리턴
    if len(column_names) == 0:
        return None
        
    #첫 column이 비어있으면 그냥 리턴
    if not column_names[0]:
        return None


    for row in range(1, row_num):
        #row가 비어있다면 거기까 json으로 만든다
        if not sheet.row_values(row)[0]:
            break
        row_dic = make_row(sheet, row, column_names)
        sheet_list.append(row_dic)

    return sheet_list

if len(sys.argv) != 2:
    print("argument error! usage: ./excel2json xlsx_file")
    exit(1)

filename = sys.argv[1]
output_filename = filename.replace(".xlsx", ".json")

wb = xlrd.open_workbook(filename)
excel_dic = collections.OrderedDict()
sheet_names = wb.sheet_names()

for name in sheet_names:
    sheet = wb.sheet_by_name(name)
    result_sheet = make_sheet(sheet)
    if result_sheet is None:
        continue
    excel_dic[name] = result_sheet

if not os.path.exists("json"):
    os.mkdir("json")

f = open("json/" + output_filename, "w", encoding="utf-8")
f.write(json.dumps(excel_dic, indent = 4, ensure_ascii=False))
f.close()

 

 위의 엑셀을 json으로 바꾸는 과정을 잘 조절하면, 본인만의 양식으로 변경하는 프로그램으로 만들 수 있다.

 

5. 쉘 스크립트로 통합하기

 위 두 프로그램을 한번에 실행하기 위해 쉘 스크립트를 이용한다.

#!/bin/bash

if [ "$#" -eq 1 ]; then
	./downloader.py
fi

for file in ./*.xlsx
do
	echo "${file}"
	./excel2json.py "${file}"
done