{
  "name": "Import E.ON W1000 energy meter data to Home Assistant with Spook integration",
  "nodes": [
    {
      "id": "631c332d-9840-419a-ae30-2717f7eee7a9",
      "name": "Extract from File",
      "type": "n8n-nodes-base.extractFromFile",
      "position": [
        -176,
        288
      ]
    },
    {
      "id": "84c8fd03-9fe3-4d7e-bd1d-db12fba92358",
      "name": "Rename \"*_1\" keys for merge",
      "type": "n8n-nodes-base.renameKeys",
      "position": [
        336,
        192
      ]
    },
    {
      "id": "8131bd99-2aae-47ee-8065-f376ecbb86eb",
      "name": "Get last 5 messages",
      "type": "n8n-nodes-base.gmail",
      "position": [
        -1296,
        320
      ]
    },
    {
      "id": "b939f7c5-3b21-4406-a9d3-f69125a96800",
      "name": "Aggregate_id",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        -848,
        336
      ]
    },
    {
      "id": "ddfbda91-1b00-4ab4-998c-93db8acd6a95",
      "name": "Get a message[0]",
      "type": "n8n-nodes-base.gmail",
      "position": [
        -624,
        336
      ]
    },
    {
      "id": "249022c5-fc9d-4030-82f6-4065fe1c9ad7",
      "name": "Rename \"*_1\" keys for merge1",
      "type": "n8n-nodes-base.renameKeys",
      "position": [
        336,
        384
      ]
    },
    {
      "id": "42aac2f4-7b4b-4e48-91e2-ec9b4863443b",
      "name": "Rename \"*_1\" keys for merge2",
      "type": "n8n-nodes-base.renameKeys",
      "position": [
        336,
        672
      ]
    },
    {
      "id": "889c7b85-64c8-4f19-bd99-c2a56d6228b6",
      "name": "Extract default data from source (+A)",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        112,
        0
      ]
    },
    {
      "id": "c04db25d-4461-4db8-a420-c6a9a2fec53a",
      "name": "Extract '*_1' data from source (-A)",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        112,
        192
      ]
    },
    {
      "id": "091a5ad7-89b5-43f2-b59f-3abec3e13d7e",
      "name": "Extract '*_2' data from source (1_8_0)",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        112,
        384
      ]
    },
    {
      "id": "2a12d83f-353d-41ba-bb9e-137d56718052",
      "name": "Extract '*_3' data from source (2_8_0)",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        112,
        672
      ]
    },
    {
      "id": "cb8a13c8-e461-4ae0-80b6-b24d55b8197a",
      "name": "Merge (+A; -A)",
      "type": "n8n-nodes-base.merge",
      "position": [
        560,
        96
      ]
    },
    {
      "id": "6e606abd-8594-4715-8c94-edfc0362798a",
      "name": "Rename \"*_1\" keys for merge3",
      "type": "n8n-nodes-base.renameKeys",
      "position": [
        336,
        0
      ]
    },
    {
      "id": "8309f812-4ee1-4b71-ae15-019e0dc3cf87",
      "name": "Merge (+A; -A)1",
      "type": "n8n-nodes-base.merge",
      "position": [
        560,
        480
      ]
    },
    {
      "id": "a59136fd-54c4-4965-94e0-ce89ef68ef97",
      "name": "Merge (+A; -A)2",
      "type": "n8n-nodes-base.merge",
      "position": [
        784,
        192
      ]
    },
    {
      "id": "d3a58b39-51cd-41ff-b0a9-e8730119f92a",
      "name": "Calculate hourly sum and",
      "type": "n8n-nodes-base.code",
      "position": [
        1600,
        192
      ]
    },
    {
      "id": "8fb7b834-6899-4c70-916d-1386fc2063db",
      "name": "Spook: update +A hitorical data1",
      "type": "n8n-nodes-base.homeAssistant",
      "position": [
        2416,
        96
      ]
    },
    {
      "id": "64031015-27d2-4ae5-9ccd-135ae36d7fcd",
      "name": "Spook: update -A hitorical data1",
      "type": "n8n-nodes-base.homeAssistant",
      "position": [
        2416,
        288
      ]
    },
    {
      "id": "49db5891-53f2-45ad-882c-ca65dc6b02ec",
      "name": "Generate 1_8_0 list for stats",
      "type": "n8n-nodes-base.set",
      "position": [
        1968,
        96
      ]
    },
    {
      "id": "4e496d23-514a-4a0e-9903-ef0ded711fc6",
      "name": "Generate 2_8_0 list for stats",
      "type": "n8n-nodes-base.set",
      "position": [
        1968,
        288
      ]
    },
    {
      "id": "ac9ea82a-3d2c-49ef-8440-82ab0aa47713",
      "name": "Generate 1_8_0 stats",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        2192,
        96
      ]
    },
    {
      "id": "81712ea5-dfc9-4988-be79-238bb31f4a28",
      "name": "Generate 2_8_0 stats",
      "type": "n8n-nodes-base.aggregate",
      "position": [
        2192,
        288
      ]
    },
    {
      "id": "c86a07b6-5ade-4ba0-a317-e75da9c7c091",
      "name": "Update input_number.exportt entity state1",
      "type": "n8n-nodes-base.homeAssistant",
      "position": [
        2832,
        288
      ]
    },
    {
      "id": "6a0092b7-172d-47a8-8fd2-1be198bf542e",
      "name": "Update input_number.import entity state1",
      "type": "n8n-nodes-base.homeAssistant",
      "position": [
        2832,
        96
      ]
    },
    {
      "id": "ae61e4e5-4441-49a0-88a3-a660483cd958",
      "name": "Gmail Trigger",
      "type": "n8n-nodes-base.gmailTrigger",
      "position": [
        -1296,
        512
      ]
    },
    {
      "id": "b16e04c4-e7cc-4ea1-9328-ef26e9741602",
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        -1520,
        320
      ]
    },
    {
      "id": "96bed2da-2176-410f-9810-01a1607b07c7",
      "name": "If attachment_0 is xlsx",
      "type": "n8n-nodes-base.if",
      "position": [
        -400,
        336
      ]
    },
    {
      "id": "7f5378b1-aa34-4ba1-9375-302c4f242e64",
      "name": "No Operation, do nothing1",
      "type": "n8n-nodes-base.noOp",
      "position": [
        -176,
        512
      ]
    },
    {
      "id": "48003569-a556-4107-a48b-a9a13862e7f2",
      "name": "Sticky Note",
      "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** "
      }
    },
    {
      "id": "b4db1439-e373-411c-aacd-cf4757af58f6",
      "name": "Sticky Note1",
      "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`"
      }
    },
    {
      "id": "2fc06f65-1720-414d-a945-3fd938f4e857",
      "name": "Email Trigger (IMAP)",
      "type": "n8n-nodes-base.emailReadImap",
      "position": [
        -624,
        672
      ]
    },
    {
      "id": "a4e84c72-f998-45f3-949b-f55f3da3730a",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1616,
        -848
      ],
      "parameters": {
        "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 "
      }
    },
    {
      "id": "de554edc-2b14-4a22-b6e3-b890962e8142",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -1184
      ],
      "parameters": {
        "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 "
      }
    },
    {
      "id": "6051e276-de9e-4629-8af3-3c278d301507",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1600,
        272
      ],
      "parameters": {
        "width": null,
        "height": 208,
        "content": "## Schedule trigger"
      }
    },
    {
      "id": "69312661-e8bb-45e4-ba65-056805504df5",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1376,
        464
      ],
      "parameters": {
        "width": null,
        "height": 208,
        "content": "## Gmail trigger"
      }
    },
    {
      "id": "07ef2161-472f-4d58-a0e3-d7cf56e40b91",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -704,
        624
      ],
      "parameters": {
        "width": null,
        "height": 208,
        "content": "## IMAP trigger"
      }
    },
    {
      "id": "41d12eca-181d-4ed3-b0df-663659815142",
      "name": "Check Email Subject",
      "type": "n8n-nodes-base.if",
      "position": [
        -1072,
        416
      ]
    },
    {
      "id": "882d8e14-710c-4869-83d1-0ac06cb9c9c6",
      "name": "Sticky Note2",
      "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"
      }
    },
    {
      "id": "47b0ac45-6718-426c-8c5d-469d948daf94",
      "name": "Convert datetime to Spook format",
      "type": "n8n-nodes-base.dateTime",
      "position": [
        1232,
        192
      ]
    },
    {
      "id": "b4f079d8-e58c-45d6-8a59-1954729d3c37",
      "name": "Convert Excel time",
      "type": "n8n-nodes-base.dateTime",
      "position": [
        1008,
        192
      ]
    },
    {
      "id": "99a605b5-0e6c-41ca-aea6-394ef8b0b028",
      "name": "Sticky Note8",
      "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 i"
      }
    },
    {
      "id": "f93df5c7-2d9a-4a0b-aebe-ebc51d0bd488",
      "name": "Sticky Note9",
      "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_incre"
      }
    },
    {
      "id": "0884b829-04ad-4643-a412-ef2a986d242a",
      "name": "Sticky Note10",
      "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"
      }
    },
    {
      "id": "d7742880-2443-45eb-8838-859c6f613643",
      "name": "Sticky Note11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -768
      ],
      "parameters": {
        "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 "
      }
    },
    {
      "id": "4a0c69b2-8e61-434e-aa6b-54e6b31975f8",
      "name": "Sticky Note12",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -288,
        -976
      ],
      "parameters": {
        "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 — "
      }
    },
    {
      "id": "7a95c640-56c2-4e8b-a1bd-923200046f94",
      "name": "Sticky Note13",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -912,
        -1184
      ],
      "parameters": {
        "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. *"
      }
    }
  ],
  "connections": {
    "Aggregate_id": {
      "main": [
        [
          {
            "node": "Get a message[0]",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Gmail Trigger": {
      "main": [
        [
          {
            "node": "Check Email Subject",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge (+A; -A)": {
      "main": [
        [
          {
            "node": "Merge (+A; -A)2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge (+A; -A)1": {
      "main": [
        [
          {
            "node": "Merge (+A; -A)2",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Merge (+A; -A)2": {
      "main": [
        [
          {
            "node": "Convert Excel time",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get a message[0]": {
      "main": [
        [
          {
            "node": "If attachment_0 is xlsx",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Schedule Trigger": {
      "main": [
        [
          {
            "node": "Get last 5 messages",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract from File": {
      "main": [
        [
          {
            "node": "Extract default data from source (+A)",
            "type": "main",
            "index": 0
          },
          {
            "node": "Extract '*_1' data from source (-A)",
            "type": "main",
            "index": 0
          },
          {
            "node": "Extract '*_2' data from source (1_8_0)",
            "type": "main",
            "index": 0
          },
          {
            "node": "Extract '*_3' data from source (2_8_0)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert Excel time": {
      "main": [
        [
          {
            "node": "Convert datetime to Spook format",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Check Email Subject": {
      "main": [
        [
          {
            "node": "Aggregate_id",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "No Operation, do nothing1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get last 5 messages": {
      "main": [
        [
          {
            "node": "Check Email Subject",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Email Trigger (IMAP)": {
      "main": [
        [
          {
            "node": "If attachment_0 is xlsx",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate 1_8_0 stats": {
      "main": [
        [
          {
            "node": "Spook: update +A hitorical data1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate 2_8_0 stats": {
      "main": [
        [
          {
            "node": "Spook: update -A hitorical data1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "If attachment_0 is xlsx": {
      "main": [
        [
          {
            "node": "Extract from File",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "No Operation, do nothing1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate hourly sum and": {
      "main": [
        [
          {
            "node": "Generate 1_8_0 list for stats",
            "type": "main",
            "index": 0
          },
          {
            "node": "Generate 2_8_0 list for stats",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Rename \"*_1\" keys for merge": {
      "main": [
        [
          {
            "node": "Merge (+A; -A)",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Rename \"*_1\" keys for merge1": {
      "main": [
        [
          {
            "node": "Merge (+A; -A)1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Rename \"*_1\" keys for merge2": {
      "main": [
        [
          {
            "node": "Merge (+A; -A)1",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Rename \"*_1\" keys for merge3": {
      "main": [
        [
          {
            "node": "Merge (+A; -A)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate 1_8_0 list for stats": {
      "main": [
        [
          {
            "node": "Generate 1_8_0 stats",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Generate 2_8_0 list for stats": {
      "main": [
        [
          {
            "node": "Generate 2_8_0 stats",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert datetime to Spook format": {
      "main": [
        [
          {
            "node": "Calculate hourly sum and",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Spook: update +A hitorical data1": {
      "main": [
        [
          {
            "node": "Update input_number.import entity state1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Spook: update -A hitorical data1": {
      "main": [
        [
          {
            "node": "Update input_number.exportt entity state1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract '*_1' data from source (-A)": {
      "main": [
        [
          {
            "node": "Rename \"*_1\" keys for merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract default data from source (+A)": {
      "main": [
        [
          {
            "node": "Rename \"*_1\" keys for merge3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract '*_2' data from source (1_8_0)": {
      "main": [
        [
          {
            "node": "Rename \"*_1\" keys for merge1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Extract '*_3' data from source (2_8_0)": {
      "main": [
        [
          {
            "node": "Rename \"*_1\" keys for merge2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}