Skip to content

Instantly share code, notes, and snippets.

@willjobs
Forked from notself/ynab-google-sheets.js
Last active May 2, 2024 17:52
Show Gist options
  • Save willjobs/8cefaa0b5f901b2a8e208a8ef023c2c8 to your computer and use it in GitHub Desktop.
Save willjobs/8cefaa0b5f901b2a8e208a8ef023c2c8 to your computer and use it in GitHub Desktop.
Revisions to the original gist to add columns, show split subtransactions on multiple rows by default, fix time discrepancies, and add an indicator of the last data pull
function YNABAccounts(accessToken, budgetId) {
const accounts = _getBudgetAccounts(accessToken, budgetId);
if(accounts == null) {
return null;
}
const columns = ["Name", "Type", "Budget", "Closed", "Balance"];
const rows = accounts.map(function (acc) {
return [
acc.name,
acc.type,
acc.on_budget,
acc.closed,
acc.balance / 1000.0
];
})
.sort(function(left, right){
// Sort by Account type
const result = left[1].localeCompare(right[1]);
if(result != 0) {
return result;
}
// Then Account name
return left[0].localeCompare(right[0]);
});
return [columns].concat(rows);
}
function YNABTransactions(accessToken, budgetId, replaceSubTransactions) {
const budget = _getBudget(accessToken, budgetId, true);
if(budget == null) {
return null;
}
const getType = function(budget, tx) {
if(tx.transfer_account_id) {
return 'Transfer';
}
if(tx.amount < 0) {
return 'Outflow';
}else if(tx.amount > 0 && budget.getPayeeName(tx.payee_id) != 'Starting Balance'){
return 'Inflow';
}
return null;
}
const transactions = replaceSubTransactions ? budget.transactions : _replaceSubTransactions(budget.transactions, budget.subtransactions);
const cur_date = new Date();
const cur_date_str = Utilities.formatDate(cur_date, "America/New_York", "yyyy-MM-dd HH:mm:ss");
const columns = ["Date (Updated " + cur_date_str + ")", "Month", "Account", "Payee", "Category Group", "Category", "Memo", "Amount", 'Type'];
return [columns].concat(transactions.map(function (tx) {
d = new Date(tx.date);
d.setDate(d.getDate()+1);
return [
Utilities.formatDate(d, "America/New_York","MM/dd/yyyy"),
Utilities.formatDate(d, "America/New_York", "yyyy/MM"),
budget.getAccountName(tx.account_id),
tx.transfer_account_id ? budget.getAccountName(tx.transfer_account_id) : budget.getPayeeName(tx.payee_id),
budget.getCategoryGroupNameFromCategory(tx.category_id),
budget.getCategoryName(tx.category_id),
tx.memo,
tx.amount / 1000.0,
getType(budget, tx)
];
}));
}
function _replaceSubTransactions(transactions, subTransactions) {
// Create an index of the transactions by ID
const transactionsById = _indexArray(transactions || []);
// Group the sub transactions by parent transaction ID
const subTransactionsById = _groupBy(subTransactions || [], 'transaction_id');
// For each parent transaction ID
Object.keys(subTransactionsById).forEach(function(txId){
// Get the parent transaction
const tx = transactionsById[txId];
if(tx) {
// Delete it
delete transactionsById[txId];
// Add the sub transactions
subTransactionsById[txId].forEach(function(subTransaction){
// Sub transactions share the parent's date, account and payee
subTransaction.date = tx.date;
subTransaction.account_id = tx.account_id;
subTransaction.payee_id = tx.payee_id;
transactionsById[subTransaction.id] = subTransaction;
});
}
});
return Object.keys(transactionsById).map(function(id){
return transactionsById[id];
})
.sort(function(left, right) {
return new Date(right.date).getTime() - new Date(left.date).getTime();
});
}
/**
* Retrieves the accounts of a budget given its ID
*
* @param accessToken {string} The user's YNAB API token
* @param budgetId {string} The budget ID
* @returns The list of accounts
*/
function _getBudgetAccounts(accessToken, budgetId) {
const response = _ynabApi(accessToken, "/v1/budgets/%s/accounts", budgetId);
return response && response.data ? response.data.accounts : null;
}
/**
* Retrieves a budget by ID
*
* @param accessToken {string} The user's YNAB API token
* @param budgetId {string} The budget ID
* @returns The YNAB budget
*
* Note: From my observations, /v1/budgets/:budgetId doesn't return all the category groups, even though it should
* . To overcome this, we get the full category hierarchy with an extra API call to /v1/budgets/:budgetId/categories
*/
function _getBudget(accessToken, budgetId) {
// Get the (full?) budget
const response = _ynabApi(accessToken, "/v1/budgets/%s", budgetId);
const budget = response && response.data ? response.data.budget : null;
if(budget == null) {
return null;
}
const categoriesResponse = _ynabApi(accessToken, "/v1/budgets/%s/categories", budgetId);
const categoryGroups = categoriesResponse && categoriesResponse.data ?
categoriesResponse.data.category_groups : {};
_indexBudget(budget, categoryGroups);
return budget;
}
/**
* Calls the YNAB API
*
* @param accessToken {string} The user's YNAB API token
* @param pathFormat {string} A format string for the path part of the YNAB API URL
* @param ... Arguments for the path format string
* @returns {string} The response from YNAB
*/
function _ynabApi(accessToken, pathFormat) {
const options = {
"headers": {
"Authorization": "Bearer " + accessToken
}
};
const url = "https://api.youneedabudget.com" + Utilities.formatString(pathFormat, Array.prototype.slice.call(arguments, 2));
const response = UrlFetchApp.fetch(url, options);
return JSON.parse(response.getContentText());
}
/**
* Enriches a budget with lookup functions
*
* @param budget The budget returned from YNAB API
* @param categoryGroups The category hierarchy returned from YNAB API
*/
function _indexBudget(budget, categoryGroups) {
const allCategories = _flatMap(categoryGroups, function(g) {
return g.categories;
});
const accountIndex = _indexArray(budget.accounts);
const payeeIndex = _indexArray(budget.payees);
const categoryIndex = _indexArray(allCategories);
const categoryGroupIndex = _indexArray(categoryGroups);
budget.getAccountName = function (id) {
var account = accountIndex[id];
return account ? account.name : null;
};
budget.getPayeeName = function (id) {
var payee = payeeIndex[id];
return payee ? payee.name : null;
};
budget.getCategoryName = function (id) {
var category = categoryIndex[id];
return category ? category.name : null;
};
budget.getCategoryGroupName = function (id) {
var categoryGroup = categoryGroupIndex[id];
return categoryGroup ? categoryGroup.name : null;
};
budget.getCategoryGroupNameFromCategory = function (id) {
var category = categoryIndex[id];
if (!category) {
return null;
}
if (!category.category_group_id) {
return null;
}
var group = categoryGroupIndex[category.category_group_id];
return group ? group.name : null;
};
}
function _flatMap(array, lambda) {
return Array.prototype.concat.apply([], array.map(lambda));
}
function _indexArray(arr, key, index) {
key = key || 'id';
index = index || {};
arr.forEach(function (x) { index[x[key]] = x; });
return index;
}
function _groupBy(xs, key) {
return xs.reduce(function(rv, x) {
(rv[x[key]] = rv[x[key]] || []).push(x);
return rv;
}, {});
};
@willjobs
Copy link
Author

Updates the original to:

  • include the memo field on transactions
  • show split transactions on multiple rows by default (rather than having to pass in true)
  • sort the transactions in descending order of date
  • fix issue with dates being 1 day behind what is shown in the application
  • show the last updated date in the column 1 header

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment