Send WhatsApp messages to everyone in a sheet

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

SHEET_ID = "your-spreadsheet-id"
WA = "https://graph.facebook.com/v20.0/your-phone-number-id/messages"

def wa_template(to, name, params):
    body = {"messaging_product": "whatsapp", "to": to, "type": "template", "template": {"name": name, "language": {"code": "en"},
            "components": [{"type": "body", "parameters": [{"type": "text", "text": str(p)} for p in params]}]}}
    return http.post(WA, headers={"Authorization": f"Bearer {credentials.secret('WHATSAPP_TOKEN')}"}, json=body).json()

@on_trigger
def run(event):
    vals = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()
    rows = vals.get(spreadsheetId=SHEET_ID, range="Contacts!A2:D").execute().get("values", [])
    pending = [(i, r) for i, r in enumerate(rows, start=2) if len(r) > 2 and r[2] == "pending"]
    report_fetched(len(pending))
    if not pending:
        return {"summary": "Nothing pending"}
    for i, r in pending:
        res = wa_template(r[1], "store_update", [r[0]])
        status = ["sent", res["messages"][0]["id"]] if "messages" in res else ["failed", res.get("error", {}).get("message", "")]
        vals.update(spreadsheetId=SHEET_ID, range=f"Contacts!C{i}:D{i}", valueInputOption="RAW", body={"values": [status]}).execute()
    return {"sent": len(pending)}