Create calendar events from rows in a sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, report_fetched

SHEET_ID, CALENDAR_ID, TZ = "your-spreadsheet-id", "primary", "Europe/London"

@on_trigger
def run(event):
    creds = Credentials(credentials.connection("google", "work"))
    sheets = build("sheets", "v4", credentials=creds, cache_discovery=False).spreadsheets()
    rows = sheets.values().get(spreadsheetId=SHEET_ID, range="Events!A2:E").execute().get("values", [])
    new = [(i + 2, r) for i, r in enumerate(rows) if len(r) >= 3 and (len(r) < 5 or not r[4])]
    report_fetched(len(new))
    if not new:
        return {"summary": "Nothing new"}
    cal = build("calendar", "v3", credentials=creds, cache_discovery=False)
    for row_no, r in new:
        ev = cal.events().insert(calendarId=CALENDAR_ID, body={"summary": r[0], "location": r[3] if len(r) > 3 else "",
            "start": {"dateTime": r[1], "timeZone": TZ}, "end": {"dateTime": r[2], "timeZone": TZ}}).execute()
        sheets.values().update(spreadsheetId=SHEET_ID, range=f"Events!E{row_no}", valueInputOption="RAW",
                               body={"values": [[ev["id"]]]}).execute()
    return {"created": len(new)}