Analyse du questionnaire du candidat

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

{
  "id": "ncJgeZeVdsws0GpM",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Candidate Questionarie analysis",
  "tags": [],
  "nodes": [
    {
      "id": "5745071e-e64f-44ab-a972-65a3b7ef2a06",
      "name": "Surveiller les nouvelles réponses aux questionnaires",
      "type": "n8n-nodes-base.googleSheetsTrigger",
      "position": [
        -80,
        816
      ],
      "parameters": {
        "options": {},
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 3745099,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1SFFWWqefQ6tcaEK0_Ovy9nVgyxS4LQPvno1i8fi39MY/edit#gid=3745099",
          "cachedResultName": "Form Responses 1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1SFFWWqefQ6tcaEK0_Ovy9nVgyxS4LQPvno1i8fi39MY",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1SFFWWqefQ6tcaEK0_Ovy9nVgyxS4LQPvno1i8fi39MY/edit?usp=drivesdk",
          "cachedResultName": "BD Questionarie"
        }
      },
      "credentials": {
        "googleSheetsTriggerOAuth2Api": {
          "id": "tjY8nawuuRO1rxAE",
          "name": "Google Sheets Trigger account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "cb360f3e-257f-49d8-930a-b3d31841edbe",
      "name": "Évaluateur IA de questionnaire",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        144,
        816
      ],
      "parameters": {
        "text": "=Role:\nQuestions and Candidate Answers:\nQ1: What is Business development according to you\nA1: {{ $json[\"What is BD according to you?\"] }}\nQ2: What is SWOT analysis\nA2: {{ $json[\"What is SWOT analysis\"] }}\n\nPlease evaluate the above responses based on knowledge depth, problem-solving ability, and communication clarity.",
        "options": {
          "systemMessage": "=You are an AI evaluator for candidate screening. \nYou will be given:\n- A set of role-specific questions.\n- A candidate's answers.\n\nYour job:\n1. Evaluate the quality of the answers against the intent of each question.\n2. Score the questionnaire (0–30 by default, or adjust scale if specified).\n3. Provide key takeaways (strengths, weaknesses, red flags, or standout qualities).\n\nOutput ONLY in JSON with this structure:\n{\n  \"questionnaire_score\": <number>,\n  \"key_takeaways\": \"<summary of candidate performance>\"\n}"
        },
        "promptType": "define"
      },
      "typeVersion": 2.1
    },
    {
      "id": "ed9b6126-dadf-4bd6-851f-dbe3e421434e",
      "name": "Modèle Azure OpenAI GPT-4",
      "type": "@n8n/n8n-nodes-langchain.lmChatAzureOpenAi",
      "position": [
        240,
        1040
      ],
      "parameters": {
        "model": "gpt-4o-mini",
        "options": {}
      },
      "credentials": {
        "azureOpenAiApi": {
          "id": "C3WzT18XqF8OdVM6",
          "name": "Azure Open AI account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "3817a7af-23d1-445d-abc3-b68556ff5ec4",
      "name": "Rechercher les données du profil candidat",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        768,
        816
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1424038785,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1JlXxy90s0we_IqErHyvomrJSijb8pd4H91hOUCH6xCA/edit#gid=1424038785",
          "cachedResultName": "Sheet2"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1JlXxy90s0we_IqErHyvomrJSijb8pd4H91hOUCH6xCA",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1JlXxy90s0we_IqErHyvomrJSijb8pd4H91hOUCH6xCA/edit?usp=drivesdk",
          "cachedResultName": "Resume store"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "5cd8411b-1851-46e5-833b-2723d14dd509",
      "name": "Analyser les résultats de l'évaluation IA",
      "type": "n8n-nodes-base.code",
      "position": [
        544,
        816
      ],
      "parameters": {
        "jsCode": "return items.map(item => {\n  let text = item.json.output;\n\n  // Remove code fences like ```json ... ```\n  text = text.replace(/```json|```/g, \"\").trim();\n\n  let parsed = {};\n  try {\n    parsed = JSON.parse(text);\n  } catch (err) {\n    parsed = { error: \"Failed to parse JSON\", raw: text };\n  }\n\n  return { json: parsed };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "abfe1e72-3291-4bfe-ab80-8b9339ea0aa9",
      "name": "Calculer les scores combinés",
      "type": "n8n-nodes-base.set",
      "position": [
        992,
        816
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "1fb84fd6-db3f-432d-baee-2e3e2cfd404a",
              "name": "=final score",
              "type": "number",
              "value": "={{ $json.Score + $('Parse AI Evaluation Results').item.json.questionnaire_score }}\n\n"
            },
            {
              "id": "d257e53b-6887-4cdc-8f50-f90715312b81",
              "name": "Questionarie Score",
              "type": "string",
              "value": "={{ $('Parse AI Evaluation Results').item.json.questionnaire_score }}"
            }
          ]
        }
      },
      "typeVersion": 3.4,
      "alwaysOutputData": true
    },
    {
      "id": "895de667-d515-4331-a2be-e3079af6b468",
      "name": "Mettre à jour la base de données des candidats",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1216,
        816
      ],
      "parameters": {
        "columns": {
          "value": {
            "Name": "John Doe",
            "Final score": "={{ $json[\"final score\"] }}",
            "Questionarie Score": "={{ $json[\"Questionarie Score\"] }}"
          },
          "schema": [
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Email",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Email",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Phone ",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Phone ",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Years of experience",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Years of experience",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "skills",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "skills",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "curent role",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "curent role",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "education",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "education",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Score",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Questionarie Score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Questionarie Score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Final score",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Final score",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Summary",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Summary",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Skill name",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Skill name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Skill level",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Skill level",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Skill years",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Skill years",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Name"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "appendOrUpdate",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1424038785,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1JlXxy90s0we_IqErHyvomrJSijb8pd4H91hOUCH6xCA/edit#gid=1424038785",
          "cachedResultName": "Sheet2"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1JlXxy90s0we_IqErHyvomrJSijb8pd4H91hOUCH6xCA",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1JlXxy90s0we_IqErHyvomrJSijb8pd4H91hOUCH6xCA/edit?usp=drivesdk",
          "cachedResultName": "Resume store"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "s4dP1fNuVZ2gWvs3",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.6
    },
    {
      "id": "60456c78-e211-4bfe-852d-81467d3d5fda",
      "name": "Note adhésive1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -448,
        512
      ],
      "parameters": {
        "width": 320,
        "height": 352,
        "content": "## 🔔 AUTOMATIC TRIGGER\n\n**Monitor New Questionnaire Responses**\n\n• **Purpose**: Automatically detects when candidates submit questionnaire forms\n• **Monitoring**: Polls Google Sheets every minute for new responses\n• **Source**: 'BD Questionarie' spreadsheet, 'Form Responses 1' sheet\n• **Trigger**: Activates immediately when new form submission is detected\n• **Reliability**: Continuous monitoring ensures no responses are missed\n• **Real-time Processing**: Enables immediate evaluation of candidate submissions"
      },
      "typeVersion": 1
    },
    {
      "id": "eb5a2306-50c1-4222-9778-8dbd51546ae8",
      "name": "Note adhésive2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        96,
        368
      ],
      "parameters": {
        "width": 320,
        "height": 420,
        "content": "## 🤖 AI EVALUATION ENGINE\n\n**AI Questionnaire Evaluator**\n\n• **Purpose**: Intelligently analyzes candidate responses to role-specific questions\n• **Evaluation Criteria**:\n  - Knowledge depth and accuracy\n  - Problem-solving ability\n  - Communication clarity and structure\n• **Scoring**: 0-30 points for questionnaire performance\n• **Questions Analyzed**:\n  - Business Development concepts\n  - SWOT analysis understanding\n• **Output**: JSON with score and detailed key takeaways"
      },
      "typeVersion": 1
    },
    {
      "id": "29578b3f-a8ff-42d9-9b30-61821ad9a807",
      "name": "Note adhésive3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -128,
        1088
      ],
      "parameters": {
        "width": 320,
        "height": 280,
        "content": "## ⚙️ AI MODEL CONFIGURATION\n\n**Azure OpenAI GPT-4 Model**\n\n• **Model**: GPT-4o-mini for efficient evaluation\n• **Purpose**: Powers the questionnaire analysis engine\n• **Capabilities**: \n  - Natural language understanding\n  - Structured JSON output\n  - Business domain knowledge\n• **Connection**: Azure OpenAI service integration\n• **Performance**: Optimized for consistent evaluation quality"
      },
      "typeVersion": 1
    },
    {
      "id": "2f0e9fba-d447-4951-ac95-7b71ed965e7a",
      "name": "Note adhésive4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        544,
        400
      ],
      "parameters": {
        "width": 320,
        "height": 340,
        "content": "## 🔧 DATA PROCESSING\n\n**Parse AI Evaluation Results**\n\n• **Purpose**: Converts AI text output into structured JSON format\n• **Process**: \n  - Removes markdown code fences (```json)\n  - Parses JSON structure safely\n  - Handles parsing errors gracefully\n• **Output**: Clean JSON object with questionnaire_score and key_takeaways\n• **Error Handling**: Returns error details if JSON parsing fails\n• **Data Quality**: Ensures consistent data structure for downstream processing"
      },
      "typeVersion": 1
    },
    {
      "id": "2ba1d0f1-4531-4b09-8283-df69a1425360",
      "name": "Note adhésive5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        656,
        992
      ],
      "parameters": {
        "width": 320,
        "height": 352,
        "content": "## 📊 DATA RETRIEVAL\n\n**Lookup Candidate Profile Data**\n\n• **Purpose**: Retrieves existing candidate information from central database\n• **Source**: 'Resume store' spreadsheet, Sheet2\n• **Data Retrieved**: \n  - Current evaluation scores\n  - Candidate profile information\n  - Previous assessment results\n• **Matching**: Links questionnaire responses to existing candidate records\n• **Integration**: Enables combining multiple evaluation sources"
      },
      "typeVersion": 1
    },
    {
      "id": "512b6d1c-c678-4d9f-9b12-0243b2b44dc0",
      "name": "Note adhésive6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        960,
        384
      ],
      "parameters": {
        "width": 320,
        "height": 372,
        "content": "## 🧮 SCORE CALCULATION\n\n**Calculate Combined Scores**\n\n• **Purpose**: Combines questionnaire score with existing evaluation score\n• **Calculations**:\n  - **Final Score**: Existing Score + Questionnaire Score\n  - **Questionnaire Score**: Extracted from AI evaluation (0-30)\n• **Data Fields Created**:\n  - 'final score': Combined total score\n  - 'Questionarie Score': Individual questionnaire performance\n• **Scoring Logic**: Additive scoring model for comprehensive evaluation"
      },
      "typeVersion": 1
    },
    {
      "id": "605aa0a9-3b28-4251-b55a-a0d5d6b8e074",
      "name": "Note adhésive7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1200,
        1008
      ],
      "parameters": {
        "width": 320,
        "height": 360,
        "content": "## 💾 DATABASE UPDATE\n\n**Update Candidate Database**\n\n• **Purpose**: Saves questionnaire results and updated scores to candidate database\n• **Update Method**: Append or update existing records using name matching\n• **Data Stored**:\n  - Questionnaire Score (0-30 points)\n  - Final Score (combined evaluation)\n  - Maintains all existing candidate data\n• **Matching Key**: Uses candidate name for record identification\n• **Database Integrity**: Preserves existing candidate information while adding new scores"
      },
      "typeVersion": 1
    },
    {
      "id": "2bd8f006-8113-4ed2-a56a-1973f6a8ecec",
      "name": "Note adhésive8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -976,
        704
      ],
      "parameters": {
        "width": 480,
        "height": 620,
        "content": "## 📝 CANDIDATE QUESTIONNAIRE ANALYSIS WORKFLOW\n\n**🎯 Purpose:** Automatically evaluates candidate questionnaire responses and integrates scores with existing evaluations for comprehensive assessment\n\n**🔄 Automated Process Flow:**\n1. **Real-time Monitoring**: Continuously watches for new questionnaire submissions\n2. **AI Analysis**: Evaluates responses using advanced language models\n3. **Score Processing**: Parses and validates evaluation results\n4. **Data Integration**: Combines questionnaire scores with existing candidate data\n5. **Score Calculation**: Creates comprehensive final scores\n6. **Database Update**: Maintains centralized candidate evaluation records\n\n**📊 Evaluation Framework:**\n• **Questionnaire Score**: 0-30 points based on:\n  - Knowledge depth and accuracy\n  - Problem-solving approach\n  - Communication clarity\n• **Final Score**: Combined evaluation (Resume + Questionnaire)\n\n**🏗️ System Integration:**\n• **Google Forms**: Questionnaire collection\n• **Google Sheets**: Data storage and monitoring\n• **Azure OpenAI**: Intelligent evaluation engine\n• **Automated Processing**: Real-time response handling\n\n**✨ Key Benefits:**\n• **Immediate Processing**: No delay between submission and evaluation\n• **Objective Analysis**: AI-powered consistent evaluation criteria\n• **Comprehensive Scoring**: Combines multiple assessment sources\n• **Scalable Solution**: Handles high volume of questionnaire responses\n• **Quality Insights**: Detailed takeaways for each candidate evaluation"
      },
      "typeVersion": 1
    },
    {
      "id": "5dceeae5-f433-42ae-9a42-8d8491133104",
      "name": "Note adhésive9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1616,
        640
      ],
      "parameters": {
        "width": 380,
        "height": 520,
        "content": "## ⚙️ CONFIGURATION & CUSTOMIZATION\n\n**🔧 Questionnaire Questions (Customizable):**\n• **Current Questions**:\n  - \"What is Business Development according to you?\"\n  - \"What is SWOT analysis?\"\n• **Question Types**: Role-specific knowledge assessment\n• **Scoring Range**: 0-30 points per questionnaire\n\n**📊 Scoring Configuration:**\n• **Questionnaire Weight**: 30 points maximum\n• **Combined Scoring**: Additive model (Resume + Questionnaire)\n• **Evaluation Criteria**:\n  - Knowledge depth (40%)\n  - Problem-solving ability (30%) \n  - Communication clarity (30%)\n\n**🔄 Monitoring Settings:**\n• **Poll Frequency**: Every minute\n• **Response Source**: Google Forms → Google Sheets\n• **Processing**: Real-time evaluation and scoring\n\n**📝 Maintenance Tasks:**\n• Update questions based on role requirements\n• Review AI evaluation criteria quarterly\n• Monitor response processing accuracy\n• Backup questionnaire data regularly"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "17c11c31-64d6-417f-b01d-863e13dd207f",
  "connections": {
    "ed9b6126-dadf-4bd6-851f-dbe3e421434e": {
      "ai_languageModel": [
        [
          {
            "node": "cb360f3e-257f-49d8-930a-b3d31841edbe",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "abfe1e72-3291-4bfe-ab80-8b9339ea0aa9": {
      "main": [
        [
          {
            "node": "895de667-d515-4331-a2be-e3079af6b468",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cb360f3e-257f-49d8-930a-b3d31841edbe": {
      "main": [
        [
          {
            "node": "5cd8411b-1851-46e5-833b-2723d14dd509",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5cd8411b-1851-46e5-833b-2723d14dd509": {
      "main": [
        [
          {
            "node": "3817a7af-23d1-445d-abc3-b68556ff5ec4",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "3817a7af-23d1-445d-abc3-b68556ff5ec4": {
      "main": [
        [
          {
            "node": "abfe1e72-3291-4bfe-ab80-8b9339ea0aa9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5745071e-e64f-44ab-a972-65a3b7ef2a06": {
      "main": [
        [
          {
            "node": "cb360f3e-257f-49d8-930a-b3d31841edbe",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}