Send portfolio risk reports with Google Sheets, GPT-4o-mini and Gmail

Quick Overview This workflow receives a portfolio via webhook (uploaded CSV or Google Sheets), calculates allocation and concentration risk metrics, generates an OpenAI HTML risk report, emails it through Gmail, and returns a JSON API response with the report status.

How it works Receives a POST webhook request containing a portfolio source type plus user name and email. Loads holdings either by decoding an uploaded base64 CSV into rows or by reading rows from Google Sheets. Combines all holdings into a single dataset and attaches user and source metadata. Calculates portfolio totals, position weights, sector and asset-class breakdowns, concentration flags, top gainers/losers, and an overall risk score and label. If the portfolio is valid, sends the computed metrics to OpenAI to generate a client-ready HTML risk narrative. Builds a formatted HTML email that includes the AI analysis plus sector and top-holdings tables, then sends it to the user via Gmail and returns a success JSON response. If the portfolio is empty or invalid, emails a failure notice via Gmail (when an email is provided) and returns a 422 JSON error response.

Setup Create and connect credentials for Google Sheets OAuth2, OpenAI, and Gmail OAuth2. Configure the webhook endpoint by copying the production URL and sending POST requests to the /portfolio-analyze path. For CSV submissions, include base64 CSV content in body.fileData (or body.csvBase64/body.file) and ensure the columns include ticker, company, sector, quantity, avg_buy_price, current_price, and asset_class. For Google Sheets submissions, update the spreadsheet document ID and sheet (gid) to the holdings table you want to analyze. Ensure the request includes userEmail (body, query, or x-user-email header) so the workflow can deliver the report or validation error message.