Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Credit to Brady from the YNAB team. Assembled from here: https://support.youneedabudget.com/t/k9rxc9/using-google-apps-script-with-the-api
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Refresh Categories",
functionName : "refresh_ynab_categories"
}];
sheet.addMenu("YNAB", entries);
};
function refresh_ynab_categories() {
SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());
}
function get_ynab_categories(accessToken, budgetId) {
const groups = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/categories").category_groups;
const columns = ["Name", "Budget", "Activity", "Balance"];
const rows = [];
for (var group_idx = 0; group_idx < groups.length; group_idx++) {
// Add the group
var group = groups[group_idx];
// Skip internal and hidden categories
if (['Internal Master Category', 'Hidden Categories'].indexOf(group.name) >= 0) continue;
rows.push([group.name]);
// Add the categories
for (var category_idx = 0; category_idx < group.categories.length; category_idx++) {
var category = group.categories[category_idx];
var name = " " + category.name; // Indent categories a bit so they are offset from groups
// Calculate currency amounts from mulliunits
var budgeted = category.budgeted / 1000.0;
var activity = category.activity / 1000.0;
var balance = category.balance / 1000.0;
rows.push([name, budgeted, activity, balance]);
}
}
return [columns].concat(rows);
}
function get_ynab_accounts(accessToken, budgetId) {
const transactions = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/accounts").accounts;
const headers = ["Name", "Type", "Budget", "Closed", "Balance"];
const rows = transactions.map(function(t){
const balance = t.balance / 1000;
return [t.name, t.type, t.on_budget, t.closed, balance]
});
return [headers].concat(rows);
}
function get_ynab_categories_for_month(accessToken, budgetId, month) {
const groups = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/categories").category_groups;
const categories = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/months/" + month).month.categories;
// Organize groups by id on groups_by_id
const groups_by_id = [];
for (var group_idx = 0; group_idx < groups.length; group_idx++) {
var group = groups[group_idx];
// Clear categories array since we'll replace with categories from a particular month
group.categories = [];
groups_by_id[group.id] = group;
}
// Collate categories back to their group
for (var category_idx = 0; category_idx < categories.length; category_idx++) {
var category = categories[category_idx];
if (groups_by_id[category.category_group_id]){
groups_by_id[category.category_group_id].categories.push(category);
}
}
const columns = ["Name", "Budgeted", "Activity", "Balance"];
const rows = [];
for (var group_idx = 0; group_idx < groups.length; group_idx++) {
// Add the group
var group = groups[group_idx];
// Skip internal and hidden categories
if (['Internal Master Category', 'Hidden Categories'].indexOf(group.name) >= 0) continue;
rows.push([group.name]);
// Add the categories
for (var category_idx = 0; category_idx < group.categories.length; category_idx++) {
var category = group.categories[category_idx];
var name = " " + category.name; // Indent categories a bit so they are offset from groups
// Calculate currency amounts from mulliunits
var budgeted = category.budgeted / 1000.0;
var activity = category.activity / 1000.0;
var balance = category.balance / 1000.0;
rows.push([name, budgeted, activity, balance]);
}
}
return [columns].concat(rows);
}
function get_ynab_transactions(accessToken, budgetId) {
const transactions = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/transactions").transactions;
const headers = ["ID","Account Name", "Date", "Payee", "Category", "Amount"];
const rows = transactions.map(function(t){
const amount = t.amount / 1000;
return [t.id,t.account_name, t.date, t.payee_name, t.category_name, amount]
});
return [headers].concat(rows);
}
function get_ynab_transactions_month(accessToken, budgetId, month) {
const data = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/transactions?since_date="+ month).transactions;
const headers = ["Type", "Account Name", "Date", "Payee", "Category", "Amount"];
const transactions = [];
const subtransactions = [];
data.forEach(function(t){
const amount = t.amount / 1000;
transactions.push(["Transaction", t.account_name, t.date, t.payee_name, t.category_name, amount]);
t.subtransactions.forEach(function(st){
subtransactions.push(["SubTransaction", t.account_name, t.date, st.payee_id, st.category_id, st.amount]);
});
});
return [headers].concat(transactions, subtransactions);
}
function get_ynab_scheduled_transactions(accessToken, budgetId) {
const transactions = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/scheduled_transactions").scheduled_transactions;
const headers = ["Account Name", "Date","Frequency", "Payee", "Category", "Amount"];
const rows = transactions.map(function(t){
const amount = t.amount / 1000;
return [t.account_name, t.date_next,t.frequency, t.payee_name, t.category_name, amount]
});
return [headers].concat(rows);
}
function fetch_ynab_data(accessToken, path){
const url = "https://api.youneedabudget.com/v1/" + path;
const options = {
"headers": {
"Authorization": "Bearer " + accessToken
}
};
const response = UrlFetchApp.fetch(url, options);
const data = JSON.parse(response.getContentText()).data;
return data;
}
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.