/ynabReport-CategoryGroupSummary.gs
Last active Sep 25, 2018
YNAB Weekly Spending Report - Google Script (Standalone) - Using Totals from Category Groups
| // YNAB Weekly Spending Report sending script | |
| function sendYnabReport() { | |
| // Set our options | |
| var accessToken = 'token goes here' // Your YNAB Personal Access Token | |
| var budgetName = "Connor's Budget" // Budget name to use, in case you have multiple budgets | |
| var categories = ['Sports & Wellness', 'Food & Drink', 'Housing'] // Your desired category groups to monitor, in Javascript array syntax | |
| var recipient = 'test@example.com, test2@example.com' // Email recipients, comma separated | |
| // API stuff | |
| var url = 'https://api.youneedabudget.com/v1' | |
| var headers = { | |
| "Authorization": "Bearer " + accessToken, | |
| "Content-Type":"application/json; charset=utf-8" | |
| } | |
| // Get 7 days ago in YNAB's date format | |
| var date = new Date() | |
| date.setDate(date.getDate()-7) | |
| var todayDate = Utilities.formatDate(new Date(), "UTC", "yyyy-MM-dd") | |
| var sinceDate = Utilities.formatDate(date, "UTC", "yyyy-MM-dd") | |
| // Get all budgets, find our budget by name | |
| var budgets = JSON.parse(UrlFetchApp.fetch(url + '/budgets', {'headers': headers})).data.budgets | |
| var budget = findObjectByKey(budgets, 'name', budgetName) | |
| // Get all categories in the budget | |
| var categoryUrl = url + '/budgets/' + budget.id + '/categories' | |
| var categoryGroups = JSON.parse(UrlFetchApp.fetch(categoryUrl, {'headers': headers})).data.category_groups | |
| var spendingSummary = [] | |
| var spendingTotal = 0 | |
| // Iterate through each category group, check if it's in the categoryGroups list | |
| categoryGroups.forEach(function (categoryGroup) { | |
| if (categories.indexOf(categoryGroup.name) != -1) { | |
| // Iterate over each category in the group, built the report details | |
| var amount = 0 | |
| categoryGroup.categories.forEach(function (category) { | |
| // Get the transactions for this category | |
| var transactionUrl = url + '/budgets/' + budget.id + '/categories/' + category.id + '/transactions' + '?since_date=' + sinceDate | |
| var transactions = JSON.parse(UrlFetchApp.fetch(transactionUrl, {'headers': headers})).data.transactions | |
| // Sum the amounts | |
| transactions.forEach(function (transaction) { | |
| amount += transaction.amount | |
| }) | |
| }) | |
| // Add the details to an object that we will later convert to an HTML table | |
| spendingTotal += amount | |
| spendingSummary.push({ | |
| "Category": categoryGroup.name, | |
| "Spending": (-amount / 1000).toFixed(2) | |
| }) | |
| } | |
| }) | |
| // Add the total line | |
| spendingSummary.push({ | |
| "Category": "<strong>Total</strong>", | |
| "Spending": "<strong>" + (-spendingTotal / 1000).toFixed(2) + "</strong>" | |
| }) | |
| /* - Set the base HTML for the email, minified because JS sucks for this kind of thing | |
| - Convert the spending summary to an HTML table with ID of 'spending' (referenced in the CSS) | |
| - Insert the sinceDate and table into the HTML */ | |
| var html = '<html><head><style>body,p,span{font-size:11pt;font-family:Calibri,Arial,sans-serif}#spending{font-family:Arial,Helvetica,sans-serif;border-collapse:collapse;width:100%}#spending td,#spending th{border:1px solid #ddd;padding:8px}#spending th{padding-top:12px;padding-bottom:12px;text-align:left;background-color:#e5f5f9;color:#000}</style></head><body><h1>Spending since $date</h1>$table</body></html>' | |
| var spendingTable = ConvertJsonToTable(spendingSummary, "spending") | |
| var htmlBody = html.replace("$table", spendingTable).replace("$date", sinceDate) | |
| // Send the email | |
| var subject = "YNAB Weekly Spending Report - " + todayDate | |
| MailApp.sendEmail({ | |
| "to": recipient, | |
| "subject": subject, | |
| "name": "YNAB Reports", | |
| "htmlBody": htmlBody | |
| }) | |
| // Wait for the email to be received, mark it as unread and move it to the inbox | |
| while (!thread) { | |
| var thread = GmailApp.search(subject)[0] | |
| if (!thread) { | |
| Utilities.sleep(5000) | |
| } else { | |
| thread.moveToInbox() | |
| thread.markUnread() | |
| } | |
| } | |
| } | |
| /** | |
| * JavaScript format string function | |
| * | |
| */ | |
| String.prototype.format = function() | |
| { | |
| var args = arguments; | |
| return this.replace(/{(\d+)}/g, function(match, number) | |
| { | |
| return typeof args[number] != 'undefined' ? args[number] : | |
| '{' + number + '}'; | |
| }); | |
| }; | |
| /** | |
| * Convert a Javascript Oject array or String array to an HTML table | |
| * JSON parsing has to be made before function call | |
| * It allows use of other JSON parsing methods like jQuery.parseJSON | |
| * http(s)://, ftp://, file:// and javascript:; links are automatically computed | |
| * | |
| * JSON data samples that should be parsed and then can be converted to an HTML table | |
| * var objectArray = '[{"Total":"34","Version":"1.0.4","Office":"New York"},{"Total":"67","Version":"1.1.0","Office":"Paris"}]'; | |
| * var stringArray = '["New York","Berlin","Paris","Marrakech","Moscow"]'; | |
| * var nestedTable = '[{ key1: "val1", key2: "val2", key3: { tableId: "tblIdNested1", tableClassName: "clsNested", linkText: "Download", data: [{ subkey1: "subval1", subkey2: "subval2", subkey3: "subval3" }] } }]'; | |
| * | |
| * Code sample to create a HTML table Javascript String | |
| * var jsonHtmlTable = ConvertJsonToTable(eval(dataString), 'jsonTable', null, 'Download'); | |
| * | |
| * Code sample explaned | |
| * - eval is used to parse a JSON dataString | |
| * - table HTML id attribute will be 'jsonTable' | |
| * - table HTML class attribute will not be added | |
| * - 'Download' text will be displayed instead of the link itself | |
| * | |
| * @author Afshin Mehrabani <afshin dot meh at gmail dot com> | |
| * | |
| * @class ConvertJsonToTable | |
| * | |
| * @method ConvertJsonToTable | |
| * | |
| * @param parsedJson object Parsed JSON data | |
| * @param tableId string Optional table id | |
| * @param tableClassName string Optional table css class name | |
| * @param linkText string Optional text replacement for link pattern | |
| * | |
| * @return string Converted JSON to HTML table | |
| */ | |
| function ConvertJsonToTable(parsedJson, tableId, tableClassName, linkText) | |
| { | |
| //Patterns for links and NULL value | |
| var italic = '<i>{0}</i>'; | |
| var link = linkText ? '<a href="{0}">' + linkText + '</a>' : | |
| '<a href="{0}">{0}</a>'; | |
| //Pattern for table | |
| var idMarkup = tableId ? ' id="' + tableId + '"' : | |
| ''; | |
| var classMarkup = tableClassName ? ' class="' + tableClassName + '"' : | |
| ''; | |
| var tbl = '<table border="1" cellpadding="1" cellspacing="1"' + idMarkup + classMarkup + '>{0}{1}</table>'; | |
| //Patterns for table content | |
| var th = '<thead>{0}</thead>'; | |
| var tb = '<tbody>{0}</tbody>'; | |
| var tr = '<tr>{0}</tr>'; | |
| var thRow = '<th>{0}</th>'; | |
| var tdRow = '<td>{0}</td>'; | |
| var thCon = ''; | |
| var tbCon = ''; | |
| var trCon = ''; | |
| if (parsedJson) | |
| { | |
| var isStringArray = typeof(parsedJson[0]) == 'string'; | |
| var headers; | |
| // Create table headers from JSON data | |
| // If JSON data is a simple string array we create a single table header | |
| if(isStringArray) | |
| thCon += thRow.format('value'); | |
| else | |
| { | |
| // If JSON data is an object array, headers are automatically computed | |
| if(typeof(parsedJson[0]) == 'object') | |
| { | |
| headers = array_keys(parsedJson[0]); | |
| for (var i = 0; i < headers.length; i++) | |
| thCon += thRow.format(headers[i]); | |
| } | |
| } | |
| th = th.format(tr.format(thCon)); | |
| // Create table rows from Json data | |
| if(isStringArray) | |
| { | |
| for (var i = 0; i < parsedJson.length; i++) | |
| { | |
| tbCon += tdRow.format(parsedJson[i]); | |
| trCon += tr.format(tbCon); | |
| tbCon = ''; | |
| } | |
| } | |
| else | |
| { | |
| if(headers) | |
| { | |
| var urlRegExp = new RegExp(/(\b(https?|ftp|file):\/\/[-A-Z0-9+&@#\/%?=~_|!:,.;]*[-A-Z0-9+&@#\/%=~_|])/ig); | |
| var javascriptRegExp = new RegExp(/(^javascript:[\s\S]*;$)/ig); | |
| for (var i = 0; i < parsedJson.length; i++) | |
| { | |
| for (var j = 0; j < headers.length; j++) | |
| { | |
| var value = parsedJson[i][headers[j]]; | |
| var isUrl = urlRegExp.test(value) || javascriptRegExp.test(value); | |
| if(isUrl) // If value is URL we auto-create a link | |
| tbCon += tdRow.format(link.format(value)); | |
| else | |
| { | |
| if(value){ | |
| if(typeof(value) == 'object'){ | |
| //for supporting nested tables | |
| tbCon += tdRow.format(ConvertJsonToTable(eval(value.data), value.tableId, value.tableClassName, value.linkText)); | |
| } else { | |
| tbCon += tdRow.format(value); | |
| } | |
| } else { // If value == null we format it like PhpMyAdmin NULL values | |
| tbCon += tdRow.format(italic.format(value).toUpperCase()); | |
| } | |
| } | |
| } | |
| trCon += tr.format(tbCon); | |
| tbCon = ''; | |
| } | |
| } | |
| } | |
| tb = tb.format(trCon); | |
| tbl = tbl.format(th, tb); | |
| return tbl; | |
| } | |
| return null; | |
| } | |
| /** | |
| * Return just the keys from the input array, optionally only for the specified search_value | |
| * version: 1109.2015 | |
| * discuss at: http://phpjs.org/functions/array_keys | |
| * + original by: Kevin van Zonneveld (http://kevin.vanzonneveld.net) | |
| * + input by: Brett Zamir (http://brett-zamir.me) | |
| * + bugfixed by: Kevin van Zonneveld (http://kevin.vanzonneveld.net) | |
| * + improved by: jd | |
| * + improved by: Brett Zamir (http://brett-zamir.me) | |
| * + input by: P | |
| * + bugfixed by: Brett Zamir (http://brett-zamir.me) | |
| * * example 1: array_keys( {firstname: 'Kevin', surname: 'van Zonneveld'} ); | |
| * * returns 1: {0: 'firstname', 1: 'surname'} | |
| */ | |
| function array_keys(input, search_value, argStrict) | |
| { | |
| var search = typeof search_value !== 'undefined', tmp_arr = [], strict = !!argStrict, include = true, key = ''; | |
| if (input && typeof input === 'object' && input.change_key_case) { // Duck-type check for our own array()-created PHPJS_Array | |
| return input.keys(search_value, argStrict); | |
| } | |
| for (key in input) | |
| { | |
| if (input.hasOwnProperty(key)) | |
| { | |
| include = true; | |
| if (search) | |
| { | |
| if (strict && input[key] !== search_value) | |
| include = false; | |
| else if (input[key] != search_value) | |
| include = false; | |
| } | |
| if (include) | |
| tmp_arr[tmp_arr.length] = key; | |
| } | |
| } | |
| return tmp_arr; | |
| } | |
| function findObjectByKey(array, key, value) { | |
| for (var i = 0; i < array.length; i++) { | |
| if (array[i][key] === value) { | |
| return array[i]; | |
| } | |
| } | |
| return null; | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment