Download dev/google_sheet_utils.py from m8chaa/moyo-crawling-requests: direct link, hf CLI and curl.
- Browser
- Download file 23.3 kB
-
https://huggingface.co/spaces/m8chaa/moyo-crawling-requests/resolve/main/dev/google_sheet_utils.py
- Command line
-
hf download hf://spaces/m8chaa/moyo-crawling-requests/dev/google_sheet_utils.py
-
curl -L -o google_sheet_utils.py https://huggingface.co/spaces/m8chaa/moyo-crawling-requests/resolve/main/dev/google_sheet_utils.py
23.3 kB
| 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}") | |
| 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 | |