#!/usr/bin/env python3
# list_google_sheets.py
from google.oauth2 import service_account
from googleapiclient.discovery import build
from datetime import datetime, timedelta
from flask import Flask, render_template_string
import os
import logging
# ---------- CONFIG ----------
SPREADSHEET_ID_1 = '15_goJX_IM8XLuGa-L2g93TlxMN3QCGXqdPNNQpjOYZ4'
SPREADSHEET_ID_2 = '1yWdr0G1zch3x88Bp5NrvPhb_LL7JK1Tuu0WsWGIpcWw'
CREDENTIALS_FILE = 'project-91002-b38d0def5b80.json'
RANGE = 'A:F' # read 6 columns: A..F so we can display B..F as columns 2..6
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
LOG_LEVEL = logging.INFO
logging.basicConfig(level=LOG_LEVEL)
logger = logging.getLogger(__name__)
# ---------- HELPERS ----------
def get_service():
if not os.path.exists(CREDENTIALS_FILE):
raise FileNotFoundError(f"Credentials file not found: {CREDENTIALS_FILE}")
creds = service_account.Credentials.from_service_account_file(CREDENTIALS_FILE, scopes=SCOPES)
service = build('sheets', 'v4', credentials=creds)
return service
def read_sheet(service, spreadsheet_id, range_):
try:
result = service.spreadsheets().values().get(
spreadsheetId=spreadsheet_id,
range=range_,
valueRenderOption='FORMATTED_VALUE' # keep dates as displayed strings
).execute()
values = result.get('values', [])
return values
except Exception as e:
logger.exception("Error reading spreadsheet %s", spreadsheet_id)
raise
def pad_row(row, length=6):
return row + [''] * (length - len(row))
def parse_date(val):
"""Return a datetime used for sorting. If not parseable, return datetime.min."""
if val is None or str(val).strip() == '':
return datetime.min
s = str(val).strip()
# numeric -> treat as Excel/Sheets serial date
try:
num = float(s)
# Excel epoch: 1899-12-30
return datetime(1899, 12, 30) + timedelta(days=int(num))
except Exception:
pass
for fmt in ('%Y-%m-%d', '%d/%m/%Y', '%m/%d/%Y', '%d-%m-%Y', '%d %b %Y', '%b %d, %Y'):
try:
return datetime.strptime(s, fmt)
except Exception:
continue
# try dateutil if available
try:
from dateutil.parser import parse as du_parse
return du_parse(s)
except Exception:
return datetime.min
def build_combined(service):
combined = []
sheets = [('st1', SPREADSHEET_ID_1), ('st2', SPREADSHEET_ID_2)]
for status, sid in sheets:
values = read_sheet(service, sid, RANGE)
if values:
# drop header row
rows = values[1:]
else:
rows = []
for row in rows:
row = pad_row(row, 6) # ensure A..F exist
display = [status] + row[1:6] # B..F -> indices 1..5
combined.append(display)
# sort by the date column which is display column 3 => index 2
combined.sort(key=lambda r: parse_date(r[2]))
return combined
# ---------- HTML Template ----------
HTML = """
Combined Sorted Sheets
Combined & Sorted Data
| Status |
Col 2 |
Col 3 (Date) |
Col 4 |
Col 5 |
Col 6 |
{% for row in rows %}
{% for c in row %}
| {{ c }} |
{% endfor %}
{% endfor %}
"""
# ---------- FLASK APP ----------
app = Flask(__name__)
@app.route('/')
def index():
try:
service = get_service()
except FileNotFoundError as e:
return f"Error: credentials file not found
{e}", 500
except Exception as e:
logger.exception("Failed to create Google service")
return f"Error creating Google service
{e}", 500
try:
rows = build_combined(service)
except Exception as e:
logger.exception("Failed to fetch or process sheets")
return f"Error reading sheets
{e}", 500
return render_template_string(HTML, rows=rows)
if __name__ == '__main__':
# run locally
app.run(host='0.0.0.0', port=8080, debug=False)