app.py

import gradio as gr
import pandas as pd
import time
import gspread
from google.oauth2.service_account import Credentials
from datetime import datetime, timedelta
import pytz

# Global variable to control process interruption
stop_flag = False

# Authenticate using the service account credentials
def authenticate_google_sheets():
    print("Authenticating with Google Sheets...")
    scope = [
        "https://www.googleapis.com/auth/spreadsheets",
        "https://www.googleapis.com/auth/drive"
    ]
    creds = Credentials.from_service_account_file('credentials.json', scopes=scope)
    client = gspread.authorize(creds)
    print("Authentication successful.")
    return client

# Main function to upload Excel data to Google Sheets
def upload_to_google_sheets(excel_file, spreadsheet_id, delay):
    global stop_flag
    stop_flag = False  # Reset stop flag when process starts
    logs = []  # To store logs for each row
    
    try:
        client = authenticate_google_sheets()
        sheet = client.open_by_key(spreadsheet_id).sheet1
        logs.append(f"Connected to Google Sheet: {spreadsheet_id}")
    except Exception as e:
        logs.append(f"Error connecting to Google Sheets: {e}")
        return "\n".join(logs)

    try:
        df = pd.read_excel(excel_file.name)
        logs.append("Excel file read successfully.")
    except Exception as e:
        logs.append(f"Error reading the Excel file: {e}")
        return "\n".join(logs)

    #df = df.replace([float('inf'), float('-inf')], None).where(pd.notnull(df), None)
    
    for index, row in df.iterrows():
        if stop_flag:
            logs.append("Process interrupted by the user.")
            return "\n".join(logs)
        try:
            row_data = row.tolist()
            sheet.append_row(row_data)
            logs.append(f"Row {index} uploaded successfully: {row_data}")
        except Exception as e:
            logs.append(f"Error processing row {index}: {row_data}, Error: {e}")
        time.sleep(delay)
    
    logs.append("All rows processed.")
    return "\n".join(logs)

# Stop the process
def stop_process():
    global stop_flag
    stop_flag = True
    return "Process will stop after the current row."

# Schedule upload with delay and target time
def schedule_upload(excel_file, spreadsheet_id, delay, target_time):
    pacific = pytz.timezone("America/Los_Angeles")
    current_time = datetime.now(pacific)
    target_time = pacific.localize(datetime.strptime(target_time, '%Y-%m-%d %H:%M:%S'))
    time_difference = (target_time - current_time).total_seconds()

    if time_difference > 0:
        time.sleep(time_difference)
    
    return upload_to_google_sheets(excel_file, spreadsheet_id, delay)

# Gradio Interface
def gradio_interface(excel_file, spreadsheet_id, delay, target_time):
    return schedule_upload(excel_file, spreadsheet_id, int(delay), target_time)

# Gradio app
with gr.Blocks() as demo:
    excel_file_input = gr.File(label="Upload Excel File")
    spreadsheet_id_input = gr.Textbox(label="Google Spreadsheet ID")
    delay_input = gr.Number(label="Delay between rows (in seconds)", value=300)
    target_time_input = gr.Textbox(label="Start time (Pacific Time, format: YYYY-MM-DD HH:MM:SS)", placeholder="2024-09-25 14:30:00")
    result_output = gr.Textbox(label="Result")
    upload_button = gr.Button("Schedule Upload")
    stop_button = gr.Button("Stop Process")
    stop_status_output = gr.Textbox(label="Status")

    upload_button.click(fn=gradio_interface, 
                        inputs=[excel_file_input, spreadsheet_id_input, delay_input, target_time_input], 
                        outputs=result_output)
    stop_button.click(fn=stop_process, inputs=[], outputs=stop_status_output)

demo.launch()