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.

40replies 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

    Like 1
  • Brady

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

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

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

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

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

      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.

    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?

     

    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;
        ...
        ...
      }
      
      Like 1
    • Brady Thanks!  Success!

      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?

    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.

      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!

      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.

      Like 1
      • MTRing
      • MTRing
      • 1 yr 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);
      }
       

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

      Like
      • MTRing
      • MTRing
      • 1 yr 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....

      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.

      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.

      Update: The API now returns payee_name and category_name for subtransactions so the script has been updated to include these.

      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_name, st.category_name, st.amount]);
         });
        });
      
        return [headers].concat(transactions, subtransactions);
      }
      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, 

    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);
      }
      Like 1
      • George
      • Developer
      • george_ynab
      • 1 yr ago
      • Reported - view

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

      Like
      • George
      • Developer
      • george_ynab
      • 1 yr ago
      • Reported - view

      Oops, see what Brady said 😄

      Like
      • Gray Horse
      • Gray_Horse.5
      • 1 yr ago
      • Reported - view

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

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

      Like
      • Gray Horse
      • Gray_Horse.5
      • 1 yr 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

      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.

      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.

    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!

      Like
      • Gray Horse
      • Gray_Horse.5
      • 1 yr ago
      • Reported - view

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

      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.

      Like
      • Gray Horse
      • Gray_Horse.5
      • 1 yr 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.

      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;
      }
      
      Like 1
  • Good work Brady . I just finished configuring it. It works like charm. 

    Like 1
  • How about a script that collects scheduled transactions,  importing them to Google Calendar? (or a format that can be sed to import into any calendar)?

    Like
    • Cirrus If someone is able/interested in taking a look at that within this community, that would indeed be a cool one! We typically don't create any of the API integrations here, as our API was meant to be community based, but we're happy to provide help and feedback as we can! 

      Like
    • Cirrus I thought this was a great suggestion, so I implemented it. Here's the code. This can be implemented as a trigger so that it will run on automatically. It will create events on a Google calendar and update them if changes are made to the transaction. The one thing it does not do is delete events if the transaction is removed. You'll need to replace the values at the start of the script for your own API key and calendar ID (and budget ID, if last-used isn't appropriate for you).

      function calendar_ynab_scheduled_transactions() {
        accessToken = "YOUR_YNAB_PERSONAL_ACCESS_TOKEN"; // https://api.youneedabudget.com/
        budgetId = "last-used"; // Or the ID of a specific budget
        calendarId = "CALENDAR_ID_TO_POPULATE"; // usually in the form [email protected] - https://docs.simplecalendar.io/find-google-calendar-id/
      
        // original code from Brady on the YNAB Team
        const transactions = fetch_ynab_data(accessToken, "budgets/" + budgetId + "/scheduled_transactions").scheduled_transactions;
      
        var eventCal = CalendarApp.getCalendarById(calendarId);
      
        // map function will run once for each item in the transactions object
        // uses YNAB transaction ID as a key to prevent dupes, allow updates to
        // existing events.
        transactions.map(function (t) {
          const amount=t.amount / 1000 * -1;
          var id = t.id;
          var oneYearFromNow = new Date();
          oneYearFromNow.setFullYear(oneYearFromNow.getFullYear() + 1);
          // We'll search for matching events from today til one year from now
          var matchingEvents = eventCal.getEvents(new Date(), oneYearFromNow, {"search": id})
      
          // if there are existing events with this trans ID, update them. (in theory there should be only one at most)
          matchingEvents.map(function (m) {
            m.setAllDayDate(new Date(t.date_next))
             .setTitle(t.payee_name + " - $" + amount)
             .setDescription("YNAB scheduled transaction. Will pay $"+amount+" to "+t.payee_name+" from "+t.account_name+", scheduled for "+t.date_next+".<p>YNAB ID: "+id)
          });
          // If there are no matching events, create a new one.
          if (matchingEvents.length == 0) {
            var event = eventCal.createAllDayEvent(t.payee_name + " - $" + amount, new Date(t.date_next),
                {description: "YNAB scheduled transaction. Will pay $"+amount+" to "+t.payee_name+" from "+t.account_name+", scheduled for "+t.date_next+".<p>YNAB ID: "+id ,
                });
            event.setColor(10) // sets color to green. List: https://developers.google.com/apps-script/reference/calendar/event-color
             .removeAllReminders() // I assume one doesn't want reminders. Remove to have your default reminders applied.
             .setTag("id", id);
          }
        });
      }
      
      //Brady's original fetch_ynab_data function
      function fetch_ynab_data(accessToken, path){
        const url = "https://api.youneedabudget.com/v1/" + path;
        const options = {
          "headers": {
            "Authorization": "Bearer " + accessToken
          },
          "muteHttpExceptions": true
        };
        const response = UrlFetchApp.fetch(url, options);
        const data = JSON.parse(response.getContentText()).data;
      
        return data;
      }
      Like
Like6 Follow
  • 6 Likes
  • 1 mth agoLast active
  • 40Replies
  • 1761Views
  • 17 Following