#!/usr/bin/env python3 """ Google Sheets API Integration Real implementation for writing data to Google Sheets """ import csv import sys import os import json import requests from datetime import datetime import time import jwt class GoogleSheetsWriter: def __init__(self): self.sheet_id = "1Wna-30aqljYMgGtTQhgOp11LIP5GV-RugX2iqlVUs_Q" self.api_key = None # Will be set from environment or config self.service_account = None # Service account credentials self.access_token = None # OAuth2 access token self.sheet_range = "A1:Z1000" # Range to write to def load_config(self): """Load API configuration from file or environment""" # Try to load service account service_account_file = "service_account.json" if os.path.exists(service_account_file): try: with open(service_account_file, 'r') as f: self.service_account = json.load(f) return "service_account" except Exception as e: print(f"Error loading service account: {e}") # Try to load from config file (API key method) config_file = "google_sheets_config.json" if os.path.exists(config_file): try: with open(config_file, 'r') as f: config = json.load(f) self.api_key = config.get('api_key') return "api_key" except: pass # Try environment variable self.api_key = os.environ.get('GOOGLE_SHEETS_API_KEY') if self.api_key: return "api_key" return None def get_access_token(self): """Get OAuth2 access token using service account""" if not self.service_account: return None # Create JWT payload now = int(time.time()) payload = { 'iss': self.service_account['client_email'], 'scope': 'https://www.googleapis.com/auth/spreadsheets', 'aud': 'https://oauth2.googleapis.com/token', 'iat': now, 'exp': now + 3600 # 1 hour } # Sign JWT with private key try: private_key = self.service_account['private_key'] assertion = jwt.encode(payload, private_key, algorithm='RS256') # Exchange JWT for access token token_data = { 'grant_type': 'urn:ietf:params:oauth:grant-type:jwt-bearer', 'assertion': assertion } response = requests.post('https://oauth2.googleapis.com/token', data=token_data) if response.status_code == 200: token_info = response.json() self.access_token = token_info['access_token'] return self.access_token else: print(f"Token error: {response.status_code} - {response.text}") return None except Exception as e: print(f"JWT error: {e}") return None def prepare_data(self, results_file): """Load and prepare data for analysis results (country positions)""" if not os.path.exists(results_file): print(f"Error: Results file {results_file} not found") return None # Read the results results = [] with open(results_file, 'r', encoding='utf-8') as file: reader = csv.DictReader(file) for row in reader: results.append({ 'country': row['country'], 'position': f"{row['position']}/{row['total_rows']}" }) if not results: print("No results to export") return None return { 'country_positions': results, 'results_count': len(results) } def _write_log(self, log_entries): """Write log entries to file""" log_file = "sheets_update.log" try: with open(log_file, 'a', encoding='utf-8') as f: for entry in log_entries: f.write(entry + '\n') f.write('\n') # Extra line between log sessions except Exception as e: print(f"Warning: Could not write to log file: {e}") def update_country_positions(self, data): """Update positions in column J based on country matches in column A""" # Create detailed log log_entries = [] log_entries.append("=" * 60) log_entries.append(f"📊 GOOGLE SHEETS UPDATE LOG - {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}") log_entries.append("=" * 60) log_entries.append(f"Target Sheet: https://docs.google.com/spreadsheets/d/{self.sheet_id}") log_entries.append(f"Countries to update: {len(data['country_positions'])}") log_entries.append("") if not self.service_account and not self.api_key: log_entries.append("❌ No authentication configured") self._write_log(log_entries) print("❌ No authentication configured") return False # Get access token if using service account if self.service_account: access_token = self.get_access_token() if not access_token: log_entries.append("❌ Failed to get access token") self._write_log(log_entries) print("❌ Failed to get access token") return False headers = { 'Content-Type': 'application/json', 'Authorization': f'Bearer {access_token}' } log_entries.append("🔑 Using Service Account authentication") else: headers = {'Content-Type': 'application/json'} log_entries.append("🔑 Using API Key authentication") try: # Get current sheet data if self.service_account: get_url = f"https://sheets.googleapis.com/v4/spreadsheets/{self.sheet_id}/values/A:H" else: get_url = f"https://sheets.googleapis.com/v4/spreadsheets/{self.sheet_id}/values/A:H?key={self.api_key}" log_entries.append("🔍 Reading current sheet data...") response = requests.get(get_url, headers=headers) if response.status_code != 200: log_entries.append(f"❌ Error reading sheet: {response.status_code}") log_entries.append(f"Response: {response.text}") self._write_log(log_entries) print(f"❌ Error reading sheet: {response.status_code}") return False sheet_data = response.json() if 'values' not in sheet_data: log_entries.append("❌ No data found in sheet") self._write_log(log_entries) print("❌ No data found in sheet") return False log_entries.append(f"✅ Found {len(sheet_data['values'])} rows in sheet") log_entries.append("") log_entries.append("🔄 Matching countries:") # Find matches and prepare updates updates = [] country_positions = data['country_positions'] matched_countries = [] unmatched_countries = [] # Get all countries from sheet (for comparison) sheet_countries = [] for i, row in enumerate(sheet_data['values']): if i == 0: # Skip header continue if len(row) > 0: sheet_countries.append(row[0].strip().upper()) for i, row in enumerate(sheet_data['values']): if i == 0: # Skip header continue if len(row) > 0: # Has country code in column A country_code = row[0].strip().upper() # Find matching country in results for result in country_positions: if result['country'].upper() == country_code: row_num = i + 1 # 1-based indexing updates.append({ 'range': f'J{row_num}', 'values': [[result['position']]] }) matched_countries.append(country_code) log_entries.append(f" ✅ {country_code}: {result['position']} (row {row_num})") print(f" {country_code}: {result['position']}") break # Find countries in analysis that are NOT in sheet analysis_countries = [c['country'].upper() for c in country_positions] for country in analysis_countries: if country not in sheet_countries: unmatched_countries.append(country) log_entries.append("") log_entries.append("📋 SUMMARY:") log_entries.append(f" Countries in analysis: {len(analysis_countries)}") log_entries.append(f" Countries in sheet: {len(sheet_countries)}") log_entries.append(f" Successfully matched: {len(matched_countries)}") log_entries.append(f" Countries NOT found in sheet: {len(unmatched_countries)}") if unmatched_countries: log_entries.append("") log_entries.append("⚠️ MISSING COUNTRIES in Google Sheets:") for country in unmatched_countries: # Find the position for this country for result in country_positions: if result['country'].upper() == country: log_entries.append(f" ❌ {country}: {result['position']} (NOT WRITTEN)") break if not updates: log_entries.append("") log_entries.append("❌ No matching countries found in sheet") self._write_log(log_entries) print("❌ No matching countries found in sheet") return False # Batch update log_entries.append("") log_entries.append("🚀 Performing batch update...") if self.service_account: batch_url = f'https://sheets.googleapis.com/v4/spreadsheets/{self.sheet_id}/values:batchUpdate' else: batch_url = f'https://sheets.googleapis.com/v4/spreadsheets/{self.sheet_id}/values:batchUpdate?key={self.api_key}' batch_data = { 'valueInputOption': 'RAW', 'data': updates } batch_response = requests.post(batch_url, json=batch_data, headers=headers) if batch_response.status_code == 200: log_entries.append(f"✅ Successfully updated {len(updates)} country positions in column J") log_entries.append("") log_entries.append("🎉 UPDATE COMPLETED SUCCESSFULLY!") self._write_log(log_entries) print(f"✅ Successfully updated {len(updates)} country positions in column J") if unmatched_countries: print(f"⚠️ Warning: {len(unmatched_countries)} countries from analysis not found in sheet") print(" Check sheets_update.log for details") return True else: log_entries.append(f"❌ Batch update error: {batch_response.status_code}") log_entries.append(f"Response: {batch_response.text}") self._write_log(log_entries) print(f"❌ Batch update error: {batch_response.status_code}") print(batch_response.text) return False except Exception as e: log_entries.append(f"❌ Error updating positions: {e}") self._write_log(log_entries) print(f"❌ Error updating positions: {e}") return False def write_to_sheets_simple(self, data): """Simple method - just create formatted output for manual copy-paste""" print("🚀 Creating formatted output for Google Sheets...") output_file = 'google_sheets_formatted.txt' with open(output_file, 'w', encoding='utf-8', newline='') as f: for row in data['values']: # Convert to tab-separated for easy paste f.write('\t'.join(str(cell) for cell in row) + '\n') print(f"✅ Formatted output created: {output_file}") print(f"📊 Contains {data['results_count']} results") print() print("📋 To import to Google Sheets:") print("1. Copy content from the file above") print("2. Open Google Sheets") print("3. Select cell A1") print("4. Paste (Ctrl+V)") print("5. Data will be automatically formatted") return output_file def write_to_sheets_api(self, data): """Write data using Google Sheets API (supports both API key and Service Account)""" # Try Service Account first if self.service_account: print("🔑 Using Service Account authentication...") access_token = self.get_access_token() if not access_token: print("❌ Failed to get access token") print("📋 Falling back to manual method...") return self.write_to_sheets_simple(data) # Use access token for API call url = f"https://sheets.googleapis.com/v4/spreadsheets/{self.sheet_id}/values/{self.sheet_range}?valueInputOption=RAW" headers = { 'Content-Type': 'application/json', 'Authorization': f'Bearer {access_token}' } elif self.api_key: print("🔑 Using API Key authentication...") url = f"https://sheets.googleapis.com/v4/spreadsheets/{self.sheet_id}/values/{self.sheet_range}?key={self.api_key}&valueInputOption=RAW" headers = {'Content-Type': 'application/json'} else: print("❌ No authentication configured") print("📋 Falling back to manual method...") return self.write_to_sheets_simple(data) payload = { "range": self.sheet_range, "majorDimension": "ROWS", "values": data['values'] } try: response = requests.put(url, json=payload, headers=headers) if response.status_code == 200: print(f"✅ Successfully wrote {data['results_count']} results to Google Sheets!") print(f"🌐 View results: https://docs.google.com/spreadsheets/d/{self.sheet_id}") return True else: print(f"❌ API Error: {response.status_code}") print(f"Response: {response.text}") print("📋 Falling back to manual method...") return self.write_to_sheets_simple(data) except Exception as e: print(f"❌ Connection error: {e}") print("📋 Falling back to manual method...") return self.write_to_sheets_simple(data) def export_results(self, results_file): """Main export function - updates country positions in column E""" print("🚀 Starting Google Sheets position update...") print(f"📁 Results file: {results_file}") print(f"🌐 Target sheet: https://docs.google.com/spreadsheets/d/{self.sheet_id}") print() # Prepare data data = self.prepare_data(results_file) if not data: return False print(f"📊 Found {data['results_count']} countries to update:") for result in data['country_positions']: print(f" {result['country']}: {result['position']}") print() # Load configuration auth_method = self.load_config() if not auth_method: print("❌ No API authentication configured") return False print(f"🔑 Using {auth_method} authentication...") print("🔄 Updating positions in column J...") # Update country positions result = self.update_country_positions(data) if result: print(f"🌐 View updated sheet: https://docs.google.com/spreadsheets/d/{self.sheet_id}") return result def main(): results_file = "analysis_results.csv" if len(sys.argv) > 1: results_file = sys.argv[1] writer = GoogleSheetsWriter() success = writer.export_results(results_file) sys.exit(0 if success else 1) if __name__ == "__main__": main()