Reporting des tickets de support classés avec Gemini AI, Typeform et Google Sheets

• •Informations d'identification Google Sheets API
• •Clé API Google Gemini

{
  "meta": {
    "instanceId": "5aaf4236c70e34e423fbdb2c7b754d19253a933bb1476d548f75848a01e473cf",
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "fc0f2155-ac48-4cd0-97d2-3eb81dac1757",
      "name": "Google Gemini Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        -840,
        120
      ],
      "parameters": {
        "options": {},
        "modelName": "models/gemini-2.0-flash"
      },
      "typeVersion": 1
    },
    {
      "id": "992c9e1b-ec91-4470-887f-e43c899cfb8c",
      "name": "Note adhésive 9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1080,
        -280
      ],
      "parameters": {
        "color": 4,
        "width": 500,
        "height": 1280,
        "content": "=======================================\n            WORKFLOW ASSISTANCE\n=======================================\nFor any questions or support, please contact:\n    Yaron@nofluff.online\n\nExplore more tips and tutorials here:\n   - YouTube: https://www.youtube.com/@YaronBeen/videos\n   - LinkedIn: https://www.linkedin.com/in/yaronbeen/\n=======================================\n\nAuthor:\nYaron Been\n![Yaron Been](https://1.gravatar.com/avatar/a4e4dcaa1f76ff5266bbf80e8df86d22efda890474c68f7796e72fd82e3f2375?size=512&d=initials)"
      },
      "typeVersion": 1
    },
    {
      "id": "4c76ce3a-6c1d-493b-b648-12c0d56fd9d8",
      "name": "Note adhésive 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -820,
        -640
      ],
      "parameters": {
        "width": 580,
        "content": "This workflow retrieves support ticket data from Google Sheets, counts how many tickets fall into each category, and sends a summary report via email."
      },
      "typeVersion": 1
    },
    {
      "id": "ab24dd90-ae20-496d-a963-6c89a65d2340",
      "name": "Note adhésive",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -960,
        -180
      ],
      "parameters": {
        "width": 170,
        "height": 80,
        "content": "Triggers the workflow  to start the summary process."
      },
      "typeVersion": 1
    },
    {
      "id": "968d3213-dafc-415f-8760-31e0fb55d94a",
      "name": "Déclencheur Formulaire",
      "type": "n8n-nodes-base.typeformTrigger",
      "position": [
        -940,
        -380
      ],
      "webhookId": "d02525cf-b9c1-492d-866c-e188bd781155",
      "parameters": {
        "formId": "lwldQPTN"
      },
      "typeVersion": 1.1
    },
    {
      "id": "d57b9477-f789-4ee2-a664-eba7f787a750",
      "name": "Note adhésive 2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -680,
        -160
      ],
      "parameters": {
        "width": 190,
        "height": 100,
        "content": "Uses Gemini model to classify or tag each support request into predefined categories."
      },
      "typeVersion": 1
    },
    {
      "id": "bb7ae6f3-5c32-47a3-a91b-97e4e4b4e0b1",
      "name": "Note adhésive 10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -400,
        -160
      ],
      "parameters": {
        "width": 170,
        "height": 100,
        "content": "Parses Gemini's response to extract only the relevant category label from the output."
      },
      "typeVersion": 1
    },
    {
      "id": "b7eb9e53-916f-4c34-babf-c8b23641b8a2",
      "name": "Catégorisation IA",
      "type": "@n8n/n8n-nodes-langchain.chainLlm",
      "position": [
        -740,
        -380
      ],
      "parameters": {
        "text": "=Classify the support request below:\nMessage: {{ $json['Your message'] }}\n\nReturn output with 'category' and 'sentiment' columns of JSON FILE:\n{\n  \"category\": one of [\"Billing\", \"Bug Report\", \"Feature Request\", \"How-To\", \"Complaint\"],\n  \"sentiment\": one of [\"Positive\", \"Neutral\", \"Negative\"]\n}\n ",
        "promptType": "define"
      },
      "typeVersion": 1.5
    },
    {
      "id": "eb3c3d0a-cdad-4792-a954-1de022d968dd",
      "name": "Extraire Catégorie",
      "type": "n8n-nodes-base.code",
      "position": [
        -360,
        -380
      ],
      "parameters": {
        "jsCode": "// Get the input string\nconst inputString = $json.text;\n\n// Remove code block markers using regex\nconst cleaned = inputString.replace(/```json|```/g, '').trim();\n\n// Parse the JSON\nconst data = JSON.parse(cleaned);\n\n// Extract the values\nconst category = data.category;\nconst sentiment = data.sentiment;\n\n// Return as output\nreturn [{ category, sentiment }];"
      },
      "typeVersion": 2
    },
    {
      "id": "b3c418d7-9113-46eb-af0a-2372de5a3eec",
      "name": "Stocker Données",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        -120,
        -380
      ],
      "parameters": {
        "columns": {
          "value": {
            "Name": "={{ $('Form Trigger').item.json['Enter your name'] }}",
            "Email": "={{ $('Form Trigger').item.json['Enter your email'] }}",
            "Message": "={{ $('Form Trigger').item.json['Your message'] }}",
            "Category": "={{ $json.category }}",
            "Sentiment": "={{ $json.sentiment }}",
            "Timestamp": "={{$now}}"
          },
          "schema": [
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Message",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Message",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Category",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Category",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Sentiment",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Sentiment",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Timestamp",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Timestamp",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1SY1fCAbsvyTzIBbmwZpdktbUFN4jKNIpF4GJ9lFKvPM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1SY1fCAbsvyTzIBbmwZpdktbUFN4jKNIpF4GJ9lFKvPM",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1SY1fCAbsvyTzIBbmwZpdktbUFN4jKNIpF4GJ9lFKvPM/edit?usp=drivesdk",
          "cachedResultName": "Support"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "51ef2daa-2c2e-409c-a103-6f915d3ef0d0",
      "name": "Récupérer Données Ticket",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        100,
        -380
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1SY1fCAbsvyTzIBbmwZpdktbUFN4jKNIpF4GJ9lFKvPM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1SY1fCAbsvyTzIBbmwZpdktbUFN4jKNIpF4GJ9lFKvPM",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1SY1fCAbsvyTzIBbmwZpdktbUFN4jKNIpF4GJ9lFKvPM/edit?usp=drivesdk",
          "cachedResultName": "Support"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "b3258a29-9bfa-4361-83d3-804aac76e976",
      "name": "Incrémenter Compteur",
      "type": "n8n-nodes-base.code",
      "position": [
        300,
        -380
      ],
      "parameters": {
        "jsCode": "const items = $input.all();\nconst counts = {};\nitems.forEach(item => {\n  const cat = item.json.Category;\n  if (cat) {\n    counts[cat] = (counts[cat] || 0) + 1;\n  }\n});\nreturn [{ json: { summary: counts } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "5c8193a0-6fab-494a-bd40-b26188c319b0",
      "name": "Résumé Ticket par Email",
      "type": "n8n-nodes-base.emailSend",
      "position": [
        500,
        -380
      ],
      "parameters": {
        "text": "=Hello,\n\nHere is the updated count of categorized support tickets:\n\nBilling: {{$json.summary.Billing || 0}}\nBug Report: {{$json.summary['Bug Report'] || 0}}\nFeature Request: {{$json.summary['Feature Request'] || 0}}\nHow-To: {{$json.summary['How-To'] || 0}}\nComplaint: {{$json.summary.Complaint || 0}}\n\nBest regards,\nSupport Tracker",
        "options": {},
        "subject": "Support Ticket Summary",
        "toEmail": "notify@example.com",
        "fromEmail": "you@example.com"
      },
      "typeVersion": 1
    },
    {
      "id": "3827fa87-5669-4217-88fe-978328f0ec4c",
      "name": "Note adhésive 6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -140,
        -180
      ],
      "parameters": {
        "width": 170,
        "height": 120,
        "content": "Appends each categorized and data ticket into a central Google Sheet for record keeping and later aggregation."
      },
      "typeVersion": 1
    },
    {
      "id": "c515d231-f260-4c5a-9895-19fe0c229762",
      "name": "Note adhésive 5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        80,
        -180
      ],
      "parameters": {
        "width": 170,
        "height": 120,
        "content": "Fetches all categorized entries from the Google Sheet to compute ticket counts by category."
      },
      "typeVersion": 1
    },
    {
      "id": "f6eb3bd4-c4fa-4b98-bc58-1930118e3b25",
      "name": "Note adhésive 4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        300,
        -180
      ],
      "parameters": {
        "width": 170,
        "height": 100,
        "content": "Groups tickets by category and counts the total per group to prepare data for reporting."
      },
      "typeVersion": 1
    },
    {
      "id": "5ec81b1c-14d1-4540-9acb-ad8c97632246",
      "name": "Note adhésive 3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        520,
        -180
      ],
      "parameters": {
        "width": 170,
        "height": 100,
        "content": "Sends an email with the total number of tickets per category from today’s run."
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "b3c418d7-9113-46eb-af0a-2372de5a3eec": {
      "main": [
        [
          {
            "node": "51ef2daa-2c2e-409c-a103-6f915d3ef0d0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "968d3213-dafc-415f-8760-31e0fb55d94a": {
      "main": [
        [
          {
            "node": "b7eb9e53-916f-4c34-babf-c8b23641b8a2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "eb3c3d0a-cdad-4792-a954-1de022d968dd": {
      "main": [
        [
          {
            "node": "b3c418d7-9113-46eb-af0a-2372de5a3eec",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b7eb9e53-916f-4c34-babf-c8b23641b8a2": {
      "main": [
        [
          {
            "node": "eb3c3d0a-cdad-4792-a954-1de022d968dd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b3258a29-9bfa-4361-83d3-804aac76e976": {
      "main": [
        [
          {
            "node": "5c8193a0-6fab-494a-bd40-b26188c319b0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "fc0f2155-ac48-4cd0-97d2-3eb81dac1757": {
      "ai_languageModel": [
        [
          {
            "node": "b7eb9e53-916f-4c34-babf-c8b23641b8a2",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "51ef2daa-2c2e-409c-a103-6f915d3ef0d0": {
      "main": [
        [
          {
            "node": "b3258a29-9bfa-4361-83d3-804aac76e976",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}