from .Google import Create_Service import logging from ratelimit import limits, sleep_and_retry import time import pytz from datetime import datetime import streamlit as st import re import psutil def google_sheet_connect(): CLIENT_SECRETS = st.secrets["GoogleDriveAPISecrets"] API_NAME = 'sheets' API_VERSION = 'v4' SCOPES = ['https://www.googleapis.com/auth/drive'] return Create_Service(CLIENT_SECRETS, API_NAME, API_VERSION, SCOPES) def get_sheet_id(spreadsheet_id, sheet_title, service_instance): try: result = service_instance.spreadsheets().get(spreadsheetId=spreadsheet_id).execute() sheets = result.get('sheets', []) for sheet in sheets: if sheet.get('properties', {}).get('title') == sheet_title: sheet_id = sheet.get('properties', {}).get('sheetId') return sheet_id logging.info(f"Sheet '{sheet_title}' not found.") return None except Exception as e: logging.error(f"Failed to get sheet ID for '{sheet_title}': {e}") return None def sort_sheet_by_column(sheet_id, sheet_id_to_sort, primary_column_index=0, secondary_column_index=0, service_instance=None): try: request_body = { "requests": [ { "sortRange": { "range": { "sheetId": sheet_id_to_sort, "startRowIndex": 1, # Exclude header row }, "sortSpecs": [ { "dimensionIndex": primary_column_index, "sortOrder": "ASCENDING" # Primary sort by AE column }, { "dimensionIndex": secondary_column_index, "sortOrder": "DESCENDING" # Secondary sort by AC column } ] } } ] } logging.info(f"Sorting sheet ID: '{sheet_id_to_sort}' by columns {primary_column_index} and {secondary_column_index}") google_sheets_api_call( service_instance.spreadsheets().batchUpdate, spreadsheetId=sheet_id, body=request_body ).execute() logging.info("Sheet sorted successfully") except Exception as e: logging.error(f"Failed to sort sheet: {e}") @sleep_and_retry @limits(calls=60, period=60) def google_sheets_api_call(func, *args, **kwargs): max_retries = 5 for attempt in range(max_retries): try: return func(*args, **kwargs) except Exception as e: if attempt < max_retries - 1: time.sleep(2 ** attempt) else: raise e first_push_time = None last_push_time = None def push_to_sheet(data, spreadsheet_id, range='Sheet3!A', service_instance=None): global first_push_time, last_push_time current_time = time.time() if first_push_time is None: first_push_time = current_time if last_push_time is not None: time_lapse = current_time - last_push_time logging.info(f"Time lapse since last push: {time_lapse} seconds") total_time = current_time - first_push_time logging.info(f"Total time since first push: {total_time} seconds") last_push_time = current_time # Track CPU, memory, network resources logging.info(f"CPU Usage: {psutil.cpu_percent()}%") logging.info(f"Memory Usage: {psutil.virtual_memory().percent}%") network_usage = psutil.net_io_counters() bytes_sent = network_usage.bytes_sent bytes_received = network_usage.bytes_recv mbps_sent = bytes_sent / (1024 * 1024) mbps_received = bytes_received / (1024 * 1024) logging.info(f"Network Usage: {mbps_sent} Mbps sent, {mbps_received} Mbps received") try: service_instance = service_instance if service_instance else google_sheet_connect() body = {'values': data} if range == 'CrawlingControl!B2': logging.info(f"Updating fetch status in Google Sheets at range: '{range}'") result = google_sheets_api_call( service_instance.spreadsheets().values().update, spreadsheetId=spreadsheet_id, range=range, valueInputOption='USER_ENTERED', body=body ).execute() else: logging.info(f"Appending data to Google Sheets at range: '{range}'") result = google_sheets_api_call( service_instance.spreadsheets().values().append, spreadsheetId=spreadsheet_id, range=range, valueInputOption='USER_ENTERED', body=body ).execute() logging.info("Data appended successfully") return result, service_instance except Exception as e: logging.error(f"Failed to push data to sheet at range '{range}': {e}") raise def price_conversion(spreadsheet_id, service_instance=None): if not service_instance: service_instance = google_sheet_connect() try: logging.info("Starting price conversion: Fetching data from Google Sheets") # Fetch data from specified ranges batch_get_response = google_sheets_api_call( service_instance.spreadsheets().values().batchGet, spreadsheetId=spreadsheet_id, ranges=["CrawlingControl!E3:J", "Sheet3!U2:U", "Sheet3!D2:D"] ).execute() logging.info("Data fetched successfully: Processing data") event_infos = batch_get_response['valueRanges'][0].get('values', []) links_data = batch_get_response['valueRanges'][1].get('values', []) prices_data = batch_get_response['valueRanges'][2].get('values', []) event_links_to_check = [] price_deductions = {} # Process event information to extract deductions for row in event_infos: link = row[0] event_links_to_check.append(link) deductions = [] for i in range(3, 6): if i < len(row) and row[i]: try: # Check if "원" and "/" are in the string to split correctly if "원" in row[i] and "/" in row[i]: value_part, period_part = row[i].split("원")[0].replace(",", "").strip(), row[i].split("/")[1].strip() # Extract value value_str = value_part value = int(re.sub(r'[^\d]', '', value_str)) # Extract period period_str = period_part.split(" ")[0] period = int(re.sub(r'[^\d]', '', period_str)) if re.search(r'\d', period_str) else 0 else: raise ValueError("Expected '원' or '/' not found in the string") deductions.append([value, period]) except (ValueError, IndexError) as e: logging.error(f"Error parsing deduction value or period from row {row}: {e}") deductions.append([0, 0]) else: deductions.append([0, 0]) price_deductions[link] = deductions new_prices = [] new_prices_numeric = [] if len(links_data) < len(prices_data): links_data.extend([[]] * (len(prices_data) - len(links_data))) # Process each row to calculate the final price after deductions for idx, (link_row, price_row) in enumerate(zip(links_data, prices_data)): if len(price_row) == 0: logging.info(f"Skipping row {idx + 1} as it has no price data.") new_prices.append([""]) new_prices_numeric.append([""]) continue try: base_price = int(price_row[0].replace(",", "").replace("원", "")) except (IndexError, ValueError) as e: logging.error(f"Error parsing base price from row {price_row}: {e}") new_prices.append([""]) new_prices_numeric.append([""]) continue discounts = 0 processed_links = set() for link_group in link_row: for link in link_group.split('\n'): if link in event_links_to_check and link not in processed_links: processed_links.add(link) for deduction, period in price_deductions.get(link, []): if period == 0: discounts += deduction else: monthly_deduction = deduction // period discounts += monthly_deduction logging.info(f"Link {link} contributes {deduction} deduction over {period} period, monthly deduction: {monthly_deduction}, total discounts now: {discounts}") final_price = base_price - discounts formatted_final_price = "{:,}원".format(final_price) new_prices.append([formatted_final_price]) new_prices_numeric.append([final_price]) end_row = len(new_prices) + 1 update_data = { "valueInputOption": "RAW", "data": [ {"range": f"Sheet3!AD2:AD{end_row}", "values": new_prices}, {"range": f"Sheet3!AE2:AE{end_row}", "values": new_prices_numeric} ] } logging.info("Updating Google Sheets with new prices") google_sheets_api_call( service_instance.spreadsheets().values().batchUpdate, spreadsheetId=spreadsheet_id, body=update_data ).execute() sheet3_id = get_sheet_id(spreadsheet_id=spreadsheet_id, sheet_title="Sheet3", service_instance=service_instance) logging.info("Sorting the sheet by columns") sort_sheet_by_column(spreadsheet_id, sheet3_id, primary_column_index=30, secondary_column_index=28, service_instance=service_instance) logging.info("Backing up and refreshing the sheet") backup_and_refresh(sheet_id=spreadsheet_id, sheet_name="Sheet3", start_row=2, service_instance=service_instance, end_of_fetch=True, backup_only=True) logging.info("Price conversion completed successfully") except Exception as e: logging.error(f"An error occurred during price conversion: {e}") def assort_event_links(spreadsheet_id, service_instance=None): if not service_instance: service_instance = google_sheet_connect() try: logging.info("Starting to assort event links: Fetching data from Google Sheets") batch_get_response = google_sheets_api_call( service_instance.spreadsheets().values().get, spreadsheetId=spreadsheet_id, range="Sheet3!U2:U", ).execute() event_links = batch_get_response.get('values', []) batch_get_response = google_sheets_api_call( service_instance.spreadsheets().values().get, spreadsheetId=spreadsheet_id, range="CrawlingControl!E3:E", ).execute() crawling_control = batch_get_response.get('values', []) event_links = [link for sublist in event_links for item in sublist for link in item.split('\n')] crawling_control = [link for sublist in crawling_control for link in sublist] event_links = list(filter(None, dict.fromkeys([x for x in event_links if x]))) if crawling_control: new_event_links = list(set(event_links) - set(crawling_control)) deleted_event_links = list(set(crawling_control) - set(event_links)) else: event_links = list(set(event_links)) spreadsheet = google_sheets_api_call( service_instance.spreadsheets().get, spreadsheetId=spreadsheet_id ).execute() sheet = [s for s in spreadsheet['sheets'] if s['properties']['title'] == 'CrawlingControl'][0] sheet_id = sheet['properties']['sheetId'] requests = [] for link in deleted_event_links: row = None if link in crawling_control: row = crawling_control.index(link) + 3 else: logging.info(f"Link {link} not found in crawling_control") if row is not None: requests.append({ "updateCells": { "range": { "sheetId": sheet_id, "startRowIndex": row, "endRowIndex": row + 1, "startColumnIndex": 4, "endColumnIndex": 5 }, "rows": [ { "values": [ { "userEnteredFormat": { "textFormat": { "strikethrough": True } } } ] } ], "fields": "userEnteredFormat.textFormat.strikethrough" } }) if requests: logging.info("Updating Google Sheets to strike through deleted event links") google_sheets_api_call( service_instance.spreadsheets().batchUpdate, spreadsheetId=spreadsheet_id, body={ "requests": requests } ).execute() values = [[link] for link in new_event_links] if values: logging.info("Appending new event links to Google Sheets") google_sheets_api_call( service_instance.spreadsheets().values().append, spreadsheetId=spreadsheet_id, range="CrawlingControl!E3:E", valueInputOption='RAW', insertDataOption='INSERT_ROWS', body={ 'values': values } ).execute() logging.info("Event links assorted successfully") except Exception as e: logging.error(f"Failed to assort event links: {e}") def backup_and_refresh(sheet_id, sheet_name='Sheet3', start_row=2, service_instance=None, end_of_fetch=False, backup_only=False): if not service_instance: service_instance = google_sheet_connect() try: logging.info(f"Starting backup and refresh for {sheet_name}: Fetching sheet metadata") batch_get_response = google_sheets_api_call( service_instance.spreadsheets().get, spreadsheetId=sheet_id, includeGridData=True ).execute() sheets = batch_get_response.get('sheets', '') sheet_id_to_copy = None sheet_id_to_delete = None crawling_control_sheet_id = None crawling_control_value = None for sheet in sheets: if sheet.get("properties", {}).get("title", "") == sheet_name: sheet_id_to_copy = sheet.get("properties", {}).get("sheetId", "") elif sheet.get("properties", {}).get("title", "") == "planDataSheet": sheet_id_to_delete = sheet.get("properties", {}).get("sheetId", "") elif sheet.get("properties", {}).get("title", "") == "CrawlingControl": grid_data = sheet.get("data", [{}])[0].get("rowData", [{}]) if len(grid_data) > 1 and len(grid_data[1].get("values", [{}])) > 1: crawling_control_value = grid_data[1]["values"][1].get("userEnteredValue", {}).get("stringValue", "") crawling_control_sheet_id = sheet.get("properties", {}).get("sheetId", "") if sheet_id_to_copy and sheet_id_to_delete and crawling_control_value: break fetch_status = crawling_control_value if crawling_control_value else None fetch_completed = False if fetch_status == "Fetch data ended successfully": fetch_completed = True if sheet_id_to_copy is not None and fetch_completed and end_of_fetch: try: logging.info("Clearing planDataSheet") google_sheets_api_call( service_instance.spreadsheets().values().clear, spreadsheetId=sheet_id, range="planDataSheet" ).execute() logging.info("Copying data from Sheet3 to planDataSheet") response = google_sheets_api_call( service_instance.spreadsheets().values().get, spreadsheetId=sheet_id, range="Sheet3" ).execute() data_to_copy = response.get('values', []) google_sheets_api_call( service_instance.spreadsheets().values().update, spreadsheetId=sheet_id, range="planDataSheet", valueInputOption="USER_ENTERED", body={ "values": data_to_copy } ).execute() except Exception as e: logging.error(f"An error occurred during backup: {e}") kst = pytz.timezone('Asia/Seoul') current_datetime = datetime.now(kst).strftime("%Y/%m/%d - %H:%M:%S") backup_file_name = f"Backup 모요 요금제 {current_datetime}" new_spreadsheet = google_sheets_api_call( service_instance.spreadsheets().create, body={'properties': {'title': backup_file_name}}, fields='spreadsheetId' ).execute() new_spreadsheet_id = new_spreadsheet.get('spreadsheetId') if sheet_id_to_copy is not None: try: logging.info("Copying sheet to new spreadsheet for backup") google_sheets_api_call( service_instance.spreadsheets().sheets().copyTo, spreadsheetId=sheet_id, sheetId=sheet_id_to_copy, body={'destinationSpreadsheetId': new_spreadsheet_id} ).execute() except Exception as e: logging.error(f"Failed to copy sheet for backup: {e}") else: logging.info(f"Sheet '{sheet_name}' not found in the original spreadsheet.") if not end_of_fetch and not backup_only: sheet_metadata = google_sheets_api_call( service_instance.spreadsheets().get, spreadsheetId=sheet_id ).execute() sheets = sheet_metadata.get('sheets', '') sheet_properties = None for sheet in sheets: if sheet.get("properties", {}).get("title", "") == sheet_name: sheet_properties = sheet.get("properties", {}) break if sheet_properties is not None: sheet_grid_properties = sheet_properties.get("gridProperties", {}) sheet_row_count = sheet_grid_properties.get("rowCount", 0) sheet_column_count = sheet_grid_properties.get("columnCount", 0) def column_number_to_letter(n): string = "" while n > 0: n, remainder = divmod(n - 1, 26) string = chr(65 + remainder) + string return string last_column_letter = column_number_to_letter(sheet_column_count) range = f'{sheet_name}!A2:{last_column_letter}{sheet_row_count}' logging.info(f"Clearing data in {sheet_name}") google_sheets_api_call( service_instance.spreadsheets().values().clear, spreadsheetId=sheet_id, range=range, body={} ).execute() range = f'CrawlingControl!B2' body = {'values': [["Fetch data started"]]} logging.info("Updating fetch status in CrawlingControl sheet") google_sheets_api_call( service_instance.spreadsheets().values().update, spreadsheetId=sheet_id, range=range, valueInputOption='USER_ENTERED', body=body ).execute() else: logging.info(f"Sheet '{sheet_name}' not found in the spreadsheet.") logging.info("Backup and refresh completed successfully") except Exception as e: logging.error(f"Failed to backup and refresh sheet: {e}") def auto_resize_columns(spreadsheet_id, sheet_index=2, service_instance=None): try: logging.info(f"Connecting to Google Sheets with spreadsheet ID: {spreadsheet_id}") service_instance = service_instance if service_instance else google_sheet_connect() logging.info(f"Fetching sheet metadata for spreadsheet ID: {spreadsheet_id}") sheet_metadata = google_sheets_api_call( service_instance.spreadsheets().get, spreadsheetId=spreadsheet_id ).execute() # Select the specific sheet using the sheet_index sheet = sheet_metadata.get('sheets', '')[sheet_index] logging.info(f"Sheet selected: {sheet}") # Extract the sheetId of the selected sheet sheet_id = sheet.get('properties', {}).get('sheetId', 0) logging.info(f"Extracted sheet ID: {sheet_id}") requests = [] # Prepare the auto resize request auto_resize_request = { "autoResizeDimensions": { "dimensions": { "sheetId": sheet_id, "dimension": "COLUMNS", "startIndex": 0, "endIndex": 1 } } } requests.append(auto_resize_request) # Prepare the sort request sort_request = { "sortRange": { "range": { "sheetId": sheet_id, "startRowIndex": 1, }, "sortSpecs": [{ "dimensionIndex": 0, "sortOrder": "ASCENDING" }] } } requests.append(sort_request) body = {"requests": requests} # Execute the batch update request using the spreadsheet ID google_sheets_api_call( service_instance.spreadsheets().batchUpdate, spreadsheetId=spreadsheet_id, # Use the spreadsheet ID here body=body ).execute() except Exception as e: logging.error(f"An error occurred in auto_resize_columns: {e}") raise