Python存取google sheet

安裝套件

pip install gspread oauth2client

申請API

到 https://console.developers.google.com/project建立project

啟動Drive API

建立憑證

在憑證-> 建立憑證 -> 服務帳戶 (或”請幫我選擇”)

“幫我選擇”例範

Q: 您目前使用哪個 API？ 

A: Google Dirve API

Q: API 的呼叫來源為何？

A: 網路伺服器 (例如 node.js、Tomcat)

Q: 您需要存取什麼資料？

A: 應用程式資料

Q: 您準備搭配 App Engine 或 Compute Engine 使用此 API 嗎？

A: 否，我並未使用任何一項服務

下載並保存好憑證檔

Python code

import gspread
import traceback
import logging
import os
from oauth2client.service_account import ServiceAccountCredentials

class GSR(object):
   
    def __init__(self, key_file_name):
        scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
        try:
            creds = ServiceAccountCredentials.from_json_keyfile_name(os.path.join(os.path.dirname(__file__),key_file_name), scope)
        except:
            msg = 'Fail to authenticate, please check the credential'
            logging.error(msg + ' {}'.format(traceback.format_exc()))
            raise Exception(msg)
        self.client = gspread.authorize(creds)
        
    def get_all_sheet_content(self, sheet_name, worksheet_name):
        try:
            worksheet = self.client.open(sheet_name).worksheet(worksheet_name)
            return worksheet.get_all_records()
        except:
            print traceback.format_exc()
            return None
        
    def get_sheet_by_url(self, link):
        try:
            return self.client.open_by_url(link)
        except:
            msg = 'Fail to get spread sheet for url: {} {}'.format(link, traceback.format_exc())
            logging.error(msg)
            raise Exception(msg)
            return None