Instantly share code, notes, and snippets.

Embed
What would you like to do?
YNAB Weekly Spending Report - Google Script (Standalone) - Using Category Groups instead of Categories
// 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
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
var amount = 0
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": category.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