| from shillelagh.backends.apsw.db import connect | |
| from google.oauth2 import service_account | |
| import os | |
| import json | |
| # Set up environment variable for authentication | |
| # If using a service account, set the environment variable for authentication | |
| credentials_json = os.getenv('GOOGLE_API_JSON_CONTENT') | |
| # Load the credentials from the JSON string | |
| credentials_info = json.loads(credentials_json) | |
| # credentials = service_account.Credentials.from_service_account_info(credentials_info) | |
| # os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = 'location-455719-d08225f31b58.json' | |
| # URL format: https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit | |
| # https://docs.google.com/spreadsheets/d/1CMdSSTxjw9ooYDzmAufL0j3EtUxAQ6vdwbAL5e3t7aY/edit?usp=sharing | |
| spreadsheet_id = '1CMdSSTxjw9ooYDzmAufL0j3EtUxAQ6vdwbAL5e3t7aY' | |
| sheet_name = 'Config' | |
| sheet_table = { | |
| "Location": 0, | |
| "Config": 1310222727 | |
| } | |
| url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit?gid={sheet_table[sheet_name]}" | |
| # Connect to the Google Sheet | |
| connection = connect( | |
| ":memory:", | |
| adapter_kwargs={ | |
| "gsheetsapi": { | |
| # "service_account_file": 'location-455719-d08225f31b58.json' | |
| "service_account_info": credentials_info, | |
| } | |
| } | |
| ) | |
| cursor = connection.cursor() | |
| # Example query to read data | |
| rows = cursor.execute(f"SELECT * FROM '{url}' LIMIT 1").fetchall() | |
| # rowid = rows[0][0] | |
| config = rows[0][1] | |
| config = json.loads(config) | |
| config["last_run"] = config.get("last_run", 0) + 1 | |
| updated_config = json.dumps(config) | |
| print(rows) | |
| # cursor.execute(f"DELETE FROM '{url}' ") | |
| try: | |
| cursor.execute( | |
| f"UPDATE '{url}' SET config = ? WHERE id = ?", | |
| (updated_config, 1) | |
| ) | |
| except Exception as e: | |
| print(f"Error inserting data: {e}") | |