Email new leads in your sheet from Outlook

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

api = lambda name, version: build(name, version, credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)

def graph(method, path, **kw):
    r = http.request(method, "https://graph.microsoft.com/v1.0" + path, headers={"Authorization": f"Bearer {credentials.connection('microsoft', 'work')}"}, **kw)
    return r.raise_for_status() or (r.json() if r.content else None)

def send_mail(to, subject, text):
    graph("POST", "/me/sendMail", json={"message": {"subject": subject, "body": {"contentType": "Text", "content": text},
          "toRecipients": [{"emailAddress": {"address": a}} for a in to.split(",")]}})

SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    sheet = api("sheets", "v4").spreadsheets().values()
    rows = sheet.get(spreadsheetId=SHEET_ID, range="Leads!A2:F").execute().get("values", [])
    sent = 0
    for n, r in enumerate(rows, start=2):
        if len(r) < 5 or r[4] != "new" or sent >= 20:
            continue
        name, email, company, notes = r[0], r[1], r[2], r[3]
        try:
            opener = ai.generate(f"{name} at {company}. Notes: {notes}", system="Write one friendly opening sentence for a first email.", max_output_tokens=60).text.strip()
        except ai.AIError:
            opener = f"Thanks for your interest in working together."
        send_mail(email, f"Hello from Northwind Consulting, {name.split()[0]}",
                  f"Hi {name.split()[0]},\n\n{opener}\n\nWould a 20-minute call next week be useful?\n\nBest,\nAlex")
        sheet.update(spreadsheetId=SHEET_ID, range=f"Leads!E{n}:F{n}", valueInputOption="RAW", body={"values": [["emailed", date.today().isoformat()]]}).execute()
        sent += 1
    return {"sent": sent}