🎫 Automate internal complaint resolution with Jotform, Gemini AI & Google Sheets

⚡ 216 views · 🎫 Ticket Management & Triage

Description

Workflow explaination video: https://youtu.be/z1grVuNOXMk

Prerequisites

Before running this workflow, you need to have the following set up:

1. JotForm: A form with fields for describing the issue and optionally naming the team member involved.
2. Google Sheet 1 (Issue Resolver Logic): A sheet with three columns: Issue Category, Normal Resolver, and Alternate Resolver. This sheet defines who handles which type of complaint.
3. Google Sheet 2 (Issue Logs): A sheet to store all submitted complaints. It needs columns like: Issue, The person Caused by, case_awarded_to, resolver_email, email_subject, email_body_html, submitted_time, and status.
4. Google Sheet 3 (Resolver Details): A simple sheet with two columns: resolver (e.g., “HR Team”) and email (e.g., “hr@yourcompany.com”).
5. Credentials: You need to have connected accounts (credentials) in n8n for JotForm, Google (a Service Account for Sheets and OAuth for Gmail), and a Gemini API Key.

Part 1: Initial Complaint Processing

This part of the workflow triggers when a new complaint is submitted, uses AI to process it, logs it, and sends an initial notification.

1. JotForm Trigger

• What it is: The starting point of the workflow.
• How it works: It constantly listens for new submissions on your specified JotForm. When someone fills out and submits the form, this node activates and pulls in all the submitted data (like the issue description and the person involved).

2. AI Agent

• What it is: The “brain” of the operation, which orchestrates several tools to make a decision.
• How it works: This node receives the complaint details from the JotForm Trigger. It follows a detailed prompt that instructs it to perform a sequence of tasks:
  1. Classify: Analyze the complaint description to categorize it.
  2. Reason: Use its connected “tools” to figure out the correct resolver based on your business logic.
  3. Generate: Create a complete email notification and format the final output as a JSON object.
• Connected Tools:
  • Google Gemini Chat Model: This is the actual language model that provides the intelligence. The AI Agent sends its prompt and the data to this model for processing.
  • Issue Resolver Allotment Logic Sheets tool: This allows the AI Agent to read your first Google Sheet. It can look up the issue category and find the designated “Normal Resolver” or “Alternate Resolver.”
  • Resolver Details Sheets tool: This allows the AI Agent to read your third Google Sheet. Once it knows the name of the resolver (e.g., “HR Team”), it uses this tool to find their corresponding email address.
  • Structured Output Parser: This ensures that the AI’s response is perfectly formatted into the required JSON structure (email, case_awarded_to, email_subject, etc.), making it reliable for the next steps.

3. Save Complaint (Google Sheets Node)

• What it is: The record-keeping step.
• How it works: This node takes the structured JSON output from the AI Agent and the original data from the JotForm Trigger. It then adds a new row to your second Google Sheet (“Issue Logs”), mapping each piece of data to its correct column (Issue, case_awarded_to, submitted_time, etc.).

4. Send a message (Gmail Node)

• What it is: The initial notification step.
• How it works: After the complaint is successfully logged, this node sends an email. It uses the resolver_email, email_subject, and email_body_html fields generated by the AI Agent to send a formal assignment email to the correct department or person.

Part 2: Daily Follow-Up

This second, independent part of the workflow runs every day to check for unresolved issues that are older than three days and sends a reminder.

1. Schedule Trigger

• What it is: The starting point for the daily check-up.
• How it works: Instead of waiting for a user action, this node activates automatically at a predefined time each day (e.g., 10:00 AM).

2. Get Complaint Logs (Google Sheets Node)

• What it is: The data gathering step for the follow-up process.
• How it works: When the schedule triggers, this node reads all the rows from your “Issue Logs” Google Sheet, bringing every recorded complaint into the workflow for evaluation.

3. If Node

• What it is: The decision-making step.
• How it works: This node examines each complaint passed to it from the previous step one by one. For each complaint, it performs a calculation: it finds the difference in days between the submitted_time and the current date. If that difference is greater than or equal to 3, the complaint is passed on to the next step. Otherwise, the workflow stops for that complaint.

4. Send a message1 (Gmail Node)

• What it is: The reminder email step.
• How it works: This node only receives complaints that met the “3 days or older” condition from the If node. For each of these old complaints, it sends a follow-up email to the resolver_email. The email body is dynamic, mentioning how many days have passed and including the original issue description to remind the resolver of the pending task.

🔗 Nodes Used

Google Sheets, Jotform Trigger, Gmail, Schedule Trigger, AI Agent, Structured Output Parser

📥 Import

Download workflow.json and import into n8n: Workflow menu → Import from File

📖 Importing guide · 🔑 Credential setup