ConnorGriffin/ynabReport-CategoryGroupSummary.gs

## ynabReport-CategoryGroupSummary.gs
// 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;
}
	// 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;
	}