{ "edges": [ { "to": "49a41b04-5e79-41b6-acd1-44df8fae51d3", "from": "7c0c0f1b-a57d-4264-9b30-ec0a1b82a7ff", "toPort": "0", "fromPort": "0" }, { "to": "dd94842a-6b14-44a0-8374-0b689c45241e", "from": "7c0c0f1b-a57d-4264-9b30-ec0a1b82a7ff", "toPort": "0", "fromPort": "1" }, { "to": "57a405ae-dcca-4295-87f3-82735d9abee1", "from": "3d4c3e82-8c57-4926-9aed-562cd7fbd010", "toPort": "0", "fromPort": "0" }, { "to": "eafd898e-978e-4943-a651-0f15ef501720", "from": "0c1deaea-9cf6-464d-a50d-b689a3150f25", "toPort": "0", "fromPort": "0" }, { "to": "7c0c0f1b-a57d-4264-9b30-ec0a1b82a7ff", "from": "eafd898e-978e-4943-a651-0f15ef501720", "toPort": "0", "fromPort": "0" }, { "to": "764bccc9-2ebc-47bd-a5ac-8ff38b5e6176", "from": "dd94842a-6b14-44a0-8374-0b689c45241e", "toPort": "0", "fromPort": "0" }, { "to": "3d4c3e82-8c57-4926-9aed-562cd7fbd010", "from": "49a41b04-5e79-41b6-acd1-44df8fae51d3", "toPort": "0", "fromPort": "0" }, { "to": "e413a007-ed1d-4d37-aafa-a7aa387eeba6", "from": "90eded44-29f3-4d2e-bc49-5e0eaab02ace", "toPort": "0", "fromPort": "0" }, { "to": "0c1deaea-9cf6-464d-a50d-b689a3150f25", "from": "7afeba35-7bde-46b7-81ea-fc47b9738d92", "toPort": "0", "fromPort": "0" }, { "to": "7c0c0f1b-a57d-4264-9b30-ec0a1b82a7ff", "from": "5a1c200c-732e-47bb-86c3-13e0d118b271", "toPort": "0", "fromPort": "0" }, { "to": "90eded44-29f3-4d2e-bc49-5e0eaab02ace", "from": "638156fb-4f6a-4418-913e-a0d171b247fd", "toPort": "0", "fromPort": "0" }, { "to": "7afeba35-7bde-46b7-81ea-fc47b9738d92", "from": "764bccc9-2ebc-47bd-a5ac-8ff38b5e6176", "toPort": "0", "fromPort": "0" }, { "to": "f65d4463-dfa3-40d6-aff1-764df96f5fdd", "from": "5635df8f-d763-4d4a-a23b-fa8eb0861b2a", "toPort": "0", "fromPort": "0" }, { "to": "5a1c200c-732e-47bb-86c3-13e0d118b271", "from": "5635df8f-d763-4d4a-a23b-fa8eb0861b2a", "toPort": "0", "fromPort": "1" }, { "to": "5635df8f-d763-4d4a-a23b-fa8eb0861b2a", "from": "e413a007-ed1d-4d37-aafa-a7aa387eeba6", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "90eded44-29f3-4d2e-bc49-5e0eaab02ace", "name": "Read Log Sheet", "type": "integration", "params": { "options": {}, "service": "googleSheets", "operation": "read", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list", "value": "1zj0LlOO4f0pOqmnvCocHMdibKVzyBglKcoCDHujjhYc", "cachedResultName": "daily jira logs" } }, "position": [ 496, 768 ] }, { "id": "e413a007-ed1d-4d37-aafa-a7aa387eeba6", "name": "Filter Pending + Today", "type": "code", "params": { "jsCode": "const items = $input.all();\nconst today = new Date();\nconst yyyy = today.getFullYear();\nconst mm = String(today.getMonth() + 1).padStart(2, '0');\nconst dd = String(today.getDate()).padStart(2, '0');\nconst todayStr = yyyy + '-' + mm + '-' + dd;\n\nconst pending = items.map((item, index) => {\n return { \n ...item.json, \n sheet_row: index + 2\n };\n}).filter(r => {\n const rowDate = (r.date || '').toString().trim();\n const rowStatus = (r.status || '').toString().trim().toLowerCase();\n return rowStatus === 'pending' && rowDate <= todayStr && rowDate !== '';\n});\n\nif (pending.length === 0) {\n return [{ json: { nothingToDo: true, todayStr, rows: [] } }];\n}\nreturn [{ json: { nothingToDo: false, todayStr, rows: pending } }];" }, "position": [ 688, 768 ] }, { "id": "5635df8f-d763-4d4a-a23b-fa8eb0861b2a", "name": "Anything To Process?", "type": "if", "params": { "options": {}, "conditions": { "options": { "version": 1, "leftValue": "", "caseSensitive": true, "typeValidation": "strict" }, "combinator": "and", "conditions": [ { "operator": { "type": "boolean", "operation": "equals" }, "leftValue": "={{ $json.nothingToDo }}", "rightValue": true } ] } }, "position": [ 912, 768 ] }, { "id": "5a1c200c-732e-47bb-86c3-13e0d118b271", "name": "Split Into Rows", "type": "code", "params": { "jsCode": "const d = $input.first().json;\nif (d.nothingToDo || !d.rows || d.rows.length === 0) return [];\n\nconst allSessions = $getWorkflowStaticData('global');\nallSessions.__loopResults = [];\n\nreturn d.rows.map(r => ({ json: r }));" }, "position": [ 1184, 976 ] }, { "id": "7c0c0f1b-a57d-4264-9b30-ec0a1b82a7ff", "name": "Loop Rows", "type": "passthrough", "params": { "mode": "noOp", "options": {} }, "position": [ 1440, 976 ] }, { "id": "dd94842a-6b14-44a0-8374-0b689c45241e", "name": "Build Payload", "type": "code", "params": { "jsCode": "const row = $input.first().json;\nconst rowDate = (row.date || new Date().toISOString().slice(0, 10)).trim();\nlet startedISO = rowDate + 'T09:00:00.000+0000';\n\nif (row.started_at) {\n try {\n const timeStr = row.started_at.toString().trim();\n const combined = rowDate + 'T' + timeStr + ':00.000+0000';\n const parsed = new Date(combined);\n if (!isNaN(parsed)) {\n startedISO = parsed.toISOString().replace('Z', '+0000');\n } else {\n const parsed2 = new Date(rowDate + ' ' + timeStr);\n if (!isNaN(parsed2)) startedISO = parsed2.toISOString().replace('Z', '+0000');\n }\n } catch(e) {}\n}\n\nlet timeSpent = '1h';\nif (row.log_time) {\n const raw = row.log_time.toString().trim().toLowerCase();\n if (/^\\d+h(\\d+m)?$/.test(raw)) { timeSpent = raw; }\n else if (/^\\d+m$/.test(raw)) { timeSpent = raw; }\n else if (/^[\\d.]+$/.test(raw)) {\n const hrs = parseFloat(raw);\n const h = Math.floor(hrs);\n const m = Math.round((hrs - h) * 60);\n timeSpent = h > 0 ? (h + 'h' + (m > 0 ? m + 'm' : '')) : m + 'm';\n }\n}\n\nconst payload = { ...row, jiraIssueKey: row.ticket_id, rowDate, timeSpent, startedISO, logComment: row.log_text || 'Daily worklog' };\n\nconst allSessions = $getWorkflowStaticData('global');\nallSessions.__currentRow = { ticket_id: row.ticket_id, date: row.date, timeSpent, log_text: row.log_text || '', retry_count: parseInt(row.retry_count || 0), row_number: row.row_number };\n\nreturn [{ json: payload }];" }, "position": [ 1792, 992 ] }, { "id": "764bccc9-2ebc-47bd-a5ac-8ff38b5e6176", "name": "Jira: Add Worklog", "type": "http", "params": { "url": "=https://YOUR-DOMAIN.atlassian.net/rest/api/3/issue/{{ $json.jiraIssueKey }}/worklog", "method": "POST", "options": { "timeout": 15000 }, "jsonBody": "={{ { \"timeSpent\": $json.timeSpent, \"started\": $json.startedISO, \"comment\": { \"type\": \"doc\", \"version\": 1, \"content\": [{ \"type\": \"paragraph\", \"content\": [{ \"type\": \"text\", \"text\": $json.logComment }] }] } } }}", "sendBody": true, "specifyBody": "json", "authentication": "genericCredentialType", "genericAuthType": "httpBasicAuth" }, "position": [ 2208, 992 ] }, { "id": "7afeba35-7bde-46b7-81ea-fc47b9738d92", "name": "Handle Response", "type": "code", "params": { "jsCode": "const jiraResp = $input.first().json;\nconst allSessions = $getWorkflowStaticData('global');\nconst rowData = allSessions.__currentRow || {};\n\nconst isSuccess = !!(jiraResp.id || jiraResp.self);\n\nconst result = {\n sheet_row: rowData.row_number || rowData.sheet_row, \n status: isSuccess ? 'Completed' : 'Pending',\n error_message: isSuccess ? '' : JSON.stringify(jiraResp).slice(0, 200),\n retry_count: isSuccess ? 0 : (rowData.retry_count + 1),\n jiraSuccess: isSuccess,\n ticket_id: rowData.ticket_id,\n timeSpent: rowData.timeSpent,\n};\n\nif (!allSessions.__loopResults) allSessions.__loopResults = [];\nallSessions.__loopResults.push(result);\n\nreturn [{ json: result }];" }, "position": [ 2512, 992 ] }, { "id": "0c1deaea-9cf6-464d-a50d-b689a3150f25", "name": "Edit Fields", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "a1", "name": "status", "type": "string", "value": "={{ $json.status }}" }, { "id": "a2", "name": "error_message", "type": "string", "value": "={{ $json.error_message }}" }, { "id": "a3", "name": "retry_count", "type": "number", "value": "={{ $json.retry_count }}" }, { "id": "a4", "name": "ticket_id", "type": "string", "value": "={{ $json.ticket_id }}" }, { "id": "a5", "name": "sheet_row", "type": "number", "value": "={{ $json.sheet_row }}" } ] } }, "position": [ 2752, 992 ] }, { "id": "eafd898e-978e-4943-a651-0f15ef501720", "name": "Update Sheet", "type": "integration", "params": { "columns": { "value": { "status": "={{ $json.status }}", "ticket_id": "={{ $json.ticket_id }}", "row_number": "={{ $json.sheet_row }}", "retry_count": "={{ $json.retry_count }}", "error_message": "={{ $json.error_message }}" }, "schema": [ { "id": "ticket_id", "type": "string", "display": true, "removed": false, "required": false, "displayName": "ticket_id", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "log_text", "type": "string", "display": true, "removed": true, "required": false, "displayName": "log_text", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "date", "type": "string", "display": true, "removed": true, "required": false, "displayName": "date", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "started_at", "type": "string", "display": true, "removed": true, "required": false, "displayName": "started_at", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "log_time", "type": "string", "display": true, "removed": true, "required": false, "displayName": "log_time", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "status", "type": "string", "display": true, "removed": false, "required": false, "displayName": "status", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "error_message", "type": "string", "display": true, "required": false, "displayName": "error_message", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "retry_count", "type": "string", "display": true, "required": false, "displayName": "retry_count", "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": {}, "service": "googleSheets", "operation": "update", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list", "value": "1zj0LlOO4f0pOqmnvCocHMdibKVzyBglKcoCDHujjhYc", "cachedResultName": "daily jira logs" } }, "position": [ 2928, 1088 ] }, { "id": "49a41b04-5e79-41b6-acd1-44df8fae51d3", "name": "Build Summary", "type": "code", "params": { "jsCode": "const allSessions = $getWorkflowStaticData('global');\nconst results = allSessions.__loopResults || [];\nallSessions.__loopResults = [];\n\nlet success = 0, failed = 0, totalMinutes = 0;\nconst failedTickets = [];\nconst ticketIds = [];\n\nfor (const item of results) {\n if (item.ticket_id) { ticketIds.push(item.ticket_id); }\n if (item.jiraSuccess === true) {\n success++;\n const ts = item.timeSpent || '0m';\n const h = ts.match(/(\\d+)h/) ? parseInt(ts.match(/(\\d+)h/)[1]) : 0;\n const m = ts.match(/(\\d+)m/) ? parseInt(ts.match(/(\\d+)m/)[1]) : 0;\n totalMinutes += (h * 60) + m;\n } else {\n failed++;\n failedTickets.push(item.ticket_id + ' (' + item.date + ')');\n }\n}\n\nconst h = Math.floor(totalMinutes / 60);\nconst m = totalMinutes % 60;\nconst totalStr = h > 0 ? h + 'h ' + (m > 0 ? m + 'm' : '') : m + 'm';\n\nreturn [{ json: { success, failed, totalStr: totalStr.trim() || '0m', today: new Date().toISOString().slice(0, 10), failedTickets: failedTickets.join(', ') || 'None', ticket_ids: ticketIds.join(', ') } }];" }, "position": [ 1776, 608 ] }, { "id": "3d4c3e82-8c57-4926-9aed-562cd7fbd010", "name": "AI Summary", "type": "llm", "params": { "modelId": { "__rl": true, "mode": "list", "value": "gpt-4o-mini" }, "options": {}, "messages": { "values": [ { "content": "={{ 'Write a friendly 2-3 sentence self-update about today\\'s Jira worklog. ' + $json.success + ' logs were added successfully, ' + $json.failed + ' failed. Total time logged: ' + $json.totalStr + '. ' + ($json.failed > 0 ? 'Failed tickets: ' + $json.failedTickets + '. ' : '') + 'Here is a summary of all tickets worked on today: ' + $json.ticket_ids + '. Keep it concise, positive, and professional.' }}" } ] }, "provider": "openai" }, "position": [ 2064, 608 ] }, { "id": "f65d4463-dfa3-40d6-aff1-764df96f5fdd", "name": "Email: Nothing To Do", "type": "integration", "params": { "sendTo": "your@email.com", "message": "

💤 Jira Worklog — {{ $json.todayStr }}

No pending rows found. Nothing to log today.

", "options": {}, "service": "gmail", "subject": "Jira Worklog — No Rows Today", "operation": "read" }, "position": [ 1200, 576 ] }, { "id": "57a405ae-dcca-4295-87f3-82735d9abee1", "name": "Email: Final Report", "type": "integration", "params": { "sendTo": "your@email.com", "message": "=

📅 Jira Daily Report — {{ $('Build Summary').first().json.today }}

✅ Success: {{ $('Build Summary').first().json.success }} ticket(s)

❌ Failed: {{ $('Build Summary').first().json.failed }} ticket(s)

⏳ Total: {{ $('Build Summary').first().json.totalStr }}

{{ $('Build Summary').first().json.failed > 0 ? '

⚠️ Failed: ' + $('Build Summary').first().json.failedTickets + '

' : '' }}

🤖 {{ $json.message.content }}

", "options": {}, "service": "gmail", "subject": "Jira Daily Worklog Report", "operation": "read" }, "position": [ 2480, 608 ] }, { "id": "638156fb-4f6a-4418-913e-a0d171b247fd", "name": "Every Night 10 PM", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "cronExpression", "expression": "0 0 22 * * *" } ] } }, "position": [ 224, 768 ] } ] }

Log Jira worklogs nightly from Google Sheets with Gmail and GPT-4o-mini