import streamlit as st import requests import pandas as pd import gspread from oauth2client.service_account import ServiceAccountCredentials import traceback import json import io # Google Sheets configuration SHEET_ID = "1PCugIPyYM1lF6KOCPUY5hL8xbGI5PWd7gVvCyudrB5c" SHEET_NAME = "RUGBY" # Service account credentials SERVICE_ACCOUNT_INFO = { "type": "service_account", "project_id": "abiding-state-451516-e6", "private_key_id": "668f5f97d95b999d0ad706445e1a9a624b6bb263", "private_key": """-----BEGIN PRIVATE KEY----- MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCCIBM+5nspxseN D/Fls62ZRrCpPhqLX2TS+EDBFwhOtwlLmFVWGUUBBGVXhLRzrLS550p5toyhfOIU R2Bd0copSmLkglEwuUyClbswz+MJD4JCVuKbLugm5rMxJSuCwH/vXyvV8LtJESjK sxfQZ5uLkXS6i+S+SlNgR5TcdxskUa0/ymCEjiLojrbaZ64eiM75b/gakljI40ee ZMWV6awSe2+EQnQ2W27H0uk4B2cMjEA0q1SHDRyLhwMNeaW3DIrCmUZntUev/KdD samjgPPA8Z2VMvYh0ONr131EFlXq8atbUwHeefWLsRxeNmpzruJtASv10Y8fa/wl zADUaU+9AgMBAAECggEAJxI2dSaDSLH07PgihWUD9Qfyt358Pejwvx/nYZLm3au7 0MT2qpl/zKRjQXoJLMpooYE/LXiQlVSIFYqw9rqtNuSs+OvgMP8SaAtJX4isJOZR OFYaSHo3HqcamuEMRYBDab5rzRsxQOk0FFjeB6d0Xt3X7MNgdXqfukMEAYaJ0cd+ 4LOhlMaDQ8jXdnthXh55Gz7Qyk0W5AjnckWdlaaNZ2y1MVI9zdJ4qDBdreua9p5g 3mgBe93s2KbCEKBSzCTsoU1GbGw5sBP2w7MyPvXuyIgk2BNdDH5Sz+u/8Wdm7gIh 8EXwdxWcDJRYxm5tTErqgGDDEvCB/bXaU0Yt2IiKSQKBgQC2mmmUGEUM87b7hOtC 1+hpEf/iucHaGBrkxW0Fcz2/Gi5mPeb60gbMtbxmFMNPdDaqKQxi5KjhLP/iZgRk VWJJsJb/P8xuL64y8sI6NUdTGqTUxqB6CJOriMkLI0b6JVK/vTj3Hd88VthdYwNC FWvFi0ckT9Ab3Fp9+309oav6ZwKBgQC2bcTjuwsiXRKzTYoODoF/Z/+NIPCI4BZ5 wUAwY/otQ0/dQB7B5+DruLnIWCVM4PzXoPvpLgrs/iiyFPDGTlYtUa2rBTfKHr13 UA0Y97uRvBd6004RAbM/xHoWeX2uSRtD8kzs5TZGlzgPyS0FT7b9nb+1iWPrcMJy aoQI3hhWOwKBgAlqLrVfBvhi/HU9Zi/dF/OK5QmYkEEhawmOkE9uoKlgvTGyUV1v RMB7Qkp1GZjYIhVl5miW54osmsjef614pYetHivs6ypnNPEViSQdKR9EyWjbEN0Z 4hs/AzaazAcLs5eFNxGurZrb024FM97NYfAefT2HC/DTzKqgM9XgpxBTAoGBAKEW QLuO2IAZi3/EwlkzOepU3PWz1nIm3fHZUWLphWoefBE0o3Q52wSK85ITM6y1Js8J GiiHPaKTsz+8+fdCCRqvexj+3IqfplIK3+XiK0C+xETy/YZK6Asw5VWfJfmWGHth I8gzhj/DG//kdtqvzzgX6cbVcOPfh/QcVka2RustAoGBAJHVNd9QSSd5Z8aFTHmf v+oKjnQKzGY7j+K+bU3yxPHxfDrN5ticodaHxbho2VFaI17qcFVHkkX12k2I2O1Z V8Nve8i2JkFVEftVpSQ/01AhsbZflDm8ORJet2M8R2o5bcZ4Yc8/shI/j7Sz8BeM YMSoAW+xwwELc6a+5m8u60ZU -----END PRIVATE KEY-----""", "client_email": "dream11-optimizer@abiding-state-451516-e6.iam.gserviceaccount.com", "client_id": "102537539700971787452", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/dream11-optimizer%40abiding-state-451516-e6.iam.gserviceaccount.com", "universe_domain": "googleapis.com" } # URL and headers (no caching headers) URL = "https://www.rugbypremierleague.in/feeds/live/MatchCenter_MatchID-43.json" HEADERS = { "accept": "*/*", "accept-encoding": "gzip, deflate, br, zstd", "accept-language": "en-US,en;q=0.9", "referer": "https://www.rugbypremierleague.in/matches/bengaluru-bravehearts-vs-delhi-redz-2025-43", "sec-ch-ua": '"Google Chrome";v="137", "Chromium";v="137", "Not/A)Brand";v="24"', "sec-ch-ua-mobile": "?0", "sec-ch-ua-platform": '"Windows"', "sec-fetch-dest": "empty", "sec-fetch-mode": "cors", "sec-fetch-site": "same-origin", "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/137.0.0.0 Safari/537.36" } # Excel data as a string (from provided DOCUMENT) EXCEL_DATA = """id name short_name jersey_no position_id position is_substitute is_substituted isPlaying minutes_played jersey starter captain is_Injured offloads line_breaks defenders_beaten points try_assists tries tackles turnovers_won yellow_cards red_cards handling_errors knock_ons conversions penalty_goals points_scored tries_scored conversions penalty_goals 165 Pol Pla Pol Pla 7 4 Back 0 0 0 18 7 1 1 0 0 0 0 0 1 0 3 0 0 0 0 0 0 0 0 0 0 0 191 Tone Ng Shiu Tone Ng Shiu 3 3 Hybrid 0 0 0 12 3 1 0 0 1 0 2 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 245 Iowane Teba Iowane Teba 10 4 Back 1 0 1 6 10 0 0 0 0 0 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 370 Liam Poulton Liam Poulton 14 4 Back 0 0 1 19 14 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 371 Mak Kwai Chung Mak Kwai Chung 99 4 Back 1 0 1 14 99 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 372 Akuila Rokolisoa Akuila Rokolisoa 4 4 Back 0 0 1 25 4 1 0 0 0 2 2 16 0 2 3 0 0 0 0 0 3 0 16 2 3 0 373 Scott Curry Scott Curry 37 5 Forward 1 0 1 13 37 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 374 Mohit Khatri Mohit Khatri 6 3 Hybrid 1 0 1 14 6 0 0 0 0 0 0 0 0 0 2 1 0 0 0 0 0 0 0 0 0 0 375 Karan Rajbhar Karan Rajbhar 1 3 Hybrid 1 0 1 13 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 376 Arpan Chhetri Arpan Chhetri 12 5 Forward 1 0 0 1 12 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 377 Suresh Kumar Suresh Kumar 2 5 Forward 0 0 0 10 2 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 378 Prashant Singh Prashant Singh 5 3 Hybrid 0 1 0 10 5 1 0 0 0 0 3 0 0 0 2 0 0 0 1 0 0 0 0 0 0 0 1575 Philip Wokorach Philip Wokorach 15 4 Back 0 0 0 17 15 1 0 0 2 1 2 5 1 1 2 0 0 0 0 0 0 0 5 1 0 0 1589 Govind Gupta Govind Gupta 17 5 Forward 0 0 0 0 17 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 140 Matteo Graziano Matteo Graziano 1 5 Forward 1 0 1 9 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 144 Matias Osadczuk Matias Osadczuk 97 5 Forward 0 0 1 22 97 1 1 0 0 1 2 9 0 1 0 0 1 0 0 0 2 0 9 1 2 0 168 Alejandro Laforga Alejandro Laforga 19 4 Back 0 0 1 25 19 1 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 0 0 0 0 0 270 Patrick Odongo Okongo Patrick Odongo Okongo 11 4 Back 0 0 1 25 11 1 0 0 0 2 2 10 0 2 2 0 0 0 0 0 0 0 10 2 0 0 284 Jordan Conroy Jordan Conroy 7 4 Back 1 0 0 0 7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 410 Michael Coverdale Michael Coverdale 25 5 Forward 0 0 0 19 25 1 0 0 0 0 0 0 1 0 1 1 0 0 0 0 0 0 0 0 0 0 411 Moritz Noll Moritz Noll 77 4 Back 1 0 0 0 77 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 413 Rajdeep Saha Rajdeep Saha 4 4 Back 0 0 1 25 4 1 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 414 Deepak Punia Deepak Punia 10 4 Back 0 0 1 25 10 1 0 0 0 0 0 2 0 0 0 1 0 0 2 0 1 0 2 0 1 0 416 Sunil Chawan Sunil Chawan 20 4 Back 1 0 0 0 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 417 Raj Kumar Raj Kumar 5 3 Hybrid 1 0 0 0 5 0 0 0 0 0 0 0 1 0 0 0 0 0 1 0 0 0 0 0 0 0 1578 Robin Plumpe Robin Plumpe 27 3 Hybrid 0 0 1 25 27 1 0 0 0 0 0 0 0 0 2 0 0 0 0 0 0 0 0 0 0 0 1584 Mohit Mohit 9 4 Back 1 0 0 0 9 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0""" def load_excel_data(): try: # Read Excel data from string df = pd.read_csv(io.StringIO(EXCEL_DATA), sep='\t') # Select required columns columns = ['id', 'name', 'short_name', 'jersey_no', 'position', 'is_substitute', 'is_substituted', 'isPlaying', 'minutes_played', 'starter', 'captain', 'is_Injured', 'offloads', 'line_breaks', 'defenders_beaten', 'points', 'try_assists', 'tries', 'tackles', 'turnovers_won', 'yellow_cards', 'red_cards', 'handling_errors', 'knock_ons', 'conversions', 'penalty_goals', 'points_scored', 'tries_scored', 'conversions', 'penalty_goals'] df = df[columns] st.session_state["excel_data"] = df return df except Exception as e: st.error(f"Error loading Excel data: {str(e)}\n{traceback.format_exc()}") return None def fetch_json_data(): try: response = requests.get(URL, headers=HEADERS) if response.status_code == 200: st.success("JSON data fetched successfully!") st.session_state["json_data"] = response.json() return st.session_state["json_data"] else: st.error(f"Failed to fetch JSON data: Status code {response.status_code}") return None except Exception as e: st.error(f"Error fetching JSON data: {str(e)}\n{traceback.format_exc()}") return None def write_to_google_sheet(data): try: with st.spinner("Writing to Google Sheet..."): st.write("Debug: Starting Google Sheets write operation") # Authenticate scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"] creds = ServiceAccountCredentials.from_json_keyfile_dict(SERVICE_ACCOUNT_INFO, scope) st.write("Debug: Authentication successful") client = gspread.authorize(creds) # Open sheet st.write("Debug: Opening Google Sheet") sheet = client.open_by_key(SHEET_ID).worksheet(SHEET_NAME) # Clear existing data st.write("Debug: Clearing existing data") sheet.clear() # Write data (pandas DataFrame) st.write("Debug: Writing Excel data") # Convert DataFrame to list of lists (including headers) data_values = [data.columns.tolist()] + data.values.tolist() sheet.update('A1', data_values) st.success("Data written to Google Sheet successfully!") return True except Exception as e: error_message = f"Error writing to Google Sheet: {str(e)}\n{traceback.format_exc()}" st.error(error_message) return False def main(): st.title("Rugby Premier League Data Fetcher") st.write("Processing data for match ID 43 (Bengaluru Bravehearts vs. Delhi Redz) at 05:17 PM IST, June 23, 2025") # Load Excel data st.subheader("Excel Data (Squad)") excel_data = load_excel_data() if excel_data is not None: st.dataframe(excel_data) else: st.warning("No Excel data loaded.") # Fetch JSON data st.subheader("Fetch JSON Data") if st.button("Fetch JSON Data"): json_data = fetch_json_data() if json_data: st.subheader("Fetched JSON Data") st.json(json_data) else: st.warning("No JSON data fetched. Please try again or check the URL.") # Write to Google Sheet st.subheader("Write to Google Sheet") if st.button("Write Excel Data to Google Sheet"): st.write("Debug: Write to Google Sheet button clicked") if "excel_data" in st.session_state: if write_to_google_sheet(st.session_state["excel_data"]): st.write(f"View the updated sheet [here](https://docs.google.com/spreadsheets/d/{SHEET_ID})") else: st.error("Failed to write to Google Sheet. See error details above.") else: st.error("No Excel data available to write. Please ensure data is loaded.") if __name__ == "__main__": main()