Automatisation du traitement des plaintes internes avec Jotform, Gemini AI et Google Sheets
Ceci est unTicket Management, AI Summarizationworkflow d'automatisation du domainecontenant 22 nœuds.Utilise principalement des nœuds comme If, Gmail, GoogleSheets, JotFormTrigger, Agent. Automatiser le traitement des réclamations internes avec Jotform, Gemini AI et Google Sheets
- •Compte Google et informations d'identification Gmail API
- •Informations d'identification Google Sheets API
- •Clé API Google Gemini
Nœuds utilisés (22)
Catégorie
{
"meta": {
"instanceId": "93f396852104089b8670e7494b0f3668b420464668ae4a8c1d6b4b5799f8e3ef",
"templateCredsSetupCompleted": true
},
"nodes": [
{
"id": "db4577d0-7cf2-487c-b9d8-374200d6dd3a",
"name": "Déclencheur JotForm",
"type": "n8n-nodes-base.jotFormTrigger",
"position": [
-2720,
-400
],
"webhookId": "69efca65-d609-4512-8f4c-53f51d15e175",
"parameters": {
"form": "252855912400050"
},
"credentials": {
"jotFormApi": {
"id": "I2Xm5wbkEW1qEJC5",
"name": "JotForm account"
}
},
"typeVersion": 1
},
{
"id": "c654df39-64c8-4ca8-9830-7248b29a320f",
"name": "Agent IA",
"type": "@n8n/n8n-nodes-langchain.agent",
"position": [
-2496,
-400
],
"parameters": {
"text": "=1. Analyze the content \"{{ $json['Please describe the issue or complaint in detail.'] }}\" and then classify the complaint into one of the five predefined categories:\n\n- Disciplinary / Behavior Issues\n- Interpersonal / Team Conflicts\n- Payroll / Compensation Issues\n- Harassment / Discrimination\n- Resource / Infrastructure Problems\n\n2. Use \"Issue Resolver Allotment Logic Sheets tool\" to know which type of issue should be alloted to which person in company. If in case the issue is caused by the Normal Resolver (Primary Assignee) allot the case to alternative resolver.\n\n3. Use \"Resolver Details Sheets tool\" to get the alloted person's email addresses.\n\n\nBefore giving an output make sure you ran step 2 and step 3 and used the both tools and then give the output in this form:\n\nexample output:\n{\n \"email\":\"email@example.com\",\n \"case_awarded_to\": \"HR Team\",\n \"Status\": \"Pending\",\n \"email_subject\": \"Confidential: Harassment / Discrimination\",\n \"email_body_html\": \"<!DOCTYPE html>\\n<html>\\n<head>\\n<style>\\n body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }\\n .container { padding: 20px; border: 1px solid #ddd; border-radius: 5px; max-width: 600px; margin: auto; }\\n h2 { color: #b22222; }\\n strong { color: #555; }\\n blockquote { background-color: #f9f9f9; border-left: 5px solid #ccc; padding: 10px; margin: 15px 0; }\\n .footer { margin-top: 20px; font-size: 0.9em; color: #777; }\\n</style>\\n</head>\\n<body>\\n<div class=\\\"container\\\">\\n <h2>Confidential Compliance Issue Assigned</h2>\\n <p>Dear HR Team,</p>\\n <p>An internal compliance issue has been automatically logged and assigned to you for investigation and resolution. Please handle this matter with the utmost confidentiality and urgency.</p>\\n \\n <h3>Issue Details:</h3>\\n <ul>\\n <li><strong>Issue Classification:</strong> Harassment / Discrimination</li>\\n <li><strong>Team Member Involved:</strong> Jane Smith</li>\\n </ul>\\n\\n <h3>Detailed Description from Submission:</h3>\\n <blockquote>\\n Jane Smith has been making inappropriate and biased comments during team meetings. She consistently interrupts female colleagues and has created a hostile environment for several members of the team. This has been ongoing for the past two months.\\n </blockquote>\\n\\n <h3>Next Steps:</h3>\\n <p>Please conduct a thorough and impartial investigation into this matter. Upon completion, you are required to report your findings and proposed resolution to the <strong>External Compliance Officer</strong>.</p>\\n\\n <p class=\\\"footer\\\">This is an automated notification from the CEO AI Internal Compliance Assistant. Please do not reply directly to this email.</p>\\n</div>\\n</body>\\n</html>\"\n}",
"options": {},
"promptType": "define",
"hasOutputParser": true
},
"typeVersion": 2.2
},
{
"id": "3fc62e02-5028-47b6-ad2a-944b27d78184",
"name": "Modèle de chat Google Gemini",
"type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
"position": [
-2736,
-176
],
"parameters": {
"options": {}
},
"credentials": {
"googlePalmApi": {
"id": "qsaK3VMNWQDWLweQ",
"name": "Google Gemini(PaLM) Api account"
}
},
"typeVersion": 1
},
{
"id": "575ea7f1-69e7-45ee-a139-bab28b7f69b4",
"name": "Analyseur de sortie structurée",
"type": "@n8n/n8n-nodes-langchain.outputParserStructured",
"position": [
-2112,
-176
],
"parameters": {
"jsonSchemaExample": "{\n \"email\":\"email@example.com\",\n \"case_awarded_to\": \"HR Team\",\n \"Status\": \"Pending\",\n \"email_subject\": \"Confidential: Harassment / Discrimination\",\n \"email_body_html\": \"<!DOCTYPE html>\\n<html>\\n<head>\\n<style>\\n body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }\\n .container { padding: 20px; border: 1px solid #ddd; border-radius: 5px; max-width: 600px; margin: auto; }\\n h2 { color: #b22222; }\\n strong { color: #555; }\\n blockquote { background-color: #f9f9f9; border-left: 5px solid #ccc; padding: 10px; margin: 15px 0; }\\n .footer { margin-top: 20px; font-size: 0.9em; color: #777; }\\n</style>\\n</head>\\n<body>\\n<div class=\\\"container\\\">\\n <h2>Confidential Compliance Issue Assigned</h2>\\n <p>Dear HR Team,</p>\\n <p>An internal compliance issue has been automatically logged and assigned to you for investigation and resolution. Please handle this matter with the utmost confidentiality and urgency.</p>\\n \\n <h3>Issue Details:</h3>\\n <ul>\\n <li><strong>Issue Classification:</strong> Harassment / Discrimination</li>\\n <li><strong>Team Member Involved:</strong> Jane Smith</li>\\n </ul>\\n\\n <h3>Detailed Description from Submission:</h3>\\n <blockquote>\\n Jane Smith has been making inappropriate and biased comments during team meetings. She consistently interrupts female colleagues and has created a hostile environment for several members of the team. This has been ongoing for the past two months.\\n </blockquote>\\n\\n <h3>Next Steps:</h3>\\n <p>Please conduct a thorough and impartial investigation into this matter. Upon completion, you are required to report your findings and proposed resolution to the <strong>External Compliance Officer</strong>.</p>\\n\\n <p class=\\\"footer\\\">This is an automated notification from the CEO AI Internal Compliance Assistant. Please do not reply directly to this email.</p>\\n</div>\\n</body>\\n</html>\"\n}"
},
"typeVersion": 1.3
},
{
"id": "deaa23e1-68ff-4454-8ce8-0ffce19684be",
"name": "Envoyer un message",
"type": "n8n-nodes-base.gmail",
"position": [
-1904,
-400
],
"webhookId": "6fc0354c-f3b3-4eb5-9695-e5015c393aae",
"parameters": {
"sendTo": "={{ $json.resolver_email }}",
"message": "={{ $('AI Agent').item.json.output.email_body_html }}",
"options": {
"appendAttribution": false
},
"subject": "={{ $('AI Agent').item.json.output.email_subject }}"
},
"credentials": {
"gmailOAuth2": {
"id": "wreuF4NMkPPxXkKm",
"name": "Abhiram.bvb"
}
},
"typeVersion": 2.1
},
{
"id": "7d84068a-e287-4490-b609-9998ccacdd24",
"name": "Note autocollante",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2784,
-544
],
"parameters": {
"height": 288,
"content": "#### The workflow gets triggered when jotform got submitted\n* Grabs the user submitted details in form"
},
"typeVersion": 1
},
{
"id": "ce07f8f6-e487-4b5b-a7df-2fa42e95cbca",
"name": "Outil Feuilles Détails du Résolveur",
"type": "n8n-nodes-base.googleSheetsTool",
"position": [
-2528,
-176
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1NxWZ5zitUp1zZDnieyOhv1ACEvmkpFUd7oYG7y_2sqs/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1NxWZ5zitUp1zZDnieyOhv1ACEvmkpFUd7oYG7y_2sqs",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1NxWZ5zitUp1zZDnieyOhv1ACEvmkpFUd7oYG7y_2sqs/edit?usp=drivesdk",
"cachedResultName": "Resolver Details"
},
"authentication": "serviceAccount"
},
"credentials": {
"googleApi": {
"id": "mYRmmCMyWcLawmcE",
"name": "Google Service Account account"
}
},
"typeVersion": 4.7
},
{
"id": "b8e86678-089c-48c9-a82a-9f5a9c47153b",
"name": "Outil Feuilles Logique d'Affectation du Résolveur",
"type": "n8n-nodes-base.googleSheetsTool",
"position": [
-2320,
-176
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1xEq6Nq9jR7BUBzwLoPd3HyhElzawjY4kKwWsfSzlX-A/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1xEq6Nq9jR7BUBzwLoPd3HyhElzawjY4kKwWsfSzlX-A",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1xEq6Nq9jR7BUBzwLoPd3HyhElzawjY4kKwWsfSzlX-A/edit?usp=drivesdk",
"cachedResultName": "Issue Resolver Allotment Logic"
},
"authentication": "serviceAccount"
},
"credentials": {
"googleApi": {
"id": "mYRmmCMyWcLawmcE",
"name": "Google Service Account account"
}
},
"typeVersion": 4.7
},
{
"id": "61b4de42-9a80-4931-bed4-ccc58c96e638",
"name": "Déclencheur programmé",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-2704,
176
],
"parameters": {
"rule": {
"interval": [
{
"triggerAtHour": 10
}
]
}
},
"typeVersion": 1.2
},
{
"id": "bf05ebb3-69ed-4ac7-9a3d-8dcf3bc44a72",
"name": "Si",
"type": "n8n-nodes-base.if",
"position": [
-2208,
176
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "loose"
},
"combinator": "and",
"conditions": [
{
"id": "668e2c22-0e4f-4477-8cdd-2a0f18b91d24",
"operator": {
"type": "number",
"operation": "gte"
},
"leftValue": "={{ Math.floor(Math.abs(DateTime.fromFormat($json.submitted_time, 'dd-MM-yyyy HH:mm').diff($now, 'days').days)) }}",
"rightValue": "=3"
}
]
},
"looseTypeValidation": true
},
"typeVersion": 2.2
},
{
"id": "40c59fba-2e1d-4518-8769-7a23a33eff55",
"name": "Envoyer un message1",
"type": "n8n-nodes-base.gmail",
"position": [
-1952,
160
],
"webhookId": "de06eb4f-8fda-4e98-baab-951dc63176d7",
"parameters": {
"sendTo": "={{ $json.resolver_email }}",
"message": "=Hey,\n\nIt's been {{ Math.floor(Math.abs(DateTime.fromFormat($json.submitted_time, 'dd-MM-yyyy HH:mm').diff($now, 'days').days)) }} days since I shared below issue with you. What was the update? \n\nThe Issue:\n{{ $json.Issue }}\n\nPlease reply to this email about the status of this issue.\n\nThanks,\nCEO Internal Compliance Team",
"options": {
"appendAttribution": false
},
"subject": "What's the update this issue?",
"emailType": "text"
},
"credentials": {
"gmailOAuth2": {
"id": "wreuF4NMkPPxXkKm",
"name": "Abhiram.bvb"
}
},
"typeVersion": 2.1
},
{
"id": "593fb913-e305-41bf-8083-1680f1b75fa0",
"name": "Note autocollante1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2528,
-544
],
"parameters": {
"width": 272,
"height": 288,
"content": "#### AI Agent Node\n* Understands the context of issue and allot the issue to relevent department"
},
"typeVersion": 1
},
{
"id": "e41da37d-d5aa-4a96-af02-744e2b5ab2c0",
"name": "Note autocollante2",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2240,
-544
],
"parameters": {
"height": 288,
"content": "#### Google Sheets Node\n* Saves the complaint in the log with relevent department allotment, email subject and body for sending to the problem solver"
},
"typeVersion": 1
},
{
"id": "4cee949d-a6f7-4aa4-8c44-d1744cc9e04a",
"name": "Note autocollante3",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1984,
-544
],
"parameters": {
"height": 288,
"content": "#### Gmail Node\n* Sends an email with dynamic AI written Email subject and body"
},
"typeVersion": 1
},
{
"id": "dcfc4819-1b09-4005-a7bf-1434e90f0bb3",
"name": "Note autocollante4",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2784,
48
],
"parameters": {
"height": 288,
"content": "#### Schedule Trigger\n* Runs the workflow once per day"
},
"typeVersion": 1
},
{
"id": "27afe67e-dd43-4a86-aabb-d173670f8ed5",
"name": "Note autocollante5",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2528,
48
],
"parameters": {
"height": 288,
"content": "#### Google Sheets Node\n* Get the issue logs that we have saved in our google sheets"
},
"typeVersion": 1
},
{
"id": "9bf9b0eb-e740-48c1-85d4-034b4e837675",
"name": "Sauvegarder la réclamation",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2176,
-400
],
"parameters": {
"columns": {
"value": {
"Issue": "={{ $('JotForm Trigger').item.json['Please describe the issue or complaint in detail.'] }}",
"email_subject": "={{ $json.output.email_subject }}",
"resolver_email": "={{ $json.output.email }}",
"submitted_time": "={{ $now.toFormat('dd-MM-yyyy HH:mm') }}",
"case_awarded_to": "={{ $json.output.case_awarded_to }}",
"email_body_html": "={{ $json.output.email_body_html }}",
"The person Caused by": "={{ $('JotForm Trigger').item.json['What is the name of the team member involved? (Optional, you may leave blank for anonymity)'] }}"
},
"schema": [
{
"id": "Issue",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Issue",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "The person Caused by",
"type": "string",
"display": true,
"required": false,
"displayName": "The person Caused by",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "case_awarded_to",
"type": "string",
"display": true,
"required": false,
"displayName": "case_awarded_to",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "resolver_email",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "resolver_email",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "email_subject",
"type": "string",
"display": true,
"required": false,
"displayName": "email_subject",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "email_body_html",
"type": "string",
"display": true,
"required": false,
"displayName": "email_body_html",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "submitted_time",
"type": "string",
"display": true,
"required": false,
"displayName": "submitted_time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "status",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "status",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Issue"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "appendOrUpdate",
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1KU7cqmE5xmi1EmDs81mC8-EPJcrn-U-JrvRBe2dcZ9I/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1KU7cqmE5xmi1EmDs81mC8-EPJcrn-U-JrvRBe2dcZ9I",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1KU7cqmE5xmi1EmDs81mC8-EPJcrn-U-JrvRBe2dcZ9I/edit?usp=drivesdk",
"cachedResultName": "Issue Logs"
},
"authentication": "serviceAccount"
},
"credentials": {
"googleApi": {
"id": "mYRmmCMyWcLawmcE",
"name": "Google Service Account account"
}
},
"typeVersion": 4.7
},
{
"id": "3da652b3-7c58-42fb-8d03-d34d0371b022",
"name": "Obtenir les journaux de réclamation",
"type": "n8n-nodes-base.googleSheets",
"position": [
-2464,
176
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": "gid=0",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1KU7cqmE5xmi1EmDs81mC8-EPJcrn-U-JrvRBe2dcZ9I/edit#gid=0",
"cachedResultName": "Sheet1"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1KU7cqmE5xmi1EmDs81mC8-EPJcrn-U-JrvRBe2dcZ9I",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1KU7cqmE5xmi1EmDs81mC8-EPJcrn-U-JrvRBe2dcZ9I/edit?usp=drivesdk",
"cachedResultName": "Issue Logs"
},
"authentication": "serviceAccount"
},
"credentials": {
"googleApi": {
"id": "mYRmmCMyWcLawmcE",
"name": "Google Service Account account"
}
},
"typeVersion": 4.7
},
{
"id": "804184a2-71b0-44ad-84c7-da2cc5c259e8",
"name": "Note autocollante6",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2272,
48
],
"parameters": {
"height": 288,
"content": "#### IF Node\n* Checks if there is an pending issue which is older than 3 days"
},
"typeVersion": 1
},
{
"id": "80ab7718-8d0f-46cd-a7a4-c9ac56e5a6e4",
"name": "Note autocollante7",
"type": "n8n-nodes-base.stickyNote",
"position": [
-2016,
48
],
"parameters": {
"height": 288,
"content": "#### Gmail Node\n* Sends a follow up email about the issue status."
},
"typeVersion": 1
},
{
"id": "27148215-6ccf-46fc-b028-78c1fd67dfb9",
"name": "Note autocollante8",
"type": "n8n-nodes-base.stickyNote",
"position": [
-3072,
-464
],
"parameters": {
"height": 80,
"content": "#### Part 1 - Collects Issues and allot them to the relevent department"
},
"typeVersion": 1
},
{
"id": "85a85d67-3d9a-40b4-9e3c-d8be5af5399e",
"name": "Note autocollante9",
"type": "n8n-nodes-base.stickyNote",
"position": [
-3072,
160
],
"parameters": {
"height": 80,
"content": "#### Part 2 - Daily Follow-Ups After 3 Days Until Resolution"
},
"typeVersion": 1
}
],
"pinData": {},
"connections": {
"bf05ebb3-69ed-4ac7-9a3d-8dcf3bc44a72": {
"main": [
[
{
"node": "40c59fba-2e1d-4518-8769-7a23a33eff55",
"type": "main",
"index": 0
}
]
]
},
"c654df39-64c8-4ca8-9830-7248b29a320f": {
"main": [
[
{
"node": "9bf9b0eb-e740-48c1-85d4-034b4e837675",
"type": "main",
"index": 0
}
]
]
},
"9bf9b0eb-e740-48c1-85d4-034b4e837675": {
"main": [
[
{
"node": "deaa23e1-68ff-4454-8ce8-0ffce19684be",
"type": "main",
"index": 0
}
]
]
},
"db4577d0-7cf2-487c-b9d8-374200d6dd3a": {
"main": [
[
{
"node": "c654df39-64c8-4ca8-9830-7248b29a320f",
"type": "main",
"index": 0
}
]
]
},
"61b4de42-9a80-4931-bed4-ccc58c96e638": {
"main": [
[
{
"node": "3da652b3-7c58-42fb-8d03-d34d0371b022",
"type": "main",
"index": 0
}
]
]
},
"3da652b3-7c58-42fb-8d03-d34d0371b022": {
"main": [
[
{
"node": "bf05ebb3-69ed-4ac7-9a3d-8dcf3bc44a72",
"type": "main",
"index": 0
}
]
]
},
"3fc62e02-5028-47b6-ad2a-944b27d78184": {
"ai_languageModel": [
[
{
"node": "c654df39-64c8-4ca8-9830-7248b29a320f",
"type": "ai_languageModel",
"index": 0
}
]
]
},
"575ea7f1-69e7-45ee-a139-bab28b7f69b4": {
"ai_outputParser": [
[
{
"node": "c654df39-64c8-4ca8-9830-7248b29a320f",
"type": "ai_outputParser",
"index": 0
}
]
]
},
"ce07f8f6-e487-4b5b-a7df-2fa42e95cbca": {
"ai_tool": [
[
{
"node": "c654df39-64c8-4ca8-9830-7248b29a320f",
"type": "ai_tool",
"index": 0
}
]
]
},
"b8e86678-089c-48c9-a82a-9f5a9c47153b": {
"ai_tool": [
[
{
"node": "c654df39-64c8-4ca8-9830-7248b29a320f",
"type": "ai_tool",
"index": 0
}
]
]
}
}
}Comment utiliser ce workflow ?
Copiez le code de configuration JSON ci-dessus, créez un nouveau workflow dans votre instance n8n et sélectionnez "Importer depuis le JSON", collez la configuration et modifiez les paramètres d'authentification selon vos besoins.
Dans quelles scénarios ce workflow est-il adapté ?
Avancé - Gestion des tickets, Résumé IA
Est-ce payant ?
Ce workflow est entièrement gratuit et peut être utilisé directement. Veuillez noter que les services tiers utilisés dans le workflow (comme l'API OpenAI) peuvent nécessiter un paiement de votre part.
Workflows recommandés
iamvaar
@iamvaarPartager ce workflow