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