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.

Update: The API now supports using "last-used" for the {budget_id}, which will reference your last used budget.

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.

35replies 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
  • 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
  • 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
    • Brady Thank you so much for your posts on this thread. They have been extremely helpful to me.

      I would be grateful, if you could help me with one more thing.

      When I use the code to get budget categories for the current month, they are grouped by Category Group, like this:
       

      But, when I use the code you provided for getting budget categories for previous months, the categories are not grouped by Category Group. They look like this:

       I see where you've said that the data returned for previous months' budget categories is different than the data returned for the current month. Unfortunately, I'm not really able to use the information returned for previous months' budgets, when the budget categories are listed randomly and not grouped by Category Group.

      If it's not too difficult or time-consuming, could you provide additional code that would group previous months' categories by Category Group, like they are grouped for the current month?

      Thanks for considering.

      Reply Like
    • Danny DeLoach 

      This is rough, but might work for your use case.  You could retrieve the groups from the /budgets/:id/categories endpoint and then retrieve the categories for the month you are looking for.  Then you can collate the categories back to the groups using the categories' category_group_id.

      Following the thread above, try replacing your get_ynab_categories_for_month function with the following.  I tested and it should work.  But again, you might need to tweak and clean it up a bit.

      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);
      }
      Reply Like
    • Brady You are awesome!  tried the code, and it works perfectly. This will be a huge help to me. Thank you!

      Reply Like 1
  • 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
    • 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 mths 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 mths 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
    • Brady Is there a way to edit the above code so that it also returns subtransactions? I spent hours fiddling with the code and reading the support documentation. I'm trying to learn as much as I can on my own, but sometimes I just can't figure it out. Thanks so much for your help.

      Reply Like
    • Danny DeLoach 

      This might be close to what you're looking for.  This modifies the script to include sub-transactions.  Also, it adds a "Type" column to indicate "Transaction" or "SubTransaction".   The API does not return payee_name and category_name (only ids) for subtransactions so these will not be available with this script.

      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);
      }
      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
      • George
      • Developer
      • george_ynab
      • 9 mths ago
      • Reported - view

      Gray Horse You'll have to use the List Scheduled Transaction endpoint.

      Reply Like
      • George
      • Developer
      • george_ynab
      • 9 mths ago
      • Reported - view

      Oops, see what Brady said 😄

      Reply Like
      • Gray Horse
      • Gray_Horse.5
      • 9 mths 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
      • 9 mths 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
  • Brady Thank you for this excellent walk through for getting starting with the YNAB API.

    I've been able to successfully run the code you provided in your original post. However, I can't figure out how to refresh the data. I put '=get_ynab_categories({access-token}, {budget_id})' (with my token and budget ID) in a cell, and my categories appear just as you described. But when I edit categories in YNAB (e.g. add a new category or modify the name of a category), the changes do not show up in my Google Sheet data.

    I can get updated data, if I insert the formula into a new cell. But I'm trying to figure out how to make the original set of data refresh to show the changes in YNAB. I tried looking into 'triggers'. I renamed the function to '=onOpen()'. When the function first pull down the data, the data is fresh. But, if I change the data in YNAB and then close and re-open my Google Sheet, the new data still doesn't appear in my Google Sheet.

    How can I get the data to refresh?

    Thanks, again, for all your help here on the forum.

    Reply Like
    • Danny DeLoach 

      Good question!  I had to dig and play a little to figure this out but I ended up using this Stack Overflow answer to solve this.  You might want to read the question itself because it has some good info and links to how things work with Google App Scripts in regards to caching.

      If you make the following 2 changes, you should get nice little "Refresh Categories" menu item (image attached) that upon clicking, will pull the last data in from your budget:

      1. Add the following to you Code.gs script file (under Tools > Script Editor)

      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('E1').setValue(new Date().toTimeString());
      }
      

      2. In the cell where you referenced the get_ynab_categories function, add a 3rd parameter to the function call like this: =get_ynab_categories("123456abc", "last-used", $E$1) .   Save your changes, and reload the sheet.

      Note: the above approach assumes the E1 cell is not in use and it will update it with a timestamp each time the Refresh menu item is used.

      Now, make a change on your YNAB budget and then click the YNAB>Refresh Categories menu item and your changes should show up!

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

      Brady Your code works like a charm. Is there a way to do the automatic refresh?

      Reply Like
    • Gray Horse I haven't experimented with it but there are Time-driven triggers that you could probably use to accomplish this: https://developers.google.com/apps-script/guides/triggers/installable.

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

      Brady How do we set muteHttpExceptions: true in fetch_ynab_data function? I think that is going to resolved my triggering errors. Thanks in advance.

      Reply Like
    • Gray Horse - See the "HERE" comment below..you can pass that in as an option.

       

      function fetch_ynab_data(accessToken, path){
        const url = "https://api.youneedabudget.com/v1/" + path;
        const options = {
          "headers": {
            "Authorization": "Bearer " + accessToken
          },
          // HERE
          muteHttpExceptions: true
        };
        const response = UrlFetchApp.fetch(url, options);
        const data = JSON.parse(response.getContentText()).data;
        return data;
      }
      
      Reply Like 1
Like4 Follow
  • 4 Likes
  • 1 mth agoLast active
  • 35Replies
  • 1261Views
  • 11 Following