Importar datos del medidor de energía E.ON W1000 a Home Assistant mediante la integración Spook

• •Cuenta de Google y credenciales de API de Gmail

{
  "meta": {
    "instanceId": "4a5b18b7956bb13cea42c4f3d7e9bb71b32449d237a47746edc89eb39825abfa"
  },
  "nodes": [
    {
      "id": "631c332d-9840-419a-ae30-2717f7eee7a9",
      "name": "Extraer desde archivo",
      "type": "n8n-nodes-base.extractFromFile",
      "position": [
        -176,
        288
      ],
      "parameters": {
        "options": {},
        "operation": "xlsx",
        "binaryPropertyName": "attachment_0"
      },
      "typeVersion": 1
    },
    {
      "id": "84c8fd03-9fe3-4d7e-bd1d-db12fba92358",
      "name": "Renombrar claves \"*_1\" para fusionar",
      "type": "n8n-nodes-base.renameKeys",
      "position": [
        336,
        192
      ],
      "parameters": {
        "keys": {
          "key": [
            {
              "newKey": "start",
              "currentKey": "Időbélyeg"
            },
            {
              "newKey": "AM",
              "currentKey": "Érték_1"
            }
          ]
        },
        "additionalOptions": {}
      },
      "typeVersion": 1
    },
    {
      "id": "8131bd99-2aae-47ee-8065-f376ecbb86eb",
      "name": "Obtener últimos 5 mensajes",
      "type": "n8n-nodes-base.gmail",
      "position": [
        -1296,
        320
      ],
      "webhookId": "d48f4845-7cc0-49b4-8c53-56a838465f7e",
      "parameters": {
        "limit": 5,
        "filters": {
          "sender": "noreply@eon.com"
        },
        "operation": "getAll"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "ctBdwDxIkxFWYxhN",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1,
      "alwaysOutputData": true
    },
    {
      "id": "b939f7c5-3b21-4406-a9d3-f69125a96800",
      "name": "Aggregate_id",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        -848,
        336
      ],
      "parameters": {
        "options": {
          "mergeLists": false
        },
        "fieldsToAggregate": {
          "fieldToAggregate": [
            {
              "fieldToAggregate": "id"
            },
            {
              "fieldToAggregate": "internalDate"
            }
          ]
        }
      },
      "typeVersion": 1
    },
    {
      "id": "ddfbda91-1b00-4ab4-998c-93db8acd6a95",
      "name": "Obtener mensaje[0]",
      "type": "n8n-nodes-base.gmail",
      "position": [
        -624,
        336
      ],
      "webhookId": "2a28f6f9-b2d9-4d3b-a398-fc6a624fbea3",
      "parameters": {
        "simple": false,
        "options": {
          "downloadAttachments": true
        },
        "messageId": "={{ $json.id[0] }}",
        "operation": "get"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "ctBdwDxIkxFWYxhN",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "249022c5-fc9d-4030-82f6-4065fe1c9ad7",
      "name": "Renombrar claves \"*_1\" para fusionar1",
      "type": "n8n-nodes-base.renameKeys",
      "position": [
        336,
        384
      ],
      "parameters": {
        "keys": {
          "key": [
            {
              "newKey": "start",
              "currentKey": "Időbélyeg"
            },
            {
              "newKey": "1_8_0",
              "currentKey": "Érték_2"
            }
          ]
        },
        "additionalOptions": {}
      },
      "typeVersion": 1
    },
    {
      "id": "42aac2f4-7b4b-4e48-91e2-ec9b4863443b",
      "name": "Renombrar claves \"*_1\" para fusionar2",
      "type": "n8n-nodes-base.renameKeys",
      "position": [
        336,
        672
      ],
      "parameters": {
        "keys": {
          "key": [
            {
              "newKey": "start",
              "currentKey": "Időbélyeg"
            },
            {
              "newKey": "2_8_0",
              "currentKey": "Érték_3"
            }
          ]
        },
        "additionalOptions": {}
      },
      "typeVersion": 1
    },
    {
      "id": "889c7b85-64c8-4f19-bd99-c2a56d6228b6",
      "name": "Extraer datos predeterminados de fuente (+A)",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        112,
        0
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "Időbélyeg,Érték"
      },
      "typeVersion": 1
    },
    {
      "id": "c04db25d-4461-4db8-a420-c6a9a2fec53a",
      "name": "Extraer datos '*_1' de fuente (-A)",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        112,
        192
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "Időbélyeg,Érték_1"
      },
      "typeVersion": 1
    },
    {
      "id": "091a5ad7-89b5-43f2-b59f-3abec3e13d7e",
      "name": "Extraer datos '*_2' de fuente (1_8_0)",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        112,
        384
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "Időbélyeg,Érték_2"
      },
      "typeVersion": 1
    },
    {
      "id": "2a12d83f-353d-41ba-bb9e-137d56718052",
      "name": "Extraer datos '*_3' de fuente (2_8_0)",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        112,
        672
      ],
      "parameters": {
        "options": {},
        "fieldToSplitOut": "Időbélyeg,Érték_3"
      },
      "typeVersion": 1
    },
    {
      "id": "cb8a13c8-e461-4ae0-80b6-b24d55b8197a",
      "name": "Fusionar (+A; -A)",
      "type": "n8n-nodes-base.merge",
      "position": [
        560,
        96
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "joinMode": "keepEverything",
        "fieldsToMatchString": "['start']"
      },
      "typeVersion": 3.2
    },
    {
      "id": "6e606abd-8594-4715-8c94-edfc0362798a",
      "name": "Renombrar claves \"*_1\" para fusionar3",
      "type": "n8n-nodes-base.renameKeys",
      "position": [
        336,
        0
      ],
      "parameters": {
        "keys": {
          "key": [
            {
              "newKey": "start",
              "currentKey": "Időbélyeg"
            },
            {
              "newKey": "AP",
              "currentKey": "Érték"
            }
          ]
        },
        "additionalOptions": {}
      },
      "typeVersion": 1
    },
    {
      "id": "8309f812-4ee1-4b71-ae15-019e0dc3cf87",
      "name": "Fusionar (+A; -A)1",
      "type": "n8n-nodes-base.merge",
      "position": [
        560,
        480
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "joinMode": "keepEverything",
        "fieldsToMatchString": "['start']"
      },
      "typeVersion": 3.2
    },
    {
      "id": "a59136fd-54c4-4965-94e0-ce89ef68ef97",
      "name": "Fusionar (+A; -A)2",
      "type": "n8n-nodes-base.merge",
      "position": [
        784,
        192
      ],
      "parameters": {
        "mode": "combine",
        "options": {},
        "joinMode": "keepEverything",
        "fieldsToMatchString": "['start']"
      },
      "typeVersion": 3.2
    },
    {
      "id": "d3a58b39-51cd-41ff-b0a9-e8730119f92a",
      "name": "Calcular suma horaria y",
      "type": "n8n-nodes-base.code",
      "position": [
        1600,
        192
      ],
      "parameters": {
        "jsCode": "// INPUT: items with { start, AP, AM, 1_8_0?, 2_8_0? }\n// OUTPUT: grouped hourly results with cumulative meters\n\nfunction toNum(x, def = 0) {\n  if (x === undefined || x === null || x === '') return def;\n  const n = Number(x);\n  return isNaN(n) ? def : n;\n}\n\n// --- group by hour ---\nconst grouped = {};\nfor (const item of items) {\n  const j = item.json;\n  const hour = j.start; // here already rounded to full hour\n  if (!grouped[hour]) {\n    grouped[hour] = { start: hour, AP: 0, AM: 0, m180: null, m280: null };\n  }\n  grouped[hour].AP += toNum(j.AP);\n  grouped[hour].AM += toNum(j.AM);\n  if (j['1_8_0'] !== undefined) grouped[hour].m180 = toNum(j['1_8_0']);\n  if (j['2_8_0'] !== undefined) grouped[hour].m280 = toNum(j['2_8_0']);\n}\n\n// --- sort by time ---\nconst hours = Object.values(grouped).sort((a, b) => new Date(a.start) - new Date(b.start));\n\n// --- build output ---\nlet last180 = null;\nlet last280 = null;\n\nconst out = [];\nfor (const h of hours) {\n  // if new meter values present -> reset base\n  if (h.m180 !== null) last180 = h.m180;\n  if (h.m280 !== null) last280 = h.m280;\n\n  // if no base yet (e.g. very first row), initialize with sums\n  if (last180 === null) last180 = 0;\n  if (last280 === null) last280 = 0;\n\n  const start180 = last180;\n  const start280 = last280;\n\n  const end180 = start180 + h.AP;\n  const end280 = start280 + h.AM;\n\n  // update for next iteration\n  last180 = end180;\n  last280 = end280;\n\n  out.push({\n    json: {\n      start: h.start,\n      AP: h.AP.toFixed(3),\n      AM: h.AM.toFixed(3),\n      '1_8_0': start180.toFixed(3),\n      '2_8_0': start280.toFixed(3),\n    }\n  });\n}\n\nreturn out;"
      },
      "typeVersion": 2
    },
    {
      "id": "8fb7b834-6899-4c70-916d-1386fc2063db",
      "name": "Spook: actualizar datos históricos +A1",
      "type": "n8n-nodes-base.homeAssistant",
      "position": [
        2416,
        96
      ],
      "parameters": {
        "domain": "recorder",
        "service": "import_statistics",
        "resource": "service",
        "operation": "call",
        "serviceAttributes": {
          "attributes": [
            {
              "name": "statistic_id",
              "value": "sensor.grid_energy_import"
            },
            {
              "name": "source",
              "value": "recorder"
            },
            {
              "name": "unit_of_measurement",
              "value": "kWh"
            },
            {
              "name": "has_mean",
              "value": "={{false}}"
            },
            {
              "name": "has_sum",
              "value": "={{ true }}"
            },
            {
              "name": "stats",
              "value": "={{ $json.data }}"
            }
          ]
        }
      },
      "credentials": {
        "homeAssistantApi": {
          "id": "5Q1lnmAQrn337KpP",
          "name": "Home Assistant account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "64031015-27d2-4ae5-9ccd-135ae36d7fcd",
      "name": "Spook: actualizar datos históricos -A1",
      "type": "n8n-nodes-base.homeAssistant",
      "position": [
        2416,
        288
      ],
      "parameters": {
        "domain": "recorder",
        "service": "import_statistics",
        "resource": "service",
        "operation": "call",
        "serviceAttributes": {
          "attributes": [
            {
              "name": "statistic_id",
              "value": "sensor.grid_energy_export"
            },
            {
              "name": "source",
              "value": "recorder"
            },
            {
              "name": "unit_of_measurement",
              "value": "kWh"
            },
            {
              "name": "has_mean",
              "value": "={{false}}"
            },
            {
              "name": "has_sum",
              "value": "={{ true }}"
            },
            {
              "name": "stats",
              "value": "={{ $json.data }}"
            }
          ]
        }
      },
      "credentials": {
        "homeAssistantApi": {
          "id": "5Q1lnmAQrn337KpP",
          "name": "Home Assistant account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "49db5891-53f2-45ad-882c-ca65dc6b02ec",
      "name": "Generar lista 1_8_0 para estadísticas",
      "type": "n8n-nodes-base.set",
      "position": [
        1968,
        96
      ],
      "parameters": {
        "mode": "raw",
        "options": {},
        "jsonOutput": "={\n  \"start\": {{new Date($json.start)}},\n  \"state\": {{ $json['1_8_0'] }},\n  \"sum\": {{ $json['1_8_0'] }}\n}"
      },
      "typeVersion": 3.4
    },
    {
      "id": "4e496d23-514a-4a0e-9903-ef0ded711fc6",
      "name": "Generar lista 2_8_0 para estadísticas",
      "type": "n8n-nodes-base.set",
      "position": [
        1968,
        288
      ],
      "parameters": {
        "mode": "raw",
        "options": {},
        "jsonOutput": "={\n  \"start\": {{new Date($json.start)}},\n  \"state\": {{ $json['2_8_0'] }},\n  \"sum\": {{ $json['2_8_0'] }}\n}"
      },
      "typeVersion": 3.4
    },
    {
      "id": "ac9ea82a-3d2c-49ef-8440-82ab0aa47713",
      "name": "Generar estadísticas 1_8_0",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        2192,
        96
      ],
      "parameters": {
        "include": "specifiedFields",
        "options": {},
        "aggregate": "aggregateAllItemData",
        "fieldsToInclude": "start,state, sum"
      },
      "typeVersion": 1
    },
    {
      "id": "81712ea5-dfc9-4988-be79-238bb31f4a28",
      "name": "Generar estadísticas 2_8_0",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        2192,
        288
      ],
      "parameters": {
        "include": "specifiedFields",
        "options": {},
        "aggregate": "aggregateAllItemData",
        "fieldsToInclude": "start,state, sum"
      },
      "typeVersion": 1
    },
    {
      "id": "c86a07b6-5ade-4ba0-a317-e75da9c7c091",
      "name": "Actualizar estado entidad input_number.exportt1",
      "type": "n8n-nodes-base.homeAssistant",
      "position": [
        2832,
        288
      ],
      "parameters": {
        "state": "={{ $('Generate 2_8_0 stats').item.json.data.at(-1).state }}",
        "entityId": "input_number.grid_export_meter",
        "resource": "state",
        "operation": "upsert"
      },
      "credentials": {
        "homeAssistantApi": {
          "id": "5Q1lnmAQrn337KpP",
          "name": "Home Assistant account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "6a0092b7-172d-47a8-8fd2-1be198bf542e",
      "name": "Actualizar estado entidad input_number.import1",
      "type": "n8n-nodes-base.homeAssistant",
      "position": [
        2832,
        96
      ],
      "parameters": {
        "state": "={{ $('Generate 1_8_0 stats').item.json.data.at(-1).state }}",
        "entityId": "input_number.grid_import_meter",
        "resource": "state",
        "operation": "upsert"
      },
      "credentials": {
        "homeAssistantApi": {
          "id": "5Q1lnmAQrn337KpP",
          "name": "Home Assistant account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "ae61e4e5-4441-49a0-88a3-a660483cd958",
      "name": "Gmail Trigger",
      "type": "n8n-nodes-base.gmailTrigger",
      "position": [
        -1296,
        512
      ],
      "parameters": {
        "filters": {
          "sender": "noreply@eon.com",
          "labelIds": []
        },
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        }
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "ctBdwDxIkxFWYxhN",
          "name": "Gmail account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "b16e04c4-e7cc-4ea1-9328-ef26e9741602",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1520,
        320
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "triggerAtHour": 14
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "96bed2da-2176-410f-9810-01a1607b07c7",
      "name": "Si attachment_0 es xlsx",
      "type": "n8n-nodes-base.if",
      "position": [
        -400,
        336
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "3b78aa20-1a72-4d43-9428-d754e9a51c55",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.subject }}",
              "rightValue": "[EON-W1000]"
            },
            {
              "id": "a046b65a-72b7-4eff-b97a-ad09acc1e753",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{$binary.attachment_0 ? true : false}}",
              "rightValue": ""
            },
            {
              "id": "4cfbc149-f429-4a68-a908-95e7db6906c6",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ [\"xls\", \"xlsx\"].includes($binary.attachment_0.fileExtension.toLowerCase()) }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "7f5378b1-aa34-4ba1-9375-302c4f242e64",
      "name": "Sin operación, no hacer nada1",
      "type": "n8n-nodes-base.noOp",
      "position": [
        -176,
        512
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "48003569-a556-4107-a48b-a9a13862e7f2",
      "name": "Nota adhesiva",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1616,
        -64
      ],
      "parameters": {
        "width": 1616,
        "height": 896,
        "content": "# Subject & Attachment checks\n- Gmail payload uses `Subject` (capital S), IMAP node uses `subject` (lowercase).\n- This template handles both: first **If** checks `$json.Subject` (Gmail), later **If** checks `$json.subject` (IMAP).\n- Attachment guard:\n  - Ensures `attachment_0` exists\n  - Ensures extension is `xls` or `xlsx`\n\n## Prerequisites\n- Configure your credentialt for the gmail and IMAP node (You can delete the IMAP node if you're not planning to use it)\n- Check the nodes for the correct `subject` and for the correct `sender`"
      },
      "typeVersion": 1
    },
    {
      "id": "b4db1439-e373-411c-aacd-cf4757af58f6",
      "name": "Nota adhesiva1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        32,
        -336
      ],
      "parameters": {
        "width": 912,
        "height": 1168,
        "content": "# Column Mapping\nThe E.ON export reuses column names; n8n appends `_1`, `_2`, `_3`.\nWe normalize to:\n\n- Default (+A): `Időbélyeg` → `start`, `Érték` → `AP`\n- *_1 (-A): `Időbélyeg` → `start`, `Érték_1` → `AM`\n- *_2 (1_8_0): `Időbélyeg` → `start`, `Érték_2` → `1_8_0`\n- *_3 (2_8_0): `Időbélyeg` → `start`, `Érték_3` → `2_8_0`\n\nThree **Merge** nodes combine on `start`.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "2fc06f65-1720-414d-a945-3fd938f4e857",
      "name": "Email Trigger (IMAP)",
      "type": "n8n-nodes-base.emailReadImap",
      "position": [
        -624,
        672
      ],
      "parameters": {
        "options": {
          "customEmailConfig": "=[\"UNSEEN\",[\"OR\",[\"FROM\",\"noreply@eon.com\"],[\"SUBJECT\",\"[EON-W1000]\"]]]"
        },
        "downloadAttachments": true
      },
      "typeVersion": 2.1
    },
    {
      "id": "a4e84c72-f998-45f3-949b-f55f3da3730a",
      "name": "Nota adhesiva3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1616,
        -848
      ],
      "parameters": {
        "color": 2,
        "width": 688,
        "height": 752,
        "content": "# E.ON W1000 → n8n → Home Assistant (Spook) — Overview\n\n**Goal:** Parse E.ON W1000 email export (.xlsx), group 15-min +A/-A into hourly totals, reconstruct 1.8.0 / 2.8.0 hourly meter states, and push long-term stats into Home Assistant via `recorder.import_statistics`. Also update `input_number.grid_import_meter` / `input_number.grid_export_meter`.\n\n**High-level path**\n1) Triggers:\n   - **Gmail Trigger** *or* **Email Trigger (IMAP)** → subject must match `[EON-W1000]` or the one that you set in the [E.ON e-portal](https://e-portal.eon-hungaria.com/w1000)\n   - **Schedule Trigger** optional daily poll → runs **Get last 5 messages** + filter.\n2) Check for correct attachment:\n   - **If** checks the subject; **If attachment_0 is xlsx** validates and ensures `.xls`/`.xlsx`.\n3) Parsing & shaping:\n   - **Extract from File (xlsx)** → 4 × **Split Out** by columns → 4 × **Rename keys** → **Merge**×3 → shaped rows `{ start, AP, AM, 1_8_0?, 2_8_0? }`.\n   - **Convert Excel time** → serial date → **Convert datetime to Spook format** (`yyyy-MM-dd HH:00:00ZZ`).\n   - **Code** node “Calculate hourly sum and” → groups 15-min into hourly sums; fills forward meter states.\n4) Stats payload:\n   - **Generate 1_8_0 list for stats** and **Generate 2_8_0 list for stats** → `{start, state, sum}` lists.\n   - **Aggregate** each list to `data` array → **Home Assistant: recorder.import_statistics** (two calls).\n   - Finally updates `input_number.*_meter` with last state value.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "de554edc-2b14-4a22-b6e3-b890962e8142",
      "name": "Nota adhesiva4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -1184
      ],
      "parameters": {
        "color": 2,
        "width": 624,
        "height": 192,
        "content": "# Credentials to configure\n- **Gmail OAuth2** *or* IMAP credentials (read-only)\n- **Home Assistant API** (Long-Lived Access Token)\n- Optional: adjust entity IDs if you renamed them in HA\n\n> Tip: Keep OAuth scopes minimal (read email only). Store secrets in n8n credentials, not in node parameters.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "6051e276-de9e-4629-8af3-3c278d301507",
      "name": "Nota adhesiva5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1600,
        272
      ],
      "parameters": {
        "color": 5,
        "height": 208,
        "content": "## Schedule trigger"
      },
      "typeVersion": 1
    },
    {
      "id": "69312661-e8bb-45e4-ba65-056805504df5",
      "name": "Nota adhesiva6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1376,
        464
      ],
      "parameters": {
        "color": 5,
        "height": 208,
        "content": "## Gmail trigger"
      },
      "typeVersion": 1
    },
    {
      "id": "07ef2161-472f-4d58-a0e3-d7cf56e40b91",
      "name": "Nota adhesiva7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -704,
        624
      ],
      "parameters": {
        "color": 5,
        "height": 208,
        "content": "## IMAP trigger"
      },
      "typeVersion": 1
    },
    {
      "id": "41d12eca-181d-4ed3-b0df-663659815142",
      "name": "Verificar asunto de correo",
      "type": "n8n-nodes-base.if",
      "position": [
        -1072,
        416
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "3b78aa20-1a72-4d43-9428-d754e9a51c55",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.Subject }}",
              "rightValue": "[EON-W1000]"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "882d8e14-710c-4869-83d1-0ac06cb9c9c6",
      "name": "Nota adhesiva2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        944,
        -16
      ],
      "parameters": {
        "width": 448,
        "height": 368,
        "content": "# Time Conversion\n- E.ON Excel time is a serial day count; **Convert Excel time** adds to `1899-12-30`.\n- **Convert datetime to Spook format** rounds to the top of the hour and formats ISO with offset.\n- If your HA runs in a different timezone, ensure n8n’s instance timezone matches HA, or adjust here.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "47b0ac45-6718-426c-8c5d-469d948daf94",
      "name": "Convertir datetime a formato Spook",
      "type": "n8n-nodes-base.dateTime",
      "position": [
        1232,
        192
      ],
      "parameters": {
        "date": "={{ $json['start'] }}",
        "format": "custom",
        "options": {
          "includeInputFields": true
        },
        "operation": "formatDate",
        "customFormat": "yyyy-MM-dd HH:00:00ZZ",
        "outputFieldName": "start"
      },
      "typeVersion": 2
    },
    {
      "id": "b4f079d8-e58c-45d6-8a59-1954729d3c37",
      "name": "Convertir tiempo Excel",
      "type": "n8n-nodes-base.dateTime",
      "position": [
        1008,
        192
      ],
      "parameters": {
        "options": {
          "includeInputFields": true
        },
        "duration": "={{ $json['start'] + 0.00000001}}",
        "timeUnit": "=days",
        "magnitude": "1899-12-30",
        "operation": "addToDate",
        "outputFieldName": "start"
      },
      "typeVersion": 2
    },
    {
      "id": "99a605b5-0e6c-41ca-aea6-394ef8b0b028",
      "name": "Nota adhesiva8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1408,
        -288
      ],
      "parameters": {
        "width": 448,
        "height": 640,
        "content": "# Hourly Grouping Logic (Code node)\nINPUT: `{ start, AP, AM, 1_8_0?, 2_8_0? }` at hourly timestamps\n- Sums 15-min AP/AM into hourly totals\n- Resets meter baselines when a new `1_8_0`/`2_8_0` reading is present\n- If missing at the very beginning, initializes from 0 (edit here if you prefer to drop earliest hour)\n\nOUTPUT: rows like\n```\n{\n  start: \"2025-09-01 10:00:00+02:00\",\n  AP: \"0.975\",\n  AM: \"0.000\",\n  \"1_8_0\": \"32749.288\",\n  \"2_8_0\": \"39627.868\"\n}\n```"
      },
      "typeVersion": 1
    },
    {
      "id": "f93df5c7-2d9a-4a0b-aebe-ebc51d0bd488",
      "name": "Nota adhesiva9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1872,
        -144
      ],
      "parameters": {
        "width": 816,
        "height": 608,
        "content": "# recorder.import_statistics payload\nWe build `{ start, state, sum }` arrays for each meter:\n- **start**: JS Date object\n- **state**: current meter state (kWh)\n- **sum**: same as state for total_increasing counters\n\nService: `recorder.import_statistics` → updates long-term stats\nRefs: Home Assistant Recorder docs. \n"
      },
      "typeVersion": 1
    },
    {
      "id": "0884b829-04ad-4643-a412-ef2a986d242a",
      "name": "Nota adhesiva10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2704,
        -176
      ],
      "parameters": {
        "width": 448,
        "height": 640,
        "content": "# Entity IDs updated\n- `sensor.grid_energy_import` / `sensor.grid_energy_export` → long-term stats via recorder\n- `input_number.grid_import_meter` / `input_number.grid_export_meter` → last known meter states\n\nIf you renamed entities, update here:\n- Home Assistant nodes: `statistic_id` and `entityId` fields\n- Template sensors in HA must be `device_class: energy` and `state_class: total_increasing`\n"
      },
      "typeVersion": 1
    },
    {
      "id": "d7742880-2443-45eb-8838-859c6f613643",
      "name": "Nota adhesiva11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -768
      ],
      "parameters": {
        "color": 2,
        "width": 624,
        "height": 272,
        "content": "# Troubleshooting\n- No rows after Extract? → Check that the email subject is `[EON-W1000]` and attachment is `.xlsx`.\n- Wrong times? → Verify timezone and Excel serial conversion (leap day quirks and locale).\n- HA not showing stats? → Confirm Recorder is enabled and entities are `total_increasing`.\n- 400/401 from HA node? → Regenerate Long-Lived Access Token; re-add credential in n8n.\n- Duplicate imports? → The code groups per hour; imports are idempotent if the same hour/state is sent.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "4a0c69b2-8e61-434e-aa6b-54e6b31975f8",
      "name": "Nota adhesiva12",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -976
      ],
      "parameters": {
        "color": 2,
        "width": 624,
        "height": 192,
        "content": "# Security & Privacy\n- Gmail/IMAP access is read-only; do not store raw attachments long-term.\n- Filter by sender (`noreply@eon.com`) + subject token `[EON-W1000]`.\n- Never hardcode tokens in nodes — use n8n Credentials."
      },
      "typeVersion": 1
    },
    {
      "id": "7a95c640-56c2-4e8b-a1bd-923200046f94",
      "name": "Nota adhesiva13",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -912,
        -1184
      ],
      "parameters": {
        "color": 2,
        "width": 608,
        "height": 1088,
        "content": "# Home Assistant prerequisites\n\nBefore running this workflow, make sure HA is prepared:\n\n1. **Recorder enabled**  \n   - Recorder integration must be active (default if you use Energy dashboard).\n\n2. **Spook integration installed**  \n   - Required for `recorder.import_statistics` service calls.  \n   - Install via HACS: search for \"Spook\".  \n   - Restart HA after installation.\n\n* **Helpers (input_number)**  \nAdd these to your configuration.yaml (or via UI → Helpers):\n\n```yaml\ninput_number:\n  grid_import_meter:\n    name: grid_import_meter\n    mode: box\n    initial: 0\n    min: 0\n    max: 9999999999\n    step: 0.001\n    unit_of_measurement: kWh\n  grid_export_meter:\n    name: grid_export_meter\n    mode: box\n    initial: 0\n    min: 0\n    max: 9999999999\n    step: 0.001\n    unit_of_measurement: kWh\n```\n*  **template sensors (sensor)**\nAdd these to your configuration.yaml (or via UI → Helpers):\n```yaml\ntemplate:\n  - sensor:\n      - name: \"grid_energy_import\"\n        state: \"{{ states('input_number.grid_import_meter') | float(0) }}\"\n        unit_of_measurement: \"kWh\"\n        device_class: energy\n        state_class: total_increasing\n      - name: \"grid_energy_export\"\n        state: \"{{ states('input_number.grid_export_meter') | float(0) }}\"\n        unit_of_measurement: \"kWh\"\n        device_class: energy\n        state_class: total_increasing\n```\n\n[![Segéd entitások](https://my.home-assistant.io/badges/helpers.svg)](https://my.home-assistant.io/redirect/helpers/)"
      },
      "typeVersion": 1
    }
  ],
  "pinData": {},
  "connections": {
    "b939f7c5-3b21-4406-a9d3-f69125a96800": {
      "main": [
        [
          {
            "node": "ddfbda91-1b00-4ab4-998c-93db8acd6a95",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ae61e4e5-4441-49a0-88a3-a660483cd958": {
      "main": [
        [
          {
            "node": "41d12eca-181d-4ed3-b0df-663659815142",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "cb8a13c8-e461-4ae0-80b6-b24d55b8197a": {
      "main": [
        [
          {
            "node": "a59136fd-54c4-4965-94e0-ce89ef68ef97",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8309f812-4ee1-4b71-ae15-019e0dc3cf87": {
      "main": [
        [
          {
            "node": "a59136fd-54c4-4965-94e0-ce89ef68ef97",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "a59136fd-54c4-4965-94e0-ce89ef68ef97": {
      "main": [
        [
          {
            "node": "b4f079d8-e58c-45d6-8a59-1954729d3c37",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ddfbda91-1b00-4ab4-998c-93db8acd6a95": {
      "main": [
        [
          {
            "node": "96bed2da-2176-410f-9810-01a1607b07c7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b16e04c4-e7cc-4ea1-9328-ef26e9741602": {
      "main": [
        [
          {
            "node": "8131bd99-2aae-47ee-8065-f376ecbb86eb",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "631c332d-9840-419a-ae30-2717f7eee7a9": {
      "main": [
        [
          {
            "node": "889c7b85-64c8-4f19-bd99-c2a56d6228b6",
            "type": "main",
            "index": 0
          },
          {
            "node": "c04db25d-4461-4db8-a420-c6a9a2fec53a",
            "type": "main",
            "index": 0
          },
          {
            "node": "091a5ad7-89b5-43f2-b59f-3abec3e13d7e",
            "type": "main",
            "index": 0
          },
          {
            "node": "2a12d83f-353d-41ba-bb9e-137d56718052",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b4f079d8-e58c-45d6-8a59-1954729d3c37": {
      "main": [
        [
          {
            "node": "47b0ac45-6718-426c-8c5d-469d948daf94",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "41d12eca-181d-4ed3-b0df-663659815142": {
      "main": [
        [
          {
            "node": "b939f7c5-3b21-4406-a9d3-f69125a96800",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "7f5378b1-aa34-4ba1-9375-302c4f242e64",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8131bd99-2aae-47ee-8065-f376ecbb86eb": {
      "main": [
        [
          {
            "node": "41d12eca-181d-4ed3-b0df-663659815142",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2fc06f65-1720-414d-a945-3fd938f4e857": {
      "main": [
        [
          {
            "node": "96bed2da-2176-410f-9810-01a1607b07c7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ac9ea82a-3d2c-49ef-8440-82ab0aa47713": {
      "main": [
        [
          {
            "node": "8fb7b834-6899-4c70-916d-1386fc2063db",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "81712ea5-dfc9-4988-be79-238bb31f4a28": {
      "main": [
        [
          {
            "node": "64031015-27d2-4ae5-9ccd-135ae36d7fcd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "96bed2da-2176-410f-9810-01a1607b07c7": {
      "main": [
        [
          {
            "node": "631c332d-9840-419a-ae30-2717f7eee7a9",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "7f5378b1-aa34-4ba1-9375-302c4f242e64",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "d3a58b39-51cd-41ff-b0a9-e8730119f92a": {
      "main": [
        [
          {
            "node": "49db5891-53f2-45ad-882c-ca65dc6b02ec",
            "type": "main",
            "index": 0
          },
          {
            "node": "4e496d23-514a-4a0e-9903-ef0ded711fc6",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "84c8fd03-9fe3-4d7e-bd1d-db12fba92358": {
      "main": [
        [
          {
            "node": "cb8a13c8-e461-4ae0-80b6-b24d55b8197a",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "249022c5-fc9d-4030-82f6-4065fe1c9ad7": {
      "main": [
        [
          {
            "node": "8309f812-4ee1-4b71-ae15-019e0dc3cf87",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "42aac2f4-7b4b-4e48-91e2-ec9b4863443b": {
      "main": [
        [
          {
            "node": "8309f812-4ee1-4b71-ae15-019e0dc3cf87",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "6e606abd-8594-4715-8c94-edfc0362798a": {
      "main": [
        [
          {
            "node": "cb8a13c8-e461-4ae0-80b6-b24d55b8197a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "49db5891-53f2-45ad-882c-ca65dc6b02ec": {
      "main": [
        [
          {
            "node": "ac9ea82a-3d2c-49ef-8440-82ab0aa47713",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "4e496d23-514a-4a0e-9903-ef0ded711fc6": {
      "main": [
        [
          {
            "node": "81712ea5-dfc9-4988-be79-238bb31f4a28",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "47b0ac45-6718-426c-8c5d-469d948daf94": {
      "main": [
        [
          {
            "node": "d3a58b39-51cd-41ff-b0a9-e8730119f92a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8fb7b834-6899-4c70-916d-1386fc2063db": {
      "main": [
        [
          {
            "node": "6a0092b7-172d-47a8-8fd2-1be198bf542e",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "64031015-27d2-4ae5-9ccd-135ae36d7fcd": {
      "main": [
        [
          {
            "node": "c86a07b6-5ade-4ba0-a317-e75da9c7c091",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c04db25d-4461-4db8-a420-c6a9a2fec53a": {
      "main": [
        [
          {
            "node": "84c8fd03-9fe3-4d7e-bd1d-db12fba92358",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "889c7b85-64c8-4f19-bd99-c2a56d6228b6": {
      "main": [
        [
          {
            "node": "6e606abd-8594-4715-8c94-edfc0362798a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "091a5ad7-89b5-43f2-b59f-3abec3e13d7e": {
      "main": [
        [
          {
            "node": "249022c5-fc9d-4030-82f6-4065fe1c9ad7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "2a12d83f-353d-41ba-bb9e-137d56718052": {
      "main": [
        [
          {
            "node": "42aac2f4-7b4b-4e48-91e2-ec9b4863443b",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}