{ "edges": [ { "to": "908cebd1-eadd-4cc7-a806-b5ded51ee1b4", "from": "5b6aeedf-b801-47dd-9465-546221d7de7c", "toPort": "0", "fromPort": "0" }, { "to": "5b6aeedf-b801-47dd-9465-546221d7de7c", "from": "166ad36b-ceca-4cd2-9602-4e1c8e6f6aca", "toPort": "0", "fromPort": "1" }, { "to": "166ad36b-ceca-4cd2-9602-4e1c8e6f6aca", "from": "d19f2e70-81eb-4eae-947a-3364cd8a234c", "toPort": "0", "fromPort": "0" }, { "to": "016ebf61-710c-49c1-b97c-ddaa5ba20fc0", "from": "908cebd1-eadd-4cc7-a806-b5ded51ee1b4", "toPort": "0", "fromPort": "0" }, { "to": "d19f2e70-81eb-4eae-947a-3364cd8a234c", "from": "c868124d-d47d-4cac-b7f6-227e5eec08eb", "toPort": "0", "fromPort": "0" }, { "to": "174d1b30-281b-424f-b70a-09f156e6fde4", "from": "016ebf61-710c-49c1-b97c-ddaa5ba20fc0", "toPort": "0", "fromPort": "0" }, { "to": "166ad36b-ceca-4cd2-9602-4e1c8e6f6aca", "from": "174d1b30-281b-424f-b70a-09f156e6fde4", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "c868124d-d47d-4cac-b7f6-227e5eec08eb", "name": "Schedule Trigger", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ {} ] } }, "position": [ -2720, 496 ] }, { "id": "d19f2e70-81eb-4eae-947a-3364cd8a234c", "name": "Get many folders", "type": "integration", "params": { "team": "YOUR_TEAM_ID", "limit": 100, "space": "YOUR_SPACE_ID", "filters": {}, "service": "clickup", "resource": "folder", "operation": "getAll" }, "position": [ -2496, 496 ] }, { "id": "5b6aeedf-b801-47dd-9465-546221d7de7c", "name": "Get many tasks", "type": "integration", "params": { "list": "={{ $json.lists[0].id }}", "team": "YOUR_TEAM_ID", "space": "YOUR_SPACE_ID", "filters": {}, "service": "clickup", "operation": "getAll", "folderless": true }, "position": [ -2016, 512 ] }, { "id": "166ad36b-ceca-4cd2-9602-4e1c8e6f6aca", "name": "Loop Over Items", "type": "passthrough", "params": { "mode": "noOp", "options": {} }, "position": [ -2272, 496 ] }, { "id": "908cebd1-eadd-4cc7-a806-b5ded51ee1b4", "name": "Message a model1", "type": "llm", "params": { "modelId": { "__rl": true, "mode": "list", "value": "gpt-4.1-mini", "cachedResultName": "GPT-4.1-MINI" }, "options": {}, "provider": "openai", "responses": { "values": [ { "content": "=You are a project management analytics assistant.\n\nGenerate a concise, professional task summary using ONLY the data provided from ClickUp. \nDo not invent data or make assumptions. Base all calculations strictly on the task data.\n\nTask Data:\nTask ID: {{$json.id}}\nName: {{$json.name}}\nDescription: {{$json.description}}\nStatus: {{$json.status.status}}\nPriority: {{ $json.priority }}\nAssignees: {{$json.assignees.map(a => a.username).join(', ')}}\nCreator: {{$json.creator.username}}\nDue Date: {{$json.due_date}}\nDate Created: {{$json.date_created}}\nDate Updated: {{$json.date_updated}}\nURL: {{$json.url}}\nProject: {{$json.project.name}}\nList: {{$json.list.name}}\nFolder: {{$json.folder.name}}\nSpace ID: {{$json.space.id}}\n\nMetrics (calculate based on task status and priority):\n- Completed: {{$json.status.status === 'done' ? 1 : 0}}\n- In Progress: {{$json.status.status === 'in progress' ? 1 : 0}}\n- Pending: {{$json.status.status === 'to do' || $json.status.status === 'pending' ? 1 : 0}}\n- Overdue: {{($json.due_date && Number($json.due_date) < Date.now() && $json.status.status !== 'done') ? 1 : 0}}\n- Productivity: {{($json.status.status === 'done' ? 100 : 0)}}%\n- KPI Score: [calculate based on completed tasks, priority, and overdue metrics]\n- Rating: [High priority & overdue → Critical, Medium priority → Needs Improvement, Low → Good]\n\nOutput plain text ONLY, in this format:\n\nTask Summary\nTask ID: \nName: \nProject: \nFolder: \nList: \nSpace ID: \n\nOverview:\n- Description: \n- Status: \n- Priority: \n- Assignees: \n- Creator: \n- Dates:\n • Due: \n • Created: \n • Updated: \n\nMetrics:\n- Completed: \n- In Progress: \n- Pending: \n- Overdue: \n- Productivity: %\n- KPI Score: \n- Rating: \n\nTask Link: \n\nInsight:\nProvide a one-line performance summary of this task's status and priority, explaining the current KPI score, rating, and any action required.\n" } ] }, "builtInTools": {} }, "position": [ -1808, 512 ] }, { "id": "016ebf61-710c-49c1-b97c-ddaa5ba20fc0", "name": "Code in JavaScript", "type": "code", "params": { "jsCode": "// Get all items from previous node\nconst items = $input.all();\n\n// Helper function to calculate KPI score\nfunction calculateKPI(task) {\n // Base KPI: 100 if done, 50 if in progress, 0 if pending\n let base = 0;\n if (task.overview.status.toLowerCase() === 'done') base = 100;\n else if (task.overview.status.toLowerCase() === 'in progress') base = 60;\n else base = 30;\n\n // Adjust based on priority\n const priority = task.overview.priority.toLowerCase();\n if (priority === 'high') base += 10;\n else if (priority === 'low') base -= 10;\n\n // Reduce if overdue\n if (task.metrics.overdue > 0) base -= 20;\n\n // Clamp 0-100\n return Math.max(0, Math.min(100, base));\n}\n\n// Helper to get Rating based on KPI Score\nfunction calculateRating(kpiScore) {\n if (kpiScore >= 80) return 'Excellent';\n if (kpiScore >= 60) return 'Needs Improvement';\n if (kpiScore >= 40) return 'Average';\n return 'Poor';\n}\n\n// Initialize result array\nconst parsedTasks = [];\n\nitems.forEach(item => {\n // Safely access the ChatGPT text\n const content = item.json.output?.[0]?.content?.[0]?.text;\n if (!content) return; // skip if no text\n\n const lines = content.split('\\n').map(l => l.trim()).filter(l => l);\n\n // Initialize task object\n let taskData = {\n taskSummary: '',\n taskId: '',\n name: '',\n project: '',\n folder: '',\n list: '',\n spaceId: '',\n overview: {\n description: '',\n status: '',\n priority: '',\n assignees: [],\n creator: '',\n dates: {\n due: null,\n created: null,\n updated: null\n }\n },\n metrics: {\n completed: 0,\n inProgress: 0,\n pending: 0,\n overdue: 0,\n productivity: '',\n kpiScore: 0,\n rating: '',\n },\n taskLink: '',\n insight: ''\n };\n\n let currentSection = '';\n\n function parseNumber(str) { const n = Number(str); return isNaN(n)?0:n; }\n\n // Parse each line\n lines.forEach(line => {\n if (line.startsWith('Task Summary')) taskData.taskSummary = 'Task Summary';\n else if (line.startsWith('Task ID:')) taskData.taskId = line.replace('Task ID:','').trim();\n else if (line.startsWith('Name:')) taskData.name = line.replace('Name:','').trim();\n else if (line.startsWith('Project:')) taskData.project = line.replace('Project:','').trim();\n else if (line.startsWith('Folder:')) taskData.folder = line.replace('Folder:','').trim();\n else if (line.startsWith('List:')) taskData.list = line.replace('List:','').trim();\n else if (line.startsWith('Space ID:')) taskData.spaceId = line.replace('Space ID:','').trim();\n else if (line.startsWith('Overview:')) currentSection = 'overview';\n else if (line.startsWith('Metrics:')) currentSection = 'metrics';\n else if (line.startsWith('Task Link:')) taskData.taskLink = line.replace('Task Link:','').trim();\n else if (line.startsWith('Insight:')) currentSection = 'insight';\n else {\n if (currentSection === 'overview') {\n if (line.startsWith('- Description:')) taskData.overview.description = line.replace('- Description:','').trim();\n else if (line.startsWith('- Status:')) taskData.overview.status = line.replace('- Status:','').trim();\n else if (line.startsWith('- Priority:')) taskData.overview.priority = line.replace('- Priority:','').trim();\n else if (line.startsWith('- Assignees:')) {\n const assigneesText = line.replace('- Assignees:','').trim();\n taskData.overview.assignees = assigneesText ? assigneesText.split(',').map(a=>a.trim()) : [];\n }\n else if (line.startsWith('- Creator:')) taskData.overview.creator = line.replace('- Creator:','').trim();\n else if (line.startsWith('• Due:')) taskData.overview.dates.due = line.replace('• Due:','').trim() || null;\n else if (line.startsWith('• Created:')) taskData.overview.dates.created = Number(line.replace('• Created:','').trim()) || null;\n else if (line.startsWith('• Updated:')) taskData.overview.dates.updated = Number(line.replace('• Updated:','').trim()) || null;\n } else if (currentSection === 'metrics') {\n if (line.startsWith('- Completed:')) taskData.metrics.completed = parseNumber(line.replace('- Completed:','').trim());\n else if (line.startsWith('- In Progress:')) taskData.metrics.inProgress = parseNumber(line.replace('- In Progress:','').trim());\n else if (line.startsWith('- Pending:')) taskData.metrics.pending = parseNumber(line.replace('- Pending:','').trim());\n else if (line.startsWith('- Overdue:')) taskData.metrics.overdue = parseNumber(line.replace('- Overdue:','').trim());\n else if (line.startsWith('- Productivity:')) taskData.metrics.productivity = line.replace('- Productivity:','').trim();\n // KPI and Rating will be recalculated\n } else if (currentSection === 'insight') {\n taskData.insight += (taskData.insight ? ' ' : '') + line;\n }\n }\n });\n\n // Calculate KPI Score and Rating based on parsed metrics\n taskData.metrics.kpiScore = calculateKPI(taskData);\n taskData.metrics.rating = calculateRating(taskData.metrics.kpiScore);\n\n // Optional: Generate summary insight automatically if not present\n if (!taskData.insight) {\n taskData.insight = `Task has status \"${taskData.overview.status}\" with priority \"${taskData.overview.priority}\" and KPI ${taskData.metrics.kpiScore}, rating ${taskData.metrics.rating}.`;\n }\n\n parsedTasks.push({ json: taskData });\n});\n\nreturn parsedTasks;\n" }, "position": [ -1472, 512 ] }, { "id": "174d1b30-281b-424f-b70a-09f156e6fde4", "name": "Append or update row in sheet1", "type": "integration", "params": { "columns": { "value": { "Folder": "={{ $json.folder }}", "Rating": "={{ $json.metrics.rating }}", "Status": "={{ $json.overview.status }}", "Overdue": "={{ $json.metrics.overdue }}", "Pending": "={{ $json.metrics.pending }}", "Project": "={{ $json.project }}", "Summary": "={{ $json.insight }}", "Priority": "={{ $json.overview.priority }}", "Completed": "={{ $json.metrics.completed }}", "KPI Score": "={{ $json.metrics.kpiScore }}", "Task Link": "={{ $json.taskLink }}", "Task Name": "={{ $json.name }}", "In Progress": "={{ $json.metrics.inProgress }}", "Productivity": "={{ $json.metrics.productivity }}", "Employee Name": "={{ $json.overview.creator }}" }, "schema": [ { "id": "Employee Name", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Employee Name", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Task Name", "type": "string", "display": true, "required": false, "displayName": "Task Name", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Project", "type": "string", "display": true, "required": false, "displayName": "Project", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Folder", "type": "string", "display": true, "required": false, "displayName": "Folder", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Status", "type": "string", "display": true, "required": false, "displayName": "Status", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Priority", "type": "string", "display": true, "required": false, "displayName": "Priority", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Completed", "type": "string", "display": true, "required": false, "displayName": "Completed", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "In Progress", "type": "string", "display": true, "required": false, "displayName": "In Progress", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Pending", "type": "string", "display": true, "required": false, "displayName": "Pending", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Overdue", "type": "string", "display": true, "required": false, "displayName": "Overdue", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Productivity", "type": "string", "display": true, "required": false, "displayName": "Productivity", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "KPI Score", "type": "string", "display": true, "required": false, "displayName": "KPI Score", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Rating", "type": "string", "display": true, "required": false, "displayName": "Rating", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Summary", "type": "string", "display": true, "required": false, "displayName": "Summary", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Task Link", "type": "string", "display": true, "required": false, "displayName": "Task Link", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [ "Employee Name" ], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "appendOrUpdate", "sheetName": { "__rl": true, "mode": "list", "value": "YOUR_SHEET_GID", "cachedResultName": "Employee summary" }, "documentId": { "__rl": true, "mode": "list", "value": "YOUR_GOOGLE_SHEET_ID", "cachedResultName": "Employee summary" } }, "position": [ -1264, 512 ] } ] }

Track employee performance KPIs from ClickUp with GPT-4.1 and Google Sheets