Fill your monthly report deck from your metrics sheet

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

SHEET_ID, TEMPLATE_ID = "your-spreadsheet-id", "report-template-id"

@on_trigger
def run(event):
    month = f"{date.today().replace(day=1) - timedelta(days=1):%B %Y}"
    rows = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Metrics!A2:B").execute().get("values", [])
    values = {r[0]: r[1] for r in rows if len(r) == 2}
    try:
        values["comment"] = ai.generate(f"One plain sentence on these monthly figures: {values}", max_output_tokens=60).text
    except ai.AIError:
        values["comment"] = ""
    drive, slides = build("drive", "v3", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False), build("slides", "v1", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).presentations()
    deck = drive.files().copy(fileId=TEMPLATE_ID, body={"name": f"Monthly report – {month}"}).execute()["id"]
    slides.batchUpdate(presentationId=deck, body={"requests": [{"replaceAllText": {"containsText": {"text": "{{" + k + "}}"}, "replaceText": v}}
                       for k, v in {**values, "month": month}.items()]}).execute()
    notify.email_me(f"Monthly report – {month}", f"https://docs.google.com/presentation/d/{deck}\n\n{values['comment']}")
    return {"deck": deck, "metrics": len(values)}