Python存取google sheet (更新版)

啟用google sheet api

https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com

管理->OAuth 同意畫面->外部/內部(按須求)->建立

填寫資料後，在「範圍」按一下「新增或移除範圍」，並搜尋「Google Sheets API」。選擇了「/auth/spreadsheets」後按一下更新

在「機密範圍」見到 Google Sheest API 後，按一下「儲存並繼續」

按一下「返回資訊主頁」，如果程式設為外部，要按一下發佈應用程式

在左邊按一下憑證，上面「建立憑證」->「服務帳戶」->輸入服務帳戶名稱->「建立並繼續」->「完成」

點選剛建立的帳戶->「金鑰」

「新增金鑰」->「建立新的金鑰」，金鑰類型選JSON，按「建立」後自動下載json

把要存取的google sheet共用給json內的email accout

from google.oauth2.service_account import Credentials
import gspread
import traceback
import logging
import os

class GSR(object):
   
    def __init__(self, json_path):
        scopes = ['https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive"]
        try:
            creds = Credentials.from_service_account_file(json_path, scopes=scopes)
        except:
            msg = 'Fail to authenticate, please check the credential'
            logging.error(msg + ' {}'.format(traceback.format_exc()))
            raise Exception(msg)
        try:
            self.client = gspread.authorize(creds)
        except Exception as e:
            raise e
        
    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 Exception as e:
            raise e
            return None
    
    def get_col(self, sheet_name, worksheet_name, col_num):
        try:
            worksheet = self.client.open(sheet_name).worksheet(worksheet_name)
            return worksheet.col_values(col_num)
        except Exception as e:
            raise e
            return None    

    def append_row(self, sheet_name, worksheet_name, values):
        try:
            worksheet = self.client.open(sheet_name).worksheet(worksheet_name)
            return worksheet.append_row(values)
        except Exception as e:
            raise e
            
    def append_rows(self, sheet_name, worksheet_name, values):
        try:
            worksheet = self.client.open(sheet_name).worksheet(worksheet_name)
            return worksheet.append_rows(values)
        except Exception as e:
            raise e