ynab-google-sheets.js

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;
  }, {});
};

@notself thanks!

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

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!!

@notself, I assembled a number of functions a YNAB developer published in their forum here: https://gist.github.com/rmclellan/36de481c41629c8048f4b6f6de67b5d7 .

May be useful if you're looking to augment your script at all.

So. . . I'm trying to use the YNABTransactions function  in the code above to import a full list of transactions from YNAB to Google Sheets. The subroutine _replaceSubTransactions does not seem to be working properly. I'm not sure what's missing. 

What I want
I'm trying to use the function that  will import a complete list of transactions with all sub-transactions included.
Below is an example of what it SHOULD look like. (Please note that I've just added the trans# so that you can see how the transactions line up).

What's actually happening?
The subroutine to include up the sub-transactions does not seem to be working properly. Here is the same set of data with what the function actually generates.

 Any thoughts on how to fix it?

Hello! I am trying to use this and I get, in cell A1, "loading..." and if I hover over the formula I see "unknown function". What am I missing?