File size: 1,768 Bytes
a7bb996 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | 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}")
|