Created
October 31, 2023 18:50
-
-
Save mfenniak/c512fb9b54cee321f8349080986820a3 to your computer and use it in GitHub Desktop.
n8n Integration to Copy Data from YNAB to Google Sheets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"name": "YNAB Report -> Google Sheets", | |
"nodes": [ | |
{ | |
"parameters": {}, | |
"name": "Start", | |
"type": "n8n-nodes-base.start", | |
"typeVersion": 1, | |
"position": [ | |
-520, | |
-720 | |
], | |
"id": "269d6bf8-ac50-4552-a1b2-0ba049094332" | |
}, | |
{ | |
"parameters": { | |
"values": { | |
"string": [ | |
{ | |
"name": "YNAB_BUDGET_ID", | |
"value": "" // a GUID, eg. aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa | |
} | |
] | |
}, | |
"options": {} | |
}, | |
"name": "Config", | |
"type": "n8n-nodes-base.set", | |
"typeVersion": 1, | |
"position": [ | |
-340, | |
-720 | |
], | |
"id": "cfa702ff-a9e4-43cc-a7d3-aaa7aa3979fd" | |
}, | |
{ | |
"parameters": { | |
"operation": "update", | |
"sheetId": "", // Google sheet ID from it's URL, eg. the "__this__" from https://docs.google.com/spreadsheets/d/__this__/edit | |
"range": "YNABRaw!A:K", | |
"key": "month", | |
"options": {} | |
}, | |
"name": "Google Sheets", | |
"type": "n8n-nodes-base.googleSheets", | |
"typeVersion": 1, | |
"position": [ | |
980, | |
-620 | |
], | |
"id": "9df79d91-301b-45ce-a3f6-19ea1b875167", | |
"credentials": { | |
"googleApi": { | |
"id": "4", | |
"name": "Google account" | |
} | |
} | |
}, | |
{ | |
"parameters": { | |
"authentication": "headerAuth", | |
"url": "=https://api.youneedabudget.com/v1/budgets/{{$node[\"Config\"].json[\"YNAB_BUDGET_ID\"]}}/months", | |
"options": {} | |
}, | |
"name": "Retrieve Budget Months", | |
"type": "n8n-nodes-base.httpRequest", | |
"typeVersion": 1, | |
"position": [ | |
-160, | |
-720 | |
], | |
"id": "73bedfb9-21e5-4ef2-8517-0f4f525b4ae7", | |
"credentials": { | |
"httpHeaderAuth": { | |
"id": "1", | |
"name": "YNAB API Token" | |
} | |
} | |
}, | |
{ | |
"parameters": { | |
"functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\nconst months = items[0].json.data.months;\nreturn months.slice(months.length - 6).map(x => ({ json: x }));\n\n\n" | |
}, | |
"name": "Extract Last Few Months", | |
"type": "n8n-nodes-base.function", | |
"typeVersion": 1, | |
"position": [ | |
20, | |
-720 | |
], | |
"id": "61255e50-c030-4ae4-9458-2f34571c7c6e" | |
}, | |
{ | |
"parameters": { | |
"authentication": "headerAuth", | |
"url": "=https://api.youneedabudget.com/v1/budgets/{{$item(0).$node[\"Config\"].json[\"YNAB_BUDGET_ID\"]}}/months/{{$json[\"month\"]}}", | |
"options": {}, | |
"headerParametersUi": { | |
"parameter": [] | |
} | |
}, | |
"name": "Retrieve Month Details", | |
"type": "n8n-nodes-base.httpRequest", | |
"typeVersion": 1, | |
"position": [ | |
200, | |
-720 | |
], | |
"id": "89bafcb3-b2fc-4d6c-bbae-9c9eaf06a246", | |
"credentials": { | |
"httpHeaderAuth": { | |
"id": "1", | |
"name": "YNAB API Token" | |
} | |
} | |
}, | |
{ | |
"parameters": { | |
"triggerTimes": { | |
"item": [ | |
{ | |
"hour": 4, | |
"minute": 48 | |
} | |
] | |
} | |
}, | |
"name": "Cron", | |
"type": "n8n-nodes-base.cron", | |
"typeVersion": 1, | |
"position": [ | |
-520, | |
-540 | |
], | |
"id": "af92f0cf-55ad-4ec0-ab08-de7851e346d2" | |
}, | |
{ | |
"parameters": { | |
"functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\nfunction category(month, name) {\n return month.categories.filter(c => c.name === name)[0];\n}\n\nreturn items.map(x => {\n const month = x.json.data.month;\n const ccCategory = \"444c02c7-0a7e-46e8-943b-5ddc6ed4e49f\"; // category_group_id for credit cards\n const otherWeird = \"a8a34377-08f1-450e-89d2-919b0f16305f\"; // category_group_id for inflow + uncategorized\n const true_spending = month.categories\n .filter(c => c.category_group_id != ccCategory && c.category_group_id != otherWeird)\n .reduce((c1, c2) => ({activity: (c1.activity + c2.activity)}), {activity:0}).activity;\n return {\n json: {\n retirement: category(month, \"Retirement Savings\").activity,\n political: category(month, \"Political Donations\").activity, \n giving: category(month, \"Giving\").activity,\n true_spending,\n ...month\n }\n };\n});\n\n" | |
}, | |
"name": "Process Monthly Data", | |
"type": "n8n-nodes-base.function", | |
"typeVersion": 1, | |
"position": [ | |
380, | |
-720 | |
], | |
"id": "9745c2f5-6a8f-42ff-9bcb-0bc73f048975" | |
}, | |
{ | |
"parameters": { | |
"authentication": "headerAuth", | |
// Insert an account ID below, in a GUID | |
"url": "=https://api.youneedabudget.com/v1/budgets/{{$node[\"Config\"].json[\"YNAB_BUDGET_ID\"]}}/accounts/...account-id.../transactions", | |
"options": {} | |
}, | |
"name": "Retrieve GWL Amanda", | |
"type": "n8n-nodes-base.httpRequest", | |
"typeVersion": 1, | |
"position": [ | |
-160, | |
-520 | |
], | |
"id": "6f10972c-956d-46a3-9745-6d87a0f65a4d", | |
"credentials": { | |
"httpHeaderAuth": { | |
"id": "1", | |
"name": "YNAB API Token" | |
} | |
} | |
}, | |
{ | |
"parameters": { | |
"authentication": "headerAuth", | |
// Insert an account ID below, in a GUID | |
"url": "=https://api.youneedabudget.com/v1/budgets/{{$node[\"Config\"].json[\"YNAB_BUDGET_ID\"]}}/accounts/...account-id.../transactions", | |
"options": {} | |
}, | |
"name": "Retrieve GWL Mathieu", | |
"type": "n8n-nodes-base.httpRequest", | |
"typeVersion": 1, | |
"position": [ | |
-160, | |
-340 | |
], | |
"id": "d5973a73-a5b6-4792-8ea8-7c6cc587714a", | |
"credentials": { | |
"httpHeaderAuth": { | |
"id": "1", | |
"name": "YNAB API Token" | |
} | |
} | |
}, | |
{ | |
"parameters": {}, | |
"name": "Merge GWL Account Data", | |
"type": "n8n-nodes-base.merge", | |
"typeVersion": 1, | |
"position": [ | |
20, | |
-440 | |
], | |
"id": "5c50c9f9-2767-4c8f-9592-a5bf71b4816e" | |
}, | |
{ | |
"parameters": {}, | |
"name": "Merge Target Months", | |
"type": "n8n-nodes-base.merge", | |
"typeVersion": 1, | |
"position": [ | |
200, | |
-520 | |
], | |
"id": "190cfb85-7281-45e2-8f6f-c97a85c4e6fe" | |
}, | |
{ | |
"parameters": { | |
"functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\n// Loop over inputs and add a new field called 'myNewField' to the JSON of each one\nconst retval = [];\n\nlet amanda = null;\nlet mathieu = null;\n\nfunction calculate_contributions(month, transactions) {\n let contrib = 0;\n for (const t of transactions) {\n // console.log(t.date, month, t.date.startsWith(month), t.payee_id);\n if (t.date.startsWith(month) && t.payee_id != \"3effd06c-ddaa-4fe5-9083-dc635ac0b058\") { // 3f44 - Investment Value Change\n contrib += t.amount;\n }\n }\n return contrib;\n}\n\nfor (item of items) {\n if (item.json.data && item.json.data.transactions[0].account_id == \"f8abe34f-495b-43a4-b358-85c160871058\") {\n amanda = item.json.data.transactions;\n }\n if (item.json.data && item.json.data.transactions[0].account_id == \"b476f673-38e5-4df5-89ec-1a67ee2a1f26\") {\n mathieu = item.json.data.transactions;\n }\n}\n\nfor (item of items) {\n if (item.json.month) {\n retval.push({\n json: {\n month: item.json.month,\n amanda_eerrsp: calculate_contributions(item.json.month.substr(0,7), amanda),\n mathieu_eerrsp: calculate_contributions(item.json.month.substr(0,7), mathieu),\n }\n });\n }\n // item.json.myNewField = 1;\n}\n\n// You can write logs to the browser console\nconsole.log('Done!');\n\nreturn retval;\n" | |
}, | |
"name": "Compute Contributions Per Month", | |
"type": "n8n-nodes-base.function", | |
"typeVersion": 1, | |
"position": [ | |
380, | |
-520 | |
], | |
"id": "316176ad-39ba-4f2e-90bb-eb1a7c516ba9" | |
}, | |
{ | |
"parameters": { | |
"authentication": "headerAuth", | |
// Insert an account ID below, in a GUID | |
"url": "=https://api.youneedabudget.com/v1/budgets/{{$node[\"Config\"].json[\"YNAB_BUDGET_ID\"]}}/accounts/...account-id.../transactions", | |
"options": {} | |
}, | |
"name": "Retrieve RBC Savings", | |
"type": "n8n-nodes-base.httpRequest", | |
"typeVersion": 1, | |
"position": [ | |
-160, | |
-140 | |
], | |
"id": "0b47af8c-3093-4936-b998-501f0cc08f1b", | |
"credentials": { | |
"httpHeaderAuth": { | |
"id": "1", | |
"name": "YNAB API Token" | |
} | |
} | |
}, | |
{ | |
"parameters": { | |
"functionCode": "const retval = [];\n\nconst monthMap = {};\n\nconst transactions = items[0].json.data.transactions;\nfor (const tx of transactions) {\n if (tx.category_name == \"Mortgage\" && tx.flag_color === 'purple') {\n // console.log(tx);\n const month = tx.date.substring(0, 7) + \"-01\";\n let monthData = monthMap[month];\n if (!monthData) {\n monthData = { month, mortgage_overpayment: 0 };\n monthMap[month] = monthData;\n }\n monthData.mortgage_overpayment += tx.amount;\n }\n}\n\nfor (const key of Object.keys(monthMap)) {\n retval.push(monthMap[key]);\n}\n\nreturn retval;\n" | |
}, | |
"name": "Compute Mortgage Overpay per Month", | |
"type": "n8n-nodes-base.function", | |
"typeVersion": 1, | |
"position": [ | |
140, | |
-140 | |
], | |
"id": "ee1895c2-5fb4-4aad-90ea-543e7ed161fe" | |
}, | |
{ | |
"parameters": { | |
"mode": "mergeByKey", | |
"propertyName1": "month", | |
"propertyName2": "month" | |
}, | |
"name": "Merge Month #2", | |
"type": "n8n-nodes-base.merge", | |
"typeVersion": 1, | |
"position": [ | |
780, | |
-620 | |
], | |
"id": "01a7bb7b-f6ab-48f4-923f-e4f5bb28425c" | |
}, | |
{ | |
"parameters": { | |
"mode": "combine", | |
"mergeByFields": { | |
"values": [ | |
{ | |
"field1": "month", | |
"field2": "month" | |
} | |
] | |
}, | |
"options": {} | |
}, | |
"id": "c97621a4-1894-495a-bd30-ca26ad7f3a43", | |
"name": "Merge Month #1", | |
"type": "n8n-nodes-base.merge", | |
"typeVersion": 2, | |
"position": [ | |
600, | |
-360 | |
] | |
} | |
], | |
"pinData": {}, | |
"connections": { | |
"Start": { | |
"main": [ | |
[ | |
{ | |
"node": "Config", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Config": { | |
"main": [ | |
[ | |
{ | |
"node": "Retrieve Budget Months", | |
"type": "main", | |
"index": 0 | |
}, | |
{ | |
"node": "Retrieve GWL Amanda", | |
"type": "main", | |
"index": 0 | |
}, | |
{ | |
"node": "Retrieve GWL Mathieu", | |
"type": "main", | |
"index": 0 | |
}, | |
{ | |
"node": "Retrieve RBC Savings", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Retrieve Budget Months": { | |
"main": [ | |
[ | |
{ | |
"node": "Extract Last Few Months", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Extract Last Few Months": { | |
"main": [ | |
[ | |
{ | |
"node": "Retrieve Month Details", | |
"type": "main", | |
"index": 0 | |
}, | |
{ | |
"node": "Merge Target Months", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Retrieve Month Details": { | |
"main": [ | |
[ | |
{ | |
"node": "Process Monthly Data", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Cron": { | |
"main": [ | |
[ | |
{ | |
"node": "Config", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Process Monthly Data": { | |
"main": [ | |
[ | |
{ | |
"node": "Merge Month #2", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Retrieve GWL Mathieu": { | |
"main": [ | |
[ | |
{ | |
"node": "Merge GWL Account Data", | |
"type": "main", | |
"index": 1 | |
} | |
] | |
] | |
}, | |
"Retrieve GWL Amanda": { | |
"main": [ | |
[ | |
{ | |
"node": "Merge GWL Account Data", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Merge GWL Account Data": { | |
"main": [ | |
[ | |
{ | |
"node": "Merge Target Months", | |
"type": "main", | |
"index": 1 | |
} | |
] | |
] | |
}, | |
"Merge Target Months": { | |
"main": [ | |
[ | |
{ | |
"node": "Compute Contributions Per Month", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Compute Contributions Per Month": { | |
"main": [ | |
[ | |
{ | |
"node": "Merge Month #1", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Retrieve RBC Savings": { | |
"main": [ | |
[ | |
{ | |
"node": "Compute Mortgage Overpay per Month", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Compute Mortgage Overpay per Month": { | |
"main": [ | |
[ | |
{ | |
"node": "Merge Month #1", | |
"type": "main", | |
"index": 1 | |
} | |
] | |
] | |
}, | |
"Merge Month #2": { | |
"main": [ | |
[ | |
{ | |
"node": "Google Sheets", | |
"type": "main", | |
"index": 0 | |
} | |
] | |
] | |
}, | |
"Merge Month #1": { | |
"main": [ | |
[ | |
{ | |
"node": "Merge Month #2", | |
"type": "main", | |
"index": 1 | |
} | |
] | |
] | |
} | |
}, | |
"active": true, | |
"settings": { | |
"executionTimeout": 300, | |
"errorWorkflow": "3" | |
}, | |
"id": 1, | |
"meta": { | |
"instanceId": "c2c8d3da4015b1942f76d34e3b9ee1cb96d02bd051c4381e738154d705957880" | |
}, | |
"tags": [] | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment