| import streamlit as st |
| import requests |
| import pandas as pd |
| import gspread |
| from oauth2client.service_account import ServiceAccountCredentials |
| import traceback |
| import json |
| import io |
|
|
| |
| SHEET_ID = "1PCugIPyYM1lF6KOCPUY5hL8xbGI5PWd7gVvCyudrB5c" |
| SHEET_NAME = "RUGBY" |
|
|
| |
| 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 = "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 = """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: |
| |
| df = pd.read_csv(io.StringIO(EXCEL_DATA), sep='\t') |
| |
| 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") |
| |
| 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) |
|
|
| |
| st.write("Debug: Opening Google Sheet") |
| sheet = client.open_by_key(SHEET_ID).worksheet(SHEET_NAME) |
|
|
| |
| st.write("Debug: Clearing existing data") |
| sheet.clear() |
|
|
| |
| st.write("Debug: Writing Excel data") |
| |
| 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") |
|
|
| |
| 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.") |
|
|
| |
| 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.") |
|
|
| |
| 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() |
