moyo-crawling-requests / dev /google_sheet_utils.py
m8chaa's picture
request implsmnt
71bd1d3
Raw History Blame Contribute Delete
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}")
@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