Spaces:
Sleeping
Sleeping
File size: 3,773 Bytes
82fc0f3 5a68d82 82fc0f3 5a68d82 82fc0f3 5a68d82 82fc0f3 5a68d82 82fc0f3 052fa72 82fc0f3 052fa72 82fc0f3 052fa72 82fc0f3 052fa72 82fc0f3 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
import os
import json
import duckdb
from shiny import render, reactive, ui
from google.oauth2 import service_account
from googleapiclient.discovery import build
import requests
import base64
import xml.etree.ElementTree as ET
from dotenv import load_dotenv
import time
import pandas as pd
load_dotenv()
WALMART_CLIENT_ID = os.getenv('WALMART_CLIENT_ID')
WALMART_CLIENT_SECRET = os.getenv('WALMART_CLIENT_SECRET')
SERVICE_ACCOUNT_JSON = json.loads(os.getenv("SERVICE_ACCOUNT_JSON"))
GOOGLE_FOLDER_ID = os.getenv("GOOGLE_DRIVE_FOLDER_ID")
SCOPES = [
"https://www.googleapis.com/auth/drive.readonly",
"https://www.googleapis.com/auth/spreadsheets.readonly"
]
credentials = service_account.Credentials.from_service_account_info(SERVICE_ACCOUNT_JSON, scopes=SCOPES)
drive_service = build("drive", "v3", credentials=credentials)
sheets_service = build("sheets", "v4", credentials=credentials)
def list_sheets_in_folder(folder_id):
query = (
f"'{folder_id}' in parents and "
"mimeType = 'application/vnd.google-apps.spreadsheet' and trashed = false"
)
results = drive_service.files().list(q=query, fields="files(id, name)").execute()
return results.get("files", [])
def load_sheet_to_duckdb(spreadsheet_id, sheet_tab="Sheet1", table_name="active_sheet"):
values = (
sheets_service.spreadsheets()
.values()
.get(spreadsheetId=spreadsheet_id, range=sheet_tab)
.execute()
.get("values", [])
)
if not values:
raise ValueError("Google Sheet is empty or not accessible.")
headers = values[0]
# pad rows to header length
rows = [r + [''] * (len(headers) - len(r)) for r in values[1:]]
# Build DataFrame (avoids all quoting issues)
df = pd.DataFrame(rows, columns=headers)
con = duckdb.connect()
# Register df and create/replace the table from it
con.register("temp_df", df)
con.execute(f'CREATE OR REPLACE TABLE "{table_name}" AS SELECT * FROM temp_df')
con.unregister("temp_df")
return con
sheet_index = {}
def server(input, output, session):
con = {"conn": None}
@reactive.Effect
def init_dropdown_from_folder():
global sheet_index
try:
sheets = list_sheets_in_folder(GOOGLE_FOLDER_ID)
sheet_index = {s['name']: s['id'] for s in sheets}
sheet_names = list(sheet_index.keys())
print(f"[DEBUG] Found {len(sheet_names)} sheets in folder: {sheet_names}")
ui.update_select("sheet_dropdown", choices=sheet_names)
except Exception as e:
print(f"[ERROR] Failed to list folder contents or populate dropdown: {e}")
@reactive.Effect
def load_selected_sheet():
sheet_name = input.sheet_dropdown()
if sheet_name not in sheet_index:
return
spreadsheet_id = sheet_index[sheet_name]
print(f"[DEBUG] Loading sheet: {sheet_name} (ID: {spreadsheet_id})")
con["conn"] = load_sheet_to_duckdb(spreadsheet_id)
@output
@render.table
def results():
sheet_name = input.sheet_dropdown() # force reactivity on sheet change
try:
if con["conn"] is None:
return duckdb.query("SELECT 'Waiting for sheet selection' AS status").to_df()
name_query = input.name_filter()
if name_query:
return con["conn"].execute(
"SELECT * FROM active_sheet WHERE item_name ILIKE '%' || ? || '%'",
(name_query,)
).fetchdf()
else:
return con["conn"].execute("SELECT * FROM active_sheet").fetchdf()
except Exception as e:
return duckdb.query(f"SELECT 'Error: {str(e).replace('\'','')}' AS error").to_df()
|