Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 ? _replaceSubTransactions(budget.transactions, budget.subtransactions) : budget.transactions;
const columns = ["Date", "Month", "Account", "Payee", "Category Group", "Category", "Amount", 'Type'];
return [columns].concat(transactions.map(function (tx) {
return [
new Date(tx.date),
Utilities.formatDate(new Date(tx.date), "GMT", "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.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(left.date).getTime() - new Date(right.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;
}, {});
};
@RohanNagar

This comment has been minimized.

Copy link

commented Mar 26, 2018

How do you get your access token? I still don't see a Developer Settings option in My Account.

@notself

This comment has been minimized.

Copy link
Owner Author

commented Mar 27, 2018

You can request access by filling in the form linked here: https://github.com/ynab/ynab-sdk-js

@RohanNagar

This comment has been minimized.

Copy link

commented Mar 28, 2018

@notself thanks!

@eloyz

This comment has been minimized.

Copy link

commented May 24, 2018

I have my token and budget ID. How or where do I pass my accessToken and budgetId in this script? Thanks for writing this.

@tmfrescata

This comment has been minimized.

Copy link

commented Jul 20, 2018

Hello, thank you very much for your script!
Could you

  • add the Memo field and
  • add the since_date parameter when querying the transactions?

Thanks!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.