Create Asana onboarding projects from new sheet rows

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

SHEET_ID = "your-spreadsheet-id"
TEMPLATE_GID = "your-asana-project-template-gid"
TEAM_GID = "your-asana-team-gid"

@on_trigger
def run(event):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    rows = sheets.spreadsheets().values().get(spreadsheetId=SHEET_ID, range="Clients!A2:C").execute().get("values", [])
    new = [(i + 2, r[0]) for i, r in enumerate(rows) if r and r[0] and (len(r) < 3 or not r[2])]
    report_fetched(len(new))
    if not new:
        return {"summary": "Nothing new"}
    asana = {"Authorization": f"Bearer {credentials.connection('asana', 'work')}"}
    for row, client in new:
        job = http.post(f"https://app.asana.com/api/1.0/project_templates/{TEMPLATE_GID}/instantiateProject", headers=asana,
                        json={"data": {"name": f"{client} onboarding", "team": TEAM_GID, "public": True}}).json()["data"]
        link = f"https://app.asana.com/0/{job['new_project']['gid']}"
        sheets.spreadsheets().values().update(spreadsheetId=SHEET_ID, range=f"Clients!C{row}",
                                              valueInputOption="RAW", body={"values": [[link]]}).execute()
        notify.slack(f"Onboarding project created for *{client}*: {link}", channel="#onboarding")
    return {"summary": f"{len(new)} projects created"}