[Python] Google Spreadsheet

먼저, Google 개발자 센터에 어플리케이션을 등록할 것

 

<구글 스프레이시트 접속 코드>

    def __init__(self):
        scope = [
        'https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive',
        ]
        json_file_name = 'JSON-FILENAME'

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

 

<스프레드시트 열기>

    def OpenSpreadsheet(self, spreadName):
        gs1 = self.GoogleCredential.open(spreadName)
        #doc = self.GoogleCredential.open_by_url(spreadsheet_url)

        return gs1

* 기 생성된 스프레드시트가 있어야 함.

 

<스프레드시트 생성>

    def CreateSpreadsheet(self, spreadName):
        gs1 = self.GoogleCredential.create(spreadName)

        return gs1

 

<특정 시트에 데이터 쓰기(저장/기록하기)>

    def WriteSheet(self, spreadSheet, dataDF, sheetName=None):
        if sheetName:
            try:
                ws1 = spreadSheet.worksheet(sheetName)
            except:
                ws1 = spreadSheet.add_worksheet(title=sheetName, rows='1', cols='1')
        else:
            ws1 = spreadSheet.worksheet('시트1')
        set_with_dataframe(ws1, dataDF)

* DataFrame 형식의 데이터(dataDF)

* 'spreadSheet'는 문자열이 아닌 객체임.

 

<특정 시트에서 데이터 읽기(불러오기)>

    def ReadSheet(self, spreadSheet, sheetName = None):
        if sheetName:
            ws1 = spreadSheet.worksheet(sheetName)
        else:
            ws1 = spreadSheet.worksheet('Sheet1')

        values = ws1.get_all_values()
        dataDF = DataFrame(values, columns=values[0])
        dataDF = dataDF.reindex(dataDF.index.drop(0))

        return dataDF

* DataFrame 형식의 데이터를 반환함.