Run an AI student helpdesk with OpenAI, Google Sheets, Slack, and Gmail

Description

Quick overview
This workflow handles incoming student chat messages, answers questions using a Google Sheets knowledge base with OpenAI, escalates unresolved or distressed conversations to Slack, logs conversations and FAQ gaps back to Google Sheets, and emails a weekly metrics report via Gmail.

How it works
Receives a student chat message via an n8n Chat trigger and extracts the session ID, student name, and message text.
Builds and updates a per-session conversation history and turn count to maintain context across messages.
Reads Q&A entries from a Google Sheets knowledge base and formats them into prompt-ready text.
Sends the student message, conversation history, and knowledge base to OpenAI (gpt-4o-mini) to generate a constrained reply that can flag unresolved or distressed cases.
Checks the AI reply for unresolved and distressed markers and escalates the full context to a Slack channel when the student appears distressed or the query remains unresolved after two turns.
Appends each exchange to a Google Sheets conversation log and records unresolved questions to a separate “FAQ Gaps” sheet for knowledge base updates.
Runs every Monday at 9 AM to read the conversation log from Google Sheets, calculate weekly metrics, and email an HTML report to the admin using Gmail.

Setup
Add credentials for Google Sheets (OAuth2), OpenAI (API key), Slack (OAuth2), and Gmail (OAuth2).
Create a Google Sheets spreadsheet with tabs for the knowledge base, “Conversation log sheet”, and “FAQ Gaps”, and update the workflow’s spreadsheet ID and sheet/tab selections as needed.
Set the Slack channel ID in the escalation and error-alert Slack nodes to your counsellor/ops channel.
Replace the Gmail recipient (sendTo) in the weekly report node with your admin email address.
If you use the chat webhook externally, copy the Chat trigger URL and configure it in your chat interface or source application.