Skip to content

Instantly share code, notes, and snippets.

@mfenniak

mfenniak/YNAB_Report____Google_Sheets.json

Created October 31, 2023 18:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mfenniak/c512fb9b54cee321f8349080986820a3 to your computer and use it in GitHub Desktop.
Save mfenniak/c512fb9b54cee321f8349080986820a3 to your computer and use it in GitHub Desktop.
Download ZIP
n8n Integration to Copy Data from YNAB to Google Sheets
Raw
YNAB_Report____Google_Sheets.json
{
"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