Monitor your sites and SSL certificates

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from datetime import datetime, timezone
from clickruntime import on_trigger, credentials, http, notify

SHEET_ID = "your-spreadsheet-id"

def cert_expiry(host):
    # Latest certificate for the host from the public Certificate Transparency log (crt.sh).
    certs = http.get("https://crt.sh/", params={"q": host, "output": "json", "exclude": "expired"}, timeout=30).json()
    latest = max(c["not_after"] for c in certs if host in c["name_value"].split("\n"))
    return datetime.fromisoformat(latest).replace(tzinfo=timezone.utc)

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    domains = [r[0] for r in sheets.values().get(spreadsheetId=SHEET_ID, range="Sites!A2:A").execute().get("values", []) if r]
    data, problems = [], []
    for i, host in enumerate(domains, start=2):
        try:
            status = str(http.get(f"https://{host}", timeout=15).status_code)
            expires = cert_expiry(host)
            days = (expires - datetime.now(timezone.utc)).days
        except Exception as e:
            status, expires, days = f"DOWN ({type(e).__name__})", None, None
        data.append({"range": f"Sites!B{i}:C{i}", "values": [[status, expires.date().isoformat() if expires else ""]]})
        if not status.startswith(("2", "3")) or (days is not None and days < 14):
            problems.append(f"{host}: {status}" + (f", SSL expires in {days} days" if days is not None else ""))
    sheets.values().batchUpdate(spreadsheetId=SHEET_ID, body={"valueInputOption": "RAW", "data": data}).execute()
    if problems:
        notify.email_me(f"{len(problems)} sites need attention", "\n".join(problems))
    return {"checked": len(domains), "problems": len(problems)}