Describe the problem/error/question
What is the error message (if any)?
Please share your workflow
(Select the nodes on your canvas and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow.)
{
"nodes": [
{
"parameters": {
"rule": {
"interval": [
{}
]
}
},
"type": "n8n-nodes-base.scheduleTrigger",
"typeVersion": 1.2,
"position": [
-480,
-80
],
"id": "1f480f34-a66a-42c6-ab9c-9ef4fc986d16",
"name": "Schedule Trigger"
},
{
"parameters": {
"operation": "create",
"documentId": {
"__rl": true,
"value": "1VAq3IKWZoWhFfpl16uyvQaIewAAws3xyxPKuFx5p0nI",
"mode": "list",
"cachedResultName": "test",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1VAq3IKWZoWhFfpl16uyvQaIewAAws3xyxPKuFx5p0nI/edit?usp=drivesdk"
},
"title": "={{ $json.Month }}",
"options": {}
},
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.5,
"position": [
-200,
-260
],
"id": "3e8685a6-a511-46a9-bc69-0ad5539bb086",
"name": "Create Monthly Sheet",
"credentials": {
"googleSheetsOAuth2Api": {
"id": "oX9zt9upbc4IKFyb",
"name": "Google Sheets account"
}
}
},
{
"parameters": {
"operation": "appendOrUpdate",
"documentId": {
"__rl": true,
"value": "1VAq3IKWZoWhFfpl16uyvQaIewAAws3xyxPKuFx5p0nI",
"mode": "list",
"cachedResultName": "test",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1VAq3IKWZoWhFfpl16uyvQaIewAAws3xyxPKuFx5p0nI/edit?usp=drivesdk"
},
"sheetName": {
"__rl": true,
"value": "={{ $('Schedule Trigger').item.json.Month }}",
"mode": "name"
},
"columns": {
"mappingMode": "autoMapInputData",
"value": {
"Datum": "={{ $('Schedule Trigger').item.json.Month }}",
"Strom OG": "={{ $json['Verbrauch OG'] }}",
"Stom Haus": "={{ $json['Verbrauch gesamt'] }}",
"Stom Heizung": "={{ $json['verbrauch heizung'] }}"
},
"matchingColumns": [
"Datum"
],
"schema": [
{
"id": "Datum",
"displayName": "Datum",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "Stom Haus",
"displayName": "Stom Haus",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "Stom Heizung",
"displayName": "Stom Heizung",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "Strom OG",
"displayName": "Strom OG",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "Strom EG",
"displayName": "Strom EG",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
},
{
"id": "Verbrauch OG",
"displayName": "Verbrauch OG",
"required": false,
"defaultMatch": false,
"display": true,
"type": "string",
"canBeUsedToMatch": true,
"removed": false
}
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {}
},
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 4.5,
"position": [
600,
-240
],
"id": "1528effb-e5fe-46d4-88c3-240398c946ac",
"name": "Google Sheets",
"executeOnce": true,
"credentials": {
"googleSheetsOAuth2Api": {
"id": "oX9zt9upbc4IKFyb",
"name": "Google Sheets account"
}
}
},
{
"parameters": {
"resource": "state",
"entityId": "sensor.monatlicher_verbrauch_og"
},
"type": "n8n-nodes-base.homeAssistant",
"typeVersion": 1,
"position": [
-60,
-180
],
"id": "afe6c31e-5f9f-4db0-98bc-58bd94b76cd8",
"name": "Verbrauch OG",
"credentials": {
"homeAssistantApi": {
"id": "KHEymzAGU4AnJ8z1",
"name": "Home Assistant account"
}
}
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "9e4ecc3f-f02c-499a-ba48-0eb4c3371a2e",
"name": "Verbrauch OG",
"value": "={{ $json.state }}",
"type": "number"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
80,
-180
],
"id": "a7ecdece-575a-47b8-90e1-081e643db50a",
"name": "Verbrauch OG1"
},
{
"parameters": {
"resource": "state",
"entityId": "sensor.monatlicher_verbrauch_gesamt"
},
"type": "n8n-nodes-base.homeAssistant",
"typeVersion": 1,
"position": [
-140,
-40
],
"id": "5b7c5a93-1c4f-4a77-b40f-3eb816493a3e",
"name": "Verbrauch gesamt",
"credentials": {
"homeAssistantApi": {
"id": "KHEymzAGU4AnJ8z1",
"name": "Home Assistant account"
}
}
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "bfc58f28-4634-48c7-83fd-800a2971bcc7",
"name": "Verbrauch gesamt",
"value": "={{ $json.state }}",
"type": "number"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
60,
-20
],
"id": "dfeb7024-d5de-4f19-ab4c-3f33383aa495",
"name": "Verbrauch gesamt1"
},
{
"parameters": {
"resource": "state",
"entityId": "sensor.monatlicher_verbrauch_heizung"
},
"type": "n8n-nodes-base.homeAssistant",
"typeVersion": 1,
"position": [
-200,
180
],
"id": "3e593f97-4ca4-47ec-8a62-48dc03bfd462",
"name": "Verbrauch Heizung",
"credentials": {
"homeAssistantApi": {
"id": "KHEymzAGU4AnJ8z1",
"name": "Home Assistant account"
}
}
},
{
"parameters": {
"assignments": {
"assignments": [
{
"id": "8e021841-42b5-41b4-8d55-000ccc132e20",
"name": "verbrauch heizung",
"value": "={{ $json.state }}",
"type": "number"
}
]
},
"options": {}
},
"type": "n8n-nodes-base.set",
"typeVersion": 3.4,
"position": [
100,
200
],
"id": "a98ba451-e384-4521-87b8-ba816f14e340",
"name": "Verbrauch Heizung1"
},
{
"parameters": {
"numberInputs": 3
},
"type": "n8n-nodes-base.merge",
"typeVersion": 3.1,
"position": [
260,
-160
],
"id": "97165a93-4a4e-4d12-aadb-7464b760207a",
"name": "Merge",
"alwaysOutputData": false
}
],
"connections": {
"Schedule Trigger": {
"main": [
[
{
"node": "Create Monthly Sheet",
"type": "main",
"index": 0
},
{
"node": "Verbrauch OG",
"type": "main",
"index": 0
},
{
"node": "Verbrauch gesamt",
"type": "main",
"index": 0
},
{
"node": "Verbrauch Heizung",
"type": "main",
"index": 0
}
]
]
},
"Create Monthly Sheet": {
"main": [
[]
]
},
"Google Sheets": {
"main": [
[]
]
},
"Verbrauch OG": {
"main": [
[
{
"node": "Verbrauch OG1",
"type": "main",
"index": 0
}
]
]
},
"Verbrauch OG1": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"Verbrauch gesamt": {
"main": [
[
{
"node": "Verbrauch gesamt1",
"type": "main",
"index": 0
}
]
]
},
"Verbrauch gesamt1": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 1
}
]
]
},
"Verbrauch Heizung": {
"main": [
[
{
"node": "Verbrauch Heizung1",
"type": "main",
"index": 0
}
]
]
},
"Verbrauch Heizung1": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 2
}
]
]
},
"Merge": {
"main": [
[
{
"node": "Google Sheets",
"type": "main",
"index": 0
}
]
]
}
},
"pinData": {},
"meta": {
"instanceId": "f5e46ac6eb7ecbca61f47bdda03955c26ccf5e1ffc9df328b5aeabc13143092d"
}
}
Share the output returned by the last node
Problem in node ‘Google Sheets‘
Invalid expression
Information on your n8n setup
- 1.86.1
- n8n EXECUTIONS_PROCESS setting (default: own, main):
- docker
- DEBIAN
What is the correct approach for my project?
These values are read by Home Assistant and should be written daily to a Google Sheet. That is, total, first floor, and heating. Additionally, a value should be written to the sheet that looks like this: Ground floor = total-first floor-heating.
I’ve now implemented a merge link because otherwise the same values are always written, but now I can’t select the values from the merge link.
