项目管理-n8n-带任务管理和照片报告
高级
这是一个Project Management领域的自动化工作流，包含 111 个节点。主要使用 If, Code, Cron, Merge, Telegram 等节点。 使用任务、照片报告、Telegram和Google表格管理建筑项目
前置要求
- •Telegram Bot Token
- •Google Drive API 凭证
- •Google Sheets API 凭证
分类
工作流预览
可视化展示节点连接关系，支持缩放和平移
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
"id": "EYT5quvgwZ4c7ka8",
"meta": {
"instanceId": "1d7b1da65c471e434188555a2959bd7f825cdfb684701b1bb9481fb29d0bd489"
},
"name": "项目管理-n8n-带任务管理和照片报告",
"tags": [],
"nodes": [
{
"id": "649fd633-873c-49ce-990d-36e44c4f5eb1",
"name": "有消息要发送吗？",
"type": "n8n-nodes-base.if",
"position": [
-544,
800
],
"parameters": {
"conditions": {
"number": [
{
"value1": "={{ $items().length }}",
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "eecf7a73-1f37-4d8d-9d2a-5089fee13141",
"name": "准备更新",
"type": "n8n-nodes-base.code",
"position": [
-144,
784
],
"parameters": {
"jsCode": "// Extract update data from all messages\nconst allMessages = $input.all();\n\n// Current time\nconst now = new Date();\nconst day = String(now.getDate()).padStart(2, '0');\nconst month = String(now.getMonth() + 1).padStart(2, '0');\nconst year = now.getFullYear();\nconst hours = String(now.getHours()).padStart(2, '0');\nconst minutes = String(now.getMinutes()).padStart(2, '0');\nconst sentDateTime = `${day}.${month}.${year}, ${hours}:${minutes}`;\n\n// Create updates for each row\nconst updates = [];\nconst processedRows = new Set();\n\n// Process ALL messages - both greeting and reminders\nallMessages.forEach(msg => {\n // For greeting messages\n if (msg.json.isGreeting === true && msg.json.rowIndexes) {\n const rowIndexes = msg.json.rowIndexes || [];\n rowIndexes.forEach(rowIndex => {\n if (rowIndex && !isNaN(rowIndex) && !processedRows.has(rowIndex)) {\n processedRows.add(rowIndex);\n updates.push({\n json: {\n values: [[\"Yes\", sentDateTime, \"1\"]], // Initial counter = 1\n _rowIndex: Number(rowIndex)\n }\n });\n }\n });\n }\n \n // For reminder messages\n if (msg.json.isReminder === true && msg.json.rowIndex) {\n const rowIndex = msg.json.rowIndex;\n const count = msg.json.count || 1;\n if (rowIndex && !isNaN(rowIndex) && !processedRows.has(rowIndex)) {\n processedRows.add(rowIndex);\n updates.push({\n json: {\n values: [[\"Yes\", sentDateTime, String(count)]],\n _rowIndex: Number(rowIndex)\n }\n });\n }\n }\n});\n\nconsole.log(`Prepared ${updates.length} updates`);\nreturn updates.length > 0 ? updates : [];"
},
"typeVersion": 2
},
{
"id": "85037572-c5a2-4f94-8fe2-cdabcc0a04a9",
"name": "有更新吗？",
"type": "n8n-nodes-base.if",
"position": [
0,
784
],
"parameters": {
"conditions": {
"number": [
{
"value1": "={{ $items().length }}",
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "b0250b6f-c00a-44cc-9d7c-30dfdad34a9c",
"name": "无消息",
"type": "n8n-nodes-base.noOp",
"position": [
-288,
864
],
"parameters": {},
"typeVersion": 1
},
{
"id": "94bc2660-9115-4bec-a3dd-723aea6a004a",
"name": "无更新",
"type": "n8n-nodes-base.noOp",
"position": [
160,
912
],
"parameters": {},
"typeVersion": 1
},
{
"id": "6f9312ce-00ac-4c41-a514-7cf4877530ce",
"name": "每分钟检查 - 照片",
"type": "n8n-nodes-base.cron",
"position": [
-1280,
800
],
"parameters": {
"triggerTimes": {
"item": [
{
"mode": "everyMinute"
}
]
}
},
"typeVersion": 1
},
{
"id": "3f577e09-d0e3-4916-a6f4-2410421167d9",
"name": "获取照片报告数据",
"type": "n8n-nodes-base.googleSheets",
"position": [
-1040,
800
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "fe036999-2e25-4fef-a677-33b9447ac883",
"name": "查找并形成消息 - 照片",
"type": "n8n-nodes-base.code",
"position": [
-784,
800
],
"parameters": {
"jsCode": "// Search and form reminders with check of current and past time, including repeated ones\nconst items = $input.all();\nconst now = new Date();\nconst todayStr = now.toLocaleDateString('en-US');\nconst executorsToNotify = {};\nconst reminderMessages = [];\n\n// Get current hours and minutes\nconst currentHours = now.getHours();\nconst currentMinutes = now.getMinutes();\nconst currentSeconds = now.getSeconds();\nconst currentTimeInMinutes = currentHours * 60 + currentMinutes;\n\n// Function to parse sent date\nfunction parseSentDate(sentDateStr) {\n if (!sentDateStr || sentDateStr.trim() === '') return null;\n try {\n const [datePart, timePart] = sentDateStr.split(', ');\n const [day, month, year] = datePart.split('.');\n const [hours, minutes] = timePart.split(':');\n return new Date(parseInt(year), parseInt(month) - 1, parseInt(day), parseInt(hours), parseInt(minutes));\n } catch (e) {\n console.error('Error parsing sent date:', sentDateStr, e);\n return null;\n }\n}\n\n// Function to parse interval\nfunction parseInterval(intervalStr) {\n if (!intervalStr || intervalStr.trim() === '') return 0;\n try {\n const [hours, minutes] = intervalStr.split(':').map(Number);\n return (hours || 0) * 60 * 60 * 1000 + (minutes || 0) * 60 * 1000;\n } catch (e) {\n console.error('Error parsing interval:', intervalStr, e);\n return 0;\n }\n}\n\n// Extended logging\nconsole.log('='.repeat(50));\nconsole.log(`TIME CHECK (PHOTO REPORTS)`);\nconsole.log(`Current date and time: ${now.toLocaleString('en-US')}`);\nconsole.log(`Current time: ${currentHours}:${currentMinutes.toString().padStart(2, '0')}:${currentSeconds.toString().padStart(2, '0')}`);\nconsole.log(`Minutes from start of day: ${currentTimeInMinutes}`);\nconsole.log(`Total records to check: ${items.length - 1}`);\nconsole.log('='.repeat(50));\n\n// Counters for debugging\nlet todayCount = 0;\nlet pastTimeCount = 0;\nlet currentTimeCount = 0;\nlet notSentCount = 0;\nlet toSendNewCount = 0;\nlet toSendReminderCount = 0;\n\nitems.forEach((item, index) => {\n if (index === 0) return; // Skip header\n \n const executorId = String(item.json['Executor ID'] || '');\n const executorName = item.json['Executor'] || '';\n const reminderDateRaw = item.json['Reminder Date'];\n const reminderTimeRaw = item.json['Reminder Time'];\n const sent = item.json['Reminder Sent'];\n const sentDateStr = item.json['Reminder Sent Date'];\n const reportId = item.json['Report ID'];\n const reportType = item.json['Report Type'] || item.json['Document Type'] || 'Unknown type';\n const reportDate = item.json['Report Date'];\n const intervalMs = parseInterval(item.json['Reminder Interval if No Report']);\n const oldCount = parseInt(item.json['Reminder Count'] || '0', 10);\n \n if (!executorId || !reminderDateRaw || !reportId || !executorName || reminderTimeRaw === undefined) {\n return;\n }\n \n // Convert date\n let reminderDate;\n try {\n if (typeof reminderDateRaw === 'number') {\n reminderDate = new Date((reminderDateRaw - 25569) * 86400 * 1000);\n } else if (typeof reminderDateRaw === 'string' && reminderDateRaw.includes('.')) {\n const [day, month, year] = reminderDateRaw.split('.');\n reminderDate = new Date(parseInt(year), parseInt(month) - 1, parseInt(day));\n } else {\n reminderDate = new Date(reminderDateRaw);\n }\n reminderDate.setHours(0, 0, 0, 0);\n } catch (e) {\n console.error(` ❌ Error converting date for row ${index + 2}:`, e);\n return;\n }\n \n // Convert time from Excel format\n let reminderHours, reminderMinutes;\n try {\n if (typeof reminderTimeRaw === 'number') {\n const totalMinutes = Math.round(reminderTimeRaw * 24 * 60);\n reminderHours = Math.floor(totalMinutes / 60);\n reminderMinutes = totalMinutes % 60;\n } else if (typeof reminderTimeRaw === 'string') {\n const cleanTime = reminderTimeRaw.trim();\n \n // Handle multiple time values (take first)\n if (cleanTime.includes(' ')) {\n const firstTime = cleanTime.split(' ')[0];\n if (/^\\d{1,2}:\\d{2}$/.test(firstTime)) {\n const [h, m] = firstTime.split(':');\n reminderHours = parseInt(h);\n reminderMinutes = parseInt(m);\n } else {\n reminderHours = parseInt(firstTime);\n reminderMinutes = 0;\n }\n } else if (/^\\d{1,2}:\\d{2}$/.test(cleanTime)) {\n const [h, m] = cleanTime.split(':');\n reminderHours = parseInt(h);\n reminderMinutes = parseInt(m);\n } else if (/^\\d{1,2}:\\d{2}:\\d{2}$/.test(cleanTime)) {\n const [h, m] = cleanTime.split(':');\n reminderHours = parseInt(h);\n reminderMinutes = parseInt(m);\n } else if (cleanTime.includes('AM') || cleanTime.includes('PM')) {\n const timeParts = cleanTime.replace(/[AP]M/i, '').trim().split(':');\n reminderHours = parseInt(timeParts[0]);\n reminderMinutes = parseInt(timeParts[1] || '0');\n if (cleanTime.toUpperCase().includes('PM') && reminderHours < 12) {\n reminderHours += 12;\n } else if (cleanTime.toUpperCase().includes('AM') && reminderHours === 12) {\n reminderHours = 0;\n }\n } else {\n console.error(` ❌ Unknown time format for row ${index + 2}:`, reminderTimeRaw);\n return;\n }\n } else {\n console.error(` ❌ Unknown time type for row ${index + 2}:`, typeof reminderTimeRaw, reminderTimeRaw);\n return;\n }\n } catch (e) {\n console.error(` ❌ Error converting time for row ${index + 2}:`, e);\n return;\n }\n \n const reminderTimeInMinutes = reminderHours * 60 + reminderMinutes;\n \n // Check if date matches today\n const today = new Date();\n today.setHours(0, 0, 0, 0);\n const isToday = reminderDate.getTime() === today.getTime();\n \n const isTimeNowOrPast = reminderTimeInMinutes <= currentTimeInMinutes;\n const notSent = !sent || sent.toString().trim() === '' || sent !== 'Yes';\n const noSentDate = !sentDateStr || sentDateStr.toString().trim() === '';\n const isNotSent = notSent && noSentDate;\n const noReport = !reportDate || reportDate.toString().trim() === '';\n \n // Update counters\n if (isToday) {\n todayCount++;\n if (isTimeNowOrPast) {\n if (reminderTimeInMinutes === currentTimeInMinutes) {\n currentTimeCount++;\n } else {\n pastTimeCount++;\n }\n }\n if (isNotSent) notSentCount++;\n }\n \n // Logic for new reminders\n if (isToday && isTimeNowOrPast && isNotSent && noReport) {\n toSendNewCount++;\n \n if (!executorsToNotify[executorId]) {\n executorsToNotify[executorId] = {\n chatId: executorId,\n executorName: executorName,\n reports: [],\n rowIndexes: []\n };\n }\n \n executorsToNotify[executorId].reports.push({ \n reportId: reportId, \n reportType: reportType,\n rowIndex: index + 2,\n reminderTime: `${reminderHours}:${reminderMinutes.toString().padStart(2, '0')}`,\n isPast: reminderTimeInMinutes < currentTimeInMinutes\n });\n executorsToNotify[executorId].rowIndexes.push(index + 2);\n }\n \n // Logic for repeated reminders\n if (!isNotSent && noReport && intervalMs > 0) {\n const sentDate = parseSentDate(sentDateStr);\n if (sentDate && (now.getTime() - sentDate.getTime()) >= intervalMs) {\n toSendReminderCount++;\n reminderMessages.push({\n json: {\n chat_id: executorId,\n text: `🔔 <b>Repeated reminder</b>\\n\\n📋 Report <b>${reportId} ${reportType}</b> is still not sent.\\n\\nPlease send the photo report (and if needed, a comment) in response to the first message in the chat for this task.`,\n parse_mode: \"HTML\",\n isReminder: true,\n reportId: reportId,\n reportType: reportType,\n rowIndex: index + 2,\n executorName: executorName,\n count: oldCount + 1,\n _isRealMessage: true\n }\n });\n }\n }\n});\n\n// Summary statistics\nconsole.log('\\n' + '='.repeat(50));\nconsole.log('SUMMARY STATISTICS:');\nconsole.log(`- Records with today's date: ${todayCount}`);\nconsole.log(`- Records with current time: ${currentTimeCount}`);\nconsole.log(`- Records with past time: ${pastTimeCount}`);\nconsole.log(`- Records not sent: ${notSentCount}`);\nconsole.log(`- New to send: ${toSendNewCount}`);\nconsole.log(`- Repeated to send: ${toSendReminderCount}`);\nconsole.log(`- Unique executors: ${Object.keys(executorsToNotify).length}`);\nconsole.log('='.repeat(50));\n\n// Form messages for each executor\nconst messages = [];\n\nObject.values(executorsToNotify).forEach(data => {\n const reportsCount = data.reports.length;\n \n let greetingText = '';\n if (reportsCount === 1) {\n greetingText = `You have 1 reminder.`;\n } else {\n greetingText = `You have ${reportsCount} reminders.`;\n }\n \n const greetingMessage = `Hello, ${data.executorName}!\\n\\n📅 <b>Date:</b> ${todayStr}\\n\\n\\n🔔 ${greetingText}\\n\\n\\nYou will receive the list of reports in the following messages.\\nTo send a report, select it in the messages and reply to it by sending a photo (if needed, with a comment)\\n\\n⚠️ <b>Please note:</b>\\n• The photo must be readable\\n• Send it exactly in response to the message with the report\\n• You can send multiple photos\\n\\n✅ Confirm receipt: <b>/read</b>\\n\\n❓ If you have any questions, write /help\\n`;\n \n messages.push({\n json: {\n chat_id: data.chatId,\n text: greetingMessage,\n parse_mode: \"HTML\",\n isGreeting: true,\n executorName: data.executorName,\n rowIndexes: data.rowIndexes,\n reportIds: data.reports.map(r => r.reportId).join(', '),\n _isRealMessage: true\n }\n });\n \n data.reports.forEach((report) => {\n const reportMessage = `📋 Reminder to send:\\n<b>🔸 ${report.reportId} ${report.reportType}</b>\\n\\n<b>➡️ How to send the report:</b>\\n1. Take a clear photo of the document\\n2. IMPORTANT: Reply to this message with the photo\\n3. Add a comment if necessary`;\n \n messages.push({\n json: {\n chat_id: data.chatId,\n text: reportMessage,\n parse_mode: \"HTML\",\n isReportMessage: true,\n reportId: report.reportId,\n reportType: report.reportType,\n rowIndex: report.rowIndex,\n executorName: data.executorName,\n _isRealMessage: true\n }\n });\n });\n});\n\n// Add repeated reminders\nmessages.push(...reminderMessages);\n\nconsole.log(`\\n💬 Formed ${messages.length} messages to send`);\n\nif (messages.length === 0) {\n console.log('📭 No messages to send at the moment');\n return [];\n}\n\nreturn messages;"
},
"typeVersion": 2
},
{
"id": "66ca6e48-da3b-4743-9a43-b7dbc3ce6aea",
"name": "发送到 Telegram - 照片",
"type": "n8n-nodes-base.telegram",
"position": [
-288,
672
],
"webhookId": "XXXXXX",
"parameters": {
"text": "={{ $json.text }}",
"chatId": "={{ $json.chat_id }}",
"additionalFields": {
"parse_mode": "HTML",
"disable_notification": false
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "7171c1ea-6bf8-4690-b39c-11939e8fe4cf",
"name": "在表中更新状态 - 照片",
"type": "n8n-nodes-base.googleSheets",
"position": [
160,
720
],
"parameters": {
"columns": {
"value": {
"row_number": "={{ $json._rowIndex }}",
"Reminder Sent": "={{ $json.values[0][0] }}",
"Reminder Count": "={{ $json.values[0][2] }}",
"Reminder Sent Date": "={{ $json.values[0][1] }}"
},
"schema": [
{
"id": "Reminder Sent",
"type": "string",
"display": true,
"required": false,
"displayName": "Reminder Sent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Sent Date",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Reminder Sent Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Count",
"type": "string",
"display": true,
"required": false,
"displayName": "Reminder Count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"row_number"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "d59d2e7e-81c2-494e-b746-aca42239b667",
"name": "每分钟检查 - 任务",
"type": "n8n-nodes-base.cron",
"position": [
-1280,
224
],
"parameters": {
"triggerTimes": {
"item": [
{
"mode": "everyMinute"
}
]
}
},
"typeVersion": 1
},
{
"id": "dbe514b6-6277-47e7-aeb0-64699fd1401f",
"name": "获取任务",
"type": "n8n-nodes-base.googleSheets",
"position": [
-1056,
224
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Tasks"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "c435a660-e357-48f2-8f3b-8c2bc18aedb6",
"name": "查找并形成消息 - 任务",
"type": "n8n-nodes-base.code",
"position": [
-848,
224
],
"parameters": {
"jsCode": "// Search and form reminders with check of current and past time, including repeated reminders\nconst items = $input.all();\nconst now = new Date();\nconst todayStr = now.toLocaleDateString('en-US');\nconst executorsToNotify = {}; // For new reminders\nconst reminderMessages = []; // For repeated reminders\n\n// Get current hours and minutes\nconst currentHours = now.getHours();\nconst currentMinutes = now.getMinutes();\nconst currentSeconds = now.getSeconds();\n\n// Current time in minutes from start of day for comparison\nconst currentTimeInMinutes = currentHours * 60 + currentMinutes;\n\n// Function to parse sent date (format dd.mm.yyyy, hh:mm)\nfunction parseSentDate(sentDateStr) {\n if (!sentDateStr || sentDateStr.trim() === '') return null;\n try {\n const [datePart, timePart] = sentDateStr.split(', ');\n const [day, month, year] = datePart.split('.');\n const [hours, minutes] = timePart.split(':');\n return new Date(parseInt(year), parseInt(month) - 1, parseInt(day), parseInt(hours), parseInt(minutes));\n } catch (e) {\n console.error('Error parsing sent date:', sentDateStr, e);\n return null;\n }\n}\n\n// Function to parse interval (format hh:mm)\nfunction parseInterval(intervalStr) {\n if (!intervalStr || intervalStr.trim() === '') return 0;\n try {\n const [hours, minutes] = intervalStr.split(':').map(Number);\n return (hours || 0) * 60 * 60 * 1000 + (minutes || 0) * 60 * 1000;\n } catch (e) {\n console.error('Error parsing interval:', intervalStr, e);\n return 0;\n }\n}\n\n// Extended logging\nconsole.log('='.repeat(50));\nconsole.log(`TIME CHECK`);\nconsole.log(`Current date and time: ${now.toLocaleString('en-US')}`);\nconsole.log(`Current time: ${currentHours}:${currentMinutes.toString().padStart(2, '0')}:${currentSeconds.toString().padStart(2, '0')}`);\nconsole.log(`Minutes from start of day: ${currentTimeInMinutes}`);\nconsole.log(`Total records to check: ${items.length - 1}`);\nconsole.log('='.repeat(50));\n\n// Counters for debugging\nlet todayCount = 0;\nlet pastTimeCount = 0;\nlet currentTimeCount = 0;\nlet notSentCount = 0;\nlet toSendNewCount = 0;\nlet toSendReminderCount = 0;\n\nitems.forEach((item, index) => {\n if (index === 0) return; // Skip header\n \n const executorId = String(item.json['Executor ID'] || '');\n const executorName = item.json['Executor'] || '';\n const reminderDateRaw = item.json['Date'];\n const reminderTimeRaw = item.json['Reminder Time'];\n const sent = item.json['Reminder Sent'];\n const sentDateStr = item.json['Reminder Sent Date'];\n const taskId = item.json['Task ID'];\n const taskName = item.json['Task Description'];\n const priority = item.json['Priority'] ? String(item.json['Priority']).toLowerCase() : 'low';\n const object = item.json['Object'];\n const section = item.json['Section'];\n const reportDate = item.json['Report Date'];\n const intervalMs = parseInterval(item.json['Reminder Interval if No Report']);\n const oldCount = parseInt(item.json['Reminder Count'] || '0', 10);\n const rowNumber = item.json['row_number'];\n \n if (!executorId || !reminderDateRaw || !taskId || !executorName || reminderTimeRaw === undefined) {\n return;\n }\n \n // Convert date\n let reminderDate;\n try {\n if (typeof reminderDateRaw === 'number') {\n reminderDate = new Date((reminderDateRaw - 25569) * 86400 * 1000);\n } else if (typeof reminderDateRaw === 'string' && reminderDateRaw.includes('.')) {\n const [day, month, year] = reminderDateRaw.split('.');\n reminderDate = new Date(parseInt(year), parseInt(month) - 1, parseInt(day));\n } else {\n reminderDate = new Date(reminderDateRaw);\n }\n reminderDate.setHours(0, 0, 0, 0);\n } catch (e) {\n console.error(` ❌ Error converting date for row ${index + 2}:`, e);\n return;\n }\n \n // Convert time from Excel format\n let reminderHours, reminderMinutes;\n try {\n if (typeof reminderTimeRaw === 'number') {\n // Excel stores time as a fractional number (1 = 24 hours)\n const totalMinutes = Math.round(reminderTimeRaw * 24 * 60);\n reminderHours = Math.floor(totalMinutes / 60);\n reminderMinutes = totalMinutes % 60;\n } else if (typeof reminderTimeRaw === 'string') {\n // Remove spaces\n const cleanTime = reminderTimeRaw.trim();\n \n // Handle multiple time values (take first)\n if (cleanTime.includes(' ')) {\n const firstTime = cleanTime.split(' ')[0];\n if (/^\\d{1,2}:\\d{2}$/.test(firstTime)) {\n const [h, m] = firstTime.split(':');\n reminderHours = parseInt(h);\n reminderMinutes = parseInt(m);\n } else if (/^\\d{1,2}$/.test(firstTime)) {\n reminderHours = parseInt(firstTime);\n reminderMinutes = 0;\n } else {\n console.error(` ❌ Unknown first time format: ${firstTime}`);\n return;\n }\n }\n // Simple format \"hours:minutes\" (e.g., \"16:14\")\n else if (/^\\d{1,2}:\\d{2}$/.test(cleanTime)) {\n const [h, m] = cleanTime.split(':');\n reminderHours = parseInt(h);\n reminderMinutes = parseInt(m);\n }\n // Format with seconds \"hours:minutes:seconds\" (e.g., \"16:14:00\")\n else if (/^\\d{1,2}:\\d{2}:\\d{2}$/.test(cleanTime)) {\n const [h, m] = cleanTime.split(':');\n reminderHours = parseInt(h);\n reminderMinutes = parseInt(m);\n }\n // AM/PM format\n else if (cleanTime.includes('AM') || cleanTime.includes('PM')) {\n const timeParts = cleanTime.replace(/[AP]M/i, '').trim().split(':');\n reminderHours = parseInt(timeParts[0]);\n reminderMinutes = parseInt(timeParts[1] || '0');\n \n if (cleanTime.toUpperCase().includes('PM') && reminderHours < 12) {\n reminderHours += 12;\n } else if (cleanTime.toUpperCase().includes('AM') && reminderHours === 12) {\n reminderHours = 0;\n }\n } else {\n console.error(` ❌ Unknown time format for row ${index + 2}:`, reminderTimeRaw);\n return;\n }\n } else {\n console.error(` ❌ Unknown time type for row ${index + 2}:`, typeof reminderTimeRaw, reminderTimeRaw);\n return;\n }\n } catch (e) {\n console.error(` ❌ Error converting time for row ${index + 2}:`, e);\n return;\n }\n \n // Reminder time in minutes from start of day\n const reminderTimeInMinutes = reminderHours * 60 + reminderMinutes;\n \n // Check if date matches today\n const today = new Date();\n today.setHours(0, 0, 0, 0);\n const isToday = reminderDate.getTime() === today.getTime();\n \n // Check if time has arrived or matches current\n const isTimeNowOrPast = reminderTimeInMinutes <= currentTimeInMinutes;\n \n // Check if reminder not sent yet\n const notSent = !sent || sent.toString().trim() === '' || sent !== 'Yes';\n const noSentDate = !sentDateStr || sentDateStr.toString().trim() === '';\n const isNotSent = notSent && noSentDate;\n \n // Check for report presence\n const noReport = !reportDate || reportDate.toString().trim() === '';\n \n // Update counters\n if (isToday) {\n todayCount++;\n if (isTimeNowOrPast) {\n if (reminderTimeInMinutes === currentTimeInMinutes) {\n currentTimeCount++;\n } else {\n pastTimeCount++;\n }\n }\n if (isNotSent) notSentCount++;\n }\n \n // Logic for new reminders\n if (isToday && isTimeNowOrPast && isNotSent && noReport) {\n toSendNewCount++;\n \n if (!executorsToNotify[executorId]) {\n executorsToNotify[executorId] = {\n chatId: executorId,\n executorName: executorName,\n tasks: [],\n rowIndexes: []\n };\n }\n \n executorsToNotify[executorId].tasks.push({ \n taskId: taskId, \n taskName: taskName,\n priority: priority,\n object: object,\n section: section,\n rowIndex: rowNumber || index + 2,\n reminderTime: `${reminderHours}:${reminderMinutes.toString().padStart(2, '0')}`,\n isPast: reminderTimeInMinutes < currentTimeInMinutes,\n isNew: true,\n oldCount: oldCount\n });\n executorsToNotify[executorId].rowIndexes.push(rowNumber || index + 2);\n }\n \n // Logic for repeated reminders\n if (!isNotSent && noReport && intervalMs > 0) {\n const sentDate = parseSentDate(sentDateStr);\n if (sentDate && (now.getTime() - sentDate.getTime()) >= intervalMs) {\n toSendReminderCount++;\n let priorityEmoji = '';\n let priorityText = '';\n switch (priority) {\n case 'high':\n priorityEmoji = '🔴';\n priorityText = 'High priority';\n break;\n case 'medium':\n priorityEmoji = '🟡';\n priorityText = 'Medium priority';\n break;\n case 'low':\n priorityEmoji = '🟢';\n priorityText = 'Low priority';\n break;\n case 'reminder':\n case 'do not forget':\n priorityEmoji = '⚠️';\n priorityText = 'Do not forget';\n break;\n default:\n priorityEmoji = '🟢';\n priorityText = 'Low priority';\n }\n \n reminderMessages.push({\n json: {\n chat_id: executorId,\n text: `${priorityEmoji} 🔔 <b>Repeated reminder</b>\\n\\nTask \"<b>${taskName}</b>\" (ID: ${taskId}) is still not closed.\\n\\nPlease send the status in response to the first message with this ID.\n \n If you have any questions, please contact your manager`,\n parse_mode: \"HTML\",\n isReminder: true,\n taskId: taskId,\n taskName: taskName,\n priority: priority,\n rowIndex: rowNumber || index + 2,\n executorName: executorName,\n count: oldCount + 1,\n _isRealMessage: true\n }\n });\n }\n }\n});\n\n// Summary statistics\nconsole.log('\\n' + '='.repeat(50));\nconsole.log('SUMMARY STATISTICS:');\nconsole.log(`- Records with today's date: ${todayCount}`);\nconsole.log(`- Records with current time: ${currentTimeCount}`);\nconsole.log(`- Records with past time: ${pastTimeCount}`);\nconsole.log(`- Records not sent: ${notSentCount}`);\nconsole.log(`- New to send: ${toSendNewCount}`);\nconsole.log(`- Repeated to send: ${toSendReminderCount}`);\nconsole.log(`- Unique executors: ${Object.keys(executorsToNotify).length}`);\nconsole.log('='.repeat(50));\n\n// Form messages for each executor (new)\nconst messages = [];\n\nObject.values(executorsToNotify).forEach(data => {\n const tasksCount = data.tasks.length;\n \n // Form text depending on number of tasks\n let greetingText = '';\n if (tasksCount === 1) {\n greetingText = `You have 1 task for today.`;\n } else {\n greetingText = `You have ${tasksCount} tasks for today.`;\n }\n \n // First message - general greeting\n const greetingMessage = `📋 <b>Good day, ${data.executorName}!</b>\\n\\n📅 <b>Date:</b> ${todayStr}\\n\\n${greetingText}\\n\\nYou will receive the list of tasks in the following messages.\\nTo send a report, reply to the message with the task, indicating:\\n• Status: completed / partially / not completed\\n• Comment (if necessary)\\n\\n📋 <b>Report example:</b>\\n<pre>completed\\nEverything is fine</pre>\\n\\nHave a productive workday! 💪\\n\\n✅ <b>Confirm receipt:</b> <b>/received</b>\\n\\n❓ If you have any questions, click or write /help`;\n \n // IMPORTANT: Add all information for update in the first message\n messages.push({\n json: {\n chat_id: data.chatId,\n text: greetingMessage,\n parse_mode: \"HTML\",\n isGreeting: true,\n executorName: data.executorName,\n rowIndexes: data.rowIndexes,\n _isRealMessage: true\n }\n });\n \n // Then separate messages for each task\n data.tasks.forEach((task) => {\n let priorityEmoji = '';\n let priorityText = '';\n switch (task.priority) {\n case 'high':\n priorityEmoji = '🔴';\n priorityText = 'High priority';\n break;\n case 'medium':\n priorityEmoji = '🟡';\n priorityText = 'Medium priority';\n break;\n case 'low':\n priorityEmoji = '🟢';\n priorityText = 'Low priority';\n break;\n case 'reminder':\n case 'do not forget':\n priorityEmoji = '⚠️';\n priorityText = 'Do not forget';\n break;\n default:\n priorityEmoji = '🟢';\n priorityText = 'Low priority';\n }\n \n const count = task.isNew ? 1 : task.oldCount + 1;\n const taskMessage = `${priorityEmoji} <b>${priorityText}:</b>\\n<b>${task.taskId} ${task.taskName} </b> | ${task.object}, ${task.section} \\n\\n📝 <b>How to send report:</b>\\nReply to this message, indicating:\\n• Status: completed / partially / not completed\\n• Comment (if necessary)\\n\\n📋 <b>Report example:</b>\\n<pre>completed\\nEverything is fine</pre>`;\n \n messages.push({\n json: {\n chat_id: data.chatId,\n text: taskMessage,\n parse_mode: \"HTML\",\n isTaskMessage: true,\n taskId: task.taskId,\n taskName: task.taskName,\n priority: task.priority,\n rowIndex: task.rowIndex,\n executorName: data.executorName,\n count: count,\n _isRealMessage: true\n }\n });\n });\n});\n\n// Add repeated messages\nmessages.push(...reminderMessages);\n\nconsole.log(`\\n💬 Formed ${messages.length} messages to send`);\n\n// If no messages to send, return empty array\nif (messages.length === 0) {\n console.log('📭 No messages to send at the moment');\n return [];\n}\n\nreturn messages;"
},
"typeVersion": 2
},
{
"id": "a3f738eb-22ad-4160-bb1b-06b32a0f2f47",
"name": "有消息要发送吗？ - 任务",
"type": "n8n-nodes-base.if",
"position": [
-656,
224
],
"parameters": {
"conditions": {
"number": [
{
"value1": "={{ $items().length }}",
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "705c7afd-1689-4864-927e-c4925c18b2fa",
"name": "发送到 Telegram - 任务",
"type": "n8n-nodes-base.telegram",
"position": [
-432,
64
],
"webhookId": "37d5ed28-94f5-4992-bc94-ddefa95bc6a4",
"parameters": {
"text": "={{ $json.text }}",
"chatId": "={{ $json.chat_id }}",
"additionalFields": {
"parse_mode": "HTML",
"disable_notification": false
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "c2d813b6-0b50-44b2-8422-447609a04de6",
"name": "准备更新 - 任务",
"type": "n8n-nodes-base.code",
"position": [
-240,
208
],
"parameters": {
"jsCode": "// Extract update data from all messages\nconst allMessages = $input.all();\n\n// Current time\nconst now = new Date();\nconst day = String(now.getDate()).padStart(2, '0');\nconst month = String(now.getMonth() + 1).padStart(2, '0');\nconst year = now.getFullYear();\nconst hours = String(now.getHours()).padStart(2, '0');\nconst minutes = String(now.getMinutes()).padStart(2, '0');\nconst sentDateTime = `${day}.${month}.${year}, ${hours}:${minutes}`;\n\n\n// Create updates for each row\nconst updates = [];\nconst processedRows = new Set();\n\n// Process ALL messages - both greeting and reminders\nallMessages.forEach(msg => {\n // For greeting messages\n if (msg.json.isGreeting === true && msg.json.rowIndexes) {\n const rowIndexes = msg.json.rowIndexes || [];\n rowIndexes.forEach(rowIndex => {\n if (rowIndex && !isNaN(rowIndex) && !processedRows.has(rowIndex)) {\n processedRows.add(rowIndex);\n updates.push({\n json: {\n values: [[\"Yes\", sentDateTime, \"1\"]], // Initial counter = 1\n _rowIndex: Number(rowIndex)\n }\n });\n }\n });\n }\n \n // For reminder messages\n if (msg.json.isReminder === true && msg.json.rowIndex) {\n const rowIndex = msg.json.rowIndex;\n const count = msg.json.count || 1;\n const track = \"ddc_task_handler_step1\";\n\n if (rowIndex && !isNaN(rowIndex) && !processedRows.has(rowIndex)) {\n processedRows.add(rowIndex);\n updates.push({\n json: {\n values: [[\"Yes\", sentDateTime, String(count)]],\n _rowIndex: Number(rowIndex)\n }\n });\n }\n }\n});\n\nconsole.log(`Prepared ${updates.length} updates`);\nreturn updates.length > 0 ? updates : [];"
},
"typeVersion": 2
},
{
"id": "2368cc21-ed51-4a63-b865-a1dacc5906a7",
"name": "有更新吗？ - 任务",
"type": "n8n-nodes-base.if",
"position": [
-64,
208
],
"parameters": {
"conditions": {
"number": [
{
"value1": "={{ $items().length }}",
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "b3aed171-764d-4677-bd0a-3369cfdce925",
"name": "在表中更新状态 - 任务",
"type": "n8n-nodes-base.googleSheets",
"position": [
160,
128
],
"parameters": {
"columns": {
"value": {
"row_number": "={{ $json._rowIndex }}",
"Reminder Sent": "={{ $json.values[0][0] }}",
"Reminder Count": "={{ $json.values[0][2] }}",
"Reminder Sent Date": "={{ $json.values[0][1] }}"
},
"schema": [
{
"id": "Reminder Sent",
"type": "string",
"display": true,
"required": false,
"displayName": "Reminder Sent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Sent Date",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Reminder Sent Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Count",
"type": "string",
"display": true,
"required": false,
"displayName": "Reminder Count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"row_number"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Tasks"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "4d6f568b-c9ee-4bcf-b724-395e96c377e7",
"name": "无消息 - 任务",
"type": "n8n-nodes-base.noOp",
"position": [
-416,
288
],
"parameters": {},
"typeVersion": 1
},
{
"id": "f7962f39-a98e-409e-92e8-d272d10463e1",
"name": "无更新 - 任务",
"type": "n8n-nodes-base.noOp",
"position": [
160,
304
],
"parameters": {},
"typeVersion": 1
},
{
"id": "74780bb6-5901-46cd-87bc-0c68aec00483",
"name": "便签",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1344,
-64
],
"parameters": {
"color": 2,
"width": 1710,
"height": 534,
"content": "## ⏰ 自动提醒"
},
"typeVersion": 1
},
{
"id": "c707b795-1179-4097-93ac-6e8f7f41c6f2",
"name": "便签1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1344,
512
],
"parameters": {
"color": 2,
"width": 1710,
"height": 566,
"content": "## ⏰ 自动提醒照片报告"
},
"typeVersion": 1
},
{
"id": "e4bbd811-64e3-462a-99fe-35c0dfec0d06",
"name": "检查 register_user1",
"type": "n8n-nodes-base.if",
"position": [
1072,
128
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "register_user"
}
]
}
},
"typeVersion": 1
},
{
"id": "230ff3e2-c604-4c82-a622-ed10d0f73501",
"name": "读取用户表1",
"type": "n8n-nodes-base.googleSheets",
"position": [
1280,
112
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupColumn": "Telegram ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1001730935,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc/edit#gid=1001730935",
"cachedResultName": "Users"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "66dd423e-bb7d-4237-b69d-c16a9322c423",
"name": "检查注册1",
"type": "n8n-nodes-base.code",
"position": [
1456,
208
],
"parameters": {
"jsCode": "// Check and register user\nconst items = $input.all();\nconst chatId = String($node['Process incoming message1'].json.chatId);\nconst userName = $node['Process incoming message1'].json.userName;\nconst userId = $node['Process incoming message1'].json.userId;\nconst now = new Date();\nconst registrationDate = now.toLocaleDateString('en-US');\nconst registrationTime = now.toLocaleString('en-US');\nconst track = \"ddc_task_handler_step1\";\n\n// Check if the user is already in the table\nlet userExists = false;\nlet userRow = null;\n\nitems.forEach((item, index) => {\n if (index === 0) return; // Skip header\n \n const itemChatId = String(item.json['Executor ID'] || '').trim();\n if (itemChatId === chatId) {\n userExists = true;\n userRow = item.json;\n }\n});\n\nif (userExists) {\n // User is already registered\n return [{\n json: {\n action: 'send_welcome_existing',\n chatId: chatId,\n userName: userRow['Executor Name'] || userName,\n registrationDate: userRow['Registration Date'] || registrationDate,\n userExists: true\n }\n }];\n} else {\n // New user - registration required\n return [{\n json: {\n action: 'register_new_user',\n chatId: chatId,\n userName: userName,\n userId: userId,\n registrationDate: registrationDate,\n registrationTime: registrationTime,\n userExists: false\n }\n }];\n}\n"
},
"typeVersion": 2
},
{
"id": "005fdb22-cb8e-48a0-98cb-48cb1df5f751",
"name": "检查新用户1",
"type": "n8n-nodes-base.if",
"position": [
1632,
96
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "register_new_user"
}
]
}
},
"typeVersion": 1
},
{
"id": "99fdba48-a9a5-4897-b2c7-908c2af25087",
"name": "生成欢迎1",
"type": "n8n-nodes-base.code",
"position": [
1920,
272
],
"parameters": {
"jsCode": "// Generate welcome message\nconst userData = $input.first().json;\nconst userName = userData.userName;\nconst isNewUser = userData.action === 'register_new_user';\n\nlet message;\n\nif (isNewUser) {\n // Message for new user\n message = `🎉 **Welcome, ${userName}!**\\n\\n`;\n message += `✅ You have been successfully registered in the photo reports system.\\n\\n`;\n message += `🆔 Your Telegram ID: \\`${userData.chatId}\\`\\n\\n`;\n message += `📋 **Your report types:**\\n`;\n message += `• Photos of the general work log\\n`;\n message += `• Photos of the welding log\\n`;\n message += `• Photos of the concrete work log\\n`;\n message += `• Photos of the construction equipment log\\n`;\n message += `• Photos of primary accounting documents\\n\\n`;\n message += `📸 **How to work with the bot:**\\n`;\n message += `1. Every morning you will receive reminders about required reports\\n`;\n message += `2. Reply to reminders by attaching photos\\n`;\n message += `3. Add comments to photos if necessary\\n\\n`;\n message += `**Main commands:**\\n`;\n message += `/help - Help on working with the bot\\n`;\n message += `/report - Send report manually\\n`;\n message += `/status - Check today's sent reports\\n`;\n message += `/read - Confirm receipt of all reminders\\n\\n`;\n message += `To start, wait for morning reminders or use the /report command`;\n} else {\n // Message for existing user\n const regDate = userData.registrationDate || 'not specified';\n message = `👋 **Welcome back, ${userName}!**\\n\\n`;\n message += `✅ You are already registered in the system.\\n`;\n message += `📅 Registration date: ${regDate}\\n\\n`;\n message += `🆔 Your Telegram ID: \\`${userData.chatId}\\`\\n\\n`;\n message += `📋 **Main commands:**\\n`;\n message += `/read - Confirm receipt of all reminders\\n\\n`;\n message += `To send reports, wait for morning reminders or use the /report command`;\n}\n\nreturn [{\n json: {\n action: 'send_message',\n chatId: userData.chatId,\n message: message\n }\n}];"
},
"typeVersion": 2
},
{
"id": "5922f0b6-daf2-4cc2-8831-e34da87065c6",
"name": "检查现有用户1",
"type": "n8n-nodes-base.if",
"position": [
1632,
272
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "send_welcome_existing"
}
]
}
},
"typeVersion": 1
},
{
"id": "e5e6ebd8-52f2-4e1b-a9a7-a1731ae1860c",
"name": "Telegram Webhook - 接收消息",
"type": "n8n-nodes-base.telegramTrigger",
"position": [
80,
1952
],
"webhookId": "photo-reports-webhook",
"parameters": {
"updates": [
"message",
"photo",
"document"
],
"additionalFields": {}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "6229b502-5c7c-4d86-b6bd-30bd2ca35e00",
"name": "检查 send_message",
"type": "n8n-nodes-base.if",
"position": [
1056,
304
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "send_message"
}
]
}
},
"typeVersion": 1
},
{
"id": "ecf60d70-b347-4009-92ae-15195b820510",
"name": "发送消息",
"type": "n8n-nodes-base.telegram",
"position": [
2352,
560
],
"webhookId": "31de700b-0a6a-4827-9a2b-99edf39c197d",
"parameters": {
"text": "={{ $json['message'] }}",
"chatId": "={{ $json['chatId'] }}",
"additionalFields": {
"parse_mode": "HTML"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "cfa18342-6079-43e0-b59a-79235d3c72d8",
"name": "检查 check_status",
"type": "n8n-nodes-base.if",
"position": [
1072,
752
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "check_today_status"
}
]
}
},
"typeVersion": 1
},
{
"id": "a1e5ce9a-5a06-4468-99d1-07bc80e85118",
"name": "读取状态数据",
"type": "n8n-nodes-base.googleSheets",
"position": [
1296,
752
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "52fd3be4-0a06-45ae-a001-d54d682a7cf2",
"name": "从表格收集状态",
"type": "n8n-nodes-base.code",
"position": [
1472,
752
],
"parameters": {
"jsCode": "// Collect user report status for today\nconst chatId = String($node['Process incoming message1'].json.chatId);\nconst today = new Date().toLocaleDateString('en-US');\nconst items = $input.all();\nconst userReports = {};\n\nitems.forEach((item, index) => {\n if (index < 1) return;\n const itemChatId = String(item.json['Executor ID'] || '');\n if (itemChatId !== chatId) return;\n \n const reportId = item.json['Report ID'];\n const reportType = item.json['Document Type'] || item.json['Report Type'] || 'Unknown type';\n const reminderDate = item.json['Reminder Date'];\n const sent = item.json['Reminder Sent'];\n const read = item.json['Reminder Read'];\n const photoDate = item.json['Report Date'];\n const photoTime = item.json['Report Received Time'];\n const fileStatus = item.json['Processing Status'];\n \n if (!reportId) return;\n \n // Initialize report entry if not exists\n if (!userReports[reportId]) {\n userReports[reportId] = {\n reportId: reportId,\n reportType: reportType,\n sent: false,\n read: false,\n photos: []\n };\n }\n \n // Check reminder\n if (reminderDate) {\n let taskDate;\n if (typeof reminderDate === 'number') {\n taskDate = new Date((reminderDate - 25569) * 86400 * 1000);\n } else {\n taskDate = new Date(reminderDate);\n }\n const taskDateStr = taskDate.toLocaleDateString('en-US');\n \n if (taskDateStr === today && sent === 'Yes') {\n userReports[reportId].sent = true;\n if (read === 'Yes') {\n userReports[reportId].read = true;\n }\n }\n }\n \n // Check received photos\n if (photoDate === today && fileStatus) {\n userReports[reportId].photos.push({\n time: photoTime,\n status: fileStatus\n });\n }\n});\n\n// Convert to array\nconst reportsArray = Object.values(userReports);\n\nreturn [{\n json: {\n userReports: reportsArray,\n totalReports: reportsArray.length\n }\n}];"
},
"typeVersion": 2
},
{
"id": "c418e056-15a5-4233-b604-78830c30dabb",
"name": "生成状态消息",
"type": "n8n-nodes-base.code",
"position": [
1680,
752
],
"parameters": {
"jsCode": "// Generate status message\nconst summaryItem = $input.first();\nconst chatId = String($node['Process incoming message1'].json.chatId);\nconst userName = $node['Process incoming message1'].json.userName;\nconst today = new Date().toLocaleDateString('en-US');\n\nconst summary = {\n sent: [],\n read: [],\n received: [],\n missing: []\n};\n\nconst reports = summaryItem.json.userReports || [];\n\nreports.forEach(report => {\n const reportStr = `${report.reportId} ${report.reportType}`;\n \n if (report.sent) {\n summary.sent.push(reportStr);\n if (report.read) {\n summary.read.push(reportStr);\n }\n if (report.photos.length > 0) {\n const photoInfo = report.photos.length > 1 ? ` (${report.photos.length} photos)` : '';\n summary.received.push(reportStr + photoInfo);\n } else {\n summary.missing.push(reportStr);\n }\n }\n});\n\nlet message = `📊 **Status of your reports for today**\\n\\n`;\nmessage += `📅 Date: ${today}\\n\\n`;\n\nif (summary.sent.length > 0) {\n message += `📤 **Reminders sent:** ${summary.sent.length}\\n`;\n \n if (summary.read.length > 0) {\n message += `✅ **Read confirmed:** ${summary.read.length}\\n`;\n } else {\n message += `⚠️ **Not confirmed:** ${summary.sent.length - summary.read.length}\\n`;\n message += `💡 Use /read to confirm\\n`;\n }\n \n message += `\\n`;\n \n if (summary.received.length > 0) {\n message += `📸 **Photo reports received:** ${summary.received.length}\\n`;\n summary.received.forEach(report => {\n message += ` ✓ ${report}\\n`;\n });\n }\n \n if (summary.missing.length > 0) {\n message += `\\n❌ **Reports expected:**\\n`;\n summary.missing.forEach(report => {\n message += ` • ${report}\\n`;\n });\n message += `\\n💡 Reply to the corresponding reminders with photos.`;\n } else {\n message += `\\n✨ **All reports sent! Great job!**`;\n }\n} else {\n message += `ℹ️ No reminders found for today.\\n\\n`;\n message += `Possible reasons:\\n`;\n message += `• Reminders not sent yet\\n`;\n message += `• No scheduled reports today\\n\\n`;\n message += `If you think this is an error, contact the manager.`;\n}\n\nreturn [{\n json: {\n action: 'send_message',\n chatId: chatId,\n message: message\n }\n}];"
},
"typeVersion": 2
},
{
"id": "e1988e2d-0839-4d47-bf6f-14a3429cae88",
"name": "检查 send_report_menu",
"type": "n8n-nodes-base.if",
"position": [
1072,
608
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "send_report_menu"
}
]
}
},
"typeVersion": 1
},
{
"id": "c19ed2c7-b49b-4dee-9027-73a789c83d90",
"name": "检查 manual_report",
"type": "n8n-nodes-base.if",
"position": [
1072,
464
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "send_manual_report_instruction"
}
]
}
},
"typeVersion": 1
},
{
"id": "4f9ff1b4-2518-45ce-9e80-c41ede66bf30",
"name": "请求注册数据1",
"type": "n8n-nodes-base.code",
"position": [
1920,
112
],
"parameters": {
"jsCode": "// Generate message requesting registration data\n\nreturn [{\n json: {\n action: 'send_message',\n chatId: $json.chatId,\n message: `👋 Welcome to the executor log!\\n\\nYour Telegram ID: ${$json.chatId}\\n\\n📝 To complete registration, reply to this message providing:\\n<pre>Name: John Doe\nCompany: BuildCorp</pre>\\n\\n✏️ Example for copying:\\n<pre>Name: \nCompany: </pre>\\n\\n⚠️ <b>Important:</b> Be sure to reply exactly to this message!`\n\n }\n}];"
},
"typeVersion": 2
},
{
"id": "22bfd7fe-b9b6-4b59-b789-59dd47631f2f",
"name": "便签7",
"type": "n8n-nodes-base.stickyNote",
"position": [
528,
528
],
"parameters": {
"width": 2094,
"height": 442,
"content": "## 🔧 服务命令"
},
"typeVersion": 1
},
{
"id": "8021cf50-fc7d-4348-bd97-68b8ddad1de9",
"name": "便签2",
"type": "n8n-nodes-base.stickyNote",
"position": [
528,
-64
],
"parameters": {
"color": 4,
"width": 2096,
"height": 570,
"content": "## 👤 注册区块"
},
"typeVersion": 1
},
{
"id": "e91948d6-a532-48e3-a885-937e6dbc13e7",
"name": "检查 read",
"type": "n8n-nodes-base.if",
"position": [
1040,
1440
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "process_read"
}
]
}
},
"typeVersion": 1
},
{
"id": "5d9a087b-62c5-4368-8226-0b9eee2809f7",
"name": "读取 read 表",
"type": "n8n-nodes-base.googleSheets",
"position": [
1248,
1472
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "cbdab081-102d-47b7-ab92-8890d6e47e7a",
"name": "准备 read 更新",
"type": "n8n-nodes-base.code",
"position": [
1408,
1472
],
"parameters": {
"jsCode": "// Logic from lager2 for processing /read\nconst items = $input.all();\nconst chatId = String($node['Process incoming message1'].json.chatId);\nconst userName = $node['Process incoming message1'].json.userName;\nconst now = new Date();\nconst day = String(now.getDate()).padStart(2, '0');\nconst month = String(now.getMonth() + 1).padStart(2, '0');\nconst year = now.getFullYear();\nconst hours = String(now.getHours()).padStart(2, '0');\nconst minutes = String(now.getMinutes()).padStart(2, '0');\nconst readDateTime = `${day}.${month}.${year}, ${hours}:${minutes}`;\nconst today = now.toLocaleDateString('en-US');\n\n// Filter tasks for this chatId for today and sent\n const updates = [];\nlet foundReminders = 0;\nconst reportDetails = [];\n\nitems.forEach((item, index) => {\n if (index === 0) return; // Skip header\n \n const itemChatId = String(item.json['Executor ID'] || '').trim();\n const sent = item.json['Reminder Sent'];\n const read = item.json['Reminder Read'];\n const reminderDateRaw = item.json['Reminder Date'];\n const reportId = item.json['Report ID'];\n const reportType = item.json['Report Type'] || item.json['Document Type'] || '';\n \n if (!reminderDateRaw || !reportId) return;\n \n let taskDate;\n try {\n if (typeof reminderDateRaw === 'number') {\n taskDate = new Date((reminderDateRaw - 25569) * 86400 * 1000);\n } else if (typeof reminderDateRaw === 'string' && reminderDateRaw.includes('.')) {\n const [day, month, year] = reminderDateRaw.split('.');\n taskDate = new Date(parseInt(year), parseInt(month) - 1, parseInt(day));\n } else {\n taskDate = new Date(reminderDateRaw);\n }\n taskDate.setHours(0, 0, 0, 0);\n } catch (e) {\n console.error('Error parsing date:', reminderDateRaw, e);\n return;\n }\n \n const taskDateStr = taskDate.toLocaleDateString('en-US');\n \n // Check conditions as in lager2\n if (itemChatId === chatId && taskDateStr === today && sent === 'Yes' && (!read || read !== 'Yes')) {\n const rowIndex = index + 2; // +2 for Excel\n foundReminders++;\n \n reportDetails.push({\n reportId: reportId,\n reportType: reportType\n });\n \n updates.push({\n json: {\n values: [['Yes', readDateTime]],\n _rowIndex: rowIndex\n }\n });\n }\n});\n\nconsole.log(`Found ${foundReminders} tasks for updating read status`);\n\n// Add summary information at the end (for user response)\nif (foundReminders > 0 || true) { // Always add for response\n updates.push({\n json: {\n isReadSummary: true,\n foundReminders: foundReminders,\n reportDetails: reportDetails,\n chatId: chatId,\n readDateTime: readDateTime\n }\n });\n}\n\nreturn updates;"
},
"typeVersion": 2
},
{
"id": "a62265f0-80a3-4dea-a7d2-feb78b40d90f",
"name": "过滤 read 摘要",
"type": "n8n-nodes-base.if",
"position": [
1664,
1312
],
"parameters": {
"conditions": {
"boolean": [
{
"value1": "={{ $json['isReadSummary'] }}",
"value2": true
}
]
}
},
"typeVersion": 1
},
{
"id": "bcb15f41-9dc6-4889-a23d-38443e62636a",
"name": "过滤有效 read 更新",
"type": "n8n-nodes-base.if",
"position": [
1632,
1504
],
"parameters": {
"conditions": {
"number": [
{
"value1": "={{ $json._rowIndex }}",
"operation": "isNotEmpty"
},
{
"value1": "={{ $json._rowIndex }}",
"value2": 1,
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "03d531ac-9cc6-4209-a15f-cd61ef83a109",
"name": "在表中更新 read 状态",
"type": "n8n-nodes-base.googleSheets",
"position": [
1856,
1472
],
"parameters": {
"columns": {
"value": {
"Read Time": "={{ $json.values[0][1] }}",
"row_number": "={{ $json._rowIndex }}",
"Reminder Read": "={{ $json.values[0][0] }}"
},
"schema": [
{
"id": "Reminder Read",
"type": "string",
"display": true,
"required": false,
"displayName": "Reminder Read",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Read Time",
"type": "string",
"display": true,
"required": false,
"displayName": "Read Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"row_number"
]
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "4b4937fd-9f96-42b6-8a8c-d7208f7e41e7",
"name": "发送 read 确认",
"type": "n8n-nodes-base.telegram",
"position": [
2320,
1360
],
"webhookId": "8d79c01c-7c9a-44d9-890f-faba6cb77712",
"parameters": {
"text": "={{ $json['message'] }}",
"chatId": "={{ $node['Process incoming message1'].json.chatId }}",
"additionalFields": {
"parse_mode": "Markdown"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "957aa208-2f46-4d61-9dce-fc85dbea9f7d",
"name": "形成 read 响应",
"type": "n8n-nodes-base.code",
"position": [
1856,
1296
],
"parameters": {
"jsCode": "// Forming response for /read\nconst summaryData = $input.first();\nif (!summaryData.json.isReadSummary) {\n return [];\n}\n\nconst totalUpdated = summaryData.json.foundReminders || 0;\nconst chatId = summaryData.json.chatId;\nconst readDateTime = summaryData.json.readDateTime;\nconst reportDetails = summaryData.json.reportDetails || [];\nconst track = \"ddc_task_handler_v1\";\n\nlet responseMessage;\n\nif (totalUpdated > 0) {\n responseMessage = `✅ **Thank you for confirming receipt!**\\n\\n`;\n responseMessage += `📋 Marked as read: ${totalUpdated} reminders\\n`;\n responseMessage += `⏰ Time: ${readDateTime}\\n\\n`;\n \n if (reportDetails.length > 0) {\n responseMessage += `📝 **Confirmed reports:**\\n`;\n reportDetails.forEach(detail => {\n responseMessage += `• ${detail.reportId} - ${detail.reportType}\\n`;\n });\n responseMessage += `\\n`;\n }\n \n responseMessage += `📸 **How to send a photo report:**\\n`;\n responseMessage += `1. Find the required reminder in the chat\\n`;\n responseMessage += `2. Click \"Reply\" on this message\\n`;\n responseMessage += `3. Attach a photo and send\\n\\n`;\n \n responseMessage += `Have a great working day 💪`;\n} else {\n responseMessage = `ℹ️ **Information**\\n\\n`;\n responseMessage += `No unread reminders found for today.\\n\\n`;\n responseMessage += `Possible reasons:\\n`;\n responseMessage += `• All reminders are already marked as read\\n`;\n responseMessage += `• No scheduled reminders for today\\n`;\n responseMessage += `• Reminders have not been sent yet\\n\\n`;\n responseMessage += `📊 Use /status to check the status of your reports.`;\n}\n\nreturn [{\n json: {\n message: responseMessage\n }\n}];"
},
"typeVersion": 2
},
{
"id": "5b2225ca-eb89-4738-a734-6fa15f830889",
"name": "检查 no_reply 错误",
"type": "n8n-nodes-base.if",
"position": [
1040,
1248
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "send_error_no_reply"
}
]
}
},
"typeVersion": 1
},
{
"id": "a0383fb5-67b9-4685-b55f-8e8e92484e6b",
"name": "错误消息",
"type": "n8n-nodes-base.telegram",
"position": [
1280,
1232
],
"webhookId": "4e3d940f-9988-41ed-aa86-7e9ee575568e",
"parameters": {
"text": "={{ $json['message'] }}",
"chatId": "={{ $json['chatId'] }}",
"additionalFields": {
"parse_mode": "Markdown"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "cce4c92c-ef56-44a9-a7e0-621f0263291c",
"name": "检查 complete_registration1",
"type": "n8n-nodes-base.if",
"position": [
1040,
1056
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json.action }}",
"value2": "complete_registration"
}
]
}
},
"typeVersion": 1
},
{
"id": "92ecb9af-1702-4aa1-8258-e793aa728f4d",
"name": "准备记录数据",
"type": "n8n-nodes-base.code",
"position": [
1280,
1040
],
"parameters": {
"jsCode": "// Add registration date and time\nconst now = new Date();\nconst registrationDate = now.toLocaleDateString('en-US');\nconst registrationTime = now.toLocaleString('en-US');\n\nreturn [{\n json: {\n ...($json || {}),\n registrationDate,\n registrationTime\n }\n}];"
},
"typeVersion": 2
},
{
"id": "b252ffbd-7662-44cb-bae7-86b2336596f5",
"name": "记录新执行者",
"type": "n8n-nodes-base.googleSheets",
"position": [
1488,
1040
],
"parameters": {
"columns": {
"value": {
"Company": "={{ $json.company }}",
"Telegram ID": "={{ $json.userId }}",
"Executor Name": "={{ $json.executorName }}",
"Telegram Name": "={{ $json.userName }}",
"Registration Date": "={{ $json.registrationTime }}"
},
"schema": [
{
"id": "Telegram ID",
"type": "string",
"display": true,
"required": false,
"displayName": "Telegram ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Registration Date",
"type": "string",
"display": true,
"required": false,
"displayName": "Registration Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Executor Name",
"type": "string",
"display": true,
"required": false,
"displayName": "Executor Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Telegram Name",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Telegram Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Company",
"type": "string",
"display": true,
"required": false,
"displayName": "Company",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "append",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1915656225,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc/edit#gid=1915656225",
"cachedResultName": "Users"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "46c24921-478d-4081-9711-882db451e685",
"name": "确认注册",
"type": "n8n-nodes-base.code",
"position": [
1696,
1040
],
"parameters": {
"jsCode": "// Get data from input stream\nconst inputData = $input.first().json;\n\n// Extract required fields\nconst executorName = inputData['Executor Name'] || inputData.executorName || 'Not specified';\nconst company = inputData['Company'] || inputData.company || 'Not specified';\nconst telegramId = inputData['Telegram ID'] || inputData.userId || inputData.chatId || 'Not specified';\nconst registrationDate = inputData['Registration Date'] || inputData.registrationTime || new Date().toLocaleString('en-US');\nconst dadbc = \"Tasks and Reports Pipeline - DDC\";\n\n// Form a nice confirmation message\nconst message = `✅ <b>Registration successfully completed!</b>\n\n👤 <b>Name:</b> ${executorName}\n🏢 <b>Company:</b> ${company}\n🆔 <b>Telegram ID:</b> ${telegramId}\n📅 <b>Registration Date:</b> ${registrationDate}\n\n✨ Now you can:\n• Receive tasks and reminders for the day from the manager\n• Send task completion statuses\n• Send photo reports for tasks\n\n🚀 Please wait for the first message with the list of reports from your manager, which usually comes at the beginning of the working day!\n\\n\n❓ If you have any questions, click or write /help\n`;\n\nreturn [{\n json: {\n chatId: telegramId,\n message: message,\n parse_mode: 'HTML'\n }\n}];"
},
"typeVersion": 2
},
{
"id": "9f04439c-c755-4df4-a3c6-1eab5db91187",
"name": "发送消息2",
"type": "n8n-nodes-base.telegram",
"position": [
1872,
1040
],
"webhookId": "b4d6fd80-8694-4e9b-abe5-86ddb964aa7a",
"parameters": {
"text": "={{ $json.message }}",
"chatId": "={{ $json.chatId }}",
"additionalFields": {
"parse_mode": "={{ $json.parse_mode }}"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "b40af9b3-0380-4b6c-9ea6-f6493a43a7a8",
"name": "按任务分组文件",
"type": "n8n-nodes-base.code",
"position": [
2608,
1904
],
"parameters": {
"jsCode": "// Grouping files by Task ID\nconst allItems = $input.all();\n\nif (!allItems || allItems.length === 0) {\n console.error('No files for grouping');\n return [];\n}\n\n// Group files by taskId\n const groupedByTask = {};\n\nallItems.forEach(item => {\n const data = item.json;\n if (!data || !data.reportData || !data.reportData.taskId) {\n console.warn('Skipped file without taskId');\n return;\n }\n \n const taskId = data.reportData.taskId;\n \n if (!groupedByTask[taskId]) {\n groupedByTask[taskId] = {\n taskId: taskId,\n files: [],\n reportData: data.reportData, // Common data from the first file\n messageData: data.messageData\n };\n }\n \n // Add file to group\n groupedByTask[taskId].files.push({\n fileData: data.fileData,\n reportData: data.reportData,\n messageData: data.messageData\n });\n});\n\n// Return groups as separate items\n const results = Object.values(groupedByTask).map(group => {\n console.log(`Group for taskId ${group.taskId}: ${group.files.length} files`);\n \n return {\n json: {\n taskId: group.taskId,\n filesGroup: group.files,\n reportData: group.reportData,\n messageData: group.messageData,\n filesCount: group.files.length\n }\n };\n});\n\nreturn results;"
},
"typeVersion": 2
},
{
"id": "b4ab9181-6006-4f04-afe6-f2e7a3a5c35a",
"name": "合并查找和分组文件",
"type": "n8n-nodes-base.merge",
"position": [
2928,
1888
],
"parameters": {
"mode": "multiplex"
},
"typeVersion": 1
},
{
"id": "26923851-ba6b-4cb0-95ff-c0789c89063c",
"name": "合并以确认",
"type": "n8n-nodes-base.merge",
"position": [
3744,
1776
],
"parameters": {
"mode": "multiplex"
},
"typeVersion": 1
},
{
"id": "55a3223a-885a-4c7e-8dac-a20862223a6f",
"name": "准备确认",
"type": "n8n-nodes-base.code",
"position": [
3920,
1776
],
"parameters": {
"jsCode": "// Get data for confirmation\n const data = $json;\n\n// Check for required data\nif (!data || !data.confirmationData) {\n console.error('Missing data for confirmation');\n return [];\n}\n\n const confirmData = data.confirmationData;\n\n// Form text depending on the operation\nlet operationText = '';\nif (confirmData._newFilesCount !== undefined) {\n // This is an update\n operationText = `Added ${confirmData._newFilesCount} ${confirmData._newFilesCount === 1 ? 'file' : 'file(s)'} to existing task.`;\n} else {\n // This is a new entry\n operationText = `Created new entry with ${confirmData._filesCount} ${confirmData._filesCount === 1 ? 'file' : 'files'}.`;\n}\n\nconsole.log('Sending confirmation:', {\n taskId: confirmData._taskId,\n filesCount: confirmData._filesCount,\n operation: confirmData._newFilesCount !== undefined ? 'update' : 'create'\n});\n\n// Return data for sending confirmation\nreturn [{\n json: {\n chatId: confirmData._chatId,\n taskId: confirmData._taskId,\n originalMessageId: confirmData._originalMessageId,\n filesCount: confirmData._filesCount,\n newFilesCount: confirmData._newFilesCount,\n rowData: {\n 'Report ID': confirmData['Report ID'],\n 'Report Date': confirmData['Report Date'],\n 'Report Received Time': confirmData['Report Received Time'],\n 'Processing Status': confirmData['Processing Status'],\n 'File Names': confirmData._fileNames\n },\n operationText: operationText,\n updateSuccess: true\n }\n}];"
},
"typeVersion": 2
},
{
"id": "d007604b-f5e9-4752-b941-5545f07f700b",
"name": "检查 process_photo3",
"type": "n8n-nodes-base.if",
"position": [
1024,
1776
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "process_photo"
}
]
}
},
"typeVersion": 1
},
{
"id": "f970538b-80cc-4f0e-9e2f-f6f310906cb7",
"name": "提取照片数据4",
"type": "n8n-nodes-base.code",
"position": [
1248,
1760
],
"parameters": {
"jsCode": "// Extract data from photo with focus on Task ID\n const input = $node['Process incoming message1'].json;\n\n// Check input data\nif (!input || !input.originalMessage) {\n return [{\n json: {\n error: true,\n errorMessage: 'Missing message data',\n chatId: input?.chatId || 'unknown'\n }\n }];\n}\n\n const message = input.originalMessage;\nconst chatId = String(message.chat?.id || '');\nconst caption = message.caption || '';\nconst replyTo = input.replyToMessage;\nconst userName = (message.from?.first_name || '') + ' ' + (message.from?.last_name || '');\n\n// Check for photo\nif (!message.photo || !Array.isArray(message.photo) || message.photo.length === 0) {\n return [{\n json: {\n error: true,\n errorMessage: 'No photo in message',\n chatId: chatId,\n originalMessage: message\n }\n }];\n}\n\n// Parse report information - IMPORTANT: Task ID\nlet reportType = 'Undefined type';\nlet taskId = null; // Task ID\n\nif (replyTo?.text) {\n const replyText = replyTo.text;\n \n // Parsing ID from format: 🔸 ID4 Photo of primary...\n const idMatch = replyText.match(/🔸\\s*(ID\\d+)\\s+(.+?)(?=\\n|$)/i);\n if (idMatch) {\n taskId = idMatch[1]; // This is Task ID\n reportType = idMatch[2].trim();\n }\n \n // Alternative format for manual reports\n if (!taskId) {\n const manualMatch = replyText.match(/(MANUAL_\\d+)/i);\n if (manualMatch) {\n taskId = manualMatch[1]; // Task ID for manual report\n const typeMatch = replyText.match(/📸\\s*\\*\\*(.+?)\\*\\*/);\n if (typeMatch) {\n reportType = typeMatch[1];\n }\n }\n }\n}\n\n// Validate Task ID\nif (!taskId) {\n console.error('Could not parse task ID from:', replyTo?.text);\n return [{\n json: {\n error: true,\n errorMessage: 'Unable to determine Task ID. Make sure you are replying to the correct reminder message.',\n chatId: chatId,\n originalMessage: message\n }\n }];\n}\n\n// Process photo\n const now = new Date();\nconst dateStr = now.toLocaleDateString('en-US');\nconst timeStr = now.toLocaleString('en-US');\nconst ddc = \"Pipeline: https://datadrivenconstruction.io/ by Artem Boiko\";\n\n// Take the largest photo size (last in array)\n const largestPhoto = message.photo[message.photo.length - 1];\nconst fileId = largestPhoto.file_id;\nconst fileSize = largestPhoto.file_size || 0;\n\n// Check file size\nif (fileSize > 20 * 1024 * 1024) {\n return [{\n json: {\n error: true,\n errorMessage: `File too large: ${(fileSize / 1024 / 1024).toFixed(2)} MB. Maximum size 20 MB.`,\n chatId: chatId,\n originalMessage: message\n }\n }];\n}\n\n// Generate unique file name\n const timestamp = Date.now();\nconst fileName = `${chatId}_${taskId}_${message.message_id}_${timestamp}.jpg`;\n\n// Determine Media Group ID for grouping files in one message\n const mediaGroupId = message.media_group_id || `single_${message.message_id}`;\n\nconsole.log(`Processing photo: ${fileName}, Task ID: ${taskId}, Media Group: ${mediaGroupId}`);\n\n// Return data for processing one photo\nreturn [{\n json: {\n chatId: chatId,\n userName: userName,\n reportType: reportType,\n taskId: taskId, // Task ID - main grouping key\n caption: caption,\n date: dateStr,\n time: timeStr,\n originalMessage: message,\n replyToMessageId: replyTo?.message_id || '',\n fileId: fileId,\n fileName: fileName,\n fileSize: fileSize,\n mediaGroupId: mediaGroupId, // For identifying files from one message\n messageId: message.message_id,\n timestamp: timestamp\n }\n}];"
},
"typeVersion": 2
},
{
"id": "c8f5e55d-be0a-4368-a42b-9465fa31f908",
"name": "检查错误2",
"type": "n8n-nodes-base.if",
"position": [
1440,
1760
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json.error }}",
"value2": "true"
}
]
}
},
"typeVersion": 1
},
{
"id": "41e8c798-469c-4d52-9a5b-9afda4aafc38",
"name": "照片错误消息4",
"type": "n8n-nodes-base.telegram",
"position": [
1632,
1744
],
"webhookId": "caa76ffe-a249-4fc3-81cf-396f0bfa9ed2",
"parameters": {
"text": "❌ **Error processing photo!**\n\n{{ $json.errorMessage || 'An unknown error occurred' }}\n\n📸 **What to do:**\n1. Check photo size (maximum 20 MB)\n2. Make sure you are replying to the correct message\n3. Try sending the photo again\n\nIf the error persists, contact the manager.",
"chatId": "={{ $json.chatId }}",
"additionalFields": {
"parse_mode": "Markdown",
"reply_to_message_id": "={{ $json.originalMessage?.message_id }}"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "6d0784dd-f2a1-402e-ae37-08be32e1dea7",
"name": "获取并下载文件4",
"type": "n8n-nodes-base.telegram",
"position": [
1632,
1888
],
"webhookId": "facb38e6-fcf3-4883-9513-7b1b9d1ac55f",
"parameters": {
"fileId": "={{ $json.fileId }}",
"resource": "file"
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "d4c00c7a-6d94-4bfd-a9cb-0edb5264fdbc",
"name": "检查文件存在4",
"type": "n8n-nodes-base.if",
"position": [
1824,
1888
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ Object.keys($binary || {}).length }}",
"value2": "0",
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "a27816e9-1d38-4c92-afbe-1943cccaea4e",
"name": "上传到 Google Drive4",
"type": "n8n-nodes-base.googleDrive",
"position": [
1984,
1760
],
"parameters": {
"name": "={{ $json.fileName }}",
"driveId": {
"__rl": true,
"mode": "list",
"value": "My Drive"
},
"options": {},
"folderId": {
"__rl": true,
"mode": "list",
"value": "root",
"cachedResultName": "/ (Root folder)"
}
},
"credentials": {
"googleDriveOAuth2Api": {
"id": "6dPaRVHx3byIvQXN",
"name": "Google Drive account"
}
},
"typeVersion": 3
},
{
"id": "f3708a3c-2716-40db-b288-7168c2587001",
"name": "准备写入数据5",
"type": "n8n-nodes-base.code",
"position": [
2240,
1760
],
"parameters": {
"jsCode": "// Preparing data for writing to table\n const photoData = $node['Extract photo data4'].json;\nconst driveFile = $json;\n\n// Check for main data\nif (!photoData) {\n console.error('Missing photo data');\n return [];\n}\n\n// Determine status and link\nlet fileLink = '';\nlet fileStatus = '';\n\nif (driveFile?.id) {\n // File successfully uploaded to Drive\n if (driveFile.webViewLink) {\n fileLink = driveFile.webViewLink;\n } else if (driveFile.webContentLink) {\n fileLink = driveFile.webContentLink;\n } else {\n fileLink = `https://drive.google.com/file/d/${driveFile.id}/view`;\n }\n fileStatus = 'uploaded';\n} else {\n // File not uploaded, save Telegram ID\n fileLink = `telegram://file_id/${photoData.fileId || ''}`;\n fileStatus = 'telegram_only';\n}\n\n// Return data for one file\nreturn [{\n json: {\n // File data\n fileData: {\n fileName: photoData.fileName || '',\n fileLink: fileLink,\n fileId: photoData.fileId || '',\n fileSize: photoData.fileSize || 0,\n fileStatus: fileStatus,\n messageId: photoData.messageId || ''\n },\n // Common report data\n reportData: {\n taskId: photoData.taskId || '', // Task ID\n chatId: photoData.chatId || '',\n userName: photoData.userName || '',\n reportType: photoData.reportType || 'Undefined type',\n date: photoData.date || new Date().toLocaleDateString('en-US'),\n time: photoData.time || new Date().toLocaleString('en-US'),\n caption: photoData.caption || '',\n originalMessageId: photoData.messageId || ''\n },\n // Message information\n messageData: {\n mediaGroupId: photoData.mediaGroupId || '',\n timestamp: photoData.timestamp || Date.now()\n }\n }\n}];"
},
"typeVersion": 2
},
{
"id": "c58a2e2d-adc1-405b-b6a1-741054623808",
"name": "处理检索错误4",
"type": "n8n-nodes-base.code",
"position": [
2112,
1904
],
"parameters": {
"jsCode": "// Handling case when file could not be obtained\n const photoData = $node['Extract photo data4'].json;\n const telegram_data = \"Workflow by Artem Boiko - DataDrivenConstruction.io\";\n// Check for data\nif (!photoData) {\n console.error('Missing photo data for error processing');\n return [];\n}\n\n// Save data with Telegram file_id\nreturn [{\n json: {\n // File data\n fileData: {\n fileName: photoData.fileName || '',\n fileLink: `telegram://file_id/${photoData.fileId || ''}`,\n fileId: photoData.fileId || '',\n fileSize: photoData.fileSize || 0,\n fileStatus: 'telegram_only',\n messageId: photoData.messageId || ''\n },\n // Common report data\n reportData: {\n taskId: photoData.taskId || '', // Task ID\n chatId: photoData.chatId || '',\n userName: photoData.userName || '',\n reportType: photoData.reportType || 'Undefined type',\n date: photoData.date || new Date().toLocaleDateString('en-US'),\n time: photoData.time || new Date().toLocaleString('en-US'),\n caption: photoData.caption || '',\n originalMessageId: photoData.messageId || ''\n },\n // Message information\n messageData: {\n mediaGroupId: photoData.mediaGroupId || '',\n timestamp: photoData.timestamp || Date.now()\n }\n }\n}];"
},
"typeVersion": 2
},
{
"id": "2d1e3a7d-8212-4c7a-a325-b2fb0779aecc",
"name": "合并文件流2",
"type": "n8n-nodes-base.merge",
"position": [
2416,
1904
],
"parameters": {},
"typeVersion": 1
},
{
"id": "7f76eaf7-2bd3-4ad4-8d21-b59d50e5b8a1",
"name": "检查现有任务1",
"type": "n8n-nodes-base.googleSheets",
"position": [
2768,
1776
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json.taskId }}",
"lookupColumn": "Report ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 334816779,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc/edit#gid=334816779",
"cachedResultName": " Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6,
"alwaysOutputData": true
},
{
"id": "dec87f18-482b-43a1-8084-d21da5a32a02",
"name": "任务存在吗？1",
"type": "n8n-nodes-base.if",
"position": [
3088,
1888
],
"parameters": {
"conditions": {
"number": [
{
"value1": "={{ $json.lookup?.length || 0 }}",
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "30eda569-64c2-4f4f-b798-47f478138893",
"name": "准备添加数据2",
"type": "n8n-nodes-base.code",
"position": [
3312,
1904
],
"parameters": {
"jsCode": "// Preparing data for adding new task\n const input = $json;\n\n// Check for data\nif (!input || !input.filesGroup) {\n console.error('Missing filesGroup data');\n return [];\n}\n\n const filesGroup = input.filesGroup;\nconst reportData = input.reportData;\n\n// Check critical data\nif (!reportData.taskId || filesGroup.length === 0) {\n console.error('Missing taskId or files');\n return [];\n}\n\n// Collect data from all files in the group\n const fileNames = [];\nconst fileLinks = [];\nconst fileIds = [];\nlet totalSize = 0;\nlet bd_total = \"QXJ0ZW0gQm9pa28uIERhdGFEcml2ZW5jb25zdHJ1Y3Rpb24uaW8=\";\nconst allCaptions = [];\nconst mediaGroupIds = new Set();\n\n// Go through all files in the group\nfilesGroup.forEach(file => {\n if (file && file.fileData) {\n // Add file name\n if (file.fileData.fileName) {\n fileNames.push(file.fileData.fileName);\n }\n \n // Add file link\n if (file.fileData.fileLink) {\n fileLinks.push(file.fileData.fileLink);\n }\n \n // Add Telegram File ID\n if (file.fileData.fileId) {\n fileIds.push(file.fileData.fileId);\n }\n \n // Sum size\n totalSize += file.fileData.fileSize || 0;\n \n // Collect comments\n if (file.reportData?.caption) {\n allCaptions.push(file.reportData.caption);\n }\n \n // Collect Media Group IDs\n if (file.messageData?.mediaGroupId) {\n mediaGroupIds.add(file.messageData.mediaGroupId);\n }\n }\n});\n\n// Combine unique comments\n const uniqueCaptions = [...new Set(allCaptions)];\nconst combinedComment = uniqueCaptions.join('; ');\n\n// Form status\n const filesCount = fileNames.length;\nconst statusText = filesCount === 1 ? \n 'File uploaded to Drive' : \n `All ${filesCount} file(s) uploaded to Drive`;\n\n// Form data for writing to table\n const result = {\n 'Report ID': reportData.taskId, // Task ID - main key\n 'Executor ID': reportData.chatId ? String(reportData.chatId) : '',\n 'Executor': reportData.userName || '',\n 'Report Date': reportData.date || new Date().toLocaleDateString('en-US'),\n 'Report Received Time': reportData.time || new Date().toLocaleString('en-US'),\n 'Document Type': reportData.reportType || 'Undefined type',\n 'File Name': fileNames.join(', '),\n 'File URL': fileLinks.join(', '),\n 'Telegram File ID': fileIds.join(', '),\n 'File Size (bytes)': String(totalSize),\n 'Processing Status': statusText,\n 'File Count': String(filesCount),\n 'Comment': combinedComment,\n 'Media Group ID': Array.from(mediaGroupIds).join(', ')\n};\n\n// Additional data for confirmation\n const confirmationData = {\n '_chatId': reportData.chatId || '',\n '_taskId': reportData.taskId || '',\n '_originalMessageId': reportData.originalMessageId || '',\n '_filesCount': filesCount,\n '_fileNames': fileNames.join(', '),\n 'Report ID': result['Report ID'],\n 'Report Date': result['Report Date'], \n 'Report Received Time': result['Report Received Time'],\n 'Processing Status': result['Processing Status']\n};\n\nconsole.log(`Prepared new entry for Task ID ${reportData.taskId}:`, {\n filesCount: filesCount,\n totalSize: totalSize,\n fileNames: fileNames\n});\n\n// Internal: Artem Boiko - DataDrivenconstruction.io\n// Return one item with combined data\nreturn [{\n json: {\n sheetsData: result,\n confirmationData: confirmationData\n }\n}];"
},
"typeVersion": 2
},
{
"id": "9abb0a98-62a8-44c1-8e9b-124325c46f49",
"name": "在表中更新行2",
"type": "n8n-nodes-base.googleSheets",
"position": [
3536,
1680
],
"parameters": {
"columns": {
"value": {
"Comment": "={{ $json.updateData['Comment'] }}",
"File URL": "={{ $json.updateData['File URL'] }}",
"File Name": "={{ $json.updateData['File Name'] }}",
"File Count": "={{ $json.updateData['File Count'] }}",
"Media Group ID": "={{ $json.updateData['Media Group ID'] }}",
"Telegram File ID": "={{ $json.updateData['Telegram File ID'] }}",
"File Size (bytes)": "={{ $json.updateData['File Size (bytes)'] }}",
"Processing Status": "={{ $json.updateData['Processing Status'] }}",
"Report Received Time": "={{ $json.updateData['Report Received Time'] }}"
},
"mappingMode": "defineBelow"
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "9c5eaed9-e292-4993-b2fa-ad0034fd8a58",
"name": "添加到报告表1",
"type": "n8n-nodes-base.googleSheets",
"position": [
3584,
1904
],
"parameters": {
"columns": {
"value": {
"Comment": "={{ $json.sheetsData['Comment'] }}",
"File URL": "={{ $json.sheetsData['File URL'] }}",
"File Name": "={{ $json.sheetsData['File Name'] }}",
"Report ID": "={{ $json.sheetsData['Report ID'] }}",
"File Count": "={{ $json.sheetsData['File Count'] }}",
"Report Date": "={{ $json.sheetsData['Report Date'] }}",
"Document Type": "={{ $json.sheetsData['Document Type'] }}",
"Processing Status": "={{ $json.sheetsData['Processing Status'] }}",
"Report Received Time": "={{ $json.sheetsData['Report Received Time'] }}"
},
"schema": [
{
"id": "Report ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Report ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Executor",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Executor",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Report Type",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Report Type",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Time",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Executor ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Executor ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Company",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Company",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Sent",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Sent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Sent Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Sent Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Read",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Read",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Read Time",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Read Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Report Received Time",
"type": "string",
"display": true,
"required": false,
"displayName": "Report Received Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Document Type",
"type": "string",
"display": true,
"required": false,
"displayName": "Document Type",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "File Name",
"type": "string",
"display": true,
"required": false,
"displayName": "File Name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "File URL",
"type": "string",
"display": true,
"required": false,
"displayName": "File URL",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Comment",
"type": "string",
"display": true,
"required": false,
"displayName": "Comment",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "File Count",
"type": "string",
"display": true,
"required": false,
"displayName": "File Count",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Processing Status",
"type": "string",
"display": true,
"required": false,
"displayName": "Processing Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Report Date",
"type": "string",
"display": true,
"required": false,
"displayName": "Report Date",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Report ID"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "0f36cd2d-331b-4dff-ba71-c4748b3d2df0",
"name": "确认照片接收4",
"type": "n8n-nodes-base.telegram",
"position": [
4096,
1776
],
"webhookId": "1bfa29ad-d706-4c4c-b5a5-0c1b1851a18c",
"parameters": {
"text": "=✅ {{ $json.filesCount > 1 ? $json.filesCount + ' photos successfully received' : 'Photo successfully received' }}! \n📋 Task ID: {{ $json.taskId }} \n🕐 Time: {{ $json.rowData['Report Received Time'] }} \n📎 Total files in task: {{ $json.rowData['Processing Status'].match(/\\d+/)?.[0] || $json.filesCount }} \n💾 Status: Saved to table \n{{ $json.operationText }} \n\nThank you for sending the report!",
"chatId": "={{ $json.chatId }}",
"forceReply": {},
"replyMarkup": "=",
"additionalFields": {
"parse_mode": "Markdown",
"reply_to_message_id": "={{ $json.originalMessageId }}"
},
"replyKeyboardRemove": {},
"replyKeyboardOptions": {}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "1ed50e9e-6966-4298-a335-0db592be5f85",
"name": "便签 10",
"type": "n8n-nodes-base.stickyNote",
"position": [
512,
1664
],
"parameters": {
"color": 5,
"width": 3756,
"height": 410,
"content": "## 🔄 照片处理流程"
},
"typeVersion": 1
},
{
"id": "07110355-e5d3-4e56-a778-0b31f63f57af",
"name": "准备更新数据2",
"type": "n8n-nodes-base.code",
"position": [
3264,
1728
],
"parameters": {
"jsCode": "// Preparing data for updating existing task\n const input = $json;\n\n// Check for data\nif (!input || !input.lookup || !input.filesGroup) {\n console.error('Missing required data');\n return [];\n}\n\n const lookupResult = input.lookup;\nconst filesGroup = input.filesGroup;\n\nif (lookupResult.length === 0) {\n console.error('Existing entry not found');\n return [];\n}\n\n// Take existing entry\n const existing = lookupResult[0];\nconst rowNumber = existing._rowNumber || 2;\n\n// Collect data from all new files\n const newFileNames = [];\nconst newFileLinks = [];\nconst newFileIds = [];\nlet newTotalSize = 0;\nconst newCaptions = [];\nconst newMediaGroupIds = new Set();\n\n// Go through all new files\nfilesGroup.forEach(file => {\n if (file && file.fileData) {\n // Add file name\n if (file.fileData.fileName) {\n newFileNames.push(file.fileData.fileName);\n }\n \n // Add file link\n if (file.fileData.fileLink) {\n newFileLinks.push(file.fileData.fileLink);\n }\n \n // Add Telegram File ID\n if (file.fileData.fileId) {\n newFileIds.push(file.fileData.fileId);\n }\n \n // Sum size\n newTotalSize += file.fileData.fileSize || 0;\n \n // Collect comments\n if (file.reportData?.caption) {\n newCaptions.push(file.reportData.caption);\n }\n \n // Collect Media Group IDs\n if (file.messageData?.mediaGroupId) {\n newMediaGroupIds.add(file.messageData.mediaGroupId);\n }\n }\n});\n\n// Parse existing values\n const currentCount = parseInt(existing['File Count']) || 0;\nconst newFilesCount = newFileNames.length;\nconst totalCount = currentCount + newFilesCount;\n\n// Combine existing and new data\n const allFileNames = existing['File Name'] ? \n existing['File Name'] + ', ' + newFileNames.join(', ') : \n newFileNames.join(', ');\n\n const allFileLinks = existing['File URL'] ? \n existing['File URL'] + ', ' + newFileLinks.join(', ') : \n newFileLinks.join(', ');\n\n const allFileIds = existing['Telegram File ID'] ? \n existing['Telegram File ID'] + ', ' + newFileIds.join(', ') : \n newFileIds.join(', ');\n\n const totalSize = (parseInt(existing['File Size (bytes)']) || 0) + newTotalSize;\n\n// Update status\n const newStatus = totalCount === 1 ? \n 'File uploaded to Drive' : \n `All ${totalCount} file(s) uploaded to Drive`;\n\n// Update comment\n const existingComment = existing['Comment'] || '';\nconst uniqueNewCaptions = [...new Set(newCaptions)];\nconst newComment = uniqueNewCaptions.length > 0 ? \n (existingComment ? existingComment + '; ' + uniqueNewCaptions.join('; ') : uniqueNewCaptions.join('; ')) : \n existingComment;\n\n// Update Media Group IDs\n const existingMediaGroups = existing['Media Group ID'] ? existing['Media Group ID'].split(', ') : [];\nconst allMediaGroups = new Set([...existingMediaGroups, ...newMediaGroupIds]);\nconst mediaGroupIdsStr = Array.from(allMediaGroups).join(', ');\n\n// Get report data\n const reportData = input.reportData;\n\nconsole.log(`Updating entry for Task ID ${existing['Report ID']}:`, {\n existingFiles: currentCount,\n newFiles: newFilesCount,\n totalFiles: totalCount\n});\n\n// Return data for update\nreturn [{\n json: {\n rowNumber: rowNumber,\n updateData: {\n 'File Name': allFileNames,\n 'File URL': allFileLinks,\n 'Telegram File ID': allFileIds,\n 'File Size (bytes)': String(totalSize),\n 'Processing Status': newStatus,\n 'File Count': String(totalCount),\n 'Comment': newComment,\n 'Media Group ID': mediaGroupIdsStr,\n 'Report Received Time': new Date().toLocaleString('en-US')\n },\n // Additional data for confirmation\n confirmationData: {\n '_chatId': reportData.chatId,\n '_taskId': existing['Report ID'],\n '_originalMessageId': reportData.originalMessageId,\n '_filesCount': totalCount,\n '_newFilesCount': newFilesCount,\n '_fileNames': allFileNames,\n 'Report ID': existing['Report ID'],\n 'Report Date': existing['Report Date'],\n 'Report Received Time': new Date().toLocaleString('en-US'),\n 'Processing Status': newStatus\n }\n }\n}];"
},
"typeVersion": 2
},
{
"id": "df10849e-c1ab-4fe7-a675-e97dabfe1d9a",
"name": "便签3",
"type": "n8n-nodes-base.stickyNote",
"position": [
512,
992
],
"parameters": {
"color": 5,
"width": 2132,
"height": 648,
"content": "## 📸 照片报告处理"
},
"typeVersion": 1
},
{
"id": "6bba08ac-24cb-4112-aaec-03c00a015e7f",
"name": "发送接收确认",
"type": "n8n-nodes-base.telegram",
"position": [
2656,
2704
],
"webhookId": "86812008-0328-4a88-ade9-d59ed3b581c0",
"parameters": {
"text": "={{ $json.message }}",
"chatId": "={{ $json.foreman_id }}",
"additionalFields": {
"parse_mode": "Markdown"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "a2b38395-76fc-4316-96f9-08ae19eb66fb",
"name": "检查任务所有者1",
"type": "n8n-nodes-base.if",
"position": [
1040,
2368
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json[\"action\"] }}",
"value2": "check_task_ownership"
}
]
}
},
"typeVersion": 1
},
{
"id": "1fced70b-f610-49d6-a053-d5a8e9fdd6e6",
"name": "获取检查任务1",
"type": "n8n-nodes-base.googleSheets",
"position": [
1264,
2352
],
"parameters": {
"range": "Tasks!A:N",
"options": {},
"sheetId": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 2
},
{
"id": "ed19e27a-aac0-4160-bd9a-66181caba703",
"name": "检查 ID 匹配1",
"type": "n8n-nodes-base.code",
"position": [
1408,
2352
],
"parameters": {
"jsCode": "// Get data from previous nodes\nconst checkData = $node[\"Process incoming message1\"].json;\nconst allTasks = $input.all();\n\nconst taskId = checkData.taskId;\nconst foremanId = checkData.foremanId;\nconst status = checkData.status;\nconst comment = checkData.comment;\nconst replyToMessage = checkData.replyToMessage;\nconst projectInfo = checkData.projectInfo;\nconst foremanName = checkData.foremanName;\nconst today = checkData.today;\nconst time = checkData.time;\nconst chatId = checkData.chat_id;\n\n// Search for the task with the given ID\nlet taskFound = false;\nlet taskBelongsToForeman = false;\nlet actualTask = null;\nlet actualForemanName = null;\n\nfor (const item of allTasks) {\n const currentTaskId = item.json['Task ID'];\n const currentForemanId = String(item.json['Executor ID']).trim();\n \n if (currentTaskId && currentTaskId.toUpperCase() === taskId.toUpperCase()) {\n taskFound = true;\n actualTask = item.json;\n \n if (currentForemanId === String(foremanId)) {\n taskBelongsToForeman = true;\n break;\n } else {\n actualForemanName = item.json['Executor'];\n }\n }\n}\n\n// If the task is not found at all\nif (!taskFound) {\n return [{\n json: {\n action: \"send_command_response\",\n chat_id: chatId,\n message: `❌ **Error!**\\n\\nTask with ID **${taskId}** not found in the system.\\n\\n📋 Please check the task ID correctness.`\n }\n }];\n}\n\n// If the task is found but belongs to another executor\nif (!taskBelongsToForeman) {\n return [{\n json: {\n action: \"send_command_response\",\n chat_id: chatId,\n message: `⚠️ **Attention!**\\n\\nTask **${taskId}** does not match your ID.\\nThis task is assigned to executor: **${actualForemanName || 'Another executor'}**`\n }\n }];\n}\n\n// Pass the status without changes\nconst finalStatus = status || \"Not specified\";\n\n// Extract task name\nconst taskName = actualTask['Task Description'] || `Task ${taskId}`;\n\n// Form report object\nconst report = {\n \"Task ID\": taskId,\n \"Date\": today,\n \"Time\": time,\n \"Executor\": foremanName || actualTask['Executor'],\n \"Chat_ID\": chatId,\n \"Executor_ID\": chatId,\n \"Object\": actualTask['Object'] || projectInfo.object,\n \"Section\": actualTask['Section'] || projectInfo.section,\n \"Task Description\": taskName,\n \"Status\": finalStatus,\n \"Comment\": comment || \"No comment\",\n \"Report Date\": `${today} ${time}`,\n \"Percent\": \"\",\n \"Problems\": \"\",\n \"Report Type\": \"By ID\"\n};\n\n// Return report for further processing\nreturn [{\n json: {\n action: \"process_report\",\n chat_id: chatId,\n reports: [report]\n }\n}];\n"
},
"typeVersion": 2
},
{
"id": "cccccf9e-d50c-447c-bff2-04526e8db29f",
"name": "检查处理报告1",
"type": "n8n-nodes-base.if",
"position": [
1632,
2528
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json[\"action\"] }}",
"value2": "process_report"
}
]
}
},
"typeVersion": 1
},
{
"id": "365a3a0c-62b8-4d65-860b-0ece4e3f5993",
"name": "提取报告1",
"type": "n8n-nodes-base.code",
"position": [
1840,
2512
],
"parameters": {
"jsCode": "// Processing reports from action process_report\nconst data = $input.first().json;\nif (data.action === 'process_report' && data.reports) {\n return data.reports.map(report => ({ json: report }));\n}\n\n// Otherwise pass data as is for compatibility\nreturn $input.all();"
},
"typeVersion": 2
},
{
"id": "b90c4bdb-ff48-48fa-9bff-854d3cfe3248",
"name": "更新任务状态1",
"type": "n8n-nodes-base.googleSheets",
"position": [
2032,
2512
],
"parameters": {
"columns": {
"value": {
"Status": "={{ $json[\"Status\"] }}",
"Comment": "={{ $json[\"Comment\"] }}",
"Task ID": "={{ $json[\"Task ID\"] }}",
"Report Date": "={{ $json[\"Report Date\"] }}"
},
"schema": [
{
"id": "Task ID",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Task ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Executor",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Executor",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Time",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Task Description",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Task Description",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Object",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Object",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Section",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Section",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Task ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Task ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Priority",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Priority",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Executor ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Executor ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Company",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Company",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Sent",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Sent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Sent Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Sent Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Read",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Read",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Read Time",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Read Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Status",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Comment",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Comment",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Report Date",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Report Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"Task ID"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1687674061,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc/edit#gid=1687674061",
"cachedResultName": "Tasks"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "8525cd22-b36a-439b-a21a-dff56bf36d61",
"name": "格式化确认1",
"type": "n8n-nodes-base.code",
"position": [
2240,
2512
],
"parameters": {
"jsCode": "// Group reports by executor\nconst reports = $input.all();\nconst reportsByForeman = {};\n\nreports.forEach(item => {\n const report = item.json;\n const foremanId = report.Chat_ID || report[\"Chat_ID\"];\n const dadbc = \"Tasks and Reports Pipeline - DDC\";\n \n if (!reportsByForeman[foremanId]) {\n reportsByForeman[foremanId] = {\n chatId: foremanId,\n foremanName: report.Executor,\n date: report.Date,\n time: report.Time,\n object: report.Object,\n section: report.Section,\n reports: []\n };\n }\n \n reportsByForeman[foremanId].reports.push({\n id: report[\"Task ID\"] || \"\",\n task: report[\"Task Description\"],\n status: report.Status,\n percent: report.Percent,\n comment: report.Comment,\n problems: report.Problems,\n type: report[\"Report Type\"] || \"General\"\n});\n});\n\n// Form confirmation messages\nreturn Object.values(reportsByForeman).map(data => {\n let message = `✅ **Report successfully saved!**\\n`;\n \n if (data.reports.length === 1) {\n const report = data.reports[0];\n if (report.id) {\n message += `🆔 Task ID: ${report.id}\\n`;\n }\n message += `📊 Status: ${report.status}\\n`;\n\n if (report.comment) {\n message += `💬 Comment: ${report.comment}\\n`;\n }\n if (report.problems) {\n message += `⚠️ Problems: ${report.problems}\\n`;\n }\n } else {\n message += `📋 **Saved reports: ${data.reports.length}**\\n\\n`;\n data.reports.forEach((report, index) => {\n const idPrefix = report.id ? `[${report.id}] ` : \"\";\n message += `${index + 1}. ${idPrefix}${report.task}\\n`;\n message += ` Status: ${report.status}`;\n if (report.percent !== undefined && report.percent > 0) {\n message += ` (${report.percent}%)`;\n }\n message += `\\n`;\n });\n }\n \n message += `\\nThank you for the report! Have a good day 🙋🏻♂️`;\n \n return {\n json: {\n chat_id: data.chatId,\n message: message\n }\n };\n});"
},
"typeVersion": 2
},
{
"id": "9773f4b5-109d-4eb5-9f63-147edad20bc3",
"name": "确认报告保存1",
"type": "n8n-nodes-base.telegram",
"position": [
2448,
2512
],
"webhookId": "86812008-0328-4a88-ade9-d59ed3b581c0",
"parameters": {
"text": "={{ $json[\"message\"] }}",
"chatId": "={{ $node[\"Process incoming message1\"].json.chat_id }}",
"additionalFields": {
"parse_mode": "Markdown"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "c3045f9a-f609-407b-a99d-70d2d6b01679",
"name": "检查 Received1",
"type": "n8n-nodes-base.if",
"position": [
1632,
2736
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json[\"action\"] }}",
"value2": "process_received"
}
]
}
},
"typeVersion": 1
},
{
"id": "ce47bb4f-34f1-469d-bf83-647f022e7031",
"name": "获取阅读任务1",
"type": "n8n-nodes-base.googleSheets",
"position": [
1856,
2720
],
"parameters": {
"range": "Tasks!A:R",
"options": {},
"sheetId": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 2
},
{
"id": "c83b45cc-353f-4a71-8fc3-deb25c9d77ae",
"name": "准备阅读更新1",
"type": "n8n-nodes-base.code",
"position": [
2064,
2800
],
"parameters": {
"jsCode": "const items = $input.all();\nconst chatId = String($node[\"Process incoming message1\"].json.chat_id);\nconst foremanId = String($node[\"Process incoming message1\"].json.foreman_id || chatId);\nconst now = new Date();\nconst day = String(now.getDate()).padStart(2, '0');\nconst month = String(now.getMonth() + 1).padStart(2, '0');\nconst year = now.getFullYear();\nconst hours = String(now.getHours()).padStart(2, '0');\nconst minutes = String(now.getMinutes()).padStart(2, '0');\nconst readDateTime = `${day}.${month}.${year}, ${hours}:${minutes}`;\n\nconst today = now.toLocaleDateString('en-US');\n\n// Filter tasks for this chatId for today and sent ones\nconst updates = [];\nlet rowIndex = 1; // Start from 1, as first row is headers\n\nitems.forEach((item) => {\n rowIndex++; // Increment index for each row\n \n const serial = item.json['Date'];\n let taskDate;\n if (serial) {\n if (typeof serial === 'number') {\n taskDate = new Date((serial - 25569) * 86400 * 1000).toLocaleDateString('en-US');\n } else if (typeof serial === 'string' && serial.includes('.')) {\n const [day, month, year] = serial.split('.');\n taskDate = new Date(parseInt(year), parseInt(month) - 1, parseInt(day)).toLocaleDateString('en-US');\n } else {\n taskDate = new Date(serial).toLocaleDateString('en-US');\n }\n } else {\n return;\n }\n \n const itemChatId = String(item.json['Executor ID']).trim();\n const sent = item.json['Reminder Sent'];\n const read = item.json['Reminder Read'];\n const ddc_marker = \"Workflow by Artem Boiko - DataDrivenConstruction.io\";\n \n if (itemChatId === foremanId && taskDate === today && sent === 'Yes' && (!read || read !== 'Yes')) {\n updates.push({\n json: {\n values: [['Yes', readDateTime]],\n _rowIndex: rowIndex\n }\n });\n }\n});\n\nconsole.log(`Found ${updates.length} tasks for read update`);\nreturn updates;"
},
"typeVersion": 2
},
{
"id": "3a9cfcb7-f3b4-46db-8d35-51f25f8737d9",
"name": "过滤有效阅读更新1",
"type": "n8n-nodes-base.if",
"position": [
2256,
2720
],
"parameters": {
"conditions": {
"number": [
{
"value1": "={{ $json._rowIndex }}",
"operation": "isNotEmpty"
},
{
"value1": "={{ $json._rowIndex }}",
"value2": 1,
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "1bb7e102-be7d-4fe9-a822-b1d556dcee51",
"name": "在表中更新阅读1",
"type": "n8n-nodes-base.googleSheets",
"position": [
2448,
2704
],
"parameters": {
"columns": {
"value": {
"Read Time": "={{ $json.values[0][1] }}",
"row_number": "={{ $json._rowIndex }}",
"Reminder Read": "={{ $json.values[0][0] }}"
},
"schema": [
{
"id": "Task ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Task ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Executor",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Executor",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Time",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Task Description",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Task Description",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Object",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Object",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Section",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Section",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Task ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Task ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Priority",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Priority",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Executor ID",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Executor ID",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Company",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Company",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Sent",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Sent",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Sent Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Reminder Sent Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Reminder Read",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Reminder Read",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Read Time",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "Read Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Status",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Status",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Comment",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Comment",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Report Date",
"type": "string",
"display": true,
"removed": true,
"required": false,
"displayName": "Report Date",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"removed": false,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"row_number"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1687674061,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc/edit#gid=1687674061",
"cachedResultName": "Tasks"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "156c5228-e414-491d-a35e-b810703cb13d",
"name": "检查命令1",
"type": "n8n-nodes-base.if",
"position": [
1632,
2176
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json[\"action\"] }}",
"value2": "send_command_response"
}
]
}
},
"typeVersion": 1
},
{
"id": "18f44efa-7f9c-45cf-9d8f-abcf49c58972",
"name": "发送命令响应1",
"type": "n8n-nodes-base.telegram",
"position": [
1904,
2160
],
"webhookId": "a7c690e3-fb37-4ef5-be90-cb47a78b94b1",
"parameters": {
"text": "={{ $json[\"message\"] }}",
"chatId": "={{ $json[\"chat_id\"] }}",
"additionalFields": {
"parse_mode": "Markdown"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "d03ee9b8-682a-4080-abe5-9248418c6fbd",
"name": "检查操作类型1",
"type": "n8n-nodes-base.if",
"position": [
1632,
2352
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json[\"action\"] }}",
"value2": "send_report_form"
}
]
}
},
"typeVersion": 1
},
{
"id": "c70de874-0040-481b-81b0-f83fff22403e",
"name": "发送报告表单1",
"type": "n8n-nodes-base.telegram",
"position": [
1840,
2336
],
"webhookId": "4e5fca96-c3d0-41de-ae37-fb2ad000c8e9",
"parameters": {
"text": "={{ $json[\"message\"] }}",
"chatId": "={{ $json[\"chat_id\"] }}",
"additionalFields": {
"parse_mode": "Markdown"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "ab678474-3263-4118-ad40-3f45575283c8",
"name": "查找 GPS 任务",
"type": "n8n-nodes-base.googleSheets",
"position": [
1648,
3216
],
"parameters": {
"options": {},
"filtersUI": {
"values": [
{
"lookupValue": "={{ $json.taskId }}",
"lookupColumn": "Report ID"
}
]
},
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6,
"alwaysOutputData": true
},
{
"id": "6955ab37-30e6-4d1d-9eab-f0849136159d",
"name": "找到任务了吗？",
"type": "n8n-nodes-base.if",
"position": [
1840,
3216
],
"parameters": {
"conditions": {
"number": [
{
"value1": "={{ $items().length }}",
"operation": "larger"
}
]
}
},
"typeVersion": 1
},
{
"id": "543ba930-4fdb-4fba-958e-6b5bbc92f710",
"name": "在表中更新 GPS",
"type": "n8n-nodes-base.googleSheets",
"position": [
2032,
3200
],
"parameters": {
"columns": {
"value": {
"Map Link": "={{ $node['Extract Location Data1'].json.mapLink }}",
"row_number": "={{ $json._rowNumber || $json.row_number || 2 }}",
"GPS Coordinates": "={{ $node['Extract Location Data1'].json.coordinatesText }}",
"GPS Received Time": "={{ $node['Extract Location Data1'].json.time }}"
},
"schema": [
{
"id": "GPS Coordinates",
"type": "string",
"display": true,
"required": false,
"displayName": "GPS Coordinates",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "Map Link",
"type": "string",
"display": true,
"required": false,
"displayName": "Map Link",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "GPS Received Time",
"type": "string",
"display": true,
"required": false,
"displayName": "GPS Received Time",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"row_number"
]
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "name",
"value": "Photo Reports"
},
"documentId": {
"__rl": true,
"mode": "id",
"value": "1fWi_0W_jqKa61h2oB3zZLdTDBK8_cQ123RtF70X1rwc"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "XXXXXX",
"name": "Google Sheets account"
}
},
"typeVersion": 4.6
},
{
"id": "9f4ae758-3f48-404a-ac17-e27e84569f3f",
"name": "确认 GPS 接收",
"type": "n8n-nodes-base.telegram",
"position": [
2224,
3200
],
"webhookId": "gps-confirmation-webhook",
"parameters": {
"text": "=✅ **GPS coordinates successfully received!**\n\n📋 Task ID: {{ $node['Extract Location Data1'].json.taskId }}\n📍 Coordinates: {{ $node['Extract Location Data1'].json.coordinatesText }}\n🗺️ [Open on map]({{ $node['Extract Location Data1'].json.mapLink }})\n🕐 Time: {{ $node['Extract Location Data1'].json.time }}\n💾 Status: GPS coordinates added to report\n\nThank you for sending the geolocation!",
"chatId": "={{ $node['Extract Location Data1'].json.chatId }}",
"additionalFields": {
"parse_mode": "Markdown",
"reply_to_message_id": "={{ $node['Extract Location Data1'].json.messageId }}"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "1a2c7d6d-9eb1-42d2-a362-b56ed335adab",
"name": "错误 - 未找到任务",
"type": "n8n-nodes-base.telegram",
"position": [
2032,
3344
],
"webhookId": "task-not-found-webhook",
"parameters": {
"text": "=❌ **Error!**\n\n⚠️ Report with ID not found: {{ $node['Extract Location Data1'].json.taskId }}\n\n📸 First, send the photo for this report, then you can add GPS coordinates.\n\n**Order of actions:**\n1. Reply to the reminder with a photo\n2. After photo send confirmation, reply to the same reminder with geolocation",
"chatId": "={{ $node['Extract Location Data1'].json.chatId }}",
"additionalFields": {
"parse_mode": "Markdown",
"reply_to_message_id": "={{ $node['Extract Location Data1'].json.messageId }}"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "63271c55-c4ef-4272-90b9-27f68be2dfce",
"name": "检查处理位置2",
"type": "n8n-nodes-base.if",
"position": [
1120,
3104
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json['action'] }}",
"value2": "process_location"
}
]
}
},
"typeVersion": 1
},
{
"id": "39387dba-8aa9-4f13-814f-59e71f5d9ea4",
"name": "提取位置数据1",
"type": "n8n-nodes-base.code",
"position": [
1296,
3088
],
"parameters": {
"jsCode": "// Extract data from geolocation for photo reports\nconst input = $node['Process incoming message1'].json;\n\n// Check input data\nif (!input || !input.originalMessage) {\n return [{\n json: {\n error: true,\n errorMessage: 'Missing message data',\n chatId: input?.chatId || 'unknown'\n }\n }];\n}\n\nconst message = input.originalMessage;\nconst chatId = String(message.chat?.id || '');\nconst replyTo = input.replyToMessage;\nconst userName = (message.from?.first_name || '') + ' ' + (message.from?.last_name || '');\n\n// Check for geolocation presence\nif (!message.location) {\n return [{\n json: {\n error: true,\n errorMessage: 'No geolocation in message',\n chatId: chatId,\n originalMessage: message\n }\n }];\n}\n\nconst location = message.location;\nconst latitude = location.latitude;\nconst longitude = location.longitude;\n\n// Parse report information - IMPORTANT: Task ID\nlet taskId = null; // Task ID\n\nif (replyTo?.text) {\n const replyText = replyTo.text;\n \n // Parse ID from format: 🔸 ID4 Photo of primary...\n const idMatch = replyText.match(/🔸\\s*(ID\\d+)\\s+(.+?)(?=\\n|$)/i);\n if (idMatch) {\n taskId = idMatch[1]; // This is the Task ID\n }\n \n // Alternative format for manual reports\n if (!taskId) {\n const manualMatch = replyText.match(/(MANUAL_\\d+)/i);\n if (manualMatch) {\n taskId = manualMatch[1]; // Task ID for manual report\n }\n }\n}\n\n// Validate Task ID\nif (!taskId) {\n console.error('Could not parse task ID from:', replyTo?.text);\n return [{\n json: {\n error: true,\n errorMessage: 'Could not determine task ID. Ensure you are replying to the correct reminder message.',\n chatId: chatId,\n originalMessage: message\n }\n }];\n}\n\n// Process geolocation\nconst now = new Date();\nconst dateStr = now.toLocaleDateString('en-US');\nconst timeStr = now.toLocaleString('en-US');\n\n// Form map link and coordinates text\nconst mapLink = `https://maps.google.com/maps?q=${latitude},${longitude}`;\nconst coordinatesText = `${latitude}, ${longitude}`;\n\nconsole.log(`Processing geolocation for Task ID: ${taskId}, Coordinates: ${latitude}, ${longitude}`);\n\n// Return data for updating GPS coordinates\nreturn [{\n json: {\n chatId: chatId,\n userName: userName,\n taskId: taskId, // Task ID for update\n latitude: latitude,\n longitude: longitude,\n coordinatesText: coordinatesText,\n mapLink: mapLink,\n date: dateStr,\n time: timeStr,\n originalMessage: message,\n replyToMessageId: replyTo?.message_id || '',\n messageId: message.message_id\n }\n}];"
},
"typeVersion": 2
},
{
"id": "8290345b-2a83-4d12-ad6e-fb05ca9a298a",
"name": "检查位置错误1",
"type": "n8n-nodes-base.if",
"position": [
1456,
3088
],
"parameters": {
"conditions": {
"string": [
{
"value1": "={{ $json.error }}",
"value2": "true"
}
]
}
},
"typeVersion": 1
},
{
"id": "c004e38b-da7c-42d7-b1b0-32a831a59207",
"name": "位置错误消息1",
"type": "n8n-nodes-base.telegram",
"position": [
1648,
3040
],
"webhookId": "a7fd744e-295d-4cab-b249-6901330ab5fd",
"parameters": {
"text": "❌ **Error processing geolocation!**\n\n{{ $json.errorMessage || 'An unknown error occurred' }}\n\n📍 **What to do:**\n1. Ensure you are replying to the correct reminder message\n2. Try sending the geolocation again\n\nIf the error persists, contact the manager.",
"chatId": "={{ $json.chatId }}",
"additionalFields": {
"parse_mode": "Markdown",
"reply_to_message_id": "={{ $json.originalMessage?.message_id }}"
}
},
"credentials": {
"telegramApi": {
"id": "fMhKXtpyOQ7yXx8M",
"name": "📋 Project Management and Monitoring of Tasks & Photo Reports"
}
},
"typeVersion": 1
},
{
"id": "d0cb7670-385f-407a-becb-24caa976f9d6",
"name": "处理传入消息1",
"type": "n8n-nodes-base.code",
"position": [
288,
1952
],
"parameters": {
"jsCode": "// Combined handler for all incoming messages\nconst data = $input.first().json;\nconst message = data.message || {};\nconst text = message.text || '';\nconst chatId = String(message.chat?.id || data.callback_query?.message?.chat?.id || '');\nconst userName = (message.from?.first_name || '') + ' ' + (message.from?.last_name || '');\nconst userId = String(message.from?.id || data.callback_query?.from?.id || '');\nconst messageText = text;\n\n// Get stored context\nconst staticData = $getWorkflowStaticData('global');\nconst foremanContext = staticData.foremanContexts?.[chatId] || {};\n\n// Format time\nconst now = new Date();\nconst today = now.toLocaleDateString('en-US');\nconst time = now.toLocaleTimeString('en-US');\n\n// Check if the message is a reply to the registration one\nif (message.reply_to_message && message.reply_to_message.text && message.reply_to_message.text.includes('To complete registration, reply to this message')) {\n const nameMatch = text.match(/Name:\\s*(.+)/i);\n const companyMatch = text.match(/Company:\\s*(.+)/i);\n \n const executorName = nameMatch ? nameMatch[1].trim() : null;\n const company = companyMatch ? companyMatch[1].trim() : null;\n \n if (executorName && company) {\n return [{\n json: {\n action: 'complete_registration',\n chatId: chatId,\n userId: userId,\n executorName: executorName,\n company: company,\n userName: userName,\n originalMessage: message\n }\n }];\n } else {\n return [{\n json: {\n action: 'send_message',\n chatId: chatId,\n message: '❌ **Error!**\\\\n\\\\nPlease provide data in the format:\\\\nName: John Doe\\\\nCompany: BuildCorp\\\\n\\\\nReply again to the registration message.'\n }\n }];\n }\n}\n\n// Handle /start command\nif (text.trim().toLowerCase() === '/start') {\n return [{\n json: {\n action: 'register_user',\n chatId: chatId,\n userName: userName,\n userId: userId,\n message: message\n }\n }];\n}\n\n// Handle /help command\nif (messageText.trim().toLowerCase() === '/help') {\n return [{\n json: {\n action: \"send_message\",\n chatId: chatId,\n message: `📚 <b>Executor Log | Work Guide</b>\n\nThis bot is your personal assistant on the construction site! Every morning it reminds you in Telegram which reports on tasks and photo documents need to be sent for the day (for example, work log, welding reports, concrete, equipment, and accounting documents).\n\nYou just take a photo of the required document on your phone and send the photo in response to the bot's reminder. The bot automatically saves all photos to the table: who, when, and which report was sent.\n\nThanks to your responses, the manager always sees that the work is on schedule. You don't need to go to the office or fill out a bunch of papers manually!\n\n---\n\n📸 <b>Photo Reports — How the Assistant Works</b>\n\n<b>Reminders are checked around the clock:</b>\nOur program constantly checks if there are photo report reminders addressed to you. If there are — you immediately receive a message.\n\n<b>🔹 What comes to you as an employee on site:</b>\n🔔 List of required photos (work log, welding, concrete, equipment, etc.) in separate short messages.\n\n<b>🔹 What to do for the report to save automatically:</b>\n- Reply to the message with the report reminder.\n- Attach the photo in the reply, add a comment (if needed) and click \"Send\".\n- Information is automatically saved in our system for this report.\n\nAfter sending, you will automatically receive confirmation that your comment has reached the project manager.\n\n---\n\n🛠 <b>Tasks — How the Assistant Works</b>\n\n<b>Task planning:</b>\nThe project manager enters tasks into the table in advance. The program tracks time and sends you tasks for today.\n\n<b>🔹 What comes to you as an executor:</b>\n- Short task text.\n- Task priority:\n - 🔴 <b>High</b> — important to complete as soon as possible, affects other work deadlines.\n - 🟡 <b>Medium</b> — desirable to complete during the day.\n - 🟢 <b>Low</b> — complete after high and medium priority tasks.\n\n<b>🔹 What to do to send a task response:</b>\n- Reply to the message with text: <b>Completed</b> / <b>Partially</b> / <b>Not completed</b>.\n- Briefly describe what was done or why it didn't work.\n\nAfter sending, you will automatically receive confirmation that your comment has reached the project manager.\n\n📥 The response is automatically saved to the table for general control — you don't need to do anything extra!\n\n---\n\n❓ <b>For all questions, please contact your manager.</b>`,\n parse_mode: \"MarkdownV2\"\n }\n }];\n}\n\n// Handle /received command (for confirming task receipt)\nif (messageText.trim().toLowerCase() === '/received') {\n // Check if there is reply_to_message and if it contains task information\n if (message.reply_to_message && message.reply_to_message.text && \n (message.reply_to_message.text.includes('tasks') || \n message.reply_to_message.text.includes('priority:') ||\n message.reply_to_message.text.includes('Have a productive workday!'))) {\n return [{\n json: {\n action: \"process_received\",\n chat_id: chatId,\n foreman_id: String(chatId),\n message: `✅ Thank you for confirmation!\n\n📝 **How to send a task report:**\n\nReply to the message with a specific task in the format:\n\\`\\`\\`copy\ncompleted\nEverything is fine\n\\`\\`\\`\nor\n\\`\\`\\`copy\npartially\n70% foundation poured\n\\`\\`\\`\nor\n\\`\\`\\`copy\nnot completed\nNo materials\n\\`\\`\\``\n }\n }];\n } else {\n // If command without reply - process as general confirmation\n return [{\n json: {\n action: \"process_received\",\n chat_id: chatId,\n foreman_id: String(chatId),\n message: `✅ Confirmation accepted!\n\n📝 **Reminder about report format:**\n\nTo send a task report, reply to the task message in a two-line format:\n- completed / partially / not completed\n- Comment (if necessary)\n\nGood work! 💪`\n }\n }];\n }\n}\n\n// Handle /read command (for photo reports)\nif (text.trim().toLowerCase() === '/read') {\n // Check if there is reply_to_message\n if (message.reply_to_message) {\n return [{\n json: {\n action: 'process_read',\n chatId: chatId,\n userName: userName,\n originalMessage: message,\n isDirectCommand: false\n }\n }];\n } else {\n // If command sent without reply\n return [{\n json: {\n action: 'process_read',\n chatId: chatId,\n userName: userName,\n originalMessage: message,\n isDirectCommand: true\n }\n }];\n }\n}\n\n// Handle /status command\nif (text.trim().toLowerCase() === '/status') {\n return [{\n json: {\n action: 'check_today_status',\n chatId: chatId,\n userId: userId,\n date: today\n }\n }];\n}\n\n// Handle /report command\nif (text.trim().toLowerCase() === '/report') {\n return [{\n json: {\n action: 'send_report_menu',\n chatId: chatId,\n message: `📸 **Select report type to send:**\\\\n\\\\nSend the number of the desired report:\\\\n\\\\n1️⃣ Photos of the general work log\\\\n2️⃣ Photos of the welding log\\\\n3️⃣ Photos of the concrete work log\\\\n4️⃣ Photos of the construction equipment log, machine hours\\\\n5️⃣ Photos of primary accounting documents\\\\n\\\\nFor example, send \"1\" to send photos of the work log.`\n }\n }];\n}\n\n// Handle report type selection (numbers 1-5)\nif (text.match(/^[1-5]$/)) {\n const reportTypes = {\n '1': 'Photos of the general work log',\n '2': 'Photos of the welding log',\n '3': 'Photos of the concrete work log',\n '4': 'Photos of the construction equipment log, machine hours',\n '5': 'Photos of primary accounting documents'\n };\n const selectedType = reportTypes[text];\n const manualReportId = 'MANUAL_' + Date.now();\n return [{\n json: {\n action: 'send_manual_report_instruction',\n chatId: chatId,\n reportType: selectedType,\n reportId: manualReportId,\n message: `📸 **${selectedType}**\\\\n\\\\n🔸 ${manualReportId} ${selectedType}\\\\n\\\\nNow send the photo in reply to this message.\\\\n\\\\n⚠️ **Important:** Be sure to use the \"Reply\" function on this message!\\\\n\\\\nAdd a comment to the photo if necessary.`\n }\n }];\n}\n\n// Handle photos\nif (message.photo) {\n if (message.reply_to_message && message.reply_to_message.text) {\n return [{\n json: {\n action: 'process_photo',\n chatId: chatId,\n userName: userName,\n originalMessage: message,\n replyToMessage: message.reply_to_message\n }\n }];\n } else {\n return [{\n json: {\n action: 'send_error_no_reply',\n chatId: chatId,\n message: `❌ **Error!**\\\\n\\\\n⚠️ The photo must be sent **in reply** to the reminder message.\\\\n\\\\n📸 **How to send photo correctly:**\\\\n1. Find the message with the required report reminder\\\\n2. Click “Reply” on that message\\\\n3. Attach the photo and send\\\\n\\\\nIf you did not receive a reminder today, use the /report command`\n }\n }];\n }\n}\n\n// Handle geolocation (GPS coordinates)\nif (message.location) {\n if (message.reply_to_message && message.reply_to_message.text) {\n // Check if it's a reply to a photo report reminder\n const replyText = message.reply_to_message.text;\n if (replyText.includes('🔸') && (replyText.includes('Photos') || replyText.includes('log') || replyText.includes('MANUAL_'))) {\n return [{\n json: {\n action: 'process_location',\n chatId: chatId,\n userName: userName,\n originalMessage: message,\n replyToMessage: message.reply_to_message\n }\n }];\n }\n } else {\n return [{\n json: {\n action: 'send_error_no_reply',\n chatId: chatId,\n message: `❌ **Error!**\\\\n\\\\n⚠️ Geolocation must be sent **in reply** to the photo report reminder message.\\\\n\\\\n📍 **How to send GPS coordinates correctly:**\\\\n1. Find the message with the required report reminder\\\\n2. Click “Reply” on that message\\\\n3. Send geolocation\\\\n\\\\nIf you did not receive a reminder today, use the /report command`\n }\n }];\n }\n}\n\n// Functions for processing text reports\nfunction extractProjectInfo(messageText) {\n const objectMatch = messageText?.match(/\\|\\s*([^,]+),\\s*([^\\n]+)/);\n return {\n object: objectMatch ? objectMatch[1].trim() : foremanContext.object || \"Not specified\",\n section: objectMatch ? objectMatch[2].trim() : foremanContext.section || \"Not specified\"\n };\n}\n\nfunction extractTaskIdFromMessage(messageText) {\n let match = messageText.match(/\\*\\*(ID\\d+)\\*\\*/i);\n if (match) return match[1];\n \n match = messageText.match(/priority:\\s*\\n\\s*(ID\\d+)/i);\n if (match) return match[1];\n \n match = messageText.match(/priority:\\s*(ID\\d+)/i);\n if (match) return match[1];\n \n match = messageText.match(/^(ID\\d+)/mi);\n if (match) return match[1];\n \n return null;\n}\n\nfunction extractTaskNameFromMessage(messageText) {\n let match = messageText.match(/ID\\d+\\s+([^|]+)/i);\n if (match) return match[1].trim();\n \n match = messageText.match(/\\*\\*ID\\d+\\*\\*\\s+([^|]+)/i);\n return match ? match[1].trim() : null;\n}\n\n// Processing text reports for tasks\nconst replyToMessage = message.reply_to_message?.text || '';\nconst projectInfo = extractProjectInfo(replyToMessage);\nconst isTaskReply = replyToMessage.includes('priority:') && replyToMessage.includes('How to send report:');\n\nif (message.text && !message.text.startsWith('/') && isTaskReply) {\n const lines = text.split('\\n').filter(line => line.trim());\n const taskId = extractTaskIdFromMessage(replyToMessage);\n const taskName = extractTaskNameFromMessage(replyToMessage);\n \n if (taskId && taskName) {\n const status = lines[0] ? lines[0].trim().toLowerCase() : '';\n const comment = lines[1] ? lines.slice(1).join(' ').trim() : '';\n \n return [{\n json: {\n action: \"check_task_ownership\",\n chat_id: chatId,\n taskId: taskId,\n foremanId: String(chatId),\n status: status,\n comment: comment,\n replyToMessage: replyToMessage,\n projectInfo: projectInfo,\n foremanName: foremanContext.foremanName || userName,\n today: today,\n time: time\n }\n }];\n } else {\n return [{\n json: {\n action: \"send_command_response\",\n chat_id: chatId,\n message: `❌ **Error!**\n \n Could not determine task ID from the message. Please reply to the task message again.`\n }\n }];\n }\n}\n\n// Parsing structured report\nlet reports = [];\nif (message.text && !message.text.startsWith('/')) {\n const lines = text.split('\\n').filter(line => line.trim());\n \n if (text.toLowerCase().includes('task:') && text.toLowerCase().includes('status:')) {\n let currentReport = null;\n let currentField = null;\n let parsedObject = null;\n let parsedSection = null;\n \n for (let i = 0; i < lines.length; i++) {\n const line = lines[i].trim();\n const lowerLine = line.toLowerCase();\n \n if (lowerLine.includes('object:')) {\n parsedObject = line.substring(line.toLowerCase().indexOf('object:') + 7).trim();\n continue;\n }\n if (lowerLine.includes('section:')) {\n parsedSection = line.substring(line.toLowerCase().indexOf('section:') + 8).trim();\n continue;\n }\n \n if (lowerLine.includes('task:')) {\n if (currentReport && currentReport.Task) {\n reports.push(currentReport);\n }\n currentReport = {\n \"Date\": today,\n \"Time\": time,\n \"Executor\": foremanContext.foremanName || userName,\n \"Chat ID\": chatId,\n \"Executor ID\": chatId,\n \"Object\": parsedObject || projectInfo.object,\n \"Section\": parsedSection || projectInfo.section,\n \"Task\": line.substring(line.toLowerCase().indexOf('task:') + 5).trim(),\n \"Status\": \"Not specified\",\n \"Percentage\": 0,\n \"Comment\": \"\",\n \"Problems\": \"\",\n \"Report Type\": \"Detailed\",\n \"Report Date\": `${today} ${time}`\n };\n currentField = null;\n }\n else if (currentReport) {\n if (lowerLine.includes('status:')) {\n const statusValue = line.substring(line.toLowerCase().indexOf('status:') + 7).trim();\n const ddcmarker = \"Workflow by Artem Boiko - DataDrivenConstruction.io\";\n currentReport[\"Status\"] = statusValue;\n if (statusValue.toLowerCase().match(/completed/) && !statusValue.toLowerCase().includes('not')) {\n currentReport[\"Percentage\"] = 100;\n } else if (statusValue.toLowerCase().includes('partially')) {\n currentReport[\"Percentage\"] = 50;\n } else if (statusValue.toLowerCase().match(/not\\s*completed/)) {\n currentReport[\"Percentage\"] = 0;\n }\n currentField = null;\n }\n else if (lowerLine.includes('percentage:')) {\n const percentValue = line.substring(line.toLowerCase().indexOf('percentage:') + 11).trim();\n currentReport[\"Percentage\"] = parseInt(percentValue.replace('%', '')) || 0;\n currentField = null;\n }\n else if (lowerLine.includes('comment:')) {\n currentReport[\"Comment\"] = line.substring(line.toLowerCase().indexOf('comment:') + 8).trim();\n currentField = \"Comment\";\n }\n else if (lowerLine.includes('problems:') || lowerLine.includes('problem:')) {\n const problemIndex = lowerLine.includes('problems:') ? \n line.toLowerCase().indexOf('problems:') + 9 : \n line.toLowerCase().indexOf('problem:') + 8;\n currentReport[\"Problems\"] = line.substring(problemIndex).trim();\n currentField = \"Problems\";\n }\n else if (currentField && line.trim() && !lowerLine.includes(':')) {\n currentReport[currentField] += \" \" + line.trim();\n }\n }\n }\n \n if (currentReport && currentReport.Task) {\n reports.push(currentReport);\n }\n }\n}\n\nif (reports.length > 0) {\n return [{\n json: {\n action: \"process_report\",\n chat_id: chatId,\n reports: reports\n }\n }];\n}\n\n// If nothing recognized, send help\nif (!messageText.startsWith('/') && reports.length === 0) {\n return [{\n json: {\n action: \"send_message\",\n chatId: chatId,\n message: `❓ I don't understand your message.\\n\\n**Available commands:**\\n/read — confirm reading reminders\\n/received — confirm receiving tasks\\n/help — get help\\n\\nIf you need help — write /help`\n }\n }];\n}\n\n// Return reports for saving\nreturn reports.map(report => ({ json: report }));"
},
"typeVersion": 2
},
{
"id": "1ec82de5-0b07-4107-9504-816c55ad8279",
"name": "便签4",
"type": "n8n-nodes-base.stickyNote",
"position": [
512,
2976
],
"parameters": {
"color": 3,
"width": 2012,
"height": 548,
"content": "## 📍 地理位置处理"
},
"typeVersion": 1
},
{
"id": "d61899ec-b8f2-46ca-9b5d-17163dda28c6",
"name": "便签8",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1328,
1120
],
"parameters": {
"color": 3,
"width": 400,
"height": 380,
"content": "## 📊 数据结构"
},
"typeVersion": 1
},
{
"id": "f875f1ce-77e9-4618-8cec-50b710194d42",
"name": "便签9",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1328,
1520
],
"parameters": {
"color": 3,
"width": 400,
"height": 280,
"content": "## ⚠️ 重要说明"
},
"typeVersion": 1
},
{
"id": "11a3b790-6050-4402-a28c-ebcbc939ad39",
"name": "便签5",
"type": "n8n-nodes-base.stickyNote",
"position": [
-368,
1872
],
"parameters": {
"color": 6,
"width": 798,
"height": 408,
"content": "## 🚀 入口点"
},
"typeVersion": 1
},
{
"id": "6c81c616-a85b-4314-8b96-12ce471b6839",
"name": "便签6",
"type": "n8n-nodes-base.stickyNote",
"position": [
512,
2112
],
"parameters": {
"color": 6,
"width": 2334,
"height": 840,
"content": "## 📋 文本报告处理"
},
"typeVersion": 1
}
],
"active": true,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "68451949-ceba-499a-b7fd-d897eac5d295",
"connections": {
"Get tasks": {
"main": [
[
{
"node": "Find and form messages - Tasks",
"type": "main",
"index": 0
}
]
]
},
"Check read": {
"main": [
[
{
"node": "Read sheet for read",
"type": "main",
"index": 0
}
]
]
},
"Task Found?": {
"main": [
[
{
"node": "Update GPS in Table",
"type": "main",
"index": 0
}
],
[
{
"node": "Error - Task Not Found",
"type": "main",
"index": 0
}
]
]
},
"Check error2": {
"main": [
[
{
"node": "Photo error message4",
"type": "main",
"index": 0
}
],
[
{
"node": "Get and download file4",
"type": "main",
"index": 0
}
]
]
},
"Check Command1": {
"main": [
[
{
"node": "Send Command Response1",
"type": "main",
"index": 0
}
]
]
},
"Check ID Match1": {
"main": [
[
{
"node": "Check Process Report1",
"type": "main",
"index": 0
},
{
"node": "Check Command1",
"type": "main",
"index": 0
}
]
]
},
"Check Received1": {
"main": [
[
{
"node": "Get Tasks for Reading1",
"type": "main",
"index": 0
},
{
"node": "Send Received Confirmation",
"type": "main",
"index": 0
}
]
]
},
"Check new user1": {
"main": [
[],
[
{
"node": "Request registration data1",
"type": "main",
"index": 0
}
]
]
},
"Prepare Updates": {
"main": [
[
{
"node": "Are there updates?",
"type": "main",
"index": 0
}
]
]
},
"Extract Reports1": {
"main": [
[
{
"node": "Update Task Status1",
"type": "main",
"index": 0
}
]
]
},
"Check Task Owner1": {
"main": [
[
{
"node": "Get Tasks for Check1",
"type": "main",
"index": 0
}
]
]
},
"Does task exist?1": {
"main": [
[
{
"node": "Prepare data for updating2",
"type": "main",
"index": 0
}
],
[
{
"node": "Prepare data for adding2",
"type": "main",
"index": 0
}
]
]
},
"Find Task for GPS": {
"main": [
[
{
"node": "Task Found?",
"type": "main",
"index": 0
}
]
]
},
"Generate welcome1": {
"main": [
[
{
"node": "Send message",
"type": "main",
"index": 0
}
]
]
},
"Read users sheet1": {
"main": [
[
{
"node": "Check registration1",
"type": "main",
"index": 0
}
]
]
},
"Are there updates?": {
"main": [
[
{
"node": "Update status in table - Photo",
"type": "main",
"index": 0
}
],
[
{
"node": "No updates",
"type": "main",
"index": 0
}
]
]
},
"Check Action Type1": {
"main": [
[
{
"node": "Send Report Form1",
"type": "main",
"index": 0
}
]
]
},
"Check check_status": {
"main": [
[
{
"node": "Read data for status",
"type": "main",
"index": 0
}
]
]
},
"Check send_message": {
"main": [
[
{
"node": "Send message",
"type": "main",
"index": 0
}
]
]
},
"Form read response": {
"main": [
[
{
"node": "Send read confirmation",
"type": "main",
"index": 0
}
]
]
},
"Check manual_report": {
"main": [
[
{
"node": "Send message",
"type": "main",
"index": 0
}
]
]
},
"Check registration1": {
"main": [
[
{
"node": "Check new user1",
"type": "main",
"index": 0
},
{
"node": "Check existing user1",
"type": "main",
"index": 0
}
]
]
},
"Extract photo data4": {
"main": [
[
{
"node": "Check error2",
"type": "main",
"index": 0
}
]
]
},
"Filter read summary": {
"main": [
[
{
"node": "Form read response",
"type": "main",
"index": 0
}
]
]
},
"Group files by task": {
"main": [
[
{
"node": "Check existing task1",
"type": "main",
"index": 0
},
{
"node": "Merge lookup and grouped files",
"type": "main",
"index": 1
}
]
]
},
"Merge file streams2": {
"main": [
[
{
"node": "Group files by task",
"type": "main",
"index": 0
}
]
]
},
"Read sheet for read": {
"main": [
[
{
"node": "Prepare read updates",
"type": "main",
"index": 0
}
]
]
},
"Record new executor": {
"main": [
[
{
"node": "Confirm registration",
"type": "main",
"index": 0
}
]
]
},
"Update GPS in Table": {
"main": [
[
{
"node": "Confirm GPS Receipt",
"type": "main",
"index": 0
}
]
]
},
"Update Task Status1": {
"main": [
[
{
"node": "Format Confirmation1",
"type": "main",
"index": 0
}
]
]
},
"Check existing task1": {
"main": [
[
{
"node": "Merge lookup and grouped files",
"type": "main",
"index": 0
}
]
]
},
"Check existing user1": {
"main": [
[
{
"node": "Generate welcome1",
"type": "main",
"index": 0
}
]
]
},
"Check file presence4": {
"main": [
[
{
"node": "Upload to Google Drive4",
"type": "main",
"index": 0
}
],
[
{
"node": "Handle retrieval error4",
"type": "main",
"index": 0
}
]
]
},
"Check no_reply error": {
"main": [
[
{
"node": "Error message",
"type": "main",
"index": 0
}
]
]
},
"Check process_photo3": {
"main": [
[
{
"node": "Extract photo data4",
"type": "main",
"index": 0
}
]
]
},
"Check register_user1": {
"main": [
[
{
"node": "Read users sheet1",
"type": "main",
"index": 0
},
{
"node": "Check new user1",
"type": "main",
"index": 0
}
]
]
},
"Confirm registration": {
"main": [
[
{
"node": "Send message2",
"type": "main",
"index": 0
}
]
]
},
"Format Confirmation1": {
"main": [
[
{
"node": "Confirm Report Save1",
"type": "main",
"index": 0
}
]
]
},
"Get Tasks for Check1": {
"main": [
[
{
"node": "Check ID Match1",
"type": "main",
"index": 0
}
]
]
},
"Prepare confirmation": {
"main": [
[
{
"node": "Confirm photo receipt4",
"type": "main",
"index": 0
}
]
]
},
"Prepare read updates": {
"main": [
[
{
"node": "Filter read summary",
"type": "main",
"index": 0
},
{
"node": "Filter valid read updates",
"type": "main",
"index": 0
}
]
]
},
"Read data for status": {
"main": [
[
{
"node": "Collect status from sheet",
"type": "main",
"index": 0
}
]
]
},
"Update row in table2": {
"main": [
[
{
"node": "Merge for confirmation",
"type": "main",
"index": 0
}
]
]
},
"Add to reports table1": {
"main": [
[
{
"node": "Merge for confirmation",
"type": "main",
"index": 0
}
]
]
},
"Check Location Error1": {
"main": [
[
{
"node": "Location Error Message1",
"type": "main",
"index": 0
}
],
[
{
"node": "Find Task for GPS",
"type": "main",
"index": 0
}
]
]
},
"Check Process Report1": {
"main": [
[
{
"node": "Extract Reports1",
"type": "main",
"index": 0
}
]
]
},
"Prepare Read Updates1": {
"main": [
[
{
"node": "Filter Valid Read Updates1",
"type": "main",
"index": 0
}
]
]
},
"Update Read in Table1": {
"main": [
[
{
"node": "Send Received Confirmation",
"type": "main",
"index": 0
}
]
]
},
"Check send_report_menu": {
"main": [
[
{
"node": "Send message",
"type": "main",
"index": 0
}
]
]
},
"Extract Location Data1": {
"main": [
[
{
"node": "Check Location Error1",
"type": "main",
"index": 0
}
]
]
},
"Get Tasks for Reading1": {
"main": [
[
{
"node": "Prepare Read Updates1",
"type": "main",
"index": 0
}
]
]
},
"Get and download file4": {
"main": [
[
{
"node": "Check file presence4",
"type": "main",
"index": 0
}
]
]
},
"Get photo reports data": {
"main": [
[
{
"node": "Find and form messages - Photo",
"type": "main",
"index": 0
}
]
]
},
"Merge for confirmation": {
"main": [
[
{
"node": "Prepare confirmation",
"type": "main",
"index": 0
}
]
]
},
"Check Process Location2": {
"main": [
[
{
"node": "Extract Location Data1",
"type": "main",
"index": 0
}
]
]
},
"Generate status message": {
"main": [
[
{
"node": "Send message",
"type": "main",
"index": 0
}
]
]
},
"Handle retrieval error4": {
"main": [
[
{
"node": "Merge file streams2",
"type": "main",
"index": 0
}
]
]
},
"Prepare updates - Tasks": {
"main": [
[
{
"node": "Are there updates? - Tasks",
"type": "main",
"index": 0
}
]
]
},
"Upload to Google Drive4": {
"main": [
[
{
"node": "Prepare data for writing5",
"type": "main",
"index": 0
}
]
]
},
"Prepare data for adding2": {
"main": [
[
{
"node": "Add to reports table1",
"type": "main",
"index": 0
},
{
"node": "Merge for confirmation",
"type": "main",
"index": 1
}
]
]
},
"Collect status from sheet": {
"main": [
[
{
"node": "Generate status message",
"type": "main",
"index": 0
}
]
]
},
"Filter valid read updates": {
"main": [
[
{
"node": "Update read status in table",
"type": "main",
"index": 0
}
]
]
},
"Prepare data for writing5": {
"main": [
[
{
"node": "Merge file streams2",
"type": "main",
"index": 0
}
]
]
},
"Process incoming message1": {
"main": [
[
{
"node": "Check send_message",
"type": "main",
"index": 0
},
{
"node": "Check check_status",
"type": "main",
"index": 0
},
{
"node": "Check send_report_menu",
"type": "main",
"index": 0
},
{
"node": "Check manual_report",
"type": "main",
"index": 0
},
{
"node": "Check register_user1",
"type": "main",
"index": 0
},
{
"node": "Check read",
"type": "main",
"index": 0
},
{
"node": "Check no_reply error",
"type": "main",
"index": 0
},
{
"node": "Check complete_registration1",
"type": "main",
"index": 0
},
{
"node": "Check process_photo3",
"type": "main",
"index": 0
},
{
"node": "Check Command1",
"type": "main",
"index": 0
},
{
"node": "Check Action Type1",
"type": "main",
"index": 0
},
{
"node": "Check Process Report1",
"type": "main",
"index": 0
},
{
"node": "Check Received1",
"type": "main",
"index": 0
},
{
"node": "Check Task Owner1",
"type": "main",
"index": 0
},
{
"node": "Check Process Location2",
"type": "main",
"index": 0
}
]
]
},
"Are there updates? - Tasks": {
"main": [
[
{
"node": "Update status in table - Tasks",
"type": "main",
"index": 0
}
],
[
{
"node": "No updates - Tasks",
"type": "main",
"index": 0
}
]
]
},
"Check every minute - Photo": {
"main": [
[
{
"node": "Get photo reports data",
"type": "main",
"index": 0
}
]
]
},
"Check every minute - Tasks": {
"main": [
[
{
"node": "Get tasks",
"type": "main",
"index": 0
}
]
]
},
"Filter Valid Read Updates1": {
"main": [
[
{
"node": "Update Read in Table1",
"type": "main",
"index": 0
}
]
]
},
"Prepare data for recording": {
"main": [
[
{
"node": "Record new executor",
"type": "main",
"index": 0
}
]
]
},
"Prepare data for updating2": {
"main": [
[
{
"node": "Merge for confirmation",
"type": "main",
"index": 1
},
{
"node": "Update row in table2",
"type": "main",
"index": 0
}
]
]
},
"Request registration data1": {
"main": [
[
{
"node": "Send message",
"type": "main",
"index": 0
}
]
]
},
"Are there messages to send?": {
"main": [
[
{
"node": "Send to Telegram - Photo",
"type": "main",
"index": 0
},
{
"node": "Prepare Updates",
"type": "main",
"index": 0
}
],
[
{
"node": "No messages",
"type": "main",
"index": 0
}
]
]
},
"Update read status in table": {
"main": [
[
{
"node": "Send read confirmation",
"type": "main",
"index": 0
}
]
]
},
"Check complete_registration1": {
"main": [
[
{
"node": "Prepare data for recording",
"type": "main",
"index": 0
}
]
]
},
"Find and form messages - Photo": {
"main": [
[
{
"node": "Are there messages to send?",
"type": "main",
"index": 0
}
]
]
},
"Find and form messages - Tasks": {
"main": [
[
{
"node": "Are there messages to send? - Tasks",
"type": "main",
"index": 0
}
]
]
},
"Merge lookup and grouped files": {
"main": [
[
{
"node": "Does task exist?1",
"type": "main",
"index": 0
}
]
]
},
"Are there messages to send? - Tasks": {
"main": [
[
{
"node": "Send to Telegram - Tasks",
"type": "main",
"index": 0
},
{
"node": "Prepare updates - Tasks",
"type": "main",
"index": 0
}
],
[
{
"node": "No messages - Tasks",
"type": "main",
"index": 0
}
]
]
},
"Telegram Webhook - Receiving messages": {
"main": [
[
{
"node": "Process incoming message1",
"type": "main",
"index": 0
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级 - 项目管理
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
第一轮 Telegram 和 LinkedIn 快速通道 AI 招聘助手
AI候选人筛选流程：LinkedIn到Telegram，集成Gemini与Apify
If
Set
Code
+15
55 节点Dean Pike
人力资源
🎙️ VoiceScribe AI：使用 OpenAI Whisper 自动转录 Telegram 音频消息
使用 OpenAI Whisper 和 Google Workspace 自动转录 Telegram 语音消息
If
Code
Merge
+6
19 节点Trung Tran
文档提取
AI 邮件分析器：处理 PDF、图片
处理PDF、图片并保存至Google云端硬盘和Telegram
If
Set
Code
+17
31 节点Davide
人工智能
家居装饰AI（Google Nano Banana）- Santhej Kallada
基于Google Gemini的AI图像生成与编辑及Telegram机器人
If
Set
Code
+9
28 节点Santhej Kallada
内容创作
AI驱动视频创作与上传至Instagram、TikTok和YouTube
从云端硬盘进行AI驱动视频创作并上传至Instagram、TikTok和YouTube
If
Set
Code
+14
53 节点DevCode Journey
内容创作
带有 Chatgpt Agent Prompt 检查的信用卡
AI驱动信用卡推荐系统，集成OpenAI GPT、Telegram和Google Sheets
If
Set
Code
+7
31 节点Nishant
AI 聊天机器人
工作流信息
难度等级
高级
节点数量111
分类1
节点类型10
作者
Artem Boiko
@datadrivenconstructionFounder DataDrivenConstruction.io | AEC Tech Consultant & Automation Expert | Bridging Software and Construction
外部链接
在 n8n.io 查看 →
分享此工作流