Spaces:
Sleeping
Sleeping
| import pandas as pd | |
| import gradio as gr | |
| from datetime import datetime | |
| import gspread | |
| from google.oauth2.service_account import Credentials | |
| import os | |
| import time | |
| import requests | |
| import json | |
| import math | |
| SHEET_ID = os.getenv('SHEETID') # Replace with your Google Sheet ID | |
| log = os.getenv('USER') | |
| password = os.getenv('PASSWORD') | |
| SOCIAL_SHEET = 'SocialSheet' | |
| # Service account credentials file path | |
| # CREDENTIALS_FILE = 'credentials.json' | |
| conf1 = os.getenv('CONF') | |
| dict = json.loads(conf1) | |
| XKEY = os.getenv('XKEY') | |
| # Initialize Google Sheets client | |
| SCOPES = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'] | |
| creds = Credentials.from_service_account_info(dict, scopes=SCOPES) | |
| gc = gspread.authorize(creds) | |
| sheet = gc.open_by_key(SHEET_ID) | |
| # API endpoint and request details | |
| url = "https://google.serper.dev/search" | |
| headers = { | |
| 'X-API-KEY': XKEY, | |
| 'Content-Type': 'application/json' | |
| } | |
| def json_serialize(obj): | |
| if isinstance(obj, float): | |
| if math.isnan(obj) or math.isinf(obj): | |
| return None | |
| return obj | |
| def search_and_store(locations, keywords): | |
| all_dfs = [] | |
| locations_list = [loc.strip() for loc in locations.split(',')] | |
| keywords_list = [kw.strip() for kw in keywords.split(',')] | |
| for location in locations_list: | |
| for keyword in keywords_list: | |
| query = f"{keyword} {location} site:facebook.com/ -inurl:groups -inurl:biz" | |
| payload = json.dumps({ | |
| "q": query, | |
| "num": 100, | |
| "tbs": "qdr:m" | |
| }) | |
| try: | |
| response = requests.post(url, headers=headers, data=payload) | |
| response.raise_for_status() | |
| response_data = response.json() | |
| if 'organic' in response_data: | |
| df = pd.DataFrame(columns=['link', 'title', 'snippet', 'date', 'query']) | |
| for item in response_data['organic']: | |
| new_row = pd.DataFrame({ | |
| 'link': [item.get('link', '')], | |
| 'title': [item.get('title', '')], | |
| 'snippet': [item.get('snippet', '')], | |
| 'date': [item.get('date', '')], | |
| 'query': [f"{keyword} {location}"] | |
| }) | |
| df = pd.concat([df, new_row], ignore_index=True) | |
| df = df.replace([float('inf'), -float('inf'), float('nan')], None) | |
| all_dfs.append(df) | |
| else: | |
| print(f"No 'organic' results for query: {query}") | |
| except Exception as e: | |
| print(f"Error for query: {query} - {str(e)}") | |
| time.sleep(1) | |
| if all_dfs: | |
| result_df = pd.concat(all_dfs, ignore_index=True) | |
| social_sheet = sheet.worksheet(SOCIAL_SHEET) | |
| existing_data = social_sheet.get_all_records() | |
| existing_df = pd.DataFrame(existing_data) | |
| new_rows = result_df[~result_df['link'].isin(existing_df['link'])] | |
| if not new_rows.empty: | |
| new_values = json.loads(json.dumps(new_rows.to_dict('records'), default=json_serialize)) | |
| new_values = [[row.get(col) for col in new_rows.columns] for row in new_values] | |
| social_sheet.append_rows(new_values) | |
| return f"{len(new_rows)} new rows have been appended to {SOCIAL_SHEET} in Google Sheets" | |
| else: | |
| return "No new data to append." | |
| else: | |
| return "No data was retrieved from the queries." | |
| def start_search(locations, keywords): | |
| return search_and_store(locations, keywords) | |
| iface = gr.Interface( | |
| fn=start_search, | |
| inputs=[ | |
| gr.Textbox(label="Locations (comma-separated)"), | |
| gr.Textbox(label="Keywords (comma-separated)") | |
| ], | |
| outputs=gr.Textbox(label="Result"), | |
| title="Facebook Page Search and Store", | |
| description="Enter locations and keywords to search for Facebook pages and store the results in Google Sheets.", | |
| live=False | |
| ) | |
| iface.launch(auth=(log,password)) |