Automatisierung der DEI-Qualifikationsprüfung mit Azure OpenAI und Google Workspace

• •Google-Konto + Gmail API-Anmeldedaten
• •Google Drive API-Anmeldedaten
• •Google Sheets API-Anmeldedaten
• •OpenAI API Key

{
  "id": "dFiA2ofAChCtsSKh",
  "meta": {
    "instanceId": "8443f10082278c46aa5cf3acf8ff0f70061a2c58bce76efac814b16290845177",
    "templateCredsSetupCompleted": true
  },
  "name": "Automate DEI Eligibility Screening with Azure OpenAI and Google Workspace",
  "tags": [],
  "nodes": [
    {
      "id": "a8050a45-eb9a-44b5-bbe8-24e1fe813f48",
      "name": "Azure OpenAI-Chat-Modell",
      "type": "@n8n/n8n-nodes-langchain.lmChatAzureOpenAi",
      "position": [
        672,
        128
      ],
      "parameters": {
        "model": "gpt-4o-mini",
        "options": {}
      },
      "credentials": {
        "azureOpenAiApi": {
          "id": "C3WzT18XqF8OdVM6",
          "name": "Azure Open AI account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "d419a8e2-13ba-4505-9b51-07b7dc07bf2a",
      "name": "Structured Output Parser",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        928,
        128
      ],
      "parameters": {
        "jsonSchemaExample": "{\n\t\"Name\": \"Jyothi\",\n   \"Location\": \"Hyderabad\",\n\t\"Language\": \"Telugu\",\n    \"Gender\" : \"Male\",\n    \"Disability\": \"No\",\n    \"DEI\": \"Yes\",\n    \"Factor\": \"Location\"\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "9c82394a-aed4-4d09-949d-a66290915071",
      "name": "Get row(s) in sheet in Google Tabellen",
      "type": "n8n-nodes-base.googleSheetsTool",
      "position": [
        800,
        128
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1P8uforIuBJCkX0i9dl5BgUmzi4Nk63EHyFKLyeQvxT8/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1P8uforIuBJCkX0i9dl5BgUmzi4Nk63EHyFKLyeQvxT8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1P8uforIuBJCkX0i9dl5BgUmzi4Nk63EHyFKLyeQvxT8/edit?usp=drivesdk",
          "cachedResultName": "HR Dei"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "sOplQNr4zvwQul4A",
          "name": "jyothi"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "e22db6e8-3dca-4f92-964d-307cfff24927",
      "name": "CV Trigger",
      "type": "n8n-nodes-base.googleDriveTrigger",
      "position": [
        0,
        -96
      ],
      "parameters": {
        "event": "fileCreated",
        "options": {},
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        },
        "triggerOn": "specificFolder",
        "folderToWatch": {
          "__rl": true,
          "mode": "list",
          "value": "1KyX5RGqeF7v0sAvvaoBnJPTQoq4aOHLz",
          "cachedResultUrl": "https://drive.google.com/drive/folders/1KyX5RGqeF7v0sAvvaoBnJPTQoq4aOHLz",
          "cachedResultName": "HR auto"
        }
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "gPmEPTuQP4KLm1KD",
          "name": "jyothi"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "2207292d-6455-478d-bcdd-1314066738ea",
      "name": "Download CV",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        224,
        -96
      ],
      "parameters": {
        "fileId": {
          "__rl": true,
          "mode": "url",
          "value": "={{ $json.webViewLink }}"
        },
        "options": {},
        "operation": "download"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "gPmEPTuQP4KLm1KD",
          "name": "jyothi"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "2d54c6ad-d960-4a5b-971d-853bdddbde47",
      "name": "Extract from PDF",
      "type": "n8n-nodes-base.extractFromFile",
      "position": [
        448,
        -96
      ],
      "parameters": {
        "options": {},
        "operation": "pdf"
      },
      "typeVersion": 1
    },
    {
      "id": "ccdb0c73-5757-45f2-84a6-8b22f743786a",
      "name": "Check for DEI Eligibility",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        736,
        -96
      ],
      "parameters": {
        "text": "={{ $json.text }}",
        "options": {
          "systemMessage": "=You are an HR analytics assistant that processes candidate resumes and questionnaires and evaluates DEI hire eligibility using organization-defined balance rules. You must never infer protected attributes; only use self-reported data and Google Sheets records. Return strict, valid JSON exactly as specified. If data is missing or uncertain, use null and include conservative confidence scores.\n\nMandatory tooling\n • You MUST use the Google Sheets tool to read current representation of already hired employees before making any DEI eligibility decision.\nstate/country to location_bucket)\n\nObjectives\n • Extract structured proxies from the candidate: gender (self-reported only), location (city/state/country), language(s) (self-reported only), disability (self-reported only)\n• Determine DEI eligibility by comparing candidate’s categories against current workforce representation from the google sheet.\n\nOutput should be:\n- Name (self-reported)\n- gender (self-reported only)\n- location (city/state/country)\n- language (self-reported only)\n- disability (self-reported only)\n- DEI Eligible (Yes/No), If Yes in which factor.\n- Factor\n\nYOu will check the current employees and against this candidate and come to a conslucion whether or not this person is being underpresented in the firm in any factor  "
        },
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "c61ef3a2-4ef5-4a2b-a4a9-07c5db11a212",
      "name": "Update on Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1136,
        -96
      ],
      "parameters": {
        "columns": {
          "value": {
            "DEI": "={{ $json.output.DEI }}",
            "Name": "={{ $json.output.Name }}",
            "Factor": "={{ $json.output.Factor }}",
            "Gender": "={{ $json.output.Gender }}",
            "Language": "={{ $json.output.Language }}",
            "Location": "={{ $json.output.Location }}",
            "Disability": "={{ $json.output.Disability }}"
          },
          "schema": [
            {
              "id": "Name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Location",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Location",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Gender",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Gender",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Disability",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Disability",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Language",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Language",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "DEI",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "DEI",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Factor",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Factor",
              "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/1P8uforIuBJCkX0i9dl5BgUmzi4Nk63EHyFKLyeQvxT8/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1P8uforIuBJCkX0i9dl5BgUmzi4Nk63EHyFKLyeQvxT8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1P8uforIuBJCkX0i9dl5BgUmzi4Nk63EHyFKLyeQvxT8/edit?usp=drivesdk",
          "cachedResultName": "HR Dei"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "sOplQNr4zvwQul4A",
          "name": "jyothi"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "9ebadf68-ba24-4889-9bcc-e74e39f3aab2",
      "name": "Logic",
      "type": "n8n-nodes-base.if",
      "position": [
        1360,
        -96
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "32ffb27d-1706-4aa8-b64c-9f661a38af22",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.DEI }}",
              "rightValue": "Yes"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "ac244e53-235e-425e-bae4-5e9d91aec305",
      "name": "Create E-Mail",
      "type": "n8n-nodes-base.code",
      "position": [
        1584,
        -96
      ],
      "parameters": {
        "jsCode": "// n8n Code node: build HTML email for DEI eligibility notification\n\n// Input data (replace with values from previous nodes or item fields)\nconst name = $json.Name || \"Candidate\";\nconst location = $json.Location || null;\nconst languages = $json.Language || null; // comma-delimited string\nconst gender = $json.Gender || null;\nconst disability = $json.Disability || null;\nconst deiEligible = ($json.DEI || \"No\").toLowerCase() === \"yes\";\nconst factor = $json.Factor || null;\n\n// Email metadata (adjust as needed or set in Gmail node)\nconst subject = `DEI Eligibility — ${name}`;\nconst preheader = `Candidate ${name} is ${deiEligible ? \"eligible\" : \"not eligible\"} for DEI hire${factor ? ` via ${factor}` : \"\"}.`;\n\n// Build a safe text summary\nconst textSummaryParts = [\n  `Candidate: ${name}`,\n  `DEI Eligibility: ${deiEligible ? \"Yes\" : \"No\"}`,\n];\nif (factor) textSummaryParts.push(`Factor: ${factor}`);\nif (location) textSummaryParts.push(`Location: ${location}`);\nif (languages) textSummaryParts.push(`Language(s): ${languages}`);\ntextSummaryParts.push(`Gender: ${gender ?? \"null\"}`);\ntextSummaryParts.push(`Disability: ${disability ?? \"null\"}`);\nconst textBody = textSummaryParts.join(\"\\n\");\n\n// Basic inline styles for email clients\nconst styles = {\n  container: \"font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Arial, sans-serif; color:#111; background:#ffffff; margin:0; padding:0;\",\n  wrapper: \"max-width:640px; margin:0 auto; padding:24px;\",\n  card: \"border:1px solid #e5e7eb; border-radius:12px; padding:20px;\",\n  h1: \"font-size:20px; margin:0 0 12px; color:#111;\",\n  p: \"margin:0 0 12px; line-height:1.5; color:#374151;\",\n  badgeYes: \"display:inline-block; background:#065f46; color:#ffffff; font-weight:600; font-size:12px; padding:6px 10px; border-radius:999px;\",\n  badgeNo: \"display:inline-block; background:#7f1d1d; color:#ffffff; font-weight:600; font-size:12px; padding:6px 10px; border-radius:999px;\",\n  table: \"width:100%; border-collapse:collapse; margin-top:12px;\",\n  th: \"text-align:left; font-size:12px; color:#6b7280; padding:6px 0;\",\n  td: \"font-size:14px; color:#111; padding:6px 0;\",\n  footer: \"margin-top:16px; font-size:12px; color:#6b7280;\",\n  divider: \"height:1px; background:#e5e7eb; border:none; margin:16px 0;\"\n};\n\n// Build HTML email\nconst html = `\n<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n  <meta charset=\"utf-8\" />\n  <meta name=\"viewport\" content=\"width=device-width,initial-scale=1\" />\n  <title>${subject}</title>\n  <meta name=\"color-scheme\" content=\"light only\" />\n  <meta name=\"supported-color-schemes\" content=\"light\" />\n  <!-- Preheader text -->\n  <span style=\"display:none !important; visibility:hidden; opacity:0; color:transparent; height:0; width:0; overflow:hidden;\">${preheader}</span>\n</head>\n<body style=\"${styles.container}\">\n  <div style=\"${styles.wrapper}\">\n    <div style=\"${styles.card}\">\n      <h1 style=\"${styles.h1}\">DEI Eligibility Review</h1>\n      <p style=\"${styles.p}\">\n        Candidate <strong>${name}</strong> has been evaluated for DEI eligibility.\n      </p>\n      <p style=\"${styles.p}\">\n        Status:\n        <span style=\"${deiEligible ? styles.badgeYes : styles.badgeNo}\">\n          ${deiEligible ? \"DEI Eligible\" : \"Not DEI Eligible\"}\n        </span>\n      </p>\n      ${factor ? `<p style=\"${styles.p}\">Primary eligibility factor: <strong>${factor}</strong>.</p>` : \"\"}\n\n      <hr style=\"${styles.divider}\" />\n\n      <table role=\"presentation\" style=\"${styles.table}\">\n        <tbody>\n          <tr>\n            <th style=\"${styles.th}\">Name</th>\n            <td style=\"${styles.td}\">${name}</td>\n          </tr>\n          <tr>\n            <th style=\"${styles.th}\">Location</th>\n            <td style=\"${styles.td}\">${location ?? \"—\"}</td>\n          </tr>\n          <tr>\n            <th style=\"${styles.th}\">Language(s)</th>\n            <td style=\"${styles.td}\">${languages ?? \"—\"}</td>\n          </tr>\n          <tr>\n            <th style=\"${styles.th}\">Gender</th>\n            <td style=\"${styles.td}\">${gender ?? \"null\"}</td>\n          </tr>\n          <tr>\n            <th style=\"${styles.th}\">Disability</th>\n            <td style=\"${styles.td}\">${disability ?? \"null\"}</td>\n          </tr>\n          <tr>\n            <th style=\"${styles.th}\">DEI Eligible</th>\n            <td style=\"${styles.td}\">${deiEligible ? \"Yes\" : \"No\"}</td>\n          </tr>\n          ${factor ? `\n          <tr>\n            <th style=\"${styles.th}\">Factor</th>\n            <td style=\"${styles.td}\">${factor}</td>\n          </tr>` : \"\"}\n        </tbody>\n      </table>\n\n      <p style=\"${styles.footer}\">\n        This notification is generated automatically from the latest evaluation records.\n      </p>\n    </div>\n  </div>\n</body>\n</html>\n`;\n\n// Return for downstream Gmail node (use fields: subject, html, text)\nreturn [\n  {\n    json: {\n      subject,\n      html,\n      text: textBody\n      // Optionally include \"to\" and \"from\" here or set them in the Gmail node configuration.\n      // to: \"manager@company.com\",\n      // from: \"automations@techdome.ai\"\n    }\n  }\n];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "9ae3966c-09cb-4e5d-9a9a-94ed01b52229",
      "name": "E-Mail to Manager",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1808,
        -96
      ],
      "webhookId": "1b8b6079-b721-4612-b513-cddb5ecfa677",
      "parameters": {
        "sendTo": "jyothi.swarup@techdome.net.in",
        "message": "={{ $json.html }}",
        "options": {},
        "subject": "={{ $json.subject }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "70f5n8rPahCANHs7",
          "name": "jyothi"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "3627dee6-a28a-410f-8a57-23d94b7a5869",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -320,
        -112
      ],
      "parameters": {
        "content": "## CV Trigger  \nWatches Google Drive for newly added CV files and starts the workflow."
      },
      "typeVersion": 1
    },
    {
      "id": "6cc70516-452d-4ace-b42a-0a9eaa97f53e",
      "name": "Haftnotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        144,
        -288
      ],
      "parameters": {
        "content": "## Download CV  \nFetches the detected CV file from Drive and makes it available for processing."
      },
      "typeVersion": 1
    },
    {
      "id": "c3c3ebeb-be02-4d71-a4ca-5844a1c1baa8",
      "name": "Haftnotiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        368,
        96
      ],
      "parameters": {
        "content": "## Extract From PDF  \nParses the CV PDF to extract key text and structured fields."
      },
      "typeVersion": 1
    },
    {
      "id": "4bdbcfdf-565a-4f21-bb12-03010a41267b",
      "name": "Haftnotiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        720,
        -288
      ],
      "parameters": {
        "content": "## Azure OpenAI Chat Model  \nUses GPT on Azure OpenAI to analyze extracted CV content and generate insights."
      },
      "typeVersion": 1
    },
    {
      "id": "458dbccb-2ba7-4123-a489-c0a576e377bd",
      "name": "Haftnotiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1072,
        96
      ],
      "parameters": {
        "content": "## Update on Sheet  \nAppends the analyzed results and eligibility status back to the Google Sheet."
      },
      "typeVersion": 1
    },
    {
      "id": "c6d4c2c5-a84f-4423-b163-c2fe27641b86",
      "name": "Haftnotiz5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1520,
        96
      ],
      "parameters": {
        "content": "## Create Email  \nGenerates a tailored email based on the candidate’s analysis and sheet data."
      },
      "typeVersion": 1
    },
    {
      "id": "408efdea-b86a-442b-9b5f-7f16e1a7892c",
      "name": "Haftnotiz6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1728,
        -304
      ],
      "parameters": {
        "content": "## Email to Manager  \nSends the composed summary email to the hiring manager for review."
      },
      "typeVersion": 1
    },
    {
      "id": "770d70e4-6035-4551-8746-f1db3a367b56",
      "name": "Haftnotiz7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1280,
        -288
      ],
      "parameters": {
        "content": "## Logic  \nEvaluates conditions and routes the flow using true/false branches to control execution."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "c99e6cbc-64e8-488b-93e9-7e3b09131955",
  "connections": {
    "9ebadf68-ba24-4889-9bcc-e74e39f3aab2": {
      "main": [
        [
          {
            "node": "Create Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "e22db6e8-3dca-4f92-964d-307cfff24927": {
      "main": [
        [
          {
            "node": "2207292d-6455-478d-bcdd-1314066738ea",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2207292d-6455-478d-bcdd-1314066738ea": {
      "main": [
        [
          {
            "node": "2d54c6ad-d960-4a5b-971d-853bdddbde47",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Email": {
      "main": [
        [
          {
            "node": "Email to Manager",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c61ef3a2-4ef5-4a2b-a4a9-07c5db11a212": {
      "main": [
        [
          {
            "node": "9ebadf68-ba24-4889-9bcc-e74e39f3aab2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2d54c6ad-d960-4a5b-971d-853bdddbde47": {
      "main": [
        [
          {
            "node": "ccdb0c73-5757-45f2-84a6-8b22f743786a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Azure OpenAI Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "ccdb0c73-5757-45f2-84a6-8b22f743786a",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "d419a8e2-13ba-4505-9b51-07b7dc07bf2a": {
      "ai_outputParser": [
        [
          {
            "node": "ccdb0c73-5757-45f2-84a6-8b22f743786a",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "ccdb0c73-5757-45f2-84a6-8b22f743786a": {
      "main": [
        [
          {
            "node": "c61ef3a2-4ef5-4a2b-a4a9-07c5db11a212",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get row(s) in sheet in Google Sheets": {
      "ai_tool": [
        [
          {
            "node": "ccdb0c73-5757-45f2-84a6-8b22f743786a",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    }
  }
}