upload-image-to-cloud / google_sheets_client.py
hyunjun1111's picture
service account ๋ฐฉ์‹์œผ๋กœ ์ „ํ™˜
ef6e3b7
Raw History Blame Contribute Delete
7.28 kB
"""
Google Sheets API ํด๋ผ์ด์–ธํŠธ - ์„œ๋น„์Šค ๊ณ„์ • ์ธ์ฆ ๋ฒ„์ „
"""
import json
import os
from typing import Dict, List, Optional, Tuple
from google.oauth2 import service_account
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
class GoogleSheetsClient:
"""Google Sheets API ํด๋ผ์ด์–ธํŠธ (์„œ๋น„์Šค ๊ณ„์ • ๋ฒ„์ „)"""
def __init__(self, service_account_info: Dict, spreadsheet_id: str):
"""
Args:
service_account_info: ์„œ๋น„์Šค ๊ณ„์ • ์ •๋ณด ๋”•์…”๋„ˆ๋ฆฌ
spreadsheet_id: Google Sheets ์Šคํ”„๋ ˆ๋“œ์‹œํŠธ ID
"""
self.spreadsheet_id = spreadsheet_id
self.service = None
# ์„œ๋น„์Šค ๊ณ„์ • ์ •๋ณด๋กœ credentials ์ƒ์„ฑ
self.credentials = service_account.Credentials.from_service_account_info(
service_account_info,
scopes=['https://www.googleapis.com/auth/spreadsheets']
)
# Google Sheets API ์„œ๋น„์Šค ๊ฐ์ฒด ์ƒ์„ฑ
self._build_service()
def _build_service(self):
"""Google Sheets API ์„œ๋น„์Šค ๊ฐ์ฒด ๋นŒ๋“œ"""
try:
self.service = build('sheets', 'v4', credentials=self.credentials)
except Exception as e:
raise Exception(f"Google Sheets ์„œ๋น„์Šค ์ดˆ๊ธฐํ™” ์‹คํŒจ: {str(e)}")
def get_sheet_data(self, sheet_name: str, range_notation: str = None) -> Tuple[bool, any]:
"""์‹œํŠธ ๋ฐ์ดํ„ฐ ์กฐํšŒ"""
try:
if range_notation:
range_param = f"{sheet_name}!{range_notation}"
else:
range_param = sheet_name
result = self.service.spreadsheets().values().get(
spreadsheetId=self.spreadsheet_id,
range=range_param
).execute()
values = result.get('values', [])
return True, values
except HttpError as e:
return False, f"๋ฐ์ดํ„ฐ ์กฐํšŒ ์‹คํŒจ: {e.error_details}"
except Exception as e:
return False, f"๋ฐ์ดํ„ฐ ์กฐํšŒ ์ค‘ ์˜ค๋ฅ˜: {str(e)}"
def append_row(self, sheet_name: str, values: List[str]) -> Tuple[bool, str]:
"""์‹œํŠธ์— ์ƒˆ ํ–‰ ์ถ”๊ฐ€"""
try:
body = {
'values': [values],
'majorDimension': 'ROWS'
}
result = self.service.spreadsheets().values().append(
spreadsheetId=self.spreadsheet_id,
range=f"{sheet_name}",
valueInputOption='USER_ENTERED',
insertDataOption='INSERT_ROWS',
body=body
).execute()
updated_cells = result.get('updates', {}).get('updatedCells', 0)
return True, f"์„ฑ๊ณต์ ์œผ๋กœ {updated_cells}๊ฐœ ์…€์ด ์ถ”๊ฐ€๋˜์—ˆ์Šต๋‹ˆ๋‹ค"
except HttpError as e:
return False, f"ํ–‰ ์ถ”๊ฐ€ ์‹คํŒจ: {e.error_details}"
except Exception as e:
return False, f"ํ–‰ ์ถ”๊ฐ€ ์ค‘ ์˜ค๋ฅ˜: {str(e)}"
def get_last_row_number(self, sheet_name: str) -> Tuple[bool, int]:
"""์‹œํŠธ์˜ ๋งˆ์ง€๋ง‰ ํ–‰ ๋ฒˆํ˜ธ ์กฐํšŒ"""
try:
success, data = self.get_sheet_data(sheet_name)
if success:
return True, len(data)
else:
return False, 0
except Exception as e:
return False, 0
def add_image_url(self, sheet_name: str, image_url: str) -> Tuple[bool, str]:
"""์ด๋ฏธ์ง€ URL์„ ์‹œํŠธ์— ์ถ”๊ฐ€"""
try:
# ๋งˆ์ง€๋ง‰ ํ–‰ ๋ฒˆํ˜ธ ํ™•์ธ
success, last_row = self.get_last_row_number(sheet_name)
# ์ƒˆ ํ–‰์— ์ด๋ฏธ์ง€ URL ์ถ”๊ฐ€
success, message = self.append_row(sheet_name, [image_url])
if success:
new_row_number = last_row + 1
return True, f"ํ–‰ {new_row_number}์— ์ด๋ฏธ์ง€ URL์ด ์ถ”๊ฐ€๋˜์—ˆ์Šต๋‹ˆ๋‹ค: {image_url}"
else:
return False, message
except Exception as e:
return False, f"์ด๋ฏธ์ง€ URL ์ถ”๊ฐ€ ์ค‘ ์˜ค๋ฅ˜: {str(e)}"
def create_sheets_client_from_service_account(service_account_info: Dict, spreadsheet_id: str) -> GoogleSheetsClient:
"""์„œ๋น„์Šค ๊ณ„์ • ์ •๋ณด๋กœ Google Sheets ํด๋ผ์ด์–ธํŠธ ์ƒ์„ฑ"""
return GoogleSheetsClient(service_account_info, spreadsheet_id)
def create_sheets_client_from_env(spreadsheet_id: str) -> Optional[GoogleSheetsClient]:
"""ํ™˜๊ฒฝ ๋ณ€์ˆ˜์—์„œ ์„œ๋น„์Šค ๊ณ„์ • ์ •๋ณด๋ฅผ ์ฝ์–ด ํด๋ผ์ด์–ธํŠธ ์ƒ์„ฑ"""
# ํ™˜๊ฒฝ ๋ณ€์ˆ˜์—์„œ ์„œ๋น„์Šค ๊ณ„์ • ์ •๋ณด ์ฝ๊ธฐ
service_account_json = os.getenv('GOOGLE_SERVICE_ACCOUNT_JSON')
if service_account_json:
try:
service_account_info = json.loads(service_account_json)
return GoogleSheetsClient(service_account_info, spreadsheet_id)
except json.JSONDecodeError:
raise Exception("GOOGLE_SERVICE_ACCOUNT_JSON ํ™˜๊ฒฝ ๋ณ€์ˆ˜๊ฐ€ ์˜ฌ๋ฐ”๋ฅธ JSON ํ˜•์‹์ด ์•„๋‹™๋‹ˆ๋‹ค")
# ๋˜๋Š” ํŒŒ์ผ์—์„œ ์ฝ๊ธฐ
service_account_file = os.getenv('GOOGLE_SERVICE_ACCOUNT_FILE', 'service_account.json')
if os.path.exists(service_account_file):
with open(service_account_file, 'r') as f:
service_account_info = json.load(f)
return GoogleSheetsClient(service_account_info, spreadsheet_id)
return None
# ๊ธฐ๋ณธ ์„œ๋น„์Šค ๊ณ„์ • ์ •๋ณด (์‹ค์ œ Private Key๊ฐ€ ํ•„์š”ํ•จ)
DEFAULT_SERVICE_ACCOUNT_INFO = {
"type": "service_account",
"project_id": "marketing-automation-463304",
"private_key_id": "dc8e23fa63789d7af4e5f3e947b6ef8dcaee0d67",
"private_key": "", # ์‹ค์ œ Private Key ํ•„์š”
"client_email": "img-upload@marketing-automation-463304.iam.gserviceaccount.com",
"client_id": "115745495358423216505",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": f"https://www.googleapis.com/robot/v1/metadata/x509/img-upload%40marketing-automation-463304.iam.gserviceaccount.com"
}
# ํ…Œ์ŠคํŠธ ์ฝ”๋“œ
if __name__ == "__main__":
# ํ…Œ์ŠคํŠธ ์„ค์ •
SPREADSHEET_ID = "1tB5rYhBGdReeSg3t-7oFXoq2h2WbejNfzCxiL1cQUxY"
# ์„œ๋น„์Šค ๊ณ„์ • ์ •๋ณด (์‹ค์ œ Private Key ํ•„์š”)
service_account_info = DEFAULT_SERVICE_ACCOUNT_INFO.copy()
service_account_info["private_key"] = "YOUR_PRIVATE_KEY_HERE" # ์‹ค์ œ ํ‚ค๋กœ ๊ต์ฒด ํ•„์š”
try:
# ํด๋ผ์ด์–ธํŠธ ์ƒ์„ฑ
client = create_sheets_client_from_service_account(service_account_info, SPREADSHEET_ID)
print("โœ… Google Sheets ํด๋ผ์ด์–ธํŠธ ์ƒ์„ฑ ์„ฑ๊ณต")
# ํ…Œ์ŠคํŠธ ์ด๋ฏธ์ง€ URL ์ถ”๊ฐ€
test_url = "https://example.com/test-image.jpg"
success, message = client.add_image_url("Storyboard", test_url)
if success:
print(f"โœ… ์ด๋ฏธ์ง€ URL ์ถ”๊ฐ€ ์„ฑ๊ณต: {message}")
else:
print(f"โŒ ์ด๋ฏธ์ง€ URL ์ถ”๊ฐ€ ์‹คํŒจ: {message}")
except Exception as e:
print(f"โŒ ์˜ค๋ฅ˜ ๋ฐœ์ƒ: {str(e)}")