Using Google Apps Script with the API

Google Apps Script is a JavaScript platform that lets you create custom functionality on Google apps (Calendar, Docs, Sheets, Slides, etc.).  You can do some really powerful things with it.

I'm going to walk through how to use the platform to fetch your categories from the YNAB API and display them in Google Sheets.  Once it is up and running, you will have a Sheet that looks something like this:

 

First off, go to Google Drive and create a new "Google Sheets" document.

 

Then, from the Tools menu, click "Script Editor":

 

You'll be dropped into a screen where you are able to edit the contents of a script file called  Code.gs .  We are going to replace the contents of this file with a couple of functions that do the heavy lifting to pull the YNAB categories through the API.

Now, copy/paste the following code into the Code.gs file in the script editor.

function get_ynab_categories(accessToken, budgetId) {
  const groups = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/categories").category_groups;

  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 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;
}

Once you do this, your script editor should look something like this:

 

Now save the script (File > Save) and then go back to your Google Sheet, which is probably on another browser tab.

Next, you'll want to make sure you have a Personal Access Token and the ID of your budget.  If you don't already have one, follow the instructions here to obtain a Personal Access Token.  If you don't know your budget ID already, an easy way to get it is to visit your budget in the YNAB web application and grab it from the URL.  The format of the URL when you first open a budget is:

https://app.youneedabudget.com/{budget_id}/budget/{month}

Grab that {budget_id} for use in just a minute.

Back in your Google Sheet, all we need to do it call the get_ynab_categories function we created in the script editor and pass in an access token and the budget ID as parameters.

In cell A:1, enter the following (replacing {access-token} and {budget_id} with your values) :

=get_ynab_categories({access-token}, {budget_id})

It should look similar to this:

Once you press Enter, you should see your categories displayed in all their glory.

This is a very simple example and there is so much more you could do, such as creating charts, scheduling a trigger to automatically update the data, and pulling data from other sources.

Have fun and let us know what you build.

24replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Another user wrote a script for this a while back. Might be useful, too.

    https://gist.github.com/notself/5a388ad04e3ac95c144b99d007b191df

    Reply Like 1
    • rsrmjs
    • rsrmjs
    • 6 mths ago
    • Reported - view

    Brady

    This script returns the current month, how do you get a different months categories?

    Reply Like
  • rsrmjs - The  /budgets/{budget_id}/months/{month}  endpoint can give data for prior months.

    Reply Like
    • rsrmjs
    • rsrmjs
    • 6 mths ago
    • Reported - view

    Brady

    I tried that and it did not work.

    In cell A1 =get_ynab_categories({access-token}, {budget_id},/months/{2018-07-01})

    I could be doing something totally stupid though ...

    Reply Like
  • rsrmjs

    The  /budgets/{budget_id}/months/{month}  endpoint does not return categories wrapped by the parent groups.  It just returns a flat list of categories.  So, the script would need to be updated a bit.  Here is an additional function that you could drop in to the Script Editor and then call it like this: =get_ynab_categories_for_month({access-token}, {budget_id},  "2018-07-01")

     

    function get_ynab_categories_for_month(accessToken, budgetId, month) {
      const budget_month = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/months/" + month).month;
    
      const columns = ["Name", "Budgeted", "Activity", "Balance"];
      const rows = [];
    
      // Add the categories
      for (var category_idx = 0; category_idx < budget_month.categories.length; category_idx++) {
        var category = budget_month.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);
    }
    Reply Like
    • rsrmjs
    • rsrmjs
    • 6 mths ago
    • Reported - view
    Brady said:
    "budgets/" + budgetId + "/months/" + month).month;

     I was close - I missed the /months/ when I tried to change the script.

    Thank you for the help.

    Reply Like
  • Hi - when I try to save my google script file after adding the accessToken and budgetId, I receive a missing formal parameter error.  see picture.  Am I missing a step?

     

    Reply Like
    • Green Falafel It looks like you are trying to hardcode the accessToken and budgetId values in there.  For it to be valid JavaScript, you'll want to just remove the parameters from the function declaration and then declare the variables with the hardcoded values:

      function get_ynab_categories() {
        accessToken = "12345";
        budgetId = "67890";
      
        const groups = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/categories").category_groups;
        ...
        ...
      }
      
      Reply Like 1
    • Brady Thanks!  Success!

      Reply Like
  • Thanks Brady , script is running perfectly!

     

    I was wondering if it’s also possible to re-create in Google Sheets the income vs expense reporting that YNAB has built in?

    Reply Like
    • Navy Blue Cobra Yes, the API provides all the data you would need to rebuild this report in Google Sheets.  It will take a little work but should be fairly straightforward!

      One way do do this would be to use the /budgets/{id}/transactions endpoint to get the list of transactions and then process them.  Using the example script here, you could call fetch_ynab_data like this:

       fetch_ynab_data(accessToken, "budgets/" + budgetId + "/transactions").transactions;

      You'd obviously have to update the script as a whole, to process the data correctly, however.

      Something else you could do is use the income and activity amounts returned by the /budgets/{id}/months endpoint.  This would be more simplistic but might be a good place to start.

      Reply Like
      • Tom Sharky
      • tomsharky
      • 13 days ago
      • Reported - view

      Brady  Thanks a lot for this script. It opens unlimited analysis possibilities in Google Sheets. However, I'm not able to adjust the script to call the list of all the transactions. I admit, I'm no developer, so I have no idea how to make it done :) Would really appreciate your help.

      Thanks a lot!

      Reply Like
    • Tom Sharky Sure thing.

      Edit that Code.gs file as described above and add the following function:

      function get_ynab_transactions(accessToken, budgetId) {
        const transactions = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/transactions").transactions;
      
        const headers = ["Account Name", "Date", "Payee", "Category", "Amount"];
        const rows = transactions.map(function(t){
         const amount = t.amount / 1000;
         return [t.account_name, t.date, t.payee_name, t.category_name, amount]
        });
      
        return [headers].concat(rows);
      }

      Save it and then enter =get_ynab_transactions("123", "last-used")  ('123' being your personal access token) into a cell and it should pull back all your transactions.  

      Good luck.

      Reply Like 1
      • MTRing
      • MTRing
      • 9 days ago
      • Reported - view

      Brady  Is there a way to filter the get transactions script by month?  I tried the script below and it didn't work


      function get_ynab_transactions_month(accessToken, budgetId, month) {
        const transactions = fetch_ynab_data(accessToken, "budgets/" + budgetId +  "/transactions?since_date="+ month).transactions;

        const headers = ["Account Name", "Date", "Payee", "Category", "Amount"];
        const rows = transactions.map(function(t){
         const amount = t.amount / 1000;
         return [t.account_name, t.date, t.payee_name, t.category_name, amount]
        });

        return [headers].concat(rows);
      }
       

      Reply Like
    • MTRing - What format are you passing in for month?  It should be ISO  and a full date like '2018-11-01'.

      Reply Like
      • MTRing
      • MTRing
      • 9 days ago
      • Reported - view

      Brady That did it I guess.  Thanks.

      Now I have to figure out how to group the results by category not date....

      Reply Like
  • I must admit that I am very new to API. And also not sure if this is the right thread to post my question. I tried pulling data to Google sheet and it works like a charm. However, I am in a situation where I want to pull only the "scheduled transactions" of current month. I am sure there must be some code to do that. Thanks for your help.

    Cheers, 

    Reply Like
    • Gray Horse 

      To get scheduled transactions, you get need to pull from the /scheduled_transactions endpoint.  Here is a function you can add to the above instructions to do just that.  Save it and then enter =get_ynab_scheduled_transactions("123", "last-used")   ('123' being your personal access token).

      function get_ynab_scheduled_transactions(accessToken, budgetId) {
        const transactions = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/scheduled_transactions").scheduled_transactions;
      
        const headers = ["Account Name", "Date", "Payee", "Category", "Amount"];
        const rows = transactions.map(function(t){
         const amount = t.amount / 1000;
         return [t.account_name, t.date, t.payee_name, t.category_name, amount]
        });
      
        return [headers].concat(rows);
      }
      Reply Like 1
    • Gray Horse You'll have to use the List Scheduled Transaction endpoint.

      Reply Like
    • Oops, see what Brady said 😄

      Reply Like
      • Gray Horse
      • Gray_Horse.5
      • 7 days ago
      • Reported - view

      Brady  Thanks. I tried but it throws this error "TypeError: Cannot call method "map" of undefined"

      Reply Like
    • Gray Horse I just updated it - please give it another try.

      Reply Like
      • Gray Horse
      • Gray_Horse.5
      • 7 days ago
      • Reported - view

      Brady  Neat! Will the dates not appear for scheduled transactions? I will have to figure out to pick the current month scheduled transaction. Thanks for your help, Brady

      Reply Like
    • Gray Horse In the spec, there is date_first and date_next available. So you could modify to something like:

      return [t.account_name, t.date_next, t.payee_name, t.category_name, amount]

      to get it to show the next scheduled date.

      Reply Like 1
Like2 Follow
  • 2 Likes
  • 7 days agoLast active
  • 24Replies
  • 908Views
  • 10 Following